RE: Rollback after truncate?? + 2 more questions :-)
Zsolt, The correct call for the function if the function exists within the package would be: Insert into A ( Select Col1 ,PackageName.MyFunction(Col1) from B ); Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 15, 2001 6:44 PM To: Multiple recipients of list ORACLE-L 1. No. 2. I do not think its a function of the number of words. DDL needs the Execute Immediate. 3. Don't Know. Need to see the function. -Original Message- Sent: Wednesday, August 15, 2001 5:08 PM To: Multiple recipients of list ORACLE-L Hi, 1.Can I rollback after doing a truncate table (it seems to me that I can' t) e.g. Execute Immediate 'Truncate Table '|| TablaNevTomb(i); Execute Immediate 'Begin '|| 'Insert into '|| TablaNevTomb(i) ||' (Select * From X$_'||TablaNevTomb(i)||');'|| 'End;'; Exception When others Then RollBack 2. Is there a way to use a command with more than one word in Pl/Sql without using it dynamically? For example I can use Truncate table only with Execute immediate within a Pl/Sql block. 3. I have a statement in a package: Insert into A ( Select Col1 ,MyFunction(Col1) from B ); If the MyFunction function is made with create or replace function everything works perfectly, but if the same function is declared within the package, I get the error message: PLS-00231 function 'string' may not be used in SQL Thank you for your help Zsolt Csillag Hungary -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Csillag Zsolt 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: Kevin Lange 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: Mercadante, Thomas F 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: Rollback after truncate?? + 2 more questions :-)
Tom, Thank you for your answer - it has solved my problem. At 05:11 2001.08.16. -0800, you wrote: Zsolt, The correct call for the function if the function exists within the package would be: Insert into A ( Select Col1 ,PackageName.MyFunction(Col1) from B ); Tom Mercadante Oracle Certified Professional Zsolt Csillag Hungary -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Csillag Zsolt 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: Rollback after truncate?? + 2 more questions :-)
1.Can I rollback after doing a truncate table (it seems to me that I can' t) The truncate command is DDL (like create) and not DML (like delete). All DDL commit work as part of their processing. So if you did some work before issuing a truncate command and then tried to rollback after the truncate command, there would be nothing to rollback since everything had been committed. 2. Is there a way to use a command with more than one word in Pl/Sql without using it dynamically? I do not understand the question. 3. I have a statement in a package: Insert into A ( Select Col1 ,MyFunction(Col1) from B ); If the MyFunction function is made with create or replace function everything works perfectly, but if the same function is declared within the package, I get the error message: PLS-00231 function 'string' may not be used in SQL You have create it using the same syntax as you did for the truncate command, since you are issuing a DDL statement instead of a DML statement. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Page, Bruce 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: Rollback after truncate?? + 2 more questions :-)
1. No. 2. I do not think its a function of the number of words. DDL needs the Execute Immediate. 3. Don't Know. Need to see the function. -Original Message- Sent: Wednesday, August 15, 2001 5:08 PM To: Multiple recipients of list ORACLE-L Hi, 1.Can I rollback after doing a truncate table (it seems to me that I can' t) e.g. Execute Immediate 'Truncate Table '|| TablaNevTomb(i); Execute Immediate 'Begin '|| 'Insert into '|| TablaNevTomb(i) ||' (Select * From X$_'||TablaNevTomb(i)||');'|| 'End;'; Exception When others Then RollBack 2. Is there a way to use a command with more than one word in Pl/Sql without using it dynamically? For example I can use Truncate table only with Execute immediate within a Pl/Sql block. 3. I have a statement in a package: Insert into A ( Select Col1 ,MyFunction(Col1) from B ); If the MyFunction function is made with create or replace function everything works perfectly, but if the same function is declared within the package, I get the error message: PLS-00231 function 'string' may not be used in SQL Thank you for your help Zsolt Csillag Hungary -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Csillag Zsolt 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: Kevin Lange 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).
