Home » SQL & PL/SQL » SQL & PL/SQL » truncate and insert
truncate and insert [message #439251] |
Fri, 15 January 2010 06:20  |
pyscho
Messages: 134 Registered: December 2009
|
Senior Member |
|
|
Firstly, I've got a truncate query within my stored procedure
TRUNCATE TABLE VF.TRANS;
Oracle gives me this error:
Quote:
PLS-00103: Encountered the symbol "TABLE" when expecting one of the following:
:= . ( @ % ;
The symbol ":= was inserted before "TABLE" to continue.
Secondly, can we not insert mulltiple values in 1 insert?
INSERT INTO INTEGRATION.TRANS ( CLIENTID, ACCIDID, CCY, VERID, AMT, ENTRY )
VALUES ( '12345', '12345~TAX', 'GBP', 'INC', '55', 'D'),
VALUES ( '12345', '12345~TAX', 'GBP', 'INC', '90', 'D');
|
|
|
Re: truncate and insert [message #439252 is a reply to message #439251] |
Fri, 15 January 2010 06:25   |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Truncate is a DDl statement so you cant use it directly inside the procedure...use 'execute immediate' for that matter...
you can insert multiple values at a time search in google for syntax...
|
|
|
|
|
Re: truncate and insert [message #439279 is a reply to message #439256] |
Fri, 15 January 2010 09:12   |
pyscho
Messages: 134 Registered: December 2009
|
Senior Member |
|
|
well this is what im trying to do
INSERT ALL
INTO INTEGRATION.CASH_TRANS ( CLIENT_ID, ACCOUNT_ID, TXN_CCY, LTY_ID, AMT, ENTRY_TYPE )
VALUES ('12345', '12345~TAX', 'GBP', 'INC', '55', 'D')
INTO INTEGRATION.CASH_TRANS ( CLIENT_ID, ACCOUNT_ID, TXN_CCY, LTY_ID, AMT, ENTRY_TYPE )
VALUES ('12345', '12345~TAX', 'GBP', 'INC', '60', 'D');
but that isnt compiling because it seems to be missing something
|
|
|
|
Re: truncate and insert [message #439285 is a reply to message #439282] |
Fri, 15 January 2010 09:22   |
pyscho
Messages: 134 Registered: December 2009
|
Senior Member |
|
|
ERROR line 651, col 59, ending_line 651, ending_col 59, Found ';', Expecting: ( SELECT -or- INTO WITH
which is erroring on the last line
VALUES ('12345', '12345~TAX', 'GBP', 'INC', '60', 'D');
|
|
|
Re: truncate and insert [message #439286 is a reply to message #439279] |
Fri, 15 January 2010 09:23   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Here is the syntax for the INSERT ALL command - it needs a SELECT statement to provide the data.
What is wrong with:
INSERT INTO INTEGRATION.CASH_TRANS ( CLIENT_ID, ACCOUNT_ID, TXN_CCY, LTY_ID, AMT, ENTRY_TYPE )
VALUES ('12345', '12345~TAX', 'GBP', 'INC', '55', 'D');
INSERT INTO INTEGRATION.CASH_TRANS ( CLIENT_ID, ACCOUNT_ID, TXN_CCY, LTY_ID, AMT, ENTRY_TYPE )
VALUES ('12345', '12345~TAX', 'GBP', 'INC', '60', 'D');
[Updated on: Fri, 15 January 2010 09:24] by Moderator Report message to a moderator
|
|
|
Re: truncate and insert [message #439287 is a reply to message #439279] |
Fri, 15 January 2010 09:23   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Have a look at syntax diagram for what is named multi_table_insert, you'd see it ends with a mandatory part which is "subquery".
Try:
INSERT ALL
INTO INTEGRATION.CASH_TRANS ( CLIENT_ID, ACCOUNT_ID, TXN_CCY, LTY_ID, AMT, ENTRY_TYPE )
VALUES ('12345', '12345~TAX', 'GBP', 'INC', '55', 'D')
INTO INTEGRATION.CASH_TRANS ( CLIENT_ID, ACCOUNT_ID, TXN_CCY, LTY_ID, AMT, ENTRY_TYPE )
VALUES ('12345', '12345~TAX', 'GBP', 'INC', '60', 'D')
SELECT 1 FROM DUAL;
Regards
Michel
|
|
|
|
Re: truncate and insert [message #439293 is a reply to message #439251] |
Fri, 15 January 2010 09:32   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If you're inserting hard-coded values what do you need insert all for?
The amount of typing you're going to save over individual inserts is going to be pretty negligible.
|
|
|
|
Re: truncate and insert [message #439443 is a reply to message #439295] |
Sat, 16 January 2010 21:37  |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
If you are using PL/SQL, why not fill up PL/SQL tables with values and then use the FORALL statement to dump them into the table in a single command.
Ross Leishman
|
|
|
Goto Forum:
Current Time: Fri Feb 07 21:38:01 CST 2025
|