All geek questions in one place

Dynamically check if a variable has a value in PL SQL

What I need to do is check if the series of variables in the procedure is significant, but the difficult part is that the list of variables that I have to check is in the table. Let me clarify further:

We have a table in which we save all variable names and an indicator for which the variable should not be zero. This means that we can change in the table which fields are needed without changing the code.

What we want to implement is similar to NAME_IN embedded in forms, where you have something like: v_value := GetValue('v_variable'); , and that will mean v_variable to v_value . And then I would just check if v_value is null. All this will be inside the LOOP cursor, which will receive all the variables in the previously mentioned table that were marked as required.

So far I have tried with EXECUTE IMMEDIATE to get the dynamic values ​​of variables, but this does not work, because EXECUTE IMMEDIATE works in its own area and therefore cannot "see" variables in the procedure area.

Another thing I tried is PL / SCOPE, which allows me to see if variables exist in my area by specifying a name, but it does not have a mechanism to get the values ​​of the variables that exist.

Ok, I hope someone can help me. Help would be greatly appreciated.

Here is an example:

Let's say I got the following table called 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:

 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 script, the loop should check if var1 value. If I changed the require_ind values ​​for other variables, they would also be checked.

I read this article on soft coding ... this is a good read, unfortunately, this scenario is not the choice I made as a developer. This is necessary because the table with the required configuration is managed by the user, not by the development team.

+4
source share
3 answers

PL / SQL does not have much to do with reflection. Of course, there is no equivalent to NAME_IN. I could not solve this using dynamic SQL, but I found a solution.

Here is an example. It has three procedures. Please note that all of them are required, but we can pass NULL in the parameter slot. This, of course, is one of my objections to such "soft coding": it confuses the API. Describing a procedure is no longer enough to know what arguments it requires.

 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; /

Checking the "dynamic" parameter is done by filling the collection with parameters in the signature order. We get the position of the configured parameters by combining the presentation of the data dictionary with our configuration table. Then we use the position as an index for the array. Note that the collection accepts strings. I declared all my parameters as Varchars, but you may need to specify dates or numbers.

So, yes, this is awkward, but " > this avoidance quest often leads to [...] complexity, convolution, and many-sided, unreachable code. " :)

Here is the contents 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 the 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>

It looks good, but to prove that nothing is in 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 customers are dumb enough to think that this ultra-flexible will be convenient in other places. The good news is that this solution can be easily retrieved into a standalone procedure that takes PROCNAME and an array as parameters.

+5
source

Wow, a rather strange setting (and thanks for the APC link), but since you are defining the variable names in the configuration table, why not refresh this table (or some other table) to tell you, the variable "matters"? (assuming you mean null or not null). In addition, they are not sure what to do with local variables that can change the value (or be null / non-null) depending on the material of the session level (or who performs the proc, privileges, location, etc.).

0
source

This is essentially the same as the APC answer . I just made the following changes:

  • use default parameter values
  • use named parameters
  • separate check of zero logic in the selected 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>
0
source

Source: https://habr.com/ru/post/1384373/

More articles:


All Articles