RE: what's wrong with this...DBMS_SQL
Igor Oops, I stand corrected - works on 722, 734 816. Well, at least I've learnt something today. Regards David Lord -Original Message- Sent: 12 June 2001 17:48 To: Multiple recipients of list ORACLE-L Did you try it? I am not sure about v7. But under 8.1.5 it works. And why 'parse' should necessarily use 'dbms_sql.v7' ? Here is an example, which proves, that for DDL it's enough to call 'parse' without 'execute': SQLWKS declare cur1 integer; 2 begin 3 cur1 := dbms_sql.open_cursor; 4 dbms_sql.parse(cur1,'create table ddd(d1 int)', dbms_sql.native); 5 dbms_sql.close_cursor(cur1); 6 end; 7 / Statement processed. SQLWKS select * from ddd; D1 -- 0 rows selected. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, June 12, 2001 10:52 AM Parse doesn't execute DDL in oracle 7. The parse call uses 'dbms_sql.v7' so I would guess that even if the d/b is 8 it will use the 7 semantics. Regards David Lord -Original Message- Sent: 12 June 2001 14:31 To: Multiple recipients of list ORACLE-L Lisa, You don't need DBMS_SQL.EXECUTE, when running other then DML statements. DBMS_SQL.PARSE is enough. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L Sent: Monday, June 11, 2001 5:55 PM Surendra, you need to execute it. Look at the spec of dbms_sql (I want to say it's dbms_sql.execute()). Either that or use EXECUTE IMMEDIATE. Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- Sent: Monday, June 11, 2001 4:55 PM To: Multiple recipients of list ORACLE-L Hi List, As the part of developing hot backup strategy, I am testing the following piece of code to take care of arch files to be made avaialable along with backup set. = SQL create or replace procedure log_switch as 2 cursor1 integer; 3 begin 4 cur1 := dbms_sql.open_cursor; 5 dbms_sql.parse(cur1,'ALTER SYSTEM SWITCH LOGFILE', dbms_sql.v7); 6 dbms_sql.close_cursor(cur1); 7 end; 8 / Procedure created. SQL execute log_switch; PL/SQL procedure successfully completed. == But, I have observed several times that the above code is not forcing a log switch. FYI, I want to track the sequence# of logfiles before and after the hot backup, so that I can backup the files in that range. Am I properly using the DBMS_SQL? Any help is highly appreciated TIA, Suren Oracle DBA Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tirumala, Surendra 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). ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord David INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Re: what's wrong with this...DBMS_SQL
Title: RE: what's wrong with this...DBMS_SQL
DBAs
I have tried to pin package into memory and I got
those message. what means for pls-00201 error here.
Mitchel
execute dbms_shared_pool.keep('SYS.STANDARD');
BEGIN dbms_shared_pool.keep('SYS.STANDARD');
END;
*ERROR at line
1:ORA-06550: line 1, column 7:PLS-00201: identifier
'DBMS_SHARED_POOL.KEEP' must be declaredORA-06550: line 1, column
7:PL/SQL: Statement ignored
RE: what's wrong with this...DBMS_SQL
Mitchel,
I think you need to run dbmspool.sql under $ORACLE_HOME/rdbms/admin as sys
to create this package first.
HTH
Prakash
-Original Message-
Sent: Wednesday, June 13, 2001 12:32 PM
To: Multiple recipients of list ORACLE-L
DBAs
I have tried to pin package into memory and I got those message. what means
for pls-00201 error here.
Mitchel
execute dbms_shared_pool.keep('SYS.STANDARD');
BEGIN dbms_shared_pool.keep('SYS.STANDARD'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bala, Prakash
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: what's wrong with this...DBMS_SQL
Title: RE: what's wrong with this...DBMS_SQL Lisa, You don't need DBMS_SQL.EXECUTE, when running other then DML statements. DBMS_SQL.PARSE is enough. Igor Neyman, OCP DBAPerceptron, Inc.(734)414-4627[EMAIL PROTECTED] - Original Message - From: Koivu, Lisa To: Multiple recipients of list ORACLE-L Sent: Monday, June 11, 2001 5:55 PM Subject: RE: what's wrong with this...DBMS_SQL Surendra, you need to execute it. Look at the spec of dbms_sql (I want to say it's dbms_sql.execute()). Either that or use EXECUTE IMMEDIATE. Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Tirumala, Surendra [SMTP:[EMAIL PROTECTED]] Sent: Monday, June 11, 2001 4:55 PM To: Multiple recipients of list ORACLE-L Subject: what's wrong with this...DBMS_SQL Hi List, As the part of developing hot backup strategy, I am testing the following piece of code to take care of arch files to be made avaialable along with backup set. = SQL create or replace procedure log_switch as 2 cursor1 integer; 3 begin 4 cur1 := dbms_sql.open_cursor; 5 dbms_sql.parse(cur1,'ALTER SYSTEM SWITCH LOGFILE', dbms_sql.v7); 6 dbms_sql.close_cursor(cur1); 7 end; 8 / Procedure created. SQL execute log_switch; PL/SQL procedure successfully completed. == But, I have observed several times that the above code is not forcing a log switch. FYI, I want to track the sequence# of logfiles before and after the hot backup, so that I can backup the files in that range. Am I properly using the DBMS_SQL? Any help is highly appreciated TIA, Suren Oracle DBA Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tirumala, Surendra 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: what's wrong with this...DBMS_SQL
Title: RE: what's wrong with this...DBMS_SQL Parse doesn't execute DDL in oracle 7. The parse call uses 'dbms_sql.v7' so I would guess that even if the d/b is 8 it will use the 7 semantics. Regards David Lord -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]Sent: 12 June 2001 14:31To: Multiple recipients of list ORACLE-LSubject: Re: what's wrong with this...DBMS_SQL Lisa, You don't need DBMS_SQL.EXECUTE, when running other then DML statements. DBMS_SQL.PARSE is enough. Igor Neyman, OCP DBAPerceptron, Inc.(734)414-4627[EMAIL PROTECTED] - Original Message - From: Koivu, Lisa To: Multiple recipients of list ORACLE-L Sent: Monday, June 11, 2001 5:55 PM Subject: RE: what's wrong with this...DBMS_SQL Surendra, you need to execute it. Look at the spec of dbms_sql (I want to say it's dbms_sql.execute()). Either that or use EXECUTE IMMEDIATE. Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Tirumala, Surendra [SMTP:[EMAIL PROTECTED]] Sent: Monday, June 11, 2001 4:55 PM To: Multiple recipients of list ORACLE-L Subject: what's wrong with this...DBMS_SQL Hi List, As the part of developing hot backup strategy, I am testing the following piece of code to take care of arch files to be made avaialable along with backup set. = SQL create or replace procedure log_switch as 2 cursor1 integer; 3 begin 4 cur1 := dbms_sql.open_cursor; 5 dbms_sql.parse(cur1,'ALTER SYSTEM SWITCH LOGFILE', dbms_sql.v7); 6 dbms_sql.close_cursor(cur1); 7 end; 8 / Procedure created. SQL execute log_switch; PL/SQL procedure successfully completed. == But, I have observed several times that the above code is not forcing a log switch. FYI, I want to track the sequence# of logfiles before and after the hot backup, so that I can backup the files in that range. Am I properly using the DBMS_SQL? Any help is highly appreciated TIA, Suren Oracle DBA Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tirumala, Surendra 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: what's wrong with this...DBMS_SQL
Igor, read the docs more carefully. it states that you should do the execute because the parse is NOT guaranteed to work that way in the future. Do does it work without the execute, yes, will in work in the future, maybe, just do the execute and don't worry about it :) right out of $ORACLE_HOME/rdbms/admin/dbmssql.sql procedure parse(c in integer, statement in varchar2, language_flag in integer); -- Parse the given statement in the given cursor. NOTE THAT PARSING AND -- EXECUTING DDL STATEMENTS CAN CAUSE HANGS! -- Currently, the deferred parsing feature of the Oracle Call Interface is not used. As a result, statements are parsed immediately. In addition, DDL statements are executed immediately when parsed. However,the behavior may change in the future so that the actual parsing (and execution of DDL statement) do not occur until the cursor is executed with execute. -- DO NOT RELY ON THE CURRENT TIMING OF THE ACTUAL PARSING! Joe From: Koivu, Lisa [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: what's wrong with this...DBMS_SQL Date: Tue, 12 Jun 2001 06:46:38 -0800 MIME-Version: 1.0 Received: from [209.68.248.164] by hotmail.com (3.2) with ESMTP id MHotMailBCEF6F660008400431CBD144F8A40A0E0; Tue Jun 12 07:17:11 2001 Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id HAA23891;Tue, 12 Jun 2001 07:16:28 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b70/bab) via UUCP id 00325649; Tue, 12 Jun 2001 06:46:38 -0800 From [EMAIL PROTECTED] Tue Jun 12 07:18:28 2001 Message-ID: [EMAIL PROTECTED] X-Comment: Oracle RDBMS Community Forum X-Sender: Koivu, Lisa [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Errors-To: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 70; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Thanks Igor for setting me straight. I don't see that in the doco but I am looking at 8.1.7. -Original Message- From: Igor Neyman [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, June 12, 2001 9:31 AM To: Multiple recipients of list ORACLE-L Subject:Re: what's wrong with this...DBMS_SQL Lisa, You don't need DBMS_SQL.EXECUTE, when running other then DML statements. DBMS_SQL.PARSE is enough. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] - Original Message - From: Koivu, Lisa mailto:[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Monday, June 11, 2001 5:55 PM Subject: RE: what's wrong with this...DBMS_SQL Surendra, you need to execute it. Look at the spec of dbms_sql (I want to say it's dbms_sql.execute()). Either that or use EXECUTE IMMEDIATE. Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Tirumala, Surendra [SMTP:[EMAIL PROTECTED]] Sent: Monday, June 11, 2001 4:55 PM To: Multiple recipients of list ORACLE-L Subject:what's wrong with this...DBMS_SQL Hi List, As the part of developing hot backup strategy, I am testing the following piece of code to take care of arch files to be made avaialable along with backup set. = SQL create or replace procedure log_switch as 2 cursor1 integer; 3 begin 4 cur1 := dbms_sql.open_cursor; 5 dbms_sql.parse(cur1,'ALTER SYSTEM SWITCH LOGFILE', dbms_sql.v7); 6 dbms_sql.close_cursor(cur1); 7 end; 8 / Procedure created. SQL execute log_switch; PL/SQL procedure successfully completed. == But, I have observed several
RE: what's wrong with this...DBMS_SQL
But using DBMS_SQL.EXECUTE only solved my problem, Suren -Original Message- Sent: Tuesday, June 12, 2001 10:47 AM To: Multiple recipients of list ORACLE-L Thanks Igor for setting me straight. I don't see that in the doco but I am looking at 8.1.7. -Original Message- Sent: Tuesday, June 12, 2001 9:31 AM To: Multiple recipients of list ORACLE-L Lisa, You don't need DBMS_SQL.EXECUTE, when running other then DML statements. DBMS_SQL.PARSE is enough. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] - Original Message - mailto:[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Sent: Monday, June 11, 2001 5:55 PM Surendra, you need to execute it. Look at the spec of dbms_sql (I want to say it's dbms_sql.execute()). Either that or use EXECUTE IMMEDIATE. Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- Sent: Monday, June 11, 2001 4:55 PM To: Multiple recipients of list ORACLE-L Hi List, As the part of developing hot backup strategy, I am testing the following piece of code to take care of arch files to be made avaialable along with backup set. = SQL create or replace procedure log_switch as 2 cursor1 integer; 3 begin 4 cur1 := dbms_sql.open_cursor; 5 dbms_sql.parse(cur1,'ALTER SYSTEM SWITCH LOGFILE', dbms_sql.v7); 6 dbms_sql.close_cursor(cur1); 7 end; 8 / Procedure created. SQL execute log_switch; PL/SQL procedure successfully completed. == But, I have observed several times that the above code is not forcing a log switch. FYI, I want to track the sequence# of logfiles before and after the hot backup, so that I can backup the files in that range. Am I properly using the DBMS_SQL? Any help is highly appreciated TIA, Suren Oracle DBA Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Tirumala, Surendra 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: Tirumala, Surendra 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: what's wrong with this...DBMS_SQL
Title: RE: what's wrong with this...DBMS_SQL Did you try it? Iam not sure about v7. But under 8.1.5 it works. And why 'parse' should necessarily use 'dbms_sql.v7' ? Here is an example, which proves, that for DDLit's enough to call 'parse' without 'execute': SQLWKS declare cur1 integer; 2 begin 3 cur1 := dbms_sql.open_cursor; 4 dbms_sql.parse(cur1,'create table ddd(d1 int)', dbms_sql.native); 5 dbms_sql.close_cursor(cur1); 6 end; 7 / Statement processed. SQLWKS select * from ddd; D1 -- 0 rows selected. Igor Neyman, OCP DBAPerceptron, Inc.(734)414-4627[EMAIL PROTECTED] - Original Message - From: Lord David To: Multiple recipients of list ORACLE-L Sent: Tuesday, June 12, 2001 10:52 AM Subject: RE: what's wrong with this...DBMS_SQL Parse doesn't execute DDL in oracle 7. The parse call uses 'dbms_sql.v7' so I would guess that even if the d/b is 8 it will use the 7 semantics. Regards David Lord -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]Sent: 12 June 2001 14:31To: Multiple recipients of list ORACLE-LSubject: Re: what's wrong with this...DBMS_SQL Lisa, You don't need DBMS_SQL.EXECUTE, when running other then DML statements. DBMS_SQL.PARSE is enough. Igor Neyman, OCP DBAPerceptron, Inc.(734)414-4627[EMAIL PROTECTED] - Original Message - From: Koivu, Lisa To: Multiple recipients of list ORACLE-L Sent: Monday, June 11, 2001 5:55 PM Subject: RE: what's wrong with this...DBMS_SQL Surendra, you need to execute it. Look at the spec of dbms_sql (I want to say it's dbms_sql.execute()). Either that or use EXECUTE IMMEDIATE. Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Tirumala, Surendra [SMTP:[EMAIL PROTECTED]] Sent: Monday, June 11, 2001 4:55 PM To: Multiple recipients of list ORACLE-L Subject: what's wrong with this...DBMS_SQL Hi List, As the part of developing hot backup strategy, I am testing the following piece of code to take care of arch files to be made avaialable along with backup set. = SQL create or replace procedure log_switch as 2 cursor1 integer; 3 begin 4 cur1 := dbms_sql.open_cursor; 5 dbms_sql.parse(cur1,'ALTER SYSTEM SWITCH LOGFILE', dbms_sql.v7); 6 dbms_sql.close_cursor(cur1); 7 end; 8 / Procedure created. SQL execute log_switch; PL/SQL procedure successfully completed. == But, I have observed several times that the above code is not forcing a log switch. FYI, I want to track the sequence# of logfiles before and after the hot backup, so that I can backup the files in that range. Am I properly using the DBMS_SQL? Any help is highly appreciated TIA, Suren Oracle DBA Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tirumala, Surendra 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: what's wrong with this...DBMS_SQL
Agreed, I should have stated, that it works with current version and might behave differently in future versions. Though this is not the only feature, which can change in the future, so thorough testing when upgrading is required anyway. And oracle does not always say in docs, what could change in future releases. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 12, 2001 11:26 AM Igor, read the docs more carefully. it states that you should do the execute because the parse is NOT guaranteed to work that way in the future. Do does it work without the execute, yes, will in work in the future, maybe, just do the execute and don't worry about it :) right out of $ORACLE_HOME/rdbms/admin/dbmssql.sql procedure parse(c in integer, statement in varchar2, language_flag in integer); -- Parse the given statement in the given cursor. NOTE THAT PARSING AND -- EXECUTING DDL STATEMENTS CAN CAUSE HANGS! -- Currently, the deferred parsing feature of the Oracle Call Interface is not used. As a result, statements are parsed immediately. In addition, DDL statements are executed immediately when parsed. However,the behavior may change in the future so that the actual parsing (and execution of DDL statement) do not occur until the cursor is executed with execute. -- DO NOT RELY ON THE CURRENT TIMING OF THE ACTUAL PARSING! Joe From: Koivu, Lisa [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: what's wrong with this...DBMS_SQL Date: Tue, 12 Jun 2001 06:46:38 -0800 MIME-Version: 1.0 Received: from [209.68.248.164] by hotmail.com (3.2) with ESMTP id MHotMailBCEF6F660008400431CBD144F8A40A0E0; Tue Jun 12 07:17:11 2001 Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id HAA23891;Tue, 12 Jun 2001 07:16:28 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b70/bab) via UUCP id 00325649; Tue, 12 Jun 2001 06:46:38 -0800 From [EMAIL PROTECTED] Tue Jun 12 07:18:28 2001 Message-ID: [EMAIL PROTECTED] X-Comment: Oracle RDBMS Community Forum X-Sender: Koivu, Lisa [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Errors-To: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 70; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Thanks Igor for setting me straight. I don't see that in the doco but I am looking at 8.1.7. -Original Message- From: Igor Neyman [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, June 12, 2001 9:31 AM To: Multiple recipients of list ORACLE-L Subject: Re: what's wrong with this...DBMS_SQL Lisa, You don't need DBMS_SQL.EXECUTE, when running other then DML statements. DBMS_SQL.PARSE is enough. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] - Original Message - From: Koivu, Lisa mailto:[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Monday, June 11, 2001 5:55 PM Subject: RE: what's wrong with this...DBMS_SQL Surendra, you need to execute it. Look at the spec of dbms_sql (I want to say it's dbms_sql.execute()). Either that or use EXECUTE IMMEDIATE. Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Tirumala, Surendra [SMTP:[EMAIL PROTECTED]] Sent: Monday, June 11, 2001 4:55 PM To: Multiple recipients of list ORACLE-L Subject:what's wrong with this...DBMS_SQL Hi List, As the part of developing hot backup strategy, I am testing the following piece of code to take care of arch files to be made
Re: what's wrong with this...DBMS_SQL
On Tuesday 12 June 2001 06:31, Igor Neyman wrote: RE: what's wrong with this...DBMS_SQLLisa, You don't need DBMS_SQL.EXECUTE, when running other then DML statements. DBMS_SQL.PARSE is enough. Oracle has stated that even though DBMS_SQL.PARSE is currently sufficient for non DML statements, this behavior will change and you should use DBMS_SQL.EXECUTE in your code. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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: what's wrong with this...DBMS_SQL
Title: RE: what's wrong with this...DBMS_SQL Surendra, you need to execute it. Look at the spec of dbms_sql (I want to say it's dbms_sql.execute()). Either that or use EXECUTE IMMEDIATE. Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Tirumala, Surendra [SMTP:[EMAIL PROTECTED]] Sent: Monday, June 11, 2001 4:55 PM To: Multiple recipients of list ORACLE-L Subject: what's wrong with this...DBMS_SQL Hi List, As the part of developing hot backup strategy, I am testing the following piece of code to take care of arch files to be made avaialable along with backup set. = SQL create or replace procedure log_switch as 2 cursor1 integer; 3 begin 4 cur1 := dbms_sql.open_cursor; 5 dbms_sql.parse(cur1,'ALTER SYSTEM SWITCH LOGFILE', dbms_sql.v7); 6 dbms_sql.close_cursor(cur1); 7 end; 8 / Procedure created. SQL execute log_switch; PL/SQL procedure successfully completed. == But, I have observed several times that the above code is not forcing a log switch. FYI, I want to track the sequence# of logfiles before and after the hot backup, so that I can backup the files in that range. Am I properly using the DBMS_SQL? Any help is highly appreciated TIA, Suren Oracle DBA Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tirumala, Surendra 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: what's wrong with this...DBMS_SQL
The DBMS_SQL is executed inside a package, so the owner of the package must be granted the ability to switch log files directly. More than likely, he only has this privilege through a role. Brian Norrell Manager, MPI Development QuadraMed 511 E John Carpenter Frwy, Su 500 Irving, TX 75062 (972) 831-6600 -Original Message- Sent: Monday, June 11, 2001 3:55 PM To: Multiple recipients of list ORACLE-L Hi List, As the part of developing hot backup strategy, I am testing the following piece of code to take care of arch files to be made avaialable along with backup set. = SQL create or replace procedure log_switch as 2 cursor1 integer; 3 begin 4 cur1 := dbms_sql.open_cursor; 5 dbms_sql.parse(cur1,'ALTER SYSTEM SWITCH LOGFILE', dbms_sql.v7); 6 dbms_sql.close_cursor(cur1); 7 end; 8 / Procedure created. SQL execute log_switch; PL/SQL procedure successfully completed. == But, I have observed several times that the above code is not forcing a log switch. FYI, I want to track the sequence# of logfiles before and after the hot backup, so that I can backup the files in that range. Am I properly using the DBMS_SQL? Any help is highly appreciated TIA, Suren Oracle DBA Sony -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tirumala, Surendra 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: Norrell, Brian 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).
