Dynamic SQL 1

[janaka12]

Hi ,
We have a table to record all the procedures/functions that a particular transaction needs to call. The user can opt to use/not to use these procedures/functions for a particular transaction. These procedures/functions are predefined and not added dynamically. The user only has the option of using it/not using it dynamically.

We attach these functions, which are flagged to be used by the user to a particular transaction type.

The problem arises since these procedures/functions may have different arguments.
How can I map the arguments to the correct variables in the calling program?
How can I cater for procedures/functions with different number of arguments?

Ex:
I may have variables prefix,suffix,companyid,maintype,subtype,qty as my variables in the main program. Now there can be different sub procedures, which needs to be called using any of these variables. The procedure to be called can be held in a variable as follows:
Wk_sql_stmt = validate_clientprefix,:suffix);
I want to be able to say ‘Execute Immediate Wk_sql_stmt using prefix,suffix;
Now if I want to call another procedure to validate company, I want to call a different procedure with different arguments and different number of arguments.
Wk_sql_stmt = validate_companycompany_id,:main_type,sub_type);
Assuming that the sequence of each of the parameters in each procedure is recorded,
We can find the variables needed for each of the 2 statements. But can we generalize or change the code such that both can be called from one execute immediate statement like
Execute Immediate Wk_sql_stmt using value1,value2…….value n
Where value1 will be the value of the 1st parameter and the number of values for the particular procedure is n.


Sorry if the question is lengthy,
Thanking in advance,
J

 

[Bimalaggarwal]

I would have another table (say, meta_proc_par) which stores the argument,and their data-types for each sub procedure/function. I am writing below the skeleton of the code...you must adapt wherever needed.
This way, you can have an unlimited number of sub-procedures/functions and unlimited number of arguments.

-------------------

declare
l_stmt varchar2(2000) := Wk_sql_stmt; -- your sub procedure/function name
l_delim varchar2(2) := '(';
l_quote varchar2(2) := null;
--------------------------------------------------------------------------
procedure add_stmt(i_par varchar2, i_val varchar2)
is
begin
if i_val is not null then
l_stmt := l_stmt || l_delim || i_par || ' => ' || l_quote || i_val || l_quote;
l_delim := ' ,';
end if;
end add_stmt;

--------------------------------------------------------------------------
begin
for a in (
select upper(name) name
,datatype datatype
from meta_proc_par
where meta_proc_name = Wk_sql_stmt
order by par_nr
) loop

l_quote := iff(a.datatype not in ('BINARY_INTEGER', 'NUMBER', 'DATE'), '''');
if a.name = 'prefix' then add_stmt(a.name, l_prefix);
elsif a.name = 'suffix' then add_stmt(a.name, l_suffix);
elsif a.name = 'companyid' then add_stmt(a.name, l_companyid);
elsif a.name = 'maintype' then add_stmt(a.name, l_maintype);
elsif a.name = 'subtype' then add_stmt(a.name, l_subtype);
elsif a.name = 'qty' then add_stmt(a.name, l_qty);
......
......
end if;

end loop;

l_stmt:= 'begin ' || def.rtn ||
l_mail_stmt || def.rtn ||
l_stmt || ');' || def.rtn ||
'end;';

execute immediate l_stmt;

end;

I hope this helps. The drawback is that you need to populate and maintain an additional table.

 

[ThomVF]

To possibly eliminate this drawback, you could query the USER_ARGUMENTS view (or ALL_ or DBA_) to get the actual arguments, their order, name and datatype on the fly.

select
argument_name
, data_type
, position
, in_out
, data_length
from
user_arguments
where
object_name = '<procedure -or- function name>'
order by
position;

This could eliminate the need to maintain (and sync) another table, making your process truly dynamic and extensible.

Tom &quot;Helping others to help themselves...&quot;
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[Bimalaggarwal]

In this case (suggested by 'ThomVF'), you have to workaround the datatypes
(pls_integer is stored as binary_integer,
boolean is stored as 'pl_sql boolean' etc.)

Its also safe to have ones own table since the user_arguements table is
subject to changes with new Oracle releases, and then you may have to
modify your source for each new Oracle release.

 

[ThomVF]

Good point. &quot;Helping others to help themselves...&quot;
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[janaka12]

Thanks for the help.
I tried it out using the method you have suggested and it works fine.
The drawback in my solution is that i assume knowing the probable variables for
use in a procedure/function. (Since i use them in the long nested if statements)

Thanks again for helping me out.

 

[Bimalaggarwal]

You mentioned that you &quot; may have variables prefix,suffix,companyid,maintype,subtype,qty as my variables in the main program &quot;. Hence I assume that you know the list of parameters that will be arguments to all your sub programs.
If the list of all arguments is very long then the code may get very long.
You may use dynamic select in this case to construct the IF-Then-Else statement.

 

[janaka12]

Since I know the maximum possible variables which will be needed for any procedure/function, i can make a nested if statement even though it may become very long.
However i guess using the long nested if statements would be more efficient than creating the nested if statement dynamically and executing it.

Also i would like to know how the program can handle diffferent number and types of return variables.
Thanks

 

[Bimalaggarwal]

The return variable types are well-defined in PL/SQL , and you can have only one return value (via Function),
unless you opt for OUT parameters.Procedures do not return any value.

You could have something like:

If function_name in (list of your_function_names) then
Wk_sql_stmt_int = your_function_name...................
elsif function_name in (list of your_function_names) then
Wk_sql_stmt_char = your_function_name...................
.........
.....
end if;

I assume you would know the return types for each function. And you need only as many return variables
as the distinct return types in your source.

I see this implementation should not be dificult.

 

[janaka12]

Hi again,
I was able to cater for any number, type of in parameters to a procedure and build the sql statement by concatanating the in parameters dynamically.

I need a method to do the same for the out parameters as well without giving them in the returning into clause.
This is because different functions may have different number of out parameters and the returning into clause cannot be built by concatanation.
If there is a method to do it like using the symbol '=>' for in parameters, I would be very grateful.

Thanks a lot
Indica