Home » SQL & PL/SQL » SQL & PL/SQL » unable truncate table in plsql block (11g,toad 12.1)
unable truncate table in plsql block [message #613462] Wed, 07 May 2014 06:20 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #613469 is a reply to message #613466] Wed, 07 May 2014 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

To complete cookiemonster's answer you'd get (commenting INSERT INTO in a table I have not) something like:
SQL> declare
  2     i             NUMBER;
  3     l_error_msg   VARCHAR2 (1000);
  4  BEGIN
  5  
  6    execute immediate 'TRUNCATE TABLE cust_rds_stage';
  7  
  8  /*
  9   INSERT INTO cust_rds_stage
 10     SELECT *
 11       FROM cust@rds_stage
 12      WHERE cust_id IN (SELECT tkey
 13                          FROM customer
 14                         WHERE tkey IS NOT NULL);
 15  */
 16  end;
 17  /
declare
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 6

if the table does not exist or is not accessible to you, or:
SQL> declare
  2     i             NUMBER;
  3     l_error_msg   VARCHAR2 (1000);
  4  BEGIN
  5  
  6    execute immediate 'TRUNCATE TABLE t';
  7  
  8  /*
  9   INSERT INTO cust_rds_stage
 10     SELECT *
 11       FROM cust@rds_stage
 12      WHERE cust_id IN (SELECT tkey
 13                          FROM customer
 14                         WHERE tkey IS NOT NULL);
 15  */
 16  end;
 17  /

PL/SQL procedure successfully completed.

If the table exists and you have the correct privileges on it.
Re: unable truncate table in plsql block [message #613471 is a reply to message #613462] Wed, 07 May 2014 08:03 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
ashwanth77 wrote on Wed, 07 May 2014 16:50
ORA-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 Go to previous message
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

Previous Topic: MV creation error PLS-512: Cannot directly access remote package variable or cursor
Next Topic: how to get the values from the another table if the table has null
Goto Forum:
  


Current Time: Fri Mar 29 10:32:24 CDT 2024