unable truncate table in plsql block [message #613462] |
Wed, 07 May 2014 06:20 |
|
ashwanth77
Messages: 95 Registered: April 2013 Location: India
|
Member |
|
|
I need to truncate a table in stored procedure and then insert data into the table
what is wrong with this statement
execute immediate 'TRUNCATE TABLE cust_rds_stage';
i get the following error
ORA-06550: line 1, column 17:
PLS-00103: Encountered the symbol "TRUNCATE TABLE cust_rds_stage" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "TRUNCATE TABLE cust_rds_stage" to continue.
the plsql block is
declare
i NUMBER;
l_error_msg VARCHAR2 (1000);
BEGIN
execute immediate 'TRUNCATE TABLE cust_rds_stage';
INSERT INTO cust_rds_stage
SELECT *
FROM cust@rds_stage
WHERE cust_id IN (SELECT tkey
FROM customer
WHERE tkey IS NOT NULL);
end;
/
|
|
|
Re: unable truncate table in plsql block [message #613465 is a reply to message #613462] |
Wed, 07 May 2014 06:27 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Are you sure you get that error with that anonymous block? That error suggests that somewhere the scope of the code block is not properly closed. But I don't see any.
What is that declare part used for? Is the code a part of some other code?
Can you execute the anonymous block in sqlplus and copy paste the entire session details alongwith the error that you claim.
[Updated on: Wed, 07 May 2014 06:36] Report message to a moderator
|
|
|
Re: unable truncate table in plsql block [message #613466 is a reply to message #613465] |
Wed, 07 May 2014 06:38 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You can't possibly have got that error from that code. PLS-00103 is a compile time error, the contents of an execute immediate statement is not checked at compile time and if it was invalid you would get ORA-00900 Invalid SQL statement when you run it.
|
|
|
|
Re: unable truncate table in plsql block [message #613471 is a reply to message #613462] |
Wed, 07 May 2014 08:03 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
ashwanth77 wrote on Wed, 07 May 2014 16:50ORA-06550: [b]line 1, column 17[/b]:
PLS-00103: Encountered the symbol "TRUNCATE TABLE cust_rds_stage" when expecting one of the following:
How could the error ever be at line 1, column 17 with that anonymous block. Unless OP proves it by posting the entire session alongwith the error.
Line 1 suggests that the PLS-00103 occured at that line number. And column 17 suggests that "TRUNCATE...." was found at column 17. Impossible in this case.
|
|
|
Re: unable truncate table in plsql block [message #613473 is a reply to message #613462] |
Wed, 07 May 2014 08:15 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
You said "I need to truncate a table in stored procedure" but posted anonymous PL/SQL block. Anyway, it looks like, based on:
ORA-06550: line 1, column 17:
you pass DDL you want to execute as procedure parameter. And I have a feeling based on:
PLS-00103: Encountered the symbol "TRUNCATE TABLE cust_rds_stage"
you enclosed literal parameter in double quotes instead of single quotes. Something like:
exec your_procedure("TRUNCATE TABLE cust_rds_stage")
instead of
exec your_procedure('TRUNCATE TABLE cust_rds_stage')
SY.
[Updated on: Wed, 07 May 2014 08:16] Report message to a moderator
|
|
|