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: 12422
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: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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: Sat Dec 10 20:55:25 CST 2016

Total time taken to generate the page: 0.31150 seconds