Re: Dynamic Sorts in Pl/Sql

> You are not allow to redefine the query on the fly.
> There are two ways: dbms_sql package OR native dynamic SQL...
> You can however use dbms_sql package to construct the sql statement and
> then execute it. This has been the mechanism for a while and is the
> most compatible if that is an issue.
>
> Native dynamic SQL : Chapter 10 of 8i PL/SQL user guide and reference
>
> You can extend this example to construct the sql_statement with the
> appropriate field for the order by clause.
> Note: also there is syntax for binding variables ( input and output ).
>
> Example from manual....
>
> As the following example shows, you can fetch rows from the result set
> of a dynamic multi-row query into a record:
>
> DECLARE
> TYPE EmpCurTyp IS REF CURSOR;
> emp_cv EmpCurTyp;
> emp_rec emp%ROWTYPE;
> sql_stmt VARCHAR2(200);
> my_job VARCHAR2(15) := 'CLERK';
> BEGIN
> sql_stmt := 'SELECT * FROM emp WHERE job = :j';
> OPEN emp_cv FOR sql_stmt USING my_job;
> LOOP
> FETCH emp_cv INTO emp_rec;
> EXIT WHEN emp_cv%NOTFOUND;
> -- process record
> END LOOP;
> CLOSE emp_cv;
> END;
So, what you posted above works in 8i and up, and if that's what I'm seeing, you can create a cursor dynamically (via the sql statement and the parameter in the OPEN), and could return this via a refcursor? This isn't possible in Oracle 8.0.6, is it?

Can you provide an example to handle multiple parameters or one that might apply to my passing of a list of jobs that we want to return the employees for, ie. the jobs are passed as a comma separated list?

If not, I'll try it at work on my testbox (Oracle 8.1.6). THANKS!! Received on Fri Jan 19 2001 - 02:53:16 CET