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
First, do a SHOW ERROR after you compile to see what/where the error is.
You can't issue DDL in a procedure. Look at using dynamic sql instead,
eg. -
EXECUTE IMMEDIATE 'Drop Table PRIMUS_TEMP_DUMP';
HTH,
Beth
-Original Message-
Sent: Monday, January 28, 2002 1:45 PM
To: Multiple
Hello Lance
This one works:
CREATE OR REPLACE PROCEDURE Primus_Report
AS
begin
execute immediate 'Drop Table PRIMUS_TEMP_DUMP';
execute immediate 'CREATE TABLE PRIMUS_TEMP_DUMP (SOLUTION_ID VARCHAR2 (85)
NOT NULL)
TITLEVARCHAR2 (3498)),
OWNERVARCHAR2
You
need to use 'Dynamic SQL' in order to drop and create
tables.
-Original Message-From: Lance Prais
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 16, 2002
4:36 PMTo: Multiple recipients of list ORACLE-LSubject:
procedure error
Why in toad when I try
try changing CREATE OR REPLACE PROCEDURE Primus_Report
AS
Drop Table PRIMUS_TEMP_DUMP;
CREATE TABLE PRIMUS_TEMP_DUMP
to CREATE OR REPLACE PROCEDURE Primus_Report
AS
BEGIN
Drop Table PRIMUS_TEMP_DUMP;
CREATE TABLE PRIMUS_TEMP_DUMP
--
=
Peter
DROP
and CREATE are DDL functions ... you have to use DBMS_SQL package or Native
Dynamic SQL (execute immediate) statement to achieve this.
Raj
__
Rajendra
Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot
com
Any opinion
by: cc:
[EMAIL PROTECTED] Subject: Re: procedure error
om
You need in the stored procedure this stmt:
pragma restrict_references (name of function, WNDS
[, WNPS] [, RNDS] [, RNPS]);
where:
WNDS means writes no database state (does not modify
database tables)
WNPS means writes no package state (does not change
the values of
packaged variables)
RNDS
It is an upgrade issue. You can place function calls in your
SELECT with 8i, but can't with older versions.
Steve Sapovits
Global Sports Interactive
Work Email: [EMAIL PROTECTED]
Home Email: [EMAIL PROTECTED]
Work Phone: 610-491-7087
Cell: 610-574-7706
Pager: 877-239-4003
Not true, you could do so before 8i. Look at the restricted_references pragma You
could do stand alone functions without invoking the restricted references pragma.
However, with packaged functions, I believe the pragma was mandatory.
One of the frustrating things, pre-8i, was when you
hi bunyamin
you have forgot to open the cusror before the start of the programme.
excute the dbms_sql.open_cursor function to get the curosr id.
cursor_name:=dbms_sql.open_cursor;
Venkata Ramana
Sierra Optima Limited,
SVR Towers, 8-2-1/B/1, Panjagutta,
Hyderabad 500082, India.
Ph: (91-40) -
11 matches
Mail list logo
