Home » SQL & PL/SQL » SQL & PL/SQL » truncate + procedure
truncate + procedure [message #355016] Wed, 22 October 2008 05:07 Go to next message
gupta_vama
Messages: 8
Registered: October 2008
Location: hyderabad
Junior Member
Hi,

i am using truncate in procedure is

create or replace procedure proc_aggr_calmonth_sales as
begin
truncate table aggr_calmonth_sales;
insert into aggr_calmonth_sales(promo_name,promo_subcategory,promo_category...) select ... from t1,t2,t3 where .... group by(...);
commit;
exception
when others then
dbms_output.put_line('error');
end;
/




error is

pls-00103 encounted symbol "table" when expecting one of the following
:=[@%;
Re: truncate + procedure [message #355018 is a reply to message #355016] Wed, 22 October 2008 05:12 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Truncate command is a DDL and to use DDL in a PLSQL you need to use Native Dynamic SQL or DBM_SQL. Read documentation for more details on this.
Re: truncate + procedure [message #355019 is a reply to message #355016] Wed, 22 October 2008 05:12 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
you need to use dynamic sql for this
Re: truncate + procedure [message #355021 is a reply to message #355016] Wed, 22 October 2008 05:14 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

You cannot use the DDL statment directly...

Use this,

EXECUTE IMMEDIATE 'truncate table aggr_calmonth_sales';

Re: truncate + procedure [message #355033 is a reply to message #355021] Wed, 22 October 2008 05:51 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.

Previous Topic: problem using utl_file.fgetattr in reports
Next Topic: deadlock
Goto Forum:
  


Current Time: Thu Feb 06 13:55:50 CST 2025