3 Replies Latest reply: Apr 23, 2015 3:35 PM by Massimo Grossi RSS

    Oracle procedure with refcursor

    Ariel Insua

      Hi all,

       

      I need execute an Oracle SP with refcursor as output, something like this:

       

      SCHEMA.SP_NAME (Par1, Par2, CURSOR_OUTPUT);

       

      I've tried all of these:

       

      LOAD *;

       

      1. SQL CALL SCHEMA.SP_NAME ('$(var1)', '$(var2)', '$(var3)');
      2. SQL CALL SCHEMA.SP_NAME ('$(var1)', '$(var2)');
      3. select SCHEMA.SQL CALL SP_NAME('$(var1)', '$(var2)') as output from SCHEMA;
      4. SQL {? = CALL SCHEMA.SP_NAME('$(var1)', '$(var2)', '$(var3)') } ;
      5. SQL select SCHEMA.SP_NAME('$(var1)', '$(var2)', '$(var3)') from dual;

       

      And anything works, any idea?

       

      Thanks in advance.

       

      Ariel

        • Re: Oracle procedure with refcursor
          Gysbert Wassenaar

          Qlikview can't do anything with a cursor reference. I think you'll have to wrap that SP in another SP that runs through the cursor and returns a set of records instead of a cursor.

          • Re: Oracle procedure with refcursor
            Normélio Junior

            Hello Friend!

             

            On the operation of the cursor I do not know if it will work as our friend quoted. Now about calling procedures from within QlikView, if you have not achieved, you can try the way that I will describe below.

             

            Step 1:

            Set your script editor and it will open the databases in read and write mode.

            Configuração do Script.jpg

            Step 2:

            Correctly write your connection string, it is ODBC or OLEDB, add, before or after the password the following content

            "Mode is write"

            String Conexão ODBC.jpg

            Step 3:

            Write correctly the call of Procedure that you want to call, in my example will look like this ...

            "SQL call user_bi.prc_custo_padrão_real('4.2014','007');"

            Chamada da Procedure.jpg

            I think this should work, I have helped.

             

            Hug!

              • Re: Oracle procedure with refcursor
                Massimo Grossi

                this is an old post, anyway, with an ODBC connection this works in my environment

                 

                //

                // QlikView

                //

                set vTable='%E%';

                Table:

                LOAD *;

                SQL {? = CALL pkg1.f_get_tables2('$(vTable)') } ;

                 

                //

                // Oracle

                //

                CREATE OR REPLACE PACKAGE TEST.pkg1 AS

                    TYPE t_cursor IS REF CURSOR;

                    function f_get_tables2 (p_table varchar2) return t_cursor;

                END pkg1;

                /

                 

                CREATE OR REPLACE PACKAGE BODY TEST.pkg1 AS

                function f_get_tables2 (p_table varchar2) return t_cursor

                AS

                  c1 t_cursor;

                BEGIN

                    OPEN C1 FOR

                        SELECT TABLE_NAME FROM user_tables where table_name like p_table;

                    RETURN C1;

                END;

                   END pkg1;

                /