RE: procedure error

2002-01-28 Thread Seefelt, Beth


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 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,
  TITLEVARCHAR2 (3498),
  OWNERVARCHAR2 (255),
  P_TYPE   VARCHAR2 (96),
  AUTHOR   VARCHAR2 (255),
  MODIFIED_BY  VARCHAR2 (255),
  ESCALATION_GROUP VARCHAR2 (255),
  TECH_RESOURCEVARCHAR2 (255),
  P_PARTITION  VARCHAR2 (96),
  STATUS   VARCHAR2 (96),
  HyperLinks   VARCHAR2 (96),
  Style_reviewer   VARCHAR2 (96),
  DATE_CREATED DATE,
  DATE_MODIFIEDDATE,
  ALERTVARCHAR2 (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seefelt, Beth
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).



RE: procedure error

2002-01-28 Thread Jamadagni, Rajendra

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,
  TITLEVARCHAR2 (3498),
  OWNERVARCHAR2 (255),
  P_TYPE   VARCHAR2 (96),
  AUTHOR   VARCHAR2 (255),
  MODIFIED_BY  VARCHAR2 (255),
  ESCALATION_GROUP VARCHAR2 (255),
  TECH_RESOURCEVARCHAR2 (255),
  P_PARTITION  VARCHAR2 (96),
  STATUS   VARCHAR2 (96),
  HyperLinks   VARCHAR2 (96),
  Style_reviewer   VARCHAR2 (96),
  DATE_CREATED DATE,
  DATE_MODIFIEDDATE,
  ALERTVARCHAR2 (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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




RE: procedure error

2002-01-17 Thread אדר יחיאל

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 (255),
  P_TYPE   VARCHAR2 (96),
  AUTHOR   VARCHAR2 (255),
  MODIFIED_BY  VARCHAR2 (255),
  ESCALATION_GROUP VARCHAR2 (255),
  TECH_RESOURCEVARCHAR2 (255),
  P_PARTITION  VARCHAR2 (96),
  STATUS   VARCHAR2 (96),
  HyperLinksVARCHAR2 (96),
  Style_reviewer VARCHAR2 (96),
  DATE_CREATED DATE,
  DATE_MODIFIEDDATE,
  ALERTVARCHAR2 (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) )';
execute immediate 'INSERT INTO PRIMUS_TEMP_DUMP (solution_id,Title) SELECT
pc_solution_id, pc_title FROM pt_solution';
end;
/


Yechiel Adar, Mehish Computer Services
[EMAIL PROTECTED]

> -Original Message-
> From: Lance Prais [SMTP:[EMAIL PROTECTED]]
> Sent: Wed, January 16, 2002 11:36 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  procedure error
> 
>   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
>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,
> TITLEVARCHAR2 (3498),
> OWNERVARCHAR2 (255),
> P_TYPE   VARCHAR2 (96),
> AUTHOR   VARCHAR2 (255),
> MODIFIED_BY  VARCHAR2 (255),
> ESCALATION_GROUP VARCHAR2 (255),
> TECH_RESOURCEVARCHAR2 (255),
> P_PARTITION  VARCHAR2 (96),
> STATUS   VARCHAR2 (96),
> HyperLinksVARCHAR2 (96),
> Style_reviewer VARCHAR2 (96),
> DATE_CREATED DATE,
> DATE_MODIFIEDDATE,
> ALERTVARCHAR2 (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;
>   /
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>  This e-mail was scanned by the eSafe Mail Gateway 
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).



Re: procedure error

2002-01-16 Thread Jared . Still


Peter,

That won't work either.

DROP and CREATE commands can only be executed in a
procedure via dynamic SQL.

Such as the older DBMS_SQL package, or the newer and
much easier to use EXECUTE IMMEDIATE.

Jared



   
 
Peter.McLarty@ 
 
mincom.com   To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>
Sent by: cc:   
 
[EMAIL PROTECTED]   Subject: Re: procedure error  
 
om 
 
   
 
   
 
01/16/02 02:15 
 
PM 
 
Please respond 
 
to ORACLE-L
 
   
 
   
 





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 McLarty   E-mail: [EMAIL PROTECTED] 
 Technical ConsultantWWW: http://www.mincom.com   
 APAC Technical Services Phone: +61 (0)7 3303 3461
 Brisbane,  AustraliaMobile: +61 (0)402 094 238   
Facsimile: +61 (0)7 3303 3048 
 =
 A great pleasure in life is doing what people say you cannot do. 
  
- Walter Bagehot (1826-1877 British Economist)
 =
 Mincom "The People, The Experience, The Vision"  
  
 =
  





  
   "Lance Prais"  
   <[EMAIL PROTECTED]>   To:Multiple recipients of 
   Sent by: [EMAIL PROTECTED]list ORACLE-L <[EMAIL PROTECTED]>  
cc:   
Fax to:   
   17/01/2002 07:35 AM  Subject:procedure error   
   Please respond to ORACLE-L 
  





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
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,
  TITLEVARCHAR2 (3498),
  OWNERVARCHAR2 (255),
  P_TYPE   VARCHAR2 (96),
  AUTHOR   VARCHAR2 (255),
  MODIFIED_BY  VARCHAR2 (255),
  ESCALATION_GROUP VARCHAR2 (255),
  TECH_RESOURCEVARCHAR2 (255),
  P_PARTITION  VARCHAR2 (96),
  STATUS   VARCHA

RE: procedure error

2002-01-16 Thread Jamadagni, Rajendra



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 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-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 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     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;
/


*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




Re: procedure error

2002-01-16 Thread Peter . McLarty

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 McLarty               E-mail: [EMAIL PROTECTED]
Technical Consultant        WWW: http://www.mincom.com
APAC Technical Services     Phone: +61 (0)7 3303 3461
Brisbane,  Australia        Mobile: +61 (0)402 094 238
                            Facsimile: +61 (0)7 3303 3048
=
A great pleasure in life is doing what people say you cannot do.

    - Walter Bagehot (1826-1877 British Economist)
=
Mincom "The People, The Experience, The Vision"

=









"Lance Prais" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
17/01/2002 07:35 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Fax to:        
        Subject:        procedure error


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
     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;
/



-- 
This transmission is for the intended addressee only and is confidential information.  If you have received this transmission in error, please delete it and notify the sender.  The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise.



RE: procedure error

2002-01-16 Thread Bala, Prakash



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 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     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: procedure error

2001-07-20 Thread MacGregor, Ian A.

A slight correction, the pragma is part of the package specification which contains 
the function. 
For example
CREATE OR REPLACE PACKAGE REMEDY  IS
 FUNCTION SLAC_DATE(ELAPSED_SECONDS IN NUMBER) RETURN DATE ;
 PRAGMA RESTRICT_REFERENCES(SLAC_DATE,WNDS, RNDS, TRUST);
END REMEDY;
/

The "TRUST" argument  was not introduced until 8i and means that you trust other 
functions called by the functions not to violate the pragma. 

Ian MacGregor 
Stanford Linear Accelerator Center
[EMAIL PROTECTED]

-Original Message-
Sent: Friday, July 20, 2001 1:16 AM
To: Multiple recipients of list ORACLE-L


You need in the stored procedure this stmt:

pragma restrict_references (, 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 means "reads no database state" (does not query
database tables)

RNPS means "reads no package state" (does not
reference the values of
packaged variables)


Regards.

PS:This is since 7.X.

--- Steve Sapovits <[EMAIL PROTECTED]>
wrote:
> 
> I have a DB instance (B) that's a snapshot of
> another (A).  
> 
> Taking a stored procedure that runs fine on A to B
> as part of a
> query (a function really I guess?) produces this
> error:
> 
> DBD::Oracle::db prepare failed: ORA-06571: Function
> GET_EVENT does not
> guarantee not to update database 
> 
> Any idea what causes this and how to fix it?
> 
> B may be a version behind A.  A is 8i.
> 
> 
> 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
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Steve Sapovits
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).


=
Eng. Christian Trassens
Senior DBA
Systems Engineer
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : 541149816062

__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christian Trassens
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).



RE: procedure error

2001-07-20 Thread Steve Sapovits


Can you summarize how you'd use that?  I'm not familiar.  Thanks.

> -Original Message-
> From: MacGregor, Ian A. [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, July 19, 2001 11:11 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: procedure error
> 
> 
> 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 
> developed a "pragmatized" function which, for example,  wrote 
> no database state and called an Oracle function which did not 
> do so either,  the function would violate its pragma because 
> Oracle did not declare its function with the pragma.
> 
> 8i invoked the trust argument to restricted_references to get 
> around this.  
> 
> 
> Of course there's always the chance the function is trying to 
> update the database.  This isn't allowed from a select statement.
> 
> Ian MacGregor
> Stanford Linear Accelerator Center
> [EMAIL PROTECTED]
> 
> -Original Message-
> Sent: Thursday, July 19, 2001 4:21 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 
> 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
> 
> > -Original Message-
> > From:   Steve Sapovits [SMTP:[EMAIL PROTECTED]]
> > Sent:   Thursday, July 19, 2001 6:11 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject:procedure error
> > 
> > 
> > I have a DB instance (B) that's a snapshot of another (A).  
> > 
> > Taking a stored procedure that runs fine on A to B as part of a
> > query (a function really I guess?) produces this error:
> > 
> > DBD::Oracle::db prepare failed: ORA-06571: Function 
> GET_EVENT does not
> > guarantee not to update database 
> > 
> > Any idea what causes this and how to fix it?
> > 
> > B may be a version behind A.  A is 8i.
> > 
> > 
> > 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
> > 
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > -- 
> > Author: Steve Sapovits
> >   INET: [EMAIL PROTECTED]
> > 
> > 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: [EMAIL PROTECTED] (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).
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Steve Sapovits
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: MacGregor, Ian A.
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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
> a

Re: procedure error

2001-07-20 Thread Christian Trassens

You need in the stored procedure this stmt:

pragma restrict_references (, 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 means "reads no database state" (does not query
database tables)

RNPS means "reads no package state" (does not
reference the values of
packaged variables)


Regards.

PS:This is since 7.X.

--- Steve Sapovits <[EMAIL PROTECTED]>
wrote:
> 
> I have a DB instance (B) that's a snapshot of
> another (A).  
> 
> Taking a stored procedure that runs fine on A to B
> as part of a
> query (a function really I guess?) produces this
> error:
> 
> DBD::Oracle::db prepare failed: ORA-06571: Function
> GET_EVENT does not
> guarantee not to update database 
> 
> Any idea what causes this and how to fix it?
> 
> B may be a version behind A.  A is 8i.
> 
> 
> 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
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Steve Sapovits
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).


=
Eng. Christian Trassens
Senior DBA
Systems Engineer
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : 541149816062

__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christian Trassens
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).



RE: procedure error

2001-07-19 Thread MacGregor, Ian A.

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 developed a "pragmatized" 
function which, for example,  wrote no database state and called an Oracle function 
which did not do so either,  the function would violate its pragma because Oracle did 
not declare its function with the pragma.

8i invoked the trust argument to restricted_references to get around this.  


Of course there's always the chance the function is trying to update the database.  
This isn't allowed from a select statement.

Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]

-Original Message-
Sent: Thursday, July 19, 2001 4:21 PM
To: Multiple recipients of list ORACLE-L



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

> -Original Message-
> From: Steve Sapovits [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, July 19, 2001 6:11 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  procedure error
> 
> 
> I have a DB instance (B) that's a snapshot of another (A).  
> 
> Taking a stored procedure that runs fine on A to B as part of a
> query (a function really I guess?) produces this error:
> 
> DBD::Oracle::db prepare failed: ORA-06571: Function GET_EVENT does not
> guarantee not to update database 
> 
> Any idea what causes this and how to fix it?
> 
> B may be a version behind A.  A is 8i.
> 
> 
> 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
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Steve Sapovits
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve Sapovits
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).



RE: procedure error

2001-07-19 Thread Steve Sapovits


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

> -Original Message-
> From: Steve Sapovits [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, July 19, 2001 6:11 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  procedure error
> 
> 
> I have a DB instance (B) that's a snapshot of another (A).  
> 
> Taking a stored procedure that runs fine on A to B as part of a
> query (a function really I guess?) produces this error:
> 
> DBD::Oracle::db prepare failed: ORA-06571: Function GET_EVENT does not
> guarantee not to update database 
> 
> Any idea what causes this and how to fix it?
> 
> B may be a version behind A.  A is 8i.
> 
> 
> 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
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Steve Sapovits
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve Sapovits
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).



RE: procedure error

2001-06-11 Thread Venkata Ramana Kanchinadam

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) - 3730321, 3746122, 3745051 Ext: 228,219
Fax:(91-40) - 3746419.
Email : [EMAIL PROTECTED]


> --
> From: Bunyamin K. Karadeniz[SMTP:[EMAIL PROTECTED]]
> Reply To: [EMAIL PROTECTED]
> Sent: Monday, June 11, 2001 3:45 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  procedure error
> 
> Dear Gurus.
> I created a procedure and this procedure takes parameters and then makes
> and alter table DDL on the database.
> But when I write 
> exec pp(table_name1,field,table_name2);
> Gives error.
> ERROR at line 1:
> ORA-06550: line 1, column 10:
> PLS-00357: Table,View Or Sequence reference 'K_CT_IST_KTP_SYF_ALN' not
> allowed in this context
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> 
> Here is the source 
>  
> create or replace procedure pp(table_name in varchar2,field in   
>  varchar2,pk_table_name in varchar2) is 
>  cursor_name  INTEGER;
>  rows_processed  INTEGER;
>  begin
> temp:='ALTER TABLE '||table_name||' ADD CONSTRAINT '||table_name||'_FK'||'
> FOREIGN KEY ('||field||') REFERENCES '|| pk_table_name||'('||field||')';
> dbms_output.put_line(temp);
> dbms_sql.parse(cursor_name, 'ALTER TABLE '||table_name||' ADD CONSTRAINT
> '||table_name||'_FK'||' FOREIGN KEY ('||field||') REFERENCES '||
> pk_table_name||'('||field||')', dbms_sql.native);
> 
> rows_processed := dbms_sql.execute(cursor_name);
> dbms_sql.close_cursor(cursor_name);
> end;
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Venkata Ramana Kanchinadam
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).