friends,

in oracle, one can define a RECORD type which is collection of attributes of built-in data type and it can be used as the data type of a parameter to a procedure...
i'm trying to find out the equivalent in DB2... i tried to create a user-defined data type and used it as the data type of a parameter in the procedure...
DB2 throws an error saying (DB2 UDB v8.1.3 windows 2000)
------------------------------------------------------------------------
SQL0789N The data type for the parameter or variable "TESTTYPE" is not supported in the SQL routine

<b>The DB2 docs says:</b>
SQL0789N The data type for parameter or variable "<name>" is not supported in the SQL routine.

Explanation: The SQL routine (function, method, or procedure) does not support variables or parameters of DATALINK, REFERENCE, DISTINCT, STRUCTURED, or LOB data types.

User Response: Do not use SQL variables or parameters of DATALINK, REFERENCE, DISTINCT, STRUCTURED, or LOB data types in the SQL routine definition. Specify a different data type for the parameter or variable "<name>".

sqlcode: -789

sqlstate: 429BB
------------------------------------------------------------------------

where am i going wrong? can anyone help me to find out the equivalent?

my procedure and type are as follows:
------------------------------------------------------------------------
CREATE TYPE TESTTYPE
AS
(
ATTR1 VARCHAR(20),
ATTR2 INT,
ATTR3 CHAR(1)
)MODE DB2SQL
@

CREATE PROCEDURE TESTTYPEPROC(PARAM1 TESTTYPE)
LANGUAGE SQL
BEGIN
DECLARE DUMMY VARCHAR(10);
SET DUMMY = 'TEST';
END
@
------------------------------------------------------------------------

Jake

I believe you can use a temporary table instead of that record.
Declare the temporary table, fill it with data and then return the data in the temporary table.

you r correct in one sense.... temp. table holds data across procedure executions without passing it as parameter... but it also holds the data across the executions (at the application level) in the same session.... which is wrong... hence temp. table can not used in this case... pls let me know if you need more info...

Jake

O my God, I am also trying what you are doing now, and I have the same problem:
DB2ADMIN.GET_PERSONS: 1: [IBM][CLI Driver][DB2/NT] SQL0789N The data type for parameter or variable "RECS" is not supported in the SQL routine. LINE NUMBER=1. SQLSTATE=429BB

My defined type is:
CREATE TYPE PERSONT AS (id INTEGER, name VARCHAR(40)) MODE DB2SQL@

Really hope someone can help us ..

Can u check the following two links?

http://publib.boulder.ibm.com/infoce...d/t0006634.htm

http://publib.boulder.ibm.com/infoce...d/c0006441.htm

With Regards

Bala

Thanks Bala.

I read your links.

Still the problem is that I can not use the "user defined structured type" as a Type in the parameter of Stored Procedure.

can u try to use as per the following link

http://www-128.ibm.com/developerwork...dm-0506melnyk/

With Regards

Bala