2 Replies Latest reply: Feb 2, 2014 8:15 AM by Partha Sarathy S RSS

    How to pass table name dynamically to cursor

    a7341009-0445-4ec5-9b34-cdb389a59865

      Please help on subject line.

        • 1. Re: How to pass table name dynamically to cursor
          AnnPricks E

          Try like below sys_refcirsor

          CREATE OR REPLACE PROCEDURE test1_proc (p_tabnm VARCHAR2)

          AS

          v_sqlstr VARCHAR2(2000);

          v_cursor SYS_REFCURSOR;

          v_ename VARCHAR2(20);

          BEGIN

          v_sqlstr := 'SELECT ename FROM '||p_tabnm;

          OPEN v_cursor FOR v_sqlstr;

            LOOP

             FETCH v_cursor INTO v_ename;

              DBMS_OUTPUT.PUT_LINE(v_ename);---- Include your code here

             EXIT WHEN v_cursor%NOTFOUND;

            END LOOP;

            CLOSE v_cursor;

          END;

          /


           

          SET SERVEROUTPUT ON

          EXEC test1_proc('EMP');

          • 2. Re: How to pass table name dynamically to cursor
            Partha Sarathy S

            For your purpose, you should use a REF CURSOR. Oracle internally provides a refcursor called SYS_REFCURSOR. You could use it like below.

             

            CREATE OR REPLACE FUNCTION sf_ref_cur_test(p_tbl VARCHAR2)

            RETURN SYS_REFCURSOR

            AS

              v_sysref SYS_REFCURSOR;

              v_sql_stmt VARCHAR2(32767);

            BEGIN

              v_sql_stmt := 'SELECT * FROM '||p_tbl;

              OPEN v_sysref FOR v_sql_stmt;

              RETURN v_sysref;

            END;

            /


            SQL> SET WRAP OFF;

            SQL> VARIABLE X REFCURSOR;

            SQL> EXEC :X := SF_REF_CUR_TEST('EMP');

             

             

            PL/SQL procedure successfully completed.

             

             

            SQL> PRINT :X;

            truncating (as requested) before column DEPTNO

             

             

             

             

                 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

            ---------- ---------- --------- ---------- --------- ---------- ----------

                  7369 SMITH      CLERK           7902 17-DEC-80        800

                  7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300

                  7521 WARD       SALESMAN        7698 22-FEB-81       1250        500

                  7566 JONES      MANAGER         7839 02-APR-81       2975

                  7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400

                  7698 BLAKE      MANAGER         7839 01-MAY-81       2850

                  7782 CLARK      MANAGER         7839 09-JUN-81       2450

                  7788 SCOTT      ANALYST         7566 19-APR-87       3000

                  7839 KING       PRESIDENT            17-NOV-81       5000

                  7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0

                  7876 ADAMS      CLERK           7788 23-MAY-87       1100

             

             

                 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

            ---------- ---------- --------- ---------- --------- ---------- ----------

                  7900 JAMES      CLERK           7698 03-DEC-81        950

                  7902 FORD       ANALYST         7566 03-DEC-81       3000

                  7934 MILLER     CLERK           7782 23-JAN-82       1300

             

             

            14 rows selected.

            This way you could use it. But make sure that REFCURSOR does not contain data. Its just pointer to the data. If you want to do some processing with the data, you can do it in another block as below.

             

              SET SERVEROUTPUT ON

              DECLARE

                v_cur SYS_REFCURSOR;

                TYPE typ_nt IS TABLE OF EMP%ROWTYPE; -- You can create table type for the required table

                v_nt typ_nt;

              BEGIN

                v_cur := sf_ref_cur_test('EMP');

                FETCH v_cur BULK COLLECT INTO v_nt;

                FOR I IN v_nt.FIRST..v_nt.LAST

                LOOP

                  DBMS_OUTPUT.PUT_LINE(v_nt(i).EMPNO||'-'||v_nt(i).ENAME);

                END LOOP;

              END;

             

            OUTPUT:

             

            7369-SMITH

            7499-ALLEN

            7521-WARD

            7566-JONES

            7654-MARTIN

            7698-BLAKE

            7782-CLARK

            7788-SCOTT

            7839-KING

            7844-TURNER

            7876-ADAMS

            7900-JAMES

            7902-FORD

            7934-MILLER

             

             

            PL/SQL procedure successfully completed.