Have you ever encountered error "Implementation Restriction: 'XX.XX': Cannot directly access remote package variable or cursor" while compiling oracle forms? If yes read below to find a method of creating a generic way of assigning values to variables from oracle forms.
Here in this article we will see how to avoid the error "Cannot directly access remote package variable or cursor" from Oracle forms through an example.
We have created a package for test purposes. The name of the package is testpck. Assume it has a lot of variables, say some 200 and you need to assign values to each of the variables from front-end. One way is to create a procedure which assigns values using a rowtype variable. But in this approach we would be writing unnecessary code to attain the logic.
In this example listed below we have used only one variable in the package specification. The variable name is l_variable. For simplicity of discussion I am restricting the variables of type VARCHAR2. This can be further enhanced to provide a logic for all of the datatypes.
Find below the code for package specification:
create or replace package testpck as
l_variable varchar2(255);
procedure l_set_variable_value(l_variable_name varchar2, l_variable_value varchar2);
end;
/
Find below the code for package body:
create or replace package body testpck as
procedure l_set_variable_value(l_variable_name varchar2, l_variable_value varchar2) as
l_sql varchar2(4000);
begin
l_sql := 'begin testpck.'||l_variable_name ||':='||chr(39)||l_variable_value||chr(39)||'; end;';
execute immediate l_sql;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
end;
/
Now to test this procedure:
begin
testpck.l_set_variable_value('l_variable',
'variables value');
end;
/
That's all. Now you can use this procedure to assign value to any of the variables present in the package specification. If an invalid variable name is passed then the following error will pop out:
ORA-06550: line 1, column 15:
PLS-00302: component 'L_VARIABLE1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
