Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure problem
Stored Procedure problem [message #36076] Fri, 02 November 2001 11:47 Go to next message
ash
Messages: 43
Registered: February 2001
Member
Can anybody tell me what am I doing wrong in below ?

insert into tmpResults
(select distinct c.Id clientid,
c.lastname,
c.firstname,
(SELECT NVL(vpa2.CodeDesc, 'UnSpecified') FROM
vCodesAgency vpa2 WHERE vpa2.Code = vpa.Code
AND AgencyName NOT IN ('HUD', 'SECTION 8') AND ROWNUM = 1 AND NVL(vpa2.CodeDesc, '%') LIKE p_propagency) Agency
from client c, vCodesPropertyAgency vpa
where ..............
)

This query when I run from sql plus it works
but gives me following error when I run in the stored procedure on the second subquery

error : Encountered the symbol "SELECT" when expected one of the following:

If I convert into dynamic sql then how do I execute it to insert records into table ?

Or Is there any other way to do it ?

Any help is highly appreciated.

----------------------------------------------------------------------
Re: Stored Procedure problem [message #36077 is a reply to message #36076] Fri, 02 November 2001 12:34 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
This is one of those cases in 8i (but not in 9i) where the PL/SQL engine doesn't support all SQL syntax. So, you will have to use dynamic SQL to execute this query:

v_query := 'insert into tmpResults
(select distinct c.Id clientid,
c.lastname,
c.firstname,
(SELECT NVL(vpa2.CodeDesc, ''UnSpecified'') FROM
vCodesAgency vpa2 WHERE vpa2.Code = vpa.Code
AND AgencyName NOT IN (''HUD'', ''SECTION 8'') AND ROWNUM = 1 AND NVL(vpa2.CodeDesc, ''%'') LIKE p_propagency) Agency
from client c, vCodesPropertyAgency vpa
where ...)';

Make sure all the parentheses are correct and notice that single quotes have been replaced by two single quotes.

Then, just:

execute immediate v_query;

----------------------------------------------------------------------
Re: Stored Procedure problem [message #36102 is a reply to message #36077] Mon, 05 November 2001 11:14 Go to previous message
ash
Messages: 43
Registered: February 2001
Member
Thanks Todd.

You really solved my problem.

----------------------------------------------------------------------
Previous Topic: does anyone know how to insert a record instead of ...
Next Topic: Re: Home directory of APACHE ( Urgent)
Goto Forum:
  


Current Time: Fri Mar 29 09:10:56 CDT 2024