Menu de navegação

[Anônimo]

Oi Pessoal

Gostaria de executar uma query dinamica dentro de uma StoreProcedure, como fa?? isso??? A minha ideia e ter uma string q contenha um SELECT montado de acordo com alguns parametros q forem passados e ele retornar um cursor com o relsultado!

cds

Nelson.

[Anônimo]


ops$marcio@ORA10G> create or replace procedure p1 ( p_query in varchar2, p_x out sys_refcursor )
2 as
3 begin
4 open p_x for p_query;
5 end;
6 /

Procedure created.

ops$marcio@ORA10G> show error
No errors.
ops$marcio@ORA10G>
ops$marcio@ORA10G> variable x refcursor
ops$marcio@ORA10G> exec p1( 'select * from emp', :x )

PL/SQL procedure successfully completed.

    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/0087 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/0087 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

14 rows selected.

ops$marcio@ORA10G>

Marcio Portes
http://mportes.blogspot.com

[Autor]

Posts