Home » SQL & PL/SQL » SQL & PL/SQL » Pass a "Cursor XXXX is" statement to another procedure (Oracle, 12.2, Unix)
Pass a "Cursor XXXX is" statement to another procedure [message #685723] Mon, 14 March 2022 17:02 Go to next message
Duane
Messages: 476
Registered: December 2002
Senior Member
Not sure if this is possible or not.

What I want to do is define a "Cursor XXXX is" in one procedure and then pass that cursor statement to another procedure. I'm trying to come up with a way to write any SQL statement that will be passed to another procedure that writes out the columns to a CSV file.

I believe this can be done with a statement like "sql_statement := select * from all_directories" but that's not what I want.

I want to be able to write a "Cursor XXXX is" statement and use that in a procedure as a normal cursor and also pass that "Cursor Statement" to a CSV procedure. So, any "Cursor XXXX is" statement could be passed to a CSV procedure.

Like this:


CREATE OR REPLACE package body Test as

procedure Test1 is
               
  
  cursor OracleDirectories is
    select *
      from all_directories;
      
      
    
  begin
    OutputToCSV (OracleDirectories);

  
  end;

procedure OutputToCSV (SelectStatement in sys_refcursor) is
               
    
  begin
    Use DBMS_SQL statement to write the columns of SelectStatement to a CSV file.

  
  end;


end Test;

Re: Pass a "Cursor XXXX is" statement to another procedure [message #685731 is a reply to message #685723] Tue, 15 March 2022 09:03 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3152
Registered: January 2010
Location: Connecticut, USA
Senior Member
PL/SQL CURSOR statement defines static cursor. You need cursor variable. Anyway, here is a simplified example. Also, I'll let you do UTL_FILE part to write results to CSV file.

CREATE OR REPLACE
  PACKAGE TEST
    IS
      PROCEDURE TEST1;
      PROCEDURE OUTPUT_TO_CSV(
                              P_REFCUR IN OUT SYS_REFCURSOR
                             );
END TEST;
/
CREATE OR REPLACE
  PACKAGE BODY TEST
    IS
      PROCEDURE OUTPUT_TO_CSV(
                              P_REFCUR IN OUT SYS_REFCURSOR
                             )
        IS
            V_DBMS_SQL_CUR NUMBER;
            V_COL_CNT      NUMBER;
            V_DESCTAB      DBMS_SQL.DESC_TAB;
            V_COLS         SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
        BEGIN
            V_DBMS_SQL_CUR := DBMS_SQL.TO_CURSOR_NUMBER(P_REFCUR);
            DBMS_SQL.DESCRIBE_COLUMNS(
                                      V_DBMS_SQL_CUR,
                                      V_COL_CNT,
                                      V_DESCTAB
                                     );
            V_COLS.EXTEND(V_COL_CNT);
            FOR V_I IN 1..V_COL_CNT LOOP
              DBMS_SQL.DEFINE_COLUMN(
                                     V_DBMS_SQL_CUR,
                                     V_I,
                                     V_COLS(V_I),
                                     4000
                                    );
            END LOOP;
            WHILE DBMS_SQL.FETCH_ROWS(V_DBMS_SQL_CUR) > 0 LOOP  
              FOR V_I IN 1..V_COL_CNT LOOP
                DBMS_SQL.COLUMN_VALUE(
                                      V_DBMS_SQL_CUR,
                                      V_I,
                                      V_COLS(V_I)
                                     );
                DBMS_OUTPUT.PUT_LINE(V_DESCTAB(V_I).COL_NAME || ' = ' || V_COLS(V_I));
             END LOOP;
           END LOOP;
           DBMS_SQL.CLOSE_CURSOR(V_DBMS_SQL_CUR);
      END;
      PROCEDURE TEST1
        IS
            V_REFCUR SYS_REFCURSOR;
        BEGIN
            OPEN V_REFCUR
              FOR SELECT  *
                    FROM  ALL_DIRECTORIES;
            OUTPUT_TO_CSV(
                          V_REFCUR
                         );
  END;
END;
/
SET SERVEROUTPUT ON
EXEC TEST.TEST1
OWNER = SYS
DIRECTORY_NAME = I_DRIVE
DIRECTORY_PATH = i:\
ORIGIN_CON_ID = 3
OWNER = SYS
DIRECTORY_NAME = TEMP
DIRECTORY_PATH = c:\temp
ORIGIN_CON_ID = 3

PL/SQL procedure successfully completed.

SQL>
SY.
Re: Pass a "Cursor XXXX is" statement to another procedure [message #685743 is a reply to message #685731] Tue, 15 March 2022 17:41 Go to previous messageGo to next message
Duane
Messages: 476
Registered: December 2002
Senior Member
Thank you. Very nice solution.

I'm assuming there is no way to do this with a Static Cursor? That's really what I'm after.
Re: Pass a "Cursor XXXX is" statement to another procedure [message #685744 is a reply to message #685743] Tue, 15 March 2022 17:56 Go to previous message
Solomon Yakobson
Messages: 3152
Registered: January 2010
Location: Connecticut, USA
Senior Member
Nop, not possible. We can't "describe" static cursor, so we wouldn't know how to fetch.

SY.
Previous Topic: Oracle calculate hours between timestamps
Next Topic: Select Query suggestion
Goto Forum:
  


Current Time: Sat Jul 02 22:31:21 CDT 2022