1 2 Previous Next 16 Replies Latest reply on Feb 2, 2012 2:02 PM by BluShadow

    Converting ref cursor output from function to table

    user10566312
      I have a function named fn_get_emp() whose return type is sys_refcursor. When I do select fn_get_emp from dual; I get the output in cursor format. I want to convert it to table like format so that I can use it in my insert statements insert into foo select * from fn_get_emp();

      Please note the columns in the output of the fn_get_emp() is not fixed.

      Edited by: user10566312 on Jan 30, 2012 10:25 PM
        • 1. Re: Converting ref cursor output from function to table
          908002
          See this sample block

          -- Open REF CURSOR variable:
          OPEN src_cur FOR sql_stmt USING empno;

          -- Switch from native dynamic SQL to DBMS_SQL package:
          curid := DBMS_SQL.TO_CURSOR_NUMBER(src_cur);
          DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);

          -- Define columns:
          FOR i IN 1 .. colcnt LOOP
          IF desctab(i).col_type = 2 THEN
          DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
          ELSIF desctab(i).col_type = 12 THEN
          DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
          -- statements
          ELSE
          DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 50);
          END IF;
          END LOOP;

          -- Fetch rows with DBMS_SQL package:
          WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
          FOR i IN 1 .. colcnt LOOP
          IF (desctab(i).col_type = 1) THEN
          DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
          ELSIF (desctab(i).col_type = 2) THEN
          DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
          ELSIF (desctab(i).col_type = 12) THEN
          DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
          -- statements
          END IF;
          END LOOP;
          END LOOP;

          DBMS_SQL.CLOSE_CURSOR(curid);
          END;
          1 person found this helpful
          • 2. Re: Converting ref cursor output from function to table
            user10566312
            I haven't understood your solution as I have never used DBMS_SQL. But my requirement is that I want to convert the ref cursor output from a function to a table so that I can use it like insert into foo select * from fn_get_emp;
            • 3. Re: Converting ref cursor output from function to table
              Suri
              Hi Kiran,

              One suggestion. Please keep the code in
               tags. So that easily we can read the code.
              
              Refer point no 9 in below link.
              
               [SQL and PL/SQL FAQ                                                                                                                                                                                                                                                                                                                                                                                                                            
              1 person found this helpful
              • 4. Re: Converting ref cursor output from function to table
                Suri
                user10566312 wrote:
                I haven't understood your solution as I have never used DBMS_SQL. But my requirement is that I want to convert the ref cursor output from a function to a table so that I can use it like insert into foo select * from fn_get_emp;
                Hi,

                The function return type is SYS_REFCURSOR. So if you execute the function using SELECT statement you will not get result like a table.

                Only way is use PL/SQL and insert the data into your target table.

                See sample code below.
                SQL> CREATE OR REPLACE
                  2  FUNCTION get_emp_rs (p_deptno    IN  NUMBER)
                  3  RETURN SYS_REFCURSOR
                  4  IS
                  5  
                  6    p_recordset SYS_REFCURSOR;
                  7  
                  8  BEGIN
                  9    OPEN p_recordset FOR
                 10      SELECT e.last_name
                 11      FROM   employees e
                 12      WHERE  e.department_id=p_deptno
                 13      ORDER BY e.last_name;
                 14  
                 15  RETURN p_recordset;
                 16  
                 17  END ;
                 18  /
                
                Function created
                
                -- Sample Script
                
                DECLARE
                
                  x_recordset SYS_REFCURSOR;
                  l_lastname_ employees.last_name%type;
                
                BEGIN
                
                  x_recordset := get_emp_rs(p_deptno => 20);
                
                  LOOP
                  
                    FETCH x_recordset
                      INTO l_lastname_;
                  
                    dbms_output.put_line(l_lastname_);
                
                   -- Your INSERT Statement
                  
                    EXIT WHEN x_recordset%NOTFOUND;
                  
                  END LOOP;
                
                END;
                Edited by: Suri on Jan 31, 2012 11:58 AM
                1 person found this helpful
                • 5. Re: Converting ref cursor output from function to table
                  BluShadow
                  You fail to understand what a ref cursor is.

                  Read this: {thread:id=886365}
                  1 person found this helpful
                  • 6. Re: Converting ref cursor output from function to table
                    user10566312
                    Thanks. So if I have a dynamic column list in my select query I cannot convert it to a table.

                    Below is the code snippet of the function I am using.
                    create or replace function get_dept_emps(col_name_list in varchar2)
                    return sys_refcursor 
                    is
                      v_rc sys_refcursor;
                      v_sql varchar2(512);
                    begin
                      v_sql := 'select '|| col_name_list || ' from user_tables';
                      open v_rc for v_sql;
                      return v_rc;
                    end;
                    / 
                    I can do select get_dept_emps('OWNER, TABLE_NAME') from dual; which gives me output as cursor. However, I want to convert this to something like select * from table.
                    --

                    BTW sir how are you? Long time you came to my help.
                    • 7. Re: Converting ref cursor output from function to table
                      BluShadow
                      user10566312 wrote:
                      BTW sir how are you? Long time you came to my help.
                      I don't recall, your username isn't exactly easy to remember, user10566312.
                      Thanks. So if I have a dynamic column list in my select query I cannot convert it to a table.
                      Not easily no. A 3rd party application layer such as .NET, Java etc. can take ref cursors and get a description of the columns they are returning.
                      Likewise in 11g you can use the new functionality in the DBMS_SQL package to convert the ref cursor to a DBMS_SQL cursor and then use the same package to DESCRIBE the query and determine what column names and datatypes are being returned by it.

                      But solely within SQL or PL/SQL... no, not unless you know the SQL projection that is coming back out of the query.
                      Below is the code snippet of the function I am using.
                      create or replace function get_dept_emps(col_name_list in varchar2)
                      return sys_refcursor 
                      is
                      v_rc sys_refcursor;
                      v_sql varchar2(512);
                      begin
                      v_sql := 'select '|| col_name_list || ' from user_tables';
                      open v_rc for vsql;
                      return v_rc;
                      end;
                      / 
                      I can do select get_dept_emps('OWNER, TABLE_NAME') from dual; which gives me output as cursor. However, I want to convert this to some like select * from table.
                      DBMS_SQL in 11g is the answer.

                      If you don't have 11g then, as the article I wrote says, you need to consider why you are using a ref cursor in the first place i.e. you've probably picked the wrong tool for the job.

                      Aside from that, step back and ask yourself why you are creating queries with dynamic column lists in them. What are you actually trying to achieve? There's probably a better way, not involving dynamic code.
                      1 person found this helpful
                      • 8. Re: Converting ref cursor output from function to table
                        user10566312
                        Yes I am using 11g. Can you please help?

                        business requirement is somewhat like they will decide on the fly which measures they want to calculate and which order. so the procedure will calculate these measures on the fly and return via ref_cursor to cognos... something like this. multiple users can run the same report simultaneously so we cannot use a table, as the definition may vary depending on the measures selected by user.
                        • 9. Re: Converting ref cursor output from function to table
                          BluShadow
                          Here's the procedure in the DBMS_SQL package you need to turn your ref cursor into a DBMS_SQL cursor...

                          http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sql.htm#CHDJDGDG

                          and then the rest of the DBMS_SQL package documentation gives plenty of examples of how to process such a dbms_sql cursor.

                          Here's a basic example of using the dbms_sql package to process a query and output whatever sql projection and data is in the query to a CSV file...

                          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
                          The procedure allows for the header and data to go to seperate files if required. Just specifying the "header" filename will put the header and data in the one file.

                          Adapt to output different datatypes and styles are required.
                          • 10. Re: Converting ref cursor output from function to table
                            user10566312
                            Hi can I convert it to a collection and return as OUT parameter? The number of columns in the ref cursor is not fixed
                            • 11. Re: Converting ref cursor output from function to table
                              user10566312
                              need one more help abt creating collections as mentioned above.
                              • 12. Re: Converting ref cursor output from function to table
                                BluShadow
                                user10566312 wrote:
                                need one more help abt creating collections as mentioned above.
                                You also need some patience. Some of us have real lives to lead (like eating and sleeping) between 17:42 and 07:08 which is when you posted your last two posts in my timezone.

                                If you don't know what columns are going to be in the ref cursor, how are you going to expect to a) create a collection to hold an unknown number of columns and b) have code that can accept and process a collection with an unknown number of columns.

                                The point of database and application design is that you 'design' your database and application to fit around known requirements. The moment a requirement comes along that says that something is going to be 'unknown' means that the requirements haven't been clearly defined in the first place, and that's where I'd be going back to the business analyst or user and saying... "so what is it exactly that you do want?". Often it's just a case that somebody at the start of the design process (the user, business analyst, even technical analyst) has been too lazy to think about or question what the real requirements are, and then we end up with developers who don't seem to know any better scratching their heads trying to create 'generic' and 'dynamic' designs to accomodate all unknown future possibilities... or what is commonly called bug-ridden code.

                                So, step back from what you're doing for a minute, and question... "what is the requirement that I actually need to achieve?"

                                Why do you need a collection or a means of passing data about that has an unknown structure to it?

                                if your ref cursor is really needed to be dynamic, then pass the ref cursor back and process it with dbms_sql elsewhere in your code when you actually need to determine the data coming back from it. Don't read all the data into expensive PGA memory in some arbitrary structure and try and pass that about your code. If it's not going to be processed in PL/SQL, then pass the ref cursor back to the calling application (.net, java or whatever) and let that application process the ref cursor, as they can typically do the same as the dbms_sql package in that they can determine the number of columns, what datatypes they are and then retrieve the data for those columns.
                                • 13. Re: Converting ref cursor output from function to table
                                  user10566312
                                  Sir actually it's not like that. At first I thought posting again will re-open the thread. But it didn't. Then I did some R&D and found that actually there's a link to re-open the thread. When I clicked on that it asked me to enter the reason for re-opening. So that is how two messages got posted.
                                  • 14. Re: Converting ref cursor output from function to table
                                    BluShadow
                                    user10566312 wrote:
                                    Sir actually it's not like that. At first I thought posting again will re-open the thread. But it didn't. Then I did some R&D and found that actually there's a link to re-open the thread. When I clicked on that it asked me to enter the reason for re-opening. So that is how two messages got posted.
                                    ok, I'll let you off. ;)

                                    However, the answer is still the same. Why do you need a collection?
                                    1 2 Previous Next