2 Replies Latest reply: Jan 14, 2013 9:29 AM by Askdineshsingh RSS

    Dynamic Columns in Ref Cursor

      Hi All,

      I have a requirement that i need to execute query having dynamic number of columns using Ref Cursor e.g.

      v_string := 'PO_NUMBER , ORG_ID' ;
      v_query := 'SELECT '|| v_string || 'FROM table_name';

      Can someone please quide me how to define Type dynamically for the above requirement ?

      Vipul Maheshwari
        • 1. Re: Dynamic Columns in Ref Cursor
          Jim Smith
          This is the forum for Oracle's SQL Developer tool, not for general SQL and PL/SQL questions. Questions like this will get a better response in the SQL and PL/SQL forum.
          • 2. Re: Dynamic Columns in Ref Cursor
            hope you understand how to use this with the help of ref cursor

            CURSOR Expressions
            A CURSORexpression returns a nested cursor. This form of expression is equivalent to
            the PL/SQL REF CURSORand can be passed as a REF CURSORargument to a function.
            A nested cursor is implicitly opened when the cursor expression is evaluated. For
            example, if the cursor expression appears in a select list, a nested cursor will be
            opened for each row fetched by the query. The nested cursor is closed only when:
            ■ The nested cursor is explicitly closed by the user
            ■ The parent cursor is reexecuted
            ■ The parent cursor is closed
            ■ The parent cursor is cancelled
            ■ An error arises during fetch on one of its parent cursors (it is closed as part of the
            Restrictions on CURSOR Expressions The following restrictions apply to CURSOR
            ■ If the enclosing statement is not a SELECTstatement, then nested cursors can
            appear only as REF CURSORarguments of a procedure.
            ■ If the enclosing statement is a SELECTstatement, then nested cursors can also
            appear in the outermost select list of the query specification or in the outermost
            select list of another nested cursor.
            ■ Nested cursors cannot appear in views.
            ■ You cannot perform BINDand EXECUTEoperations on nested cursors.
            Examples The following example shows the use of a CURSORexpression in the
            select list of a query:
            SELECT department_name, CURSOR(SELECT salary, commission_pct
            FROM employees e
            WHERE e.department_id = d.department_id)
            FROM departments d
            ORDER BY department_name;
            The next example shows the use of a CURSORexpression as a function argument. The
            example begins by creating a function in the sample OEschema that can accept the
            REF CURSORargument. (The PL/SQL function body is shown in italics.)
            CREATE FUNCTION f(cur SYS_REFCURSOR, mgr_hiredate DATE)
            RETURN NUMBER IS
            emp_hiredate DATE;
            before number :=0;
            after number:=0;
            fetch cur into emp_hiredate;
            exit when cur%NOTFOUND;
            if emp_hiredate > mgr_hiredate then
            end if;
            end loop;
            close cur;
            if before > after then
            return 1;
            return 0;
            end if;
            The function accepts a cursor and a date. The function expects the cursor to be a query
            returning a set of dates. The following query uses the function to find those managers
            in the sample employeestable, most of whose employees were hired before the
            SELECT e1.last_name FROM employees e1
            WHERE f(
            CURSOR(SELECT e2.hire_date FROM employees e2
            WHERE e1.employee_id = e2.manager_id),
            e1.hire_date) = 1
            ORDER BY last_name;