This discussion is archived
2 Replies Latest reply: Jan 14, 2013 7:29 AM by Askdineshsingh RSS

Dynamic Columns in Ref Cursor

user568781 Newbie
Currently Being Moderated
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 ?

Thanks
Vipul Maheshwari
  • 1. Re: Dynamic Columns in Ref Cursor
    Jim Smith Expert
    Currently Being Moderated
    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
    Askdineshsingh Explorer
    Currently Being Moderated
    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
    clean-up)
    Restrictions on CURSOR Expressions The following restrictions apply to CURSOR
    expressions:
    ■ 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;
    begin
    loop
    fetch cur into emp_hiredate;
    exit when cur%NOTFOUND;
    if emp_hiredate > mgr_hiredate then
    after:=after+1;
    else
    before:=before+1;
    end if;
    end loop;
    close cur;
    if before > after then
    return 1;
    else
    return 0;
    end if;
    end;
    /
    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
    manager.
    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points