Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    107

    Unanswered: STRUCTURED data type in Procedures????

    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

  2. #2
    Join Date
    Jun 2004
    Posts
    57

    Use temporary table

    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.

  3. #3
    Join Date
    Feb 2004
    Posts
    107
    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

  4. #4
    Join Date
    Sep 2003
    Posts
    24
    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 ..

  5. #5
    Join Date
    Jan 2004
    Posts
    49

  6. #6
    Join Date
    Sep 2003
    Posts
    24
    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.

  7. #7
    Join Date
    Jan 2004
    Posts
    49
    can u try to use as per the following link

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

    With Regards

    Bala

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •