Hi Tom,

I'm trying to create a stored procedure only when a particular table already exists, using the following code:
SET SERVEROUTPUT ON

DECLARE
v_row_count INTEGER := 0;
e_wrong_db EXCEPTION;
BEGIN
SELECT count(*) INTO v_row_count
FROM all_objects
WHERE object_name = 'TABLE_A';

IF (v_row_count < 1) THEN
RAISE e_wrong_db;
ELSE
CREATE OR REPLACE PROCEDURE Create_report()
BEGIN
DBMS_OUTPUT.PUT_LINE('Fetch records and create the report.');
END Create_report;
END IF;
EXCEPTION
WHEN e_wrong_db THEN
DBMS_OUTPUT.PUT_LINE('>> ERROR: DB script not applicable.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('other exceptions...');
RAISE;
END;

And I got the following error messages:
Error report -
ORA-06550: line 12, column 9:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.

Can you help me out?

Thanks,
David

SQL> create or replace
  2  procedure MY_PROC is
  3  begin
  4    --
  5    -- truncate is DDL
  6    --
  7    truncate table T;
  8  end;
  9  /

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE MY_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/12     PLS-00103: Encountered the symbol "TABLE" when expecting one of
         the following:
         := . ( @ % ;
         The symbol ":= was inserted before "TABLE" to continue.

SQL>
SQL> create or replace
  2  procedure MY_PROC is
  3  begin
  4    execute immediate 'truncate table T';
  5  end;
  6  /

Procedure created.

SQL>
SQL> exec my_proc;

PL/SQL procedure successfully completed.

SQL> create or replace
  2  procedure MY_PROC is
  3  begin
  4    execute immediate 'this is a piece of nonsense';
  5  end;
  6  /

Procedure created.