Dynamically check if a variable has value in PL SQL

Question

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.

Solution

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 proecdure. It has three procedures. 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.

Comments

[flaria]

Yep, quite a little challenge I've got hehe. The problem with the table inserting as you proposed would be that all the same I wouldn't have a way of know which variable matched which record in the table except by hardcoding that. And although that's not so bad, the other problem is that I don't get privileges to create or modify tables, and the db arquitect probably wouldn't want to create such table because it could grow quite a lot considering it would have to manage values per session. What I want is basically what you can achieve in Oracle Forms with the NAME_IN built-in.

[flaria]

Thanks a lot. It's, as you said, clunky and complex, but solves my requirement. I'll be sure to point out how "unnatural" this is to PL/SQL. They'll probably not really listen to me but I'll sure try hehehe. Again, thanks. :-)

[APC]

Obviously I posted a proof of concept. If I was building such a procedure for real it would have a signatures of is_missing_mandatory_args (p_procname in varchar2, args sys.dbms_debug_vc2coll). Only I would use my own collection type rather than a SYS one. Also I wouldn't give a procedure a name starting with IS_ : it sounds like a function returning a boolean.