Re: insert abstract datatype in procedure

From: Gerard Averill <gaverill_at_chsra.wisc.eduNOSPAM>
Date: 22 Mar 2001 18:12:07 GMT
Message-ID: <906C708DAgaverill@144.92.88.10>

no_at_damn.spam wrote in <998qs8$9os$1_at_news.netmar.com>:

>
>
>i have a table in which a column is an abstract datatype, like this:
>
>CREATE TYPE FULLNAME_TY AS OBJECT
>(
>LastName VARCHAR2(50),
>FirstName VARCHAR2(25),
>MiddleName VARCHAR2(25)
>);
>
>CREATE TABLE CLIENTDATA
>(
>ID NUMBER(10),
>FullName FULLNAME_TY,
>JoinDate DATE
>);
>
>now i can insert to this table via a perl script using DBI like this:
>
>insert into CLIENTDATA
>(ID, FullName, JoinDate)
>values
>(?, FULLNAME_TY(?,?,?), to_date(?,'mm/dd/yyyy'));
>
>the problem is that i want to create a procedure to do the insert (so
>that it can be called from any of several scripts), and the procedure
>doesn't seem to recognize the abstract datatype:
>
>CREATE OR REPLACE PROCEDURE NewClient
>(
>v_ID IN NUMBER,
>v_LastName IN VARCHAR2,
>v_FirstName IN VARCHAR2,
>v_MiddleName IN VARCHAR2,
>v_JoinDate IN DATE
>)
>IS
>BEGIN
>insert into CLIENTDATA
>(
>ID,
>FullName,
>JoinDate
>) values (
>v_ID,
>v_LastName,
>v_FirstName,
>v_MiddleName,
>to_date(v_JoinDate,'DD-MON-YYYY')
>);
>
>i get the error:
>"PLS-00382: expression is of wrong type"
>
>does anyone know how to have a procedure insert to a table with
>datatypes which are objects, as in this example?
>
>

In the non-procedural INSERT you used the FULLNAME_TY contructor, why not in the procedural version? Try:

CREATE OR REPLACE PROCEDURE NewClient
(

v_ID         IN NUMBER,
v_LastName   IN VARCHAR2,
v_FirstName  IN VARCHAR2,
v_MiddleName IN VARCHAR2,
v_JoinDate   IN DATE

)
IS
BEGIN
insert into CLIENTDATA
(
ID,
FullName,
JoinDate
) values (
v_ID,
FULLNAME_TY(v_LastName, v_FirstName, v_MiddleName), to_date(v_JoinDate,'DD-MON-YYYY')
);
END; HTH,
g

-- 
Gerard Averill
gaverill<at>chsra<dot>wisc<dot>edu

Received on Thu Mar 22 2001 - 12:12:07 CST