Home » SQL & PL/SQL » SQL & PL/SQL » This work interactively, not in stored proc; why?
This work interactively, not in stored proc; why? [message #36226] Mon, 12 November 2001 08:29 Go to next message
Mike Austin
Messages: 2
Registered: November 2001
Junior Member
All:

The following code works if the "Create or Replace..." line is replaced by a "Declare" statement, yet if I try to run the stored proc by "execute ProcessSiteDimension;" a "ORA-00900: Invalid SQL Statement" is returned. IOW, the code works if run interactively but not in a stored procedure. Any ideas?

TIA,

Mike

CREATE OR REPLACE PROCEDURE ProcessSiteDimension AS
v_sqlString VarChar2(1000);
BEGIN

/*
MJA, 11/9/2001
This procedure completely refreshes the site dimension.
*/

/*
TO DO: Test for existence of SiteDimension table and drop it if it exists.
*/

-- Second, create an empty target table...

v_sqlString :=
'CREATE TABLE mikerepo.SiteDimension AS
SELECT 0 AS SiteKey,
S.SITE AS Site,
S.SITENAME AS SiteName,
S.CITY AS City,
S.STATE AS State,
S.COUNTRY AS Country,
S.POSTALCODE AS PostalCode,
DECODE(S.PEDS, ''T'', ''Yes'', ''No'') AS PediatricFacility,
R.REGIONDESC AS Region,
T.TYPE AS FacilityType
FROM CORIREPO.SITES_CRM S,
CORIREPO.REGION R,
CORIREPO.SITETYPES T
WHERE (S.REGION = R.REGION (+)) AND (S.TYPE = T.SITETYPE (+)) AND (S.SITE = 99999999)';
Execute Immediate v_sqlString;

-- Third, create a sequence...

v_sqlString :=
'CREATE SEQUENCE SiteKey INCREMENT BY 1 START WITH 1';
Execute Immediate v_sqlString;

-- Fourth, Create a Trigger on SiteDimension...

v_sqlString :=
'CREATE OR REPLACE TRIGGER SiteDimensionKey BEFORE INSERT ON SiteDimension
FOR EACH ROW
DECLARE next_SiteKey INTEGER;
BEGIN
SELECT SiteKey.nextval into next_SiteKey from dual;
:new.SiteKey := next_SiteKey;
end;';
Execute Immediate v_sqlString;

-- Fifth, Populate SiteDimension . . .

v_sqlString :=
'INSERT INTO SiteDimension
(Site, SiteName, City, State, Country, PostalCode, PediatricFacility,
Region, FacilityType)
SELECT S.SITE AS Site,
S.SITENAME AS SiteName,
S.CITY AS City,
S.STATE AS State,
S.COUNTRY AS Country,
S.POSTALCODE AS PostalCode,
DECODE(S.PEDS, ''T'', ''Yes'', ''No'') AS PediatricFacility,
R.REGIONDESC AS Region,
T.TYPE AS FacilityType
FROM CORIREPO.SITES_CRM S,
CORIREPO.REGION R,
CORIREPO.SITETYPES T
WHERE (S.REGION = R.REGION (+)) AND (S.TYPE = T.SITETYPE (+))';
-- ORDER BY S.SITE';
Execute Immediate v_sqlString;

/*
First, drop SiteDimension table and its associated
trigger and sequence in DataWarehouse
*/

v_sqlString := 'DROP TRIGGER SiteDimensionKey';
Execute Immediate v_sqlString;

v_sqlString := 'DROP SEQUENCE SiteKey';
Execute Immediate v_sqlString;

/*
v_sqlString := 'DROP TABLE mikerepo.SiteDimension';
Execute Immediate v_sqlString;
*/

END;

----------------------------------------------------------------------
Re: This work interactively, not in stored proc; why? [message #36229 is a reply to message #36226] Mon, 12 November 2001 09:42 Go to previous messageGo to next message
kriser
Messages: 11
Registered: October 2001
Junior Member
The correct syntax is:

CREATE OR REPLACE PROCEDURE IS

instead of:
CREATE OR REPLACE PROCEDURE AS

----------------------------------------------------------------------
Re: This work interactively, not in stored proc; why? [message #36233 is a reply to message #36229] Mon, 12 November 2001 10:13 Go to previous messageGo to next message
Mike Austin
Messages: 2
Registered: November 2001
Junior Member
Scott Urman's book, 'Advanced PL/SQL Programming", states that either "IS" or "AS" are acceptable syntax. In addition, the procedure compiles without errors.

Mike

----------------------------------------------------------------------
Re: This work interactively, not in stored proc; why? [message #36239 is a reply to message #36226] Mon, 12 November 2001 14:00 Go to previous message
oraboy
Messages: 97
Registered: October 2001
Member
Hi Mike
I tried coping your code from here and in my first attempt it went fine saying 'Procedure created'

so..dont bother about syntax and all.

May be SQL+ didnt like you that time
;-)

Oraboy

----------------------------------------------------------------------
Previous Topic: Re: How to Retrieve CLOB withADODB Command Parameter...?
Next Topic: What wrong with this code
Goto Forum:
  


Current Time: Thu Mar 28 05:53:34 CDT 2024