Home » SQL & PL/SQL » SQL & PL/SQL » truncate and insert
truncate and insert [message #439251] Fri, 15 January 2010 06:20 Go to next message
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 Go to previous messageGo to next message
pointers
Messages: 410
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 #439253 is a reply to message #439252] Fri, 15 January 2010 06:29 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Thanks, execute immediate 'truncate..' worked fine.

I found an INSERT ALL but that looks like its doing multiple inserts

[Updated on: Fri, 15 January 2010 06:31]

Report message to a moderator

Re: truncate and insert [message #439256 is a reply to message #439251] Fri, 15 January 2010 07:15 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're probably looking for insert select.
Re: truncate and insert [message #439279 is a reply to message #439256] Fri, 15 January 2010 09:12 Go to previous messageGo to next message
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 #439282 is a reply to message #439251] Fri, 15 January 2010 09:20 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
And the compile error is?
Re: truncate and insert [message #439285 is a reply to message #439282] Fri, 15 January 2010 09:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 #439289 is a reply to message #439287] Fri, 15 January 2010 09:27 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Hm but im inserting hard coded values so what do i need to select from?
Re: truncate and insert [message #439293 is a reply to message #439251] Fri, 15 January 2010 09:32 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 #439295 is a reply to message #439289] Fri, 15 January 2010 09:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
pyscho wrote on Fri, 15 January 2010 16:27
Hm but im inserting hard coded values so what do i need to select from?

Because it is the syntax. Full stop.

Regards
Michel

Re: truncate and insert [message #439443 is a reply to message #439295] Sat, 16 January 2010 21:37 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Ext Table
Next Topic: Difficulty in resolving Date ORA-12899: value too large for column (merged)
Goto Forum:
  


Current Time: Sun Dec 04 22:46:13 CST 2016

Total time taken to generate the page: 0.08589 seconds