Access Oracle function in Dataiku

pnaik1 · June 2021

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

pnaik1 · June 2021

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.

Andrey · June 2021

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 · June 2021

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 · June 2021

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

pnaik1 · June 2021

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

Access Oracle function in Dataiku

Best Answer

Answers

Categories

Setup Info

Tags