Friday, April 15, 2016

%TYPE Declaration Gives PLS-00302

A developer reported a problem with a %TYPE declaration which was returning PLS-00302 in an Oracle 10 database. The cause turned out to be a variation on a problem which I have already reported. However, as it took me some time to work out, I have reproduced it below. First I created a user, called USER1, who would own a table:

SQL> conn / as sysdba
Connected.
SQL> create user user1
  2  identified by user1
  3  default tablespace users
  4  quota 10m on users
  5  /
 
User created.
 
SQL> grant create session, create table
  2  to user1
  3  /
 
Grant succeeded.
 
SQL>

Then I created a user, called USER2, to declare a variable using %TYPE, basing it on a column in the table created by USER1:

SQL> create user user2 identified by user2
  2  /
 
User created.
 
SQL> grant create session, create synonym
  2  to user2
  3  /
 
Grant succeeded.
 
SQL> 

USER1 created a table called TAB1 and allowed USER2 to see it:

SQL> conn user1/user1
Connected.
SQL> create table tab1
  2  (col1 number)
  3  /
 
Table created.
 
SQL> grant select on tab1 to user2
  2  /
 
Grant succeeded.
 
SQL> 

USER2 declared a variable called BLAH using %TYPE to base it on column COL1 in table TAB1. This was successful:

SQL> conn user2/user2
Connected.
SQL> declare
  2  blah user1.tab1.col1%type;
  3  begin
  4  null;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
SQL> 

USER2 created a synonym called USER1. N.B. It is not a good idea for an object in one schema to have the same name as a schema elsewhere in the database.

SQL> create synonym user1 for user_tables
  2  /
 
Synonym created.
 
SQL> 

USER2 tried to declare a variable called BLAH as before. This time, Oracle probably thought that USER1 referred to the synonym created in the previous step rather than the username created at the start of the post. The declaration therefore failed:

SQL> declare
  2  blah user1.tab1.col1%type;
  3  begin
  4  null;
  5  end;
  6  /
blah user1.tab1.col1%type;
           *
ERROR at line 2:
ORA-06550: line 2, column 12:
PLS-00302: component 'TAB1' must be declared
ORA-06550: line 2, column 6:
PL/SQL: Item ignored
 
SQL>

Tuesday, March 29, 2016

Bind Variables

This example, tested on Oracle 11, shows how you can define bind variables in SQL*Plus, assign values to them in PL/SQL then display those values afterwards back in SQL*Plus:

SQL> variable bv1 varchar2(3)
SQL> variable bv2 number
SQL> begin
  2  select 'ABC' into :bv1 from dual;
  3  select 123 into :bv2 from dual;
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> print bv1

BV1
--------------------------------
ABC

SQL> print bv2

       BV2
----------
       123

SQL> execute :bv1 := 'XYZ';

PL/SQL procedure successfully completed.

SQL> execute :bv2 := 456;

PL/SQL procedure successfully completed.

SQL> print bv1

BV1
--------------------------------
XYZ

SQL> print bv2

       BV2
----------
       456

SQL>