| Problem with a procedure [message #37264] |
Mon, 28 January 2002 08:51  |
Lance Pris
Messages: 40
Registered: January 2002
|
Member |
|
|
I am trying to create the following procedure but getting the error "compiled with errors" Can anyone tell me why?
Thanks In advance
CREATE OR REPLACE PROCEDURE Primus_Report
as
Begin
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_7_DAYS VARCHAR2 (255),
NO_LINKS_LAST_120_DAYS 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 [message #37266 is a reply to message #37264] |
Mon, 28 January 2002 09:09  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You can't execute DDL statements (create/alter/drop table) directly in PL/SQL. You can do it using dynamic SQL.
execute immediate 'drop table t';
Have you thought about creating the table outside the proc, and then within the proc, truncating the table (deleting the data) and doing the insert?
execute immediate 'truncate table primus_temp_dump';
insert into ...
You would normally not have a drop table/create table combo in a procedure.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
