What I need to do is check if a series of variables in a procedure have value, but the tricky part is that the list of which variables I have to check is in a table. Let me explain further:
We have a table where we keep all the variable names and an indicator for which variable shouldn't be null. This is so we can change in the that table which fields are required without having to change the code.
What we want to implement is something similar to the NAME_IN built-in in forms, where you have something like: v_value := GetValue('v_variable'); and that would assing the value of v_variable to v_value. And afterwards I would just check if v_value is null. This whole thing would be inside a LOOP of a cursor that would get all the variables in the table I mentioned earlier that were marked as required.
So far I've tried with EXECUTE IMMEDIATE to get the variable values assigned dynamically, but that doesn't work because EXECUTE IMMEDIATE runs in it's own scope and so it's not able to "see" the variables in the procedure scope.
The other thing I've tried is PL/SCOPE which allows me to actually see if the variables exists within my scope by supplying the name, but it has no mechanism to get the values of the variables that do exist.
Well, I hope anyone can help me. Help will be greatly appreciated.
Here is an example:
Say I got the following table named tblConfig with two columns: variable_name and required_ind.
variable_name | required_ind
-----------------------------
var1 | Y
var2 | N
var3 | N
Then I would have a procedure called check_variables like this:
PROCEDURE check_variables (
var1 VARCHAR2,
var2 VARCHAR2,
var3 VARCHAR2)
IS
CURSOR c_var_check IS
SELECT variable_name
FROM tblConfig
WHERE required_ind = 'Y';
BEGIN
FOR rec_var IN c_var_check LOOP
... check if rec_var.variable_name is the name of variable that has value ...
END LOOP;
END;
In this fisrt scenario, the loop would have to check if var1 has value. If I changed the values of required_ind for the other variables, they would be checked too.
I've read that article about soft coding... it's a good read, unfortunately in this scenario is not a choice I made as the developer. This is necessary because the table with the required config is managed by the user, not the development team.
PL/SQL doesn't have much in the way of reflection. There's certainly no equivalent of NAME_IN. I couldn't solve this with dynamic SQL but I have found a solution.
Here is a procedure. It has three parameters. Note that they are all mandatory, but we can pass NULL in a parameter's slot. This of course is one of my objections to such "soft coding": it obfuscates the API. Describing a procedure is no longer sufficient to know what arguments it demands.
create or replace procedure do_something
(p1 in varchar2
, p2 in varchar2
, p3 in varchar2)
is
args sys.dbms_debug_vc2coll;
begin
args := new sys.dbms_debug_vc2coll(p1, p2, p3);
for r in ( select s.varname, a.position
from syscfg s
join user_arguments a
on (s.procname = a.object_name
and s.varname = a.argument_name)
where s.procname = 'DO_SOMETHING'
and s.mandatory = 'Y'
order by a.position
)
loop
if args(r.position) is null
then
raise_application_error(-20000, r.varname ||' cannot be null');
end if;
end loop;
dbms_output.put_line('Procedure executed successfully!');
end;
/
The "dynamic" parameter check works by populating a collection with the parameters in signature order. We get the position of the configured parameters by joing a data dictionary view with our config table. We then use the position as an index to the array. Note that the collection takes strings. I declared all my parameters as Varchars, but you might need to cast dates or numbers.
So, yes, it is clunky, but "this quest of avoidance often leads towards [...] complication, convolution, and all-around unmaintainable code." :)
Here is the content of the config table:
SQL> select * from syscfg
2 /
PROCNAME VARNAME M
------------------------------ ------------------------------ -
DO_SOMETHING P1 Y
DO_SOMETHING P2 Y
DO_SOMETHING P3 N
SQL>
So, let's roll!
SQL> set serveroutput on
SQL> exec do_something('A', 'Y', null)
Procedure executed successfully!
PL/SQL procedure successfully completed.
SQL> exec do_something('A', null, 'X')
BEGIN do_something('A', null, 'X'); END;
*
ERROR at line 1:
ORA-20000: P2 cannot be null
ORA-06512: at "APC.DO_SOMETHING", line 24
ORA-06512: at line 1
SQL>
Looks good, but to prove there's nothing up my sleeve....
SQL> update syscfg
set mandatory = 'N'
where varname = 'P2'
/
2 3 4
1 row updated.
SQL> select * from syscfg
2 /
PROCNAME VARNAME M
------------------------------ ------------------------------ -
DO_SOMETHING P1 Y
DO_SOMETHING P2 N
DO_SOMETHING P3 N
SQL> exec do_something('A', null, 'X')
Procedure executed successfully!
PL/SQL procedure successfully completed.
SQL>
Perhaps your clients are nutty enough to think this ultra flexiblility would be handy in other places. Well the good news is this solution could easily be extracted into a standalone procedure which takes the PROCNAME and the array as parameters.