RE: Help with a truncate command in a procedure
This is it create or replace PROCEDURE truncate_table(nom_de_table in varchar2) AUTHID DEFINER is total INTEGER ; trouve EXCEPTION ; cursor_name INTEGER ; ret INTEGER ; trun VARCHAR2(30) := 'truncate table ' || nom_de_table ; BEGIN select 1 intototal fromuser_tables where table_name = upper(nom_de_table); Raise TROUVE; EXCEPTION When TROUVE then cursor_name := DBMS_SQL.OPEN_CURSOR ; DBMS_SQL.PARSE(cursor_name, trun, DBMS_SQL.native); ret := DBMS_SQL.EXECUTE(cursor_name) ; DBMS_SQL.CLOSE_CURSOR(cursor_name); When others then raise_application_error(-20011,'*** La table '||nom_de_table||' n a pas d autorisation ! ***'); END ; / -Message d'origine- De: Smith, Ron L. [mailto:[EMAIL PROTECTED]] Date: vendredi 7 février 2003 15:09 À: Multiple recipients of list ORACLE-L Objet: RE: Help with a truncate command in a procedure Thanks for the help! Ron -Original Message- Sent: Thursday, February 06, 2003 5:49 PM To: Multiple recipients of list ORACLE-L (pant pant) Will I be the first to say that you need to use dynamic SQL? dbms_sql package in Oracle version < 8.1 execute immediate in Oracle version >= 8.1 -Original Message- I am not a coder but I received this from one of our developers. I can't find anything about this anywhere. Can someone tell me how to make the truncate work? Thanks! R.Smith In a DB procedure, I wanted to include the following SQL: BEGIN TRUNCATE TABLE LOT837_GLOBAL_TBL_KMG; END; Error messages: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "TABLE" to continue. (It does not like it, if you take out TABLE, either.) So, I had to settle for the following SQL: BEGIN DELETE LOT837_GLOBAL_TBL_KMG; END; Do you know why I can not use the TRUNCATE command? If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Help with a truncate command in a procedure
Title: Message Thanks for the help! Ron -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 06, 2003 5:49 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Help with a truncate command in a procedure (pant pant) Will I be the first to say that you need to use dynamic SQL? dbms_sql package in Oracle version < 8.1 execute immediate in Oracle version >= 8.1 -Original Message- From: Smith, Ron L. [mailto:[EMAIL PROTECTED]] I am not a coder but I received this from one of our developers. I can't find anything about this anywhere. Can someone tell me how to make the truncate work? Thanks! R.Smith In a DB procedure, I wanted to include the following SQL: BEGIN TRUNCATE TABLE LOT837_GLOBAL_TBL_KMG; END; Error messages: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "TABLE" to continue. (It does not like it, if you take out TABLE, either.) So, I had to settle for the following SQL: BEGIN DELETE LOT837_GLOBAL_TBL_KMG; END; Do you know why I can not use the TRUNCATE command? If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you.
Re: Help with a truncate command in a procedure
Ron, Trunacte is DDL not DML. The easiest was around this is to wrap it in an execute immediate command: BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE LOT837_GLOBAL_TBL_KMG'; END; / Note that the semi-colon is outside the quotes - don't place a semi-colon inside the quotes. Regards, Mark. "Smith, Ron L." To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> <[EMAIL PROTECTED] cc: om> Subject: Help with a truncate command in a procedure Sent by: [EMAIL PROTECTED] om 07/02/2003 09:08 Please respond to ORACLE-L I am not a coder but I received this from one of our developers. I can't find anything about this anywhere. Can someone tell me how to make the truncate work? Thanks! R.Smith In a DB procedure, I wanted to include the following SQL: BEGIN TRUNCATE TABLE LOT837_GLOBAL_TBL_KMG; END; Error messages: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "TABLE" to continue. (It does not like it, if you take out TABLE, either.) So, I had to settle for the following SQL: BEGIN DELETE LOT837_GLOBAL_TBL_KMG; END; Do you know why I can not use the TRUNCATE command? If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Help with a truncate command in a procedure
truncate is a sqlplus command(and DDL), so to do it, you need to use the dbms_sql(or execute immediate) command, i think. joe Smith, Ron L. wrote: I am not a coder but I received this from one of our developers. I can't find anything about this anywhere. Can someone tell me how to make the truncate work? Thanks! R.Smith In a DB procedure, I wanted to include the following SQL: BEGIN TRUNCATE TABLE LOT837_GLOBAL_TBL_KMG; END; _Error messages:_ PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "TABLE" to continue. (It does not like it, if you take out TABLE, either.) _So, I had to settle for the following SQL:_ BEGIN DELETE LOT837_GLOBAL_TBL_KMG; END; Do you know why I can not use the TRUNCATE command? If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Help with a truncate command in a procedure
Look up info on using "execute immediate" This will let you put non-DML-type statements in a PL/SQL block. -Original Message- I am not a coder but I received this from one of our developers. I can't find anything about this anywhere. Can someone tell me how to make the truncate work? Thanks! R.Smith In a DB procedure, I wanted to include the following SQL: BEGIN TRUNCATE TABLE LOT837_GLOBAL_TBL_KMG; END; Error messages: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "TABLE" to continue. (It does not like it, if you take out TABLE, either.) So, I had to settle for the following SQL: BEGIN DELETE LOT837_GLOBAL_TBL_KMG; END; Do you know why I can not use the TRUNCATE command? If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Help with a truncate command in a procedure
Title: RE: Help with a truncate command in a procedure (pant pant) Will I be the first to say that you need to use dynamic SQL? dbms_sql package in Oracle version < 8.1 execute immediate in Oracle version >= 8.1 -Original Message- From: Smith, Ron L. [mailto:[EMAIL PROTECTED]] I am not a coder but I received this from one of our developers. I can't find anything about this anywhere. Can someone tell me how to make the truncate work? Thanks! R.Smith In a DB procedure, I wanted to include the following SQL: BEGIN TRUNCATE TABLE LOT837_GLOBAL_TBL_KMG; END; Error messages: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "TABLE" to continue. (It does not like it, if you take out TABLE, either.) So, I had to settle for the following SQL: BEGIN DELETE LOT837_GLOBAL_TBL_KMG; END; Do you know why I can not use the TRUNCATE command?
RE: Help with a truncate command in a procedure
Title: Help with a truncate command in a procedure Truncate is a DDL. So you can't just do "truncate" like you would with delete which is a DML. You didn't say which version of Oracle you are running. If it's 8i, do execute immediate "truncate table ."; Otherwise, look into DBMS_SQL. Richard Ji -Original Message-From: Smith, Ron L. [mailto:[EMAIL PROTECTED]]Sent: Thursday, February 06, 2003 5:09 PMTo: Multiple recipients of list ORACLE-LSubject: Help with a truncate command in a procedure I am not a coder but I received this from one of our developers. I can't find anything about this anywhere. Can someone tell me how to make the truncate work? Thanks! R.Smith In a DB procedure, I wanted to include the following SQL: BEGIN TRUNCATE TABLE LOT837_GLOBAL_TBL_KMG; END; Error messages: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "TABLE" to continue. (It does not like it, if you take out TABLE, either.) So, I had to settle for the following SQL: BEGIN DELETE LOT837_GLOBAL_TBL_KMG; END; Do you know why I can not use the TRUNCATE command? If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you.
Help with a truncate command in a procedure
Title: Help with a truncate command in a procedure I am not a coder but I received this from one of our developers. I can't find anything about this anywhere. Can someone tell me how to make the truncate work? Thanks! R.Smith In a DB procedure, I wanted to include the following SQL: BEGIN TRUNCATE TABLE LOT837_GLOBAL_TBL_KMG; END; Error messages: PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "TABLE" to continue. (It does not like it, if you take out TABLE, either.) So, I had to settle for the following SQL: BEGIN DELETE LOT837_GLOBAL_TBL_KMG; END; Do you know why I can not use the TRUNCATE command? If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you.
