RE: procedure error

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Mon, 28 Jan 2002 11:23:59 -0800
Message-ID: <F001.003FDA78.20020128111731@fatcity.com>

You can't run a DDL in a procedure directly, you have to use dynamic_sql to execute DDL statements.

Raj

---

Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
Sent: Monday, January 28, 2002 1:45 PM
To: Multiple recipients of list ORACLE-L

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;

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Lance Prais
  INET: lprais_at_ts.checkpoint.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

*********************************************************************1

This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.

*********************************************************************1

Received on Mon Jan 28 2002 - 13:23:59 CST