Re: Problem running PL/SQL Procedure

sannabr_at_ozonline.com.au (Sanna B) wrote i:

> The procedure compiles without errors and returns the number of
> records as required. When attempting to execute - I get errors. Any
> ideas?

Simple logic. :-)

BTW, the procedure does _not_ compile. Do you get a message saying that the procedure was compiled/created? Nope.

> (Running 8i - pl/sql 8.1.7.0.0)
>
> here's the script:
> ---------------------------------------------------
> --comments start here
> --set serveroutput on
>
> create or replace proc testSB
> is
>
> declare
>
> vcount number;
>
> begin
>
> SELECT count(*) into vcount from testdba.test;
>
> dbms_output.put_line('count records:'||vcount);
>
> end;
> /
> ---------------------------------------------------
>
> SQL>@ C:\_work\sql\test_proc.sql
> count records:185119
>
> PL/SQL procedure successfully completed.
> ------------------------------------------------
>
> SQL> exec testSB
> BEGIN testSB; END;
>
> *
> ERROR at line 1:
> ORA-06550: line 1, column 7:
> PLS-00201: identifier 'TESTSB' must be declared
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored

Look at ths last error.

TESTSB does not exist. Therefore, it was not created. Thus the script failed to create TESTSB.

Look at the script.

The TESTSB procedure is not created due to a syntax error (hint : do you use a declare in stored procedures or only in anonomous PL/SQL blocks?).

The CREATE OR REPLACE command fails.. but the _rest_ of the script executes as an anonymous PL/SQL block.

Why do you get a "> count records:185119" returned from the script? You did not execute the TESTSB in the script (only defined it), yet you get that DBMS_OUTPUT returned. If TESTSB was created, it would not have been executed too.

You also get a message that the Pl/SQL procedures was successfully COMPLETED... not CREATED. Thus it was executed.. with no hint that it was created.

To pick up such errors, set echo and termout on. Use SHOW ERRORS after you submit a CREATE OR REPLACE PROCEDURE/FUNCTION command. Most of all.. read the last error and worked your way back up to the first error.. and most times it will make sense. Just add some logic. :-)  

--
Billy