11 Replies Latest reply: Feb 11, 2013 11:19 PM by Billy~Verreynne RSS

    Ref cursor trouble

    rishwinger
      Hi All,

      My requirement is to fetch values from any given tables through ref cursor, so i have used a below procedure
      CREATE OR REPLACE
      PROCEDURE et1(
          tab_name IN VARCHAR2,
          c1 OUT sys_refcursor)
      AS
        v_prim_val VARCHAR2(2000);
        v_sql      VARCHAR2(2000);
      BEGIN
        SELECT wm_concat(s.column_name)
        INTO v_prim_val
        FROM user_cons_columns s ,
          user_constraints c
        WHERE c.constraint_name=s.constraint_name
        AND c.constraint_type  ='P'
        AND s.table_name       =tab_name;
        dbms_output.put_line('Col val='||v_prim_val);
        v_sql:='select '||v_prim_val|| ' from easy where id in (1,2,3)';
        OPEN c1 FOR v_sql;
      END et1;
      /
      Problem with above code is the record set returned by ref cursor will be unknown so in which variable should i put it in?

      below code will create table for above procedure
      create table easy(id integer, event_rowid integer,txn_rowid integer,txn_name varchar2(200));
      
      begin
      for i in 1..5 loop
      insert into easy values(i,'777'||i,i||89,'Ris'||i||i);
      end loop;
      end;
      commit;
      /
      
      alter table easy add constraint pk_easy primary key(id,event_rowid,txn_name);
      While googling i found "we can't fetch into a variable of the weak cursor's row type." But I don't know the record set or the variables being returned .

      Please help!
        • 1. Re: Ref cursor trouble
          SomeoneElse
          SELECT wm_concat(s.column_name)
          Careful here. wm_concat is undocumented and unsupported.

          Could lead to a nasty surprise down the road.
          • 2. Re: Ref cursor trouble
            _Karthick_
            Dont use WM_CONCATE, Its undocumented and an tool internal to oracle. Oracle does not support it. There could be a situation where it may not be available in the later version.

            What are you going to do with the refcursor that your procedure returns, If you tell that we can say if you really need that refcursor.
            • 3. Re: Ref cursor trouble
              BluShadow
              Suggest you read this...

              {thread:id=886365}

              And then, if you really have a justified reason for creating dynamic queries (99% of the time people don't but they think they do), then look at using the DBMS_SQL package. (In 11g you can convert Ref Cursors to DBMS_SQL cursors so that you can describe them and fetch the columns by position rather than name).
              • 4. Re: Ref cursor trouble
                user13325846
                v_sql:='select '||v_prim_val|| ' from easy where id in (1,2,3)';

                I think in this case, your query will return only one record, because there is no group by clause with the query.

                So, if you really need to store the result you can store in a varchar type variable after defining the ref cursor as a strong one.
                • 5. Re: Ref cursor trouble
                  rishwinger
                  Thanks for your reply

                  My requirement is to get primary key column name and there values of a table and store in a stage table
                  I was able to do it for 1 record but there can be more than 1 records so i though of using Ref cursor because with ref cursor we can change the whole sql even the where condition

                  Is there alternative for wm_concat?
                  DECLARE
                     V_prim_key VARCHAR2(2000);
                     l_tab DBMS_UTILITY.uncl_array;
                     u_tab DBMS_UTILITY.uncl_array;
                     l_tablen    INTEGER;
                     v_prim_data VARCHAR2(2000);
                    
                  BEGIN
                    
                    SELECT wm_concat(s.column_name) 
                    INTO v_prim_key 
                    FROM user_cons_columns s ,
                       user_constraints c 
                    WHERE c.constraint_name=s.constraint_name 
                    AND c.constraint_type  ='P' 
                    AND s.table_name       ='EASY';
                     dbms_output.put_line('Col val='||v_prim_key);
                     DBMS_UTILITY.comma_to_table (  list => v_prim_key, tablen => l_tablen, tab => l_tab);
                     dbms_output.put_line(l_tablen);
                     FOR i IN 1 .. l_tablen 
                    LOOP
                       DBMS_OUTPUT.put_line(l_tab(i));
                       EXECUTE immediate 'select ' ||l_tab(i)|| ' from easy where id =1' INTO u_tab(i);
                      
                    END LOOP;
                     DBMS_UTILITY.table_to_comma ( tab => u_tab, tablen => l_tablen,  list => v_prim_data);
                     DBMS_OUTPUT.put_line('v_prim_data : ' || v_prim_data);
                    
                    INSERT INTO stage VALUES 
                      ('EASY',v_prim_key,v_prim_data,sysdate 
                      );
                     COMMIT;
                    
                  END;
                   /
                  Col val=TXN_NAME,EVENT_ROWID,ID
                  3
                  TXN_NAME
                  EVENT_ROWID
                  ID
                  v_prim_data : Ris11,7771,1
                  
                  PL/SQL procedure successfully completed.
                  create table stage(tab_name varchar2(200),v_prim_key varchar2(2000),v_prim_data varchar2(2000),dt date);
                  • 6. Re: Ref cursor trouble
                    Billy~Verreynne
                    You are missing a fundamental point here - the refcursor is not intended to be used by PL/SQL code. The primary purpose of the refcursor data type, is to enable PL/SQL to create a SQL cursor, and then return a reference pointer, to that SQL cursor, to an external client - where that code can describe the cursor (determine the data structure returned by it), and fetch the output of that SQL cursor.

                    Chances are excellent that refcursor consumed in PL/SQL are INCORRECTLY DESIGNED AND USED.

                    If you think you need to use dynamic SQL? Also wrong. Dynamic SQL is an exception in PL/SQL. Always. There are very seldom sensible and sound reasons to use dynamic SQL in PL/SQL.

                    PL/SQL has been designed for using static SQL. That is this language's greatest strength and why it makes a better language (for using SQL) than Java, C/C++, .Net and so on.

                    One does not throw that greatest strength away on a mere whim of "+oh, I want to use dynamic SQL+" as you are seemingly doing.
                    • 7. Re: Ref cursor trouble
                      rishwinger
                      Thanks Billy for your reply

                      I had a requirement and i tried something , apparently it's a fiasco

                      I understand static sql is the key but not for my Lock.

                      I have 300 tables in my Database ,I have to fetch primary key and there values on some condition which user will pass .

                      Now please tell me when i don't know which table they will pass and table may have any number of primary keys and if i have used dynamic sql so what's wrong in that?

                      what approach should i follow?
                      Billy ,BTW u made my day i was just telling my girlfriend i got a reply from Oracle Ace, THX
                      • 8. Re: Ref cursor trouble
                        BluShadow
                        983088 wrote:
                        Is there alternative for wm_concat?
                        Read the FAQ: {message:id=9360005}
                        under the string aggregation techniques
                        Thanks Billy for your reply

                        I had a requirement and i tried something , apparently it's a fiasco

                        I understand static sql is the key but not for my Lock.

                        I have 300 tables in my Database ,I have to fetch primary key and there values on some condition which user will pass .
                        So, you're saying a user should be able to extract any data they want from any table of their choice with any conditions they want? Such 'users' are usually technical administrators who know the database and are trusted not to damage things.
                        Now please tell me when i don't know which table they will pass and table may have any number of primary keys and if i have used dynamic sql so what's wrong in that?
                        It means that your business requirement is too 'generic' and there is no solid design to what the requirements are.
                        what approach should i follow?
                        Use the DBMS_SQL package. This allows a 'dynamic' query to be constructed, parsed (and thus validated) and then queried where you can fetch the columns by numeric position (column number 1, column number 2 etc.) as well as determining their datatypes and names etc.

                        An example from my standard library of examples, where I use DBMS_SQL to take any query and extract the details of that query to output the data returned as 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
                                DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                                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
                        -----
                        Billy ,BTW u made my day i was just telling my girlfriend i got a reply from Oracle Ace, THX
                        Small things please ....
                        • 9. Re: Ref cursor trouble
                          Billy~Verreynne
                          983088 wrote:

                          I have 300 tables in my Database ,I have to fetch primary key and there values on some condition which user will pass .

                          Now please tell me when i don't know which table they will pass and table may have any number of primary keys and if i have used dynamic sql so what's wrong in that?
                          The problem is that SQL is not an I/O interface layer. It is not the database version of the C language's File I/O interface.

                          SQL does not exist merely as a read() and write() interface for reading and writing rows from and to a table.

                          Database data is not records that are to be read and written. The database is about information.

                          You can ask the database, via SQL:
                          give me the top 10 products sold by volume for year 2012 in California, after 5PM, to female customers

                          And SQL provides answers (information) to make business decisions and support business processes.

                          You do not ask the database for raw data from 300 different tables. That is not what databases are used for. That is what files and C's I/O are (were) used for - back in the 80's. 30 years ago.

                          It honestly does not make sense to use PL/SQL and ref cursors for providing raw row access to 300 tables. In this case, no PL/SQL is needed. As it does not contribute to performance, does not provide abstraction, does not implement business rules and business logic, does not apply security rules, etc.

                          In the scenario you have sketched? No PL/SQL. No ref cursors.

                          What is needed is for the application to generate the (rudimentary) SQL that says "+select ... from table+" and have the application treat tables as files, rows as records, and the powerful SQL language as a mere read-rows and write-rows interface.

                          And you should also reconsider using Oracle - and instead use a free database like mySQL or Firebird - as the code and approach used to Oracle SQL and the Oracle database, do not warrant the cost of the Oracle database.
                          • 10. Re: Ref cursor trouble
                            user13328581
                            Can you suggest where such a code will be useful in a real entity. I am just curious....
                            • 11. Re: Ref cursor trouble
                              Billy~Verreynne
                              PL/SQL web service that returns a data set in CSV format. A PL/SQL process that creates a CSV as a CLOB and then ftp's it to a target server.

                              The underlying concept Blu shown, using the describe cursor interface of DBMS_SQL, is what Apex uses extensively for report rendering.