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.

It would be a lot easier for us to understand your problem if you posted two or three scenarios with data, to illustrate how and why the dynamic thang works. — APC
– APC, Commented Dec 2, 2011 at 17:46
This reminds me of the classic Daily WTF on "Soft Coding": thedailywtf.com/Articles/Soft_Coding.aspx — APC
– APC, Commented Dec 2, 2011 at 17:49
@APC: added the example of the data scenario. Hope this makes my question clearer. Also read the article about soft coding, but unfortunately this is not a developer choice, but a requirement by our users. They manage the config of required fields. — flaria
– flaria, Commented Dec 2, 2011 at 18:38
@flaria - In the check_variables procedure, you've declared three variables all named var1. I'm assuming you meant to declare parameters var1, var2, and var3. Does that mean that you know in advance the number and names of all the possible variables? What if the users added another row to tblConfig with a variable_name of foo? Would you then add a new parameter to the check_variables procedure? — Justin Cave
– Justin Cave, Commented Dec 2, 2011 at 18:59
@APC: Well the thing is that what's gonna be dynamic is mostly which variables are required, not so much the number of variables. But actually since the values for the variables are coming from a website (we're using Oracle® Application Server PL/SQL Web Toolkit), they could also be dynamic using the !procedure syntax, in which if you declare to arrays named name_array and value_array, you could receive undefined parameters into your procedure. But I digress. As I said, what the users will be changing most often is which fields are required, not the fields themselves. — flaria
– flaria, Commented Dec 2, 2011 at 21:32

Luke Girvin · Accepted Answer · 2024-12-04 11:05:45Z

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.

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

someuser1 · Accepted Answer · 2011-12-02 18:57:29Z

0

Wow, pretty strange setup (and thanks for the link APC), but since you're defining/tracking variable names in the config table, why not have the procedure update that table (or some other table) to tell you if the variable "has a value"? (assuming you mean null or not null). Also, not sure what to do with local variables that can change value (or be null/not null) depending on session level stuff (or who's executing the proc, privileges, location, etc).

answered Dec 2, 2011 at 18:57

someuser1

1

1 Comment

flaria Over a year ago

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.

2 revs · Accepted Answer · 2017-05-23 11:50:29Z

This is essentially the same than APC's answer. I have just made the following modifications:

use default parameter values
use named parameters
separate null check logic into a dedicated procedure

create table syscfg (
  procname varchar2(30) not null,
  varname varchar2(30) not null,
  mandatory varchar2(1) not null
);

insert all
into syscfg values('DO_SOMETHING', 'P_1', 'Y')
into syscfg values('DO_SOMETHING', 'P_2', 'Y')
into syscfg values('DO_SOMETHING', 'P_3', 'N')
into syscfg values('DO_SOMETHING_TWO', 'P_1', 'Y')
into syscfg values('DO_SOMETHING_TWO', 'P_2', 'Y')
into syscfg values('DO_SOMETHING_TWO', 'P_3', 'N')
into syscfg values('DO_SOMETHING_TWO', 'P_4', 'N')
into syscfg values('DO_SOMETHING_TWO', 'P_5', 'N')
select 1 from dual;

col procname for a20
col varname for a5
col mandatory for a1
select * from syscfg;

/* Supports only up to 5 parameters. */
create or replace procedure is_missing_mandatory_args (
  p_procname in varchar2,
  p_1 in varchar2 default null,
  p_2 in varchar2 default null,
  p_3 in varchar2 default null,
  p_4 in varchar2 default null,
  p_5 in varchar2 default null
) as
  args constant sys.dbms_debug_vc2coll :=
    sys.dbms_debug_vc2coll(p_1, p_2, p_3, p_4, p_5);
begin
  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 = upper(p_procname)
             and s.mandatory = 'Y' 
             order by a.position
           )
  loop
    if args(r.position) is null then
      raise_application_error(-20000, upper(p_procname) || '.' || r.varname
                              ||' cannot be null');       
    end if;        
  end loop;
end;
/
show errors

create or replace procedure do_something (
  p_1 in varchar2 default null,
  p_2 in varchar2 default null,
  p_3 in varchar2 default null
) as
begin
  is_missing_mandatory_args('do_something', p_1, p_2, p_3);
  /* The real work takes place here. */
  dbms_output.put_line('do_something() executed successfully !');
end;
/
show errors

create or replace procedure do_something_two (
  p_1 in varchar2 default null,
  p_2 in varchar2 default null,
  p_3 in varchar2 default null,
  p_4 in varchar2 default null,
  p_5 in varchar2 default null
) as
begin
  is_missing_mandatory_args('do_something_two', p_1, p_2, p_3, p_4, p_5);
  /* The real work takes place here. */
  dbms_output.put_line('do_something_two() executed successfully !');
end;
/
show errors

SQL> exec do_something(p_1 => 'foo', p_2 => 'foo');
do_something() executed successfully !

PL/SQL procedure successfully completed.

SQL> exec do_something(p_2 => 'foo');
BEGIN do_something(p_2 => 'foo'); END;

*
ERROR at line 1:
ORA-20000: DO_SOMETHING.P_1 cannot be null
ORA-06512: at "JANI.IS_MISSING_MANDATORY_ARGS", line 23
ORA-06512: at "JANI.DO_SOMETHING", line 7
ORA-06512: at line 1


SQL> exec do_something(p_1 => 'foo');
BEGIN do_something(p_1 => 'foo'); END;

*
ERROR at line 1:
ORA-20000: DO_SOMETHING.P_2 cannot be null
ORA-06512: at "JANI.IS_MISSING_MANDATORY_ARGS", line 23
ORA-06512: at "JANI.DO_SOMETHING", line 7
ORA-06512: at line 1


SQL> exec do_something_two(p_2 => 'baz', p_1 => 'buz', p_5 => 'boz');
do_something_two() executed successfully !

PL/SQL procedure successfully completed.

SQL> exec do_something_two(p_1 => 'baz');
BEGIN do_something_two(p_1 => 'baz'); END;

*
ERROR at line 1:
ORA-20000: DO_SOMETHING_TWO.P_2 cannot be null
ORA-06512: at "JANI.IS_MISSING_MANDATORY_ARGS", line 23
ORA-06512: at "JANI.DO_SOMETHING_TWO", line 9
ORA-06512: at line 1


SQL>

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.

Collectives™ on Stack Overflow

Dynamically check if a variable has value in PL SQL

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related