insert abstract datatype in procedure

From: <no_at_damn.spam>
Date: 20 Mar 2001 23:59:04 GMT
Message-ID: <998qs8$9os$1@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?

Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net

Received on Tue Mar 20 2001 - 17:59:04 CST