| Problem with a procedure in toad [message #37080] |
Wed, 16 January 2002 11:43  |
Lance Pris
Messages: 40
Registered: January 2002
|
Member |
|
|
Why in toad when I try to create a stored proc where I drop and create a table I get the following error:
PLS-00103: Encountered the symbol "DROP" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> cur
Here is my code:
CREATE OR REPLACE PROCEDURE Primus_Report
AS
Drop Table PRIMUS_TEMP_DUMP;
CREATE TABLE PRIMUS_TEMP_DUMP (
SOLUTION_ID VARCHAR2 (85) NOT NULL,
TITLE VARCHAR2 (3498),
OWNER VARCHAR2 (255),
P_TYPE VARCHAR2 (96),
AUTHOR VARCHAR2 (255),
MODIFIED_BY VARCHAR2 (255),
ESCALATION_GROUP VARCHAR2 (255),
TECH_RESOURCE VARCHAR2 (255),
P_PARTITION VARCHAR2 (96),
STATUS VARCHAR2 (96),
HyperLinks VARCHAR2 (96),
Style_reviewer VARCHAR2 (96),
DATE_CREATED DATE,
DATE_MODIFIED DATE,
ALERT VARCHAR2 (96),
URGENCY VARCHAR2 (96),
NO_PAGEVIEWS_LAST_MONTH VARCHAR2 (255),
NO_PAGEVIEWS_QUARTER VARCHAR2 (255),
NO_LINKS_LAST_WEEK VARCHAR2 (255),
NO_LINKS_LAST_MONTH VARCHAR2 (255) );
INSERT INTO PRIMUS_TEMP_DUMP (solution_id,Title) SELECT pc_solution_id, pc_title FROM pt_solution;
end;
/
|
|
|
|
| Re: Problem with a procedure in toad [message #37082 is a reply to message #37080] |
Wed, 16 January 2002 11:55  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
ddl commands are not allowed in procedures directly.
use execute immediate command.
CREATE OR REPLACE PROCEDURE Primus_Report
AS
execute immediate 'Drop Table PRIMUS_TEMP_DUMP';
execute immediate 'create table....';
end;
why dont you put all these commands in .sql file and run that file at sqlprompt?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
