Re: whenever sqlerror is an unknown statement?

jjsavage_at_gmail.com wrote:
> Thanks! But it doesn't work:
>
> declare x varchar(20);
> begin
> select id into x from pass where id = '1';
> exception
> when others then
> if sqlcode = -942 then
> execute immediate 'create table pass(a char(10));';
> end if;
> end;
>
> Gives ORA-06550: line 4, column 23: PL/SQL: ORA-00942: table or view
> does not exist ORA-06550: line 4, column 1: PL/SQL: SQL Statement
> ignored. So it's not catching any 'others', and it's not creating the
> table (I know because the same thing happens if I run it again).
> What's wrong?
>
> Thanks again,
> John

You are misunderstanding what you see.

1. Believe the ORA-06550. The privilege must be granted explicitly.
2. Creating a table in any PL/SQL is deplorable. In an exception handler about as bad as bad can be.
3. VARCHAR is NOT an Oracle data type for declaring PL/SQL variables. Oracle is NOT SQL Server.
4. Is ID a string or a number?
5. Try recoding like this:

DECLARE
  x VARCHAR2(5);
  sc NUMBER;
  se VARCHAR2(200);
BEGIN
   SELECT id
   INTO x
   FROM pass
   WHERE id = '1';
EXCEPTION
   WHEN OTHERS THEN

     sc := sqlcode;
     se := sqlerrm;
     dbms_output.put_line(TO_CHAR(sc) || ' ' || se);

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)