OraFAQ Forum: SQL & PL/SQL » WITH STATEMENT in PL-SQL procedure block

Home » SQL & PL/SQL » SQL & PL/SQL » WITH STATEMENT in PL-SQL procedure block

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

WITH STATEMENT in PL-SQL procedure block [message #9814]

Fri, 05 December 2003 10:30

Alvin
Messages: 7
Registered: December 2000

Junior Member

Dear all,

Would anyone know if it's possible to include a WITH block inside an anonymous PL/SQL block? Following is the code I would like to perform :

DECLARE
WITH test AS (
SELECT * from subscribers
where id_subs = 1749
);
BEGIN
SELECT * from test;
END;
/

It doesn't work, and I'm not sure if its my syntax, or just the PL/SQL doesn't offer this option.

I am trying to avoid using a cursor because I'm running subsequent SQL select statments to get summarized data from the sql block.

Thanks in advance!
Regards,
Alvin

Report message to a moderator

Re: WITH STATEMENT in PL-SQL procedure block [message #9816 is a reply to message #9814]

Fri, 05 December 2003 11:43

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

Yes, but it requires 9i. In 8i you would have to use a view or dynamic SQL.

Report message to a moderator

Re: WITH STATEMENT in PL-SQL procedure block [message #9817 is a reply to message #9816]

Fri, 05 December 2003 12:15

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Actually, you can't do what you are asking in 9i either. The WITH clause is only viable in a single SQL construct. In a PL/SQL context, if you need to use the results of that same query over and over again within a proc and the query is expensive, you would need to load the results into a GTT or collection at the start of the proc.

Report message to a moderator

Re: WITH STATEMENT in PL-SQL procedure block [message #9818 is a reply to message #9817]

Fri, 05 December 2003 12:52

Alvin
Messages: 7
Registered: December 2000

Junior Member

Thanks for your prompt help William and Todd. Too bad it won't work, at least I know for sure now!

Kind regards,
Alvin

Report message to a moderator

Re: WITH STATEMENT in PL-SQL procedure block [message #9820 is a reply to message #9814]

Fri, 05 December 2003 23:21

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

You can, if you have Oracle 9i and execute it dynamically. Here are a couple of examples, using two different type of dynamic SQL:

-- first method:
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> DECLARE
  2    v_deptno dept.deptno%TYPE;
  3    v_dname	dept.dname%TYPE;
  4    v_loc	dept.loc%TYPE;
  5  BEGIN
  6    EXECUTE IMMEDIATE
  7    'WITH test AS
  8  	 (SELECT * FROM dept
  9  	  WHERE  deptno = 10)
 10    SELECT * FROM test'
 11    INTO v_deptno, v_dname, v_loc;
 12    DBMS_OUTPUT.PUT_LINE (v_deptno);
 13    DBMS_OUTPUT.PUT_LINE (v_dname);
 14    DBMS_OUTPUT.PUT_LINE (v_loc);
 15  END;
 16  /
10
ACCOUNTING
NEW YORK

PL/SQL procedure successfully completed.

scott@ORA92> 
scott@ORA92> 

-- second method:
scott@ORA92> VARIABLE g_ref REFCURSOR
scott@ORA92> BEGIN
  2    OPEN :g_ref FOR
  3    'WITH test AS
  4  	 (SELECT * FROM dept
  5  	  WHERE  deptno = 10)
  6    SELECT * FROM test';
  7  END;
  8  /

PL/SQL procedure successfully completed.

scott@ORA92> PRINT g_ref

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

scott@ORA92>

Report message to a moderator

Re: WITH STATEMENT in PL-SQL procedure block [message #9821 is a reply to message #9820]

Fri, 05 December 2003 23:34

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

Additional information: Notice that the with clause is part of the select statement and does not go in the declaration section. It is intended to be used where the same sub-query is re-used multiple times within the same select statement. It is not intended to be used for separate select statements. Without more specific information, it is difficult to tell if a WITH clause, AKA sub-query factoring clause, is what you need, or whether you need a global temporary table or something else.

Report message to a moderator

Re: WITH STATEMENT in PL-SQL procedure block [message #9824 is a reply to message #9820]

Sat, 06 December 2003 07:23

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

This is a correction to my own response. You can do it in PL/SQL in 9i without even having to execute it dynamically. I got so accustomed to having to execute new SQL features dynamically in PL/SQL in 8i that I forgot that you don't have to do that in 9i since the SQL and PL/SQL engines are the same. Please see the examples below.

scott@ORA92> -- first method:
scott@ORA92> DECLARE
  2    v_deptno dept.deptno%TYPE;
  3    v_dname	dept.dname%TYPE;
  4    v_loc	dept.loc%TYPE;
  5  BEGIN
  6    WITH test AS
  7  	  (SELECT * FROM dept WHERE deptno = 10)
  8  	SELECT *
  9  	INTO v_deptno, v_dname, v_loc
 10  	FROM test;
 11  	DBMS_OUTPUT.PUT_LINE (v_deptno);
 12  	DBMS_OUTPUT.PUT_LINE (v_dname);
 13  	DBMS_OUTPUT.PUT_LINE (v_loc);
 14  END;
 15  /
10
ACCOUNTING
NEW YORK

PL/SQL procedure successfully completed.

scott@ORA92> 
scott@ORA92> 
scott@ORA92> 
scott@ORA92> 
scott@ORA92> 
scott@ORA92> -- second method:
scott@ORA92> VARIABLE g_ref REFCURSOR
scott@ORA92> BEGIN
  2    OPEN :g_ref FOR
  3    WITH test AS
  4  	  (SELECT * FROM dept WHERE deptno = 10)
  5  	SELECT * FROM test;
  6  END;
  7  /

PL/SQL procedure successfully completed.

scott@ORA92> PRINT g_ref

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

Report message to a moderator

Re: WITH STATEMENT in PL-SQL procedure block [message #9832 is a reply to message #9817]

Sun, 07 December 2003 15:20

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

Oops, didn't read the post properly! Apologies.

Report message to a moderator

Previous Topic:	Oracle Clinical SQL scripts
Next Topic:	number converstion

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 16 06:29:02 CDT 2025