Truncate Table in a Stored Procedure... 1

[THoey]

I know I have seen this before, but the keyword search is still down.

I need to create a stored procedure with the following code:

CREATE PROCEDURE EHGRIFF.TRUNC_STATIC_TABLES IS 
BEGIN

TRUNCATE TABLE EHGRIFF.C_AGENT;

END;

When I run this, I get the following message:

MGR-00073: Warning: PROCEDURE EHGRIFF.TRUNC_STATIC_TABLES created with compilation errors.

The error shows up as:

Line # = 4 Column # = 10 Error Text = PLS-00103: Encountered the symbol "TABLE" when expecting one of the following:

   := . ( @ % ;
The symbol ":= was inserted before "TABLE" to continue.

There was something about another command you needed to be able to execute DDL statements in a stored procedure.

Any help would be greatly appreciated...
Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[ninfan]

I suspect that you need to execute the statement like this:

sql_trunc VARCHAR2(1000) :=NULL;

sql_trunc := 'TRUNCATE TABLE EHGRIFF.C_AGENT';
EXECUTE IMMEDIATE sql_trunc;

 

[THoey]

Thanks!!! Works great!!! Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.