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.
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.