Home » SQL & PL/SQL » SQL & PL/SQL » Problem with a procedure
Problem with a procedure [message #37264] Mon, 28 January 2002 08:51 Go to next message
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 Go to previous message
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.
Previous Topic: Text removal after space
Next Topic: tokenize CSV data for UTL_FILE load
Goto Forum:
  


Current Time: Mon Aug 03 05:06:22 CDT 2020