Why is the schema name necessary here and how to compile without it

oracleoracle-11goracle-11g-r2oracle-18coracle-forms

I'm working on an Oracle Forms 11g application linked with an Oracle 18c database and I stumbled upon a strange error when compiling my form.

Here's a snippet to illustrate the problem (I changed the identifiers name but it doesn't matter):

PROCEDURE SOME_PROCEDURE(...) IS
  v_arg1 relation.c_arg1%type;
  v_arg2 relation.c_arg2%type;
  v_arg3 relation.c_arg3%type;
BEGIN

  SELECT r.c_arg1, r.c_arg2, r.c_arg3
    INTO v_arg1, v_arg2, v_arg3
    FROM relation r
   WHERE ...

  -- Some more code ...

END;

When I'm compiling, the error I get is :

Compilation errors on SOME_PROCEDURE:
PL/SQL ERROR 302 at line 2, column 19
component 'C_ARG1' must be declared
PL/SQL ERROR 0 at line 2, column 10
Item ignored
PL/SQL ERROR 302 at line 3, column 19
component 'C_ARG2' must be declared
PL/SQL ERROR 0 at line 3, column 10
Item ignored
PL/SQL ERROR 302 at line 4, column 19
component 'C_ARG3' must be declared
PL/SQL ERROR 0 at line 4, column 10
Item ignored

relation here is a table name and is causing the problem. If, for testing purposes, I pick another table and field name, the code compiles fine e.g. v_arg1 other_table.other_field%type;.

I figured maybe relation is a reserved keyword (even though I couldn't find anything in the documentation about this specific keyword, yikes), so I tried to put the schema name admin before the table name:

PROCEDURE SOME_PROCEDURE(...) IS
  v_arg1 admin.relation.c_arg1%type;
  v_arg2 admin.relation.c_arg2%type;
  v_arg3 admin.relation.c_arg3%type;
BEGIN

And it works just fine. Now this is a problem, because admin is not a fixed name and will change because multiple schemas are used for multiple purposes ie development, testing, production, etc.

This is where I'm stuck. I don't know how to do this the "right" way: because the %type is a VARCHAR2 with some length, I'll use something larger than usual such as VARCHAR2(100) and hopefully it downcasts without errors in the future.

My question is, is it possible to come up with a way to reference this table without specifying the schema?

This is for my work, I don't have much freedom in changing the table/schema name, etc.

Thank you

Best Answer

Indeed relation is the name of a table. It must exist in the schema where you run this code (= belong to the user you are connected as).

Later you say that admin.relation works fine. This means that the table called relation belongs to a user called admin.

So ...

  • Either compile and run your code as admin (= connect as admin).
  • Or use fully qualified names and run the code from another user. Provided that user has the proper right (SELECT privilege) on table relation

Actually there is a third option that avoids the need to fully qualify the table name: have user admin create a public synonym to the table, like this:

create public synonym relation for relation;

or have a DBA do it:

create public synonym relation for admin.relation;

That way all users (provided they have the right to read that table) can refer to it by its name, without needing to fully qualify it with the name of the owner.

Related Question