5 Replies Latest reply: Apr 20, 2010 6:27 AM by BluShadow RSS

    using refcursor as out parameter of procedure

    737544
      Hi,
      I am using a sys_refcursor as an out parameter in a procedure. like
      declare
      cur sys_refcursor;
      var number;
      begin
      SP_TEST(var,cur);
      .
      .
      end;

      Now I want to print using DBMS_OUTPUT.PUT_LINE the content the refcursor(the whole recordset) that has been returned by the procedure.
      How do I do it?
        • 2. Re: using refcursor as out parameter of procedure
          737544
          Thanks for ur quick reply..

          but tell me, just in case i do not know the columns which the cursor fetches from the procedure, that means I dont have any clue regarding the output recordset what columns it contains. In that case if I want to print it as a whole, is it possible??
          • 3. Re: using refcursor as out parameter of procedure
            Prazy
            At least you should know how many columns you will be fetching and what are the data types, without these you cannot do fetch from a ref cursor.

            HTH,
            Prazy
            • 4. Re: using refcursor as out parameter of procedure
              737544
              Thanks Prazy... thats was useful
              • 5. Re: using refcursor as out parameter of procedure
                BluShadow
                user8094217 wrote:
                Thanks for ur quick reply..

                but tell me, just in case i do not know the columns which the cursor fetches from the procedure, that means I dont have any clue regarding the output recordset what columns it contains. In that case if I want to print it as a whole, is it possible??
                If you don't know what columns are being returned from a query then you are working from a poorly designed specification and should send it back to your technical design team to request that the requirements are clearly specified. All requirements should have known inputs and known outputs, otherwise it's just wasting people's time.

                It is possible to write queries that return an unknown number of columns and then query that data from those columns, but this is done positionally (i.e. referencing the columns by number) rather than by name. In 10g, for example, you would have to use the DBMS_SQL package to write a DBMS_SQL cursor and then process that cursor for it's columns and data... e.g.

                As sys user:
                CREATE OR REPLACE DIRECTORY TEST_DIR AS '\tmp\myfiles'
                /
                GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser
                /
                As myuser:
                CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2
                                                     ,p_dir IN VARCHAR2
                                                     ,p_header_file IN VARCHAR2
                                                     ,p_data_file IN VARCHAR2 := NULL) IS
                  v_finaltxt  VARCHAR2(4000);
                  v_v_val     VARCHAR2(4000);
                  v_n_val     NUMBER;
                  v_d_val     DATE;
                  v_ret       NUMBER;
                  c           NUMBER;
                  d           NUMBER;
                  col_cnt     INTEGER;
                  f           BOOLEAN;
                  rec_tab     DBMS_SQL.DESC_TAB;
                  col_num     NUMBER;
                  v_fh        UTL_FILE.FILE_TYPE;
                  v_samefile  BOOLEAN := (NVL(p_data_file,p_header_file) = p_header_file);
                BEGIN
                  c := DBMS_SQL.OPEN_CURSOR;
                  DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
                  d := DBMS_SQL.EXECUTE(c);
                  DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
                  FOR j in 1..col_cnt
                  LOOP
                    CASE rec_tab(j).col_type
                      WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
                      WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
                      WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
                    ELSE
                      DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
                    END CASE;
                  END LOOP;
                  -- This part outputs the HEADER
                  v_fh := UTL_FILE.FOPEN(upper(p_dir),p_header_file,'w',32767);
                  FOR j in 1..col_cnt
                  LOOP
                    v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
                  END LOOP;
                  --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
                  UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
                  IF NOT v_samefile THEN
                    UTL_FILE.FCLOSE(v_fh);
                  END IF;
                  --
                  -- This part outputs the DATA
                  IF NOT v_samefile THEN
                    v_fh := UTL_FILE.FOPEN(upper(p_dir),p_data_file,'w',32767);
                  END IF;
                  LOOP
                    v_ret := DBMS_SQL.FETCH_ROWS(c);
                    EXIT WHEN v_ret = 0;
                    v_finaltxt := NULL;
                    FOR j in 1..col_cnt
                    LOOP
                      CASE rec_tab(j).col_type
                        WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                                    v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
                        WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                                    v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
                        WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                                    v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
                      ELSE
                        v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
                      END CASE;
                    END LOOP;
                  --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
                    UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
                  END LOOP;
                  UTL_FILE.FCLOSE(v_fh);
                  DBMS_SQL.CLOSE_CURSOR(c);
                END;
                This allows for the header row and the data to be written to seperate files if required.

                e.g.
                SQL> exec run_query('select * from emp','TEST_DIR','output.txt');
                 
                PL/SQL procedure successfully completed.
                Output.txt file contains:
                empno,ename,job,mgr,hiredate,sal,comm,deptno
                7369,"SMITH","CLERK",7902,17/12/1980 00:00:00,800,,20
                7499,"ALLEN","SALESMAN",7698,20/02/1981 00:00:00,1600,300,30
                7521,"WARD","SALESMAN",7698,22/02/1981 00:00:00,1250,500,30
                7566,"JONES","MANAGER",7839,02/04/1981 00:00:00,2975,,20
                7654,"MARTIN","SALESMAN",7698,28/09/1981 00:00:00,1250,1400,30
                7698,"BLAKE","MANAGER",7839,01/05/1981 00:00:00,2850,,30
                7782,"CLARK","MANAGER",7839,09/06/1981 00:00:00,2450,,10
                7788,"SCOTT","ANALYST",7566,19/04/1987 00:00:00,3000,,20
                7839,"KING","PRESIDENT",,17/11/1981 00:00:00,5000,,10
                7844,"TURNER","SALESMAN",7698,08/09/1981 00:00:00,1500,0,30
                7876,"ADAMS","CLERK",7788,23/05/1987 00:00:00,1100,,20
                7900,"JAMES","CLERK",7698,03/12/1981 00:00:00,950,,30
                7902,"FORD","ANALYST",7566,03/12/1981 00:00:00,3000,,20
                7934,"MILLER","CLERK",7782,23/01/1982 00:00:00,1300,,10
                If you are using 11g, you can take a ref cursor and then use a new function called TO_CURSOR_NUMBER in the DBMS_SQL package (http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm#BHCBJGEH) which can convert the ref cursor to a dbms_sql cursor for which you can then do the same thing as above to query and extract the data.