Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Oracle user type : Nested table in SP out param.

  1. #1
    Join Date
    Jul 2007
    Posts
    3

    Default Oracle user type : Nested table in SP out param.

    I'm using Spoon 2.5 and I want to get data from an oracle database (9.2.0.6) using stored procedures.

    Eg: Package/SP source :
    Code:
    drop type PO_TEST_ARRAY;
    
    create or replace type PO_TEST_OBJECT as OBJECT(
        STRIN    VARCHAR2(32),
        THEDAY    VARCHAR2(32) );
    /
    create or replace type PO_TEST_ARRAY as table of PO_TEST_OBJECT;
    /
    
    --
    
    CREATE OR REPLACE PACKAGE PO_PKG AS
    
    --
    PROCEDURE PO_SP_TEST( strIn IN CHAR, arrayOut OUT PO_TEST_ARRAY );
    
    END;
    /
    
    CREATE OR REPLACE PACKAGE BODY PO_PKG AS
    
    PROCEDURE PO_SP_TEST
    (
        strIn IN CHAR,
        arrayOut OUT PO_TEST_ARRAY
    )
    IS
    tmp_today VARCHAR2(32);
    --
    BEGIN
    --
    select sysdate into tmp_today from dual;
    arrayOut := PO_TEST_ARRAY();
    arrayOut.EXTEND;
    arrayOut(1) := PO_TEST_OBJECT(NULL, NULL);
    arrayOut(1).STRIN := strIn;
    arrayOut(1).THEDAY := tmp_today;
    --
    END PO_SP_TEST;
    --
    
    END;
    /
    I'd like to know how to declare the PO_TEST_ARRAY type as output param.

    Thanks in advance
    PJ.
    Attached Images Attached Images  

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Not... not in the current version anyway. Only the basic types are supported.

    Regards,
    Sven

  3. #3
    Join Date
    Jul 2007
    Posts
    3

    Default

    Thanks Sven, u think it will be implemented soon ?

    PJ

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    Not by me... but if you want to you can raise a CSR for it. Problem is that such things are pretty database specific, so I don't know whether it would really make sense.

    The current trick is usually to give a cursor back on the array, e.g. in a wrapper function in Oracle.

    Regards,
    Sven

  5. #5
    Join Date
    Jul 2007
    Posts
    3

    Default

    Thanks for your help,
    I have already coded this kind of layer for a c# app but it is really heavy to make evolve.

    So I will surely code my own java class for this stuff

    Regards,
    PJ.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.