Access Oracle function in Dataiku

pnaik1
pnaik1 Registered Posts: 23 ✭✭✭✭

Is it possible to access an Oracle function returning REF CURSOR in dataiku.

I tried both ways

1) Creating Oracle dataset with SQl query option by running following queries:

a) Execute dataIkuExtract$b.func_name(‘20210221’); b) SELECT * FROM table_name;

Query a) gives a syntax error which seems correct to me.

2) Access oracle function by first making database connection using "cx_Oracle.connect" but this is also throwing an error.

Just want to make sure whether it is possible at all? If yes, can you please help me, how to access it?

Thanks!!!

Best Answer

  • pnaik1
    pnaik1 Registered Posts: 23 ✭✭✭✭
    edited July 17 Answer ✓

    Hi @Andrey
    thanks a lot for your help. I found the solution. So SQL recipe (SQL script) will do the trick with following script:

    declare variable_name datatype;
    begin
         variable_name :=
         schema_name.package_name.function_name(function_argument);
    end;
    -- DKU_END_STATEMENT

    Also, from "Advanced" tab we have to set parameter "Multiple statements parsing" as "Submit as a single statement".

    Thanks.

Answers

  • Andrey
    Andrey Dataiker Alumni Posts: 119 ✭✭✭✭✭✭✭
    edited July 17

    Hi @pnaik1
    ,

    Based on my previous answer to a similar question here:

    https://community.dataiku.com/t5/General-Discussion/Viewing-Oracle-s-DBMS-OUTPUT-in-Dataiku/m-p/12503/highlight/true#M601

    Here's an example of what you can do:

    create or replace function select_employee (empid number)
        return sys_refcursor
        is
        rf_cur   sys_refcursor;
    begin
        open rf_cur for
            select *
            from MY_TABLE
            where "id" = empid;
    
        return rf_cur;
    end select_employee;
    
    
    -- DKU_END_STATEMENT
    
    select extractvalue(column_value,'/ROW/first_name') first_name from table(xmlsequence(select_employee('10001')))

  • pnaik1
    pnaik1 Registered Posts: 23 ✭✭✭✭
    edited July 17

    Thanks a lot for quick response @Andrey

    So I already have following created procedure like:

    CREATE OR REPLACE PROCEDURE FILLTABLE (SHIPMENTDATE IN VARCHAR2)
    IS 
    l_cursor SYS_REFCURSOR;
    TRAILER_ID VARCHAR2(100);
    WAREHOUSE_CODE VARCHAR2(100);
    ACTUAL_DATE DATE;
    OK NUMERIC;
    BEGIN
    l_cursor := EUROWMS.DATAIKUEXTRACT$B.GETTRAILERSSHIPPED (TO_DATE(SHIPMENTDATE, 'YYYY-MM-DD'));
    LOOP 
        FETCH l_cursor
        INTO  TRAILER_ID , WAREHOUSE_CODE, ACTUAL_DATE;
        EXIT WHEN l_cursor%NOTFOUND;
            OK := EUROWMS.DATAIKUEXTRACT$B.GETSHIPMENTDATA(TRAILER_ID,WAREHOUSE_CODE );
      END LOOP;
      CLOSE l_cursor;
    END;

    Like you said, when I am trying to execute following SQL command:

    select * from table(xmlsequence(dataIkuExtract$b.fillTable('20210221')))

    the I am getting following error "ORA-00904: "DATAIKUEXTRACT$B"."FILLTABLE": invalid identifier"

    I am new to Dataiku and also I don't have access to Oracle where all these functions are written, please let me know if I am doing something wrong.

    Thanks!!!

  • Andrey
    Andrey Dataiker Alumni Posts: 119 ✭✭✭✭✭✭✭

    are you able to run the given SQL query outside of DSS? It seems that there might be some Oracle package visibility issues.

  • pnaik1
    pnaik1 Registered Posts: 23 ✭✭✭✭
    edited July 17

    I connected to oracle database through command line using

    conn user/pass @host:port/service

    I got the same error but when I tried following query:

    declare
       result number;
    begin
       result := EUROWMS.dataIkuExtract$b.fillTable('20210221');
    end;

    I could see the message "PL/SQL procedure successfully completed". So I don't think it's package visibility issue.

    However, when I tried to run same query on dataiku its giving me error "ORA-06550: line 1, column 21: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: := . ( @ % ; not null range default character"

    and if I modify my query to

    select * from table(xmlsequence(EUROWMS.dataIkuExtract$b.fillTable('20210221')))

    its showing new error "ORA-29900: operator binding does not exist ORA-06553: PLS-306: wrong number or types of arguments in call to 'XMLSEQUENCE'"

    Thanks!!!

Setup Info
    Tags
      Help me…