Access Oracle function in Dataiku

Solved!
pnaik1
Level 3
Access Oracle function in Dataiku

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!!!

0 Kudos
1 Solution
pnaik1
Level 3
Author

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.

View solution in original post

0 Kudos
5 Replies
Andrey
Dataiker Alumni

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/1250...

 

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')))

 

Andrey Avtomonov
R&D Engineer @ Dataiku
0 Kudos
pnaik1
Level 3
Author

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!!!

 

0 Kudos
Andrey
Dataiker Alumni

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

Andrey Avtomonov
R&D Engineer @ Dataiku
0 Kudos
pnaik1
Level 3
Author

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!!!

0 Kudos
pnaik1
Level 3
Author

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.

0 Kudos