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; /
procedure error
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).
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).
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 contextORA-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; begintemp:='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;
RE: procedure error
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
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
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
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
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
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
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
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
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
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
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
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
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).
PL/SQL procedure - error
Hallo, anyone who can help me with a pl/sql procedure that I have four procedures that will run one after one: in an error table logs the procedurename, the start time of the procedure and the end time of the procedure.the number of rows inserted(there are insertstatements in the procedures), error code(if anything goes wrong) and error-message. Let us say that procedure 1 goes fine and the next procedure(nr 2) fails. Then I want to run rollback so that no data were inserted by procedure one at all, so it should be a rollback, so no data at all were inserted by any previous procedure. Please help me with some pl/sql code on this: Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: PL/SQL procedure - error
hi Roland Hope below code will help u, BEGIN p1; p2; p3; p4; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; procedure p1 is BEGIN insert into aa values (1,'1'); END; procedure p2 is BEGIN insert into aa values (2,'2'); END; procedure p3 is BEGIN insert into aa values (3,'3'); END; procedure p4 is BEGIN insert into aa values (4,'4'); END; Nitheesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pullikol Kumar 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).
Ang: Re: PL/SQL procedure - error
Thanks, and can you please tell me how to get hold of the time when the procedures starts and finishes.? Thanks in advance. Roland "Pullikol Kumar" <[EMAIL PROTECTED]>@fatcity.com den 2002-01-22 04:40 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: hi Roland Hope below code will help u, BEGIN p1; p2; p3; p4; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; procedure p1 is BEGIN insert into aa values (1,'1'); END; procedure p2 is BEGIN insert into aa values (2,'2'); END; procedure p3 is BEGIN insert into aa values (3,'3'); END; procedure p4 is BEGIN insert into aa values (4,'4'); END; Nitheesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pullikol Kumar 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: 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: Ang: Re: PL/SQL procedure - error
Use SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS FROM dual; Rick Roland.Skoldb [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: root@fatcity.Subject: Ang: Re: PL/SQL procedure - error com 01/22/2002 09:15 AM Please respond to ORACLE-L Thanks, and can you please tell me how to get hold of the time when the procedures starts and finishes.? Thanks in advance. Roland "Pullikol Kumar" <[EMAIL PROTECTED]>@fatcity.com den 2002-01-22 04:40 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: hi Roland Hope below code will help u, BEGIN p1; p2; p3; p4; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; procedure p1 is BEGIN insert into aa values (1,'1'); END; procedure p2 is BEGIN insert into aa values (2,'2'); END; procedure p3 is BEGIN insert into aa values (3,'3'); END; procedure p4 is BEGIN insert into aa values (4,'4'); END; Nitheesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pullikol Kumar 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: 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: 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).
Ang: Re: Ang: Re: PL/SQL procedure - error
But which query asks for the start date of the procedure an dwhich selectstatement is to display th etime when th eprocedure starts. Roland [EMAIL PROTECTED]@fatcity.com den 2002-01-22 08:25 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: Use SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS FROM dual; Rick Roland.Skoldb [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: root@fatcity.Subject: Ang: Re: PL/SQL procedure - error com 01/22/2002 09:15 AM Please respond to ORACLE-L Thanks, and can you please tell me how to get hold of the time when the procedures starts and finishes.? Thanks in advance. Roland "Pullikol Kumar" <[EMAIL PROTECTED]>@fatcity.com den 2002-01-22 04:40 PST Sänd svar till [EMAIL PROTECTED] Sänt av: [EMAIL PROTECTED] Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Kopia: hi Roland Hope below code will help u, BEGIN p1; p2; p3; p4; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; procedure p1 is BEGIN insert into aa values (1,'1'); END; procedure p2 is BEGIN insert into aa values (2,'2'); END; procedure p3 is BEGIN insert into aa values (3,'3'); END; procedure p4 is BEGIN insert into aa values (4,'4'); END; Nitheesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pullikol Kumar 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: 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: 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: 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: Re: Ang: Re: PL/SQL procedure - error
Just put the select before the activation of each procedure in your program and after the last one: BEGIN SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS') FROM dual; p1; SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS') FROM dual; p2; SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS') FROM dual; p3; SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS') FROM dual; p4; SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS') FROM dual; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -Original Message- > From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] > Sent: Thu, January 24, 2002 3:00 PM > To: Multiple recipients of list ORACLE-L > Subject: Ang: Re: Ang: Re: PL/SQL procedure - error > > > But which query asks for the start date of the procedure an dwhich > selectstatement is to display th etime when th eprocedure starts. > > Roland > > > > > > [EMAIL PROTECTED]@fatcity.com den 2002-01-22 08:25 PST > > Sänd svar till [EMAIL PROTECTED] > > Sänt av: [EMAIL PROTECTED] > > > Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Kopia: > > > Use SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS FROM dual; > > Rick > > > > Roland.Skoldb > [EMAIL PROTECTED] To: Multiple recipients of > list ORACLE-L <[EMAIL PROTECTED]> > Sent by: cc: > root@fatcity.Subject: Ang: Re: PL/SQL > procedure - error > com > > > 01/22/2002 > 09:15 AM > Please > respond to > ORACLE-L > > > > > > > > Thanks, > > and can you please tell me how to get hold of the time when the procedures > starts and finishes.? > > Thanks in advance. > > > Roland > > > > > > > "Pullikol Kumar" <[EMAIL PROTECTED]>@fatcity.com den 2002-01-22 > 04:40 PST > > Sänd svar till [EMAIL PROTECTED] > > Sänt av: [EMAIL PROTECTED] > > > Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Kopia: > > > hi Roland > > Hope below code will help u, > > BEGIN > p1; > p2; > p3; > p4; > COMMIT; > EXCEPTION > WHEN OTHERS THEN > ROLLBACK; > END; > > procedure p1 > is > BEGIN > insert into aa values (1,'1'); > END; > procedure p2 > is > BEGIN > insert into aa values (2,'2'); > END; > procedure p3 > is > BEGIN > insert into aa values (3,'3'); > END; > > procedure p4 > is > BEGIN > insert into aa values (4,'4'); > END; > > Nitheesh > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Pullikol Kumar > 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: > 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: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California
Re: Ang: Re: Ang: Re: PL/SQL procedure - error
I believe you will need to code it into your procedure i.e. beginning of proc get start time (via sql below) do work get end time then either store the elapsed time somewhere, or print it out. -Joe --- [EMAIL PROTECTED] wrote: > > But which query asks for the start date of the procedure an dwhich > selectstatement is to display th etime when th eprocedure starts. > > Roland > > > > > > [EMAIL PROTECTED]@fatcity.com den 2002-01-22 08:25 PST > > Sänd svar till [EMAIL PROTECTED] > > Sänt av: [EMAIL PROTECTED] > > > Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Kopia: > > > Use SELECT TO_CHAR(sysdate,'MM/DD/ HH:MI:SS FROM dual; > > Rick > > > > Roland.Skoldb > [EMAIL PROTECTED] To: Multiple > recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent by: cc: > root@fatcity.Subject: Ang: Re: > PL/SQL procedure - error > com > > > 01/22/2002 > 09:15 AM > Please > respond to > ORACLE-L > > > > > > > > Thanks, > > and can you please tell me how to get hold of the time when the > procedures > starts and finishes.? > > Thanks in advance. > > > Roland > > > > > > > "Pullikol Kumar" <[EMAIL PROTECTED]>@fatcity.com den > 2002-01-22 > 04:40 PST > > Sänd svar till [EMAIL PROTECTED] > > Sänt av: [EMAIL PROTECTED] > > > Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Kopia: > > > hi Roland > > Hope below code will help u, > > BEGIN > p1; > p2; > p3; > p4; > COMMIT; > EXCEPTION > WHEN OTHERS THEN > ROLLBACK; > END; > > procedure p1 > is > BEGIN > insert into aa values (1,'1'); > END; > procedure p2 > is > BEGIN > insert into aa values (2,'2'); > END; > procedure p3 > is > BEGIN > insert into aa values (3,'3'); > END; > > procedure p4 > is > BEGIN > insert into aa values (4,'4'); > END; > > Nitheesh > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Pullikol Kumar > 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: > 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: > 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: > INET: [EMAIL PROTECTED] > > Fat City Network Service
