Home » SQL & PL/SQL » SQL & PL/SQL » Inquiry on calling procedures within a procedure
Inquiry on calling procedures within a procedure [message #417290] Thu, 06 August 2009 23:54 Go to next message
jr_db_dev
Messages: 7
Registered: June 2009
Junior Member
May I just ask if it is possible to excecute a variable stored procedure(meaning user-provided) and variable parameters within a procedure?

Sample:
CREATE OR REPLACE PACKAGE BODY pkTEST
.
.
PROCEDURE pmain_test (
p_in_txn IN VARCHAR2,
p_in_spname IN VARCHAR2,
.
.

BEGIN
.
.
SELECT flag, input_param
INTO v_flag, v_params
FROM tssp_svcs_test
WHERE service_code = p_in_txn
AND service_sp = p_in_spname;
.
.
trade_apps.p_in_spname (v_params); -- (execute the sp_name provided by the user)
Re: Inquiry on calling procedures within a procedure [message #417291 is a reply to message #417290] Thu, 06 August 2009 23:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Yes, it is possible to excecute a variable stored procedure(meaning user-provided) and variable parameters within a procedure?

You can do almost anything in software when necessary resources are available.

But what you are proposing is very, very, very bad & non-scalable "solution".
Re: Inquiry on calling procedures within a procedure [message #417303 is a reply to message #417291] Fri, 07 August 2009 02:00 Go to previous messageGo to next message
jr_db_dev
Messages: 7
Registered: June 2009
Junior Member
Thanks for checking out my inquiry.

May I just ask why it is a poor approach/implementation based on your assessment?

TO elaborate on my simple inquiry:
1. The overall task of the procedure is to be able to insert transaction details (or online logging) into a DB
2. Details: I hope to be able to create a Generic SP that several java applications will call to the purpose of logging.
However, they will need to specify the service_type of their transaction which I will need to retrieve the specific sub-SP that will use the parameters passed to the Generic SP, and then in turn, insert it in the DB.

CODE:
PROCEDURE pmain_test (
p_in_txn IN VARCHAR2, -> service_type
p_in1 IN VARCHAR2,
p_in2 IN VARCHAR2,
p_out1 IN VARCHAR2
)

BEGIN
.
.
SELECT sp_name, flag, input_param
INTO v_spname, v_flag, v_params
FROM tssp_svcs_test
WHERE service_code = p_in_txn;
.
.

IF v_flag != '1' THEN
p_out_respcode := '2000'; --Inactive Service
ELSE
-- execute specific procedures
trade_apps.v_spname (v_params);

EXAMPLE:
trade_apps.pktest.pinsertmktg (p_in2, p_out1);


Hope this clears the inquiry..
Re: Inquiry on calling procedures within a procedure [message #417309 is a reply to message #417303] Fri, 07 August 2009 02:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
A better approach would (could) be a series of if-then conditions, followed by the (static) call to the desired procedure.
Using dynamic SQL introduces a lot of problems and will very likely make you regret your choice in the long run.
One thing that is awkward with dynamic SQL is the impossibility to view dependencies. Suppose you want the user to be able to call procedure X from your code. In a year or so, a new developer comes along and he is assigned the task to clean up the database of unused stuff. No reference to X is found anywhere, so he removes it. Then, fast forward another 2 months, an application user enters data that forces a call to X. This will fail. I bet you the person who has to solve that issue will NOT be happy (especially if all exceptions are caught "nicely" and replaced with user-friendly messages, making it mere impossible to pinpoint the exact error)
Re: Inquiry on calling procedures within a procedure [message #417314 is a reply to message #417309] Fri, 07 August 2009 02:52 Go to previous messageGo to next message
jr_db_dev
Messages: 7
Registered: June 2009
Junior Member
Noted on your recommendation.

Actually that is our current implementation. However, this requires code change for every new service that is added, and since it is a generic SP, all services using it should perform post-testing every update on this Generic SP, whic is an undersirable scenario for the tester, considering there are more than 10 services calling the Generic SP.
This is why I would would like to explore if my suggested procedure is feasible, and therefore ask how it could be done.

On your concern the possible user-error of dropping the procedure, it is unlikely, unless the whole package is dropped, and may be avoided by proper docu.

Anyway, again, many thanks for assessing the situation. Furthermore I would appreciate if you can suggest also as to how it can be done as well. The procedure below cannot be compiled because of the line:
trade_apps.v_spname (v_params);
It requires the above object name to be declared.
Re: Inquiry on calling procedures within a procedure [message #417315 is a reply to message #417314] Fri, 07 August 2009 03:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The fact that dropping one of the procedures implies dropping the whole package, does that mean that the subsequent procedures reside in the same package as the caller?
That would mean (extrapolating on your reasoning) that the tester should test all scenarios anyway, since the package as a whole changed..
The way to do this in dynamic sql is to build a character string that contains the call to the procedure, then use EXECUTE IMMEDIATE to call it.
Example (not tested, just an example)
sqlstring := 'begin '||trade_apps.v_spname||'(:1)';
-- use a logging mechanism here to be able to log the contents of sqlstring
execute immediate sqlstring using v_params;

I really hope that v_params is a scalar (despite its name)..

[Updated on: Fri, 07 August 2009 03:05]

Report message to a moderator

Re: Inquiry on calling procedures within a procedure [message #417318 is a reply to message #417314] Fri, 07 August 2009 03:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I had a ssytem that worked by using NDS to call a procedure dynamically, and made a noticable performance improvement by switching to an IF-THEN-ELSE list.

What you can do is use a hybrid approach - have an explicit IF list for everything that you currently know aqbout, and then add an ELSE clause at the end that wil run anything else using EXECUTE IMMEDIATE. That way you can update the generic procedure once in a while to explicitly add in new procedures, and it will still handle other procedures.


Of course, in response to
Quote:
However, this requires code change for every new service that is added
I'd point out that adding your new service will require a code change anyway.
Re: Inquiry on calling procedures within a procedure [message #417321 is a reply to message #417318] Fri, 07 August 2009 03:29 Go to previous messageGo to next message
jr_db_dev
Messages: 7
Registered: June 2009
Junior Member
Thanks for all your comments, JRowbottom and Frank.

I understand what you mean abt the specific procedures to be excluded from the package containing the Generic Procedure.

Perhaps we will leave the specific Procs to be stand-alone.

However, given your inputs, I think we should assess this implementation further.

Again many thanks for the time,

JRowbottom, may I ask on what you meant by 'noticable performance improvement'? On what specific area? Is this on the duration of execution? or DB utilization? etc?
Re: Inquiry on calling procedures within a procedure [message #417324 is a reply to message #417321] Fri, 07 August 2009 04:08 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Using NDS was causing a hard parse every time a procedure was executed. Removing this reduced cpu usage, and also reduced the time taken for procedures to execute.

Here's a little test you can use to see the difference in times for normal as opposed to NDS executions:
create or replace procedure test_032 as begin null; end;
/

create or replace type ty_timing_table as table of number;
/

declare
  
  t_timing    ty_timing_table := ty_timing_table();
  
  v_iter      pls_integer := 100000;
  v_max       pls_integer := 1000;
  v_time      pls_integer;
  v_avg       number;
  v_total     pls_integer;
  v_stddev    number;
  
begin
  t_timing.delete;
  t_timing.extend(v_iter);

  for i in 1..v_iter loop
    v_time := dbms_utility.get_time;
    
    begin
      test_032;
    end;
    
    t_timing(i) := dbms_utility.get_time - v_time;
  end loop;
  
  select avg(column_value)
        ,stddev(column_value)
        ,sum(column_value)
  into   v_avg,v_stddev,v_total
  from   table(t_timing);
  
  dbms_output.put_line('Test 1: Total: '||v_total||' Avg: '||round(v_avg,3-trunc(log(10,v_avg)))||' stddev: '||round(v_stddev,3));

  t_timing.delete;
  t_timing.extend(v_iter);

  for i in 1..v_iter loop
    v_time := dbms_utility.get_time;
    
    execute immediate 'begin test_032; end;';
    
    t_timing(i) := dbms_utility.get_time - v_time;
  end loop;

  select avg(column_value)
        ,stddev(column_value)
        ,sum(column_value)
  into   v_avg,v_stddev,v_total
  from   table(t_timing);
  
  dbms_output.put_line('Test 1: Total: '||v_total||' Avg: '||round(v_avg,3-trunc(log(10,v_avg)))||' stddev: '||round(v_stddev,3));
  
end;
/
Previous Topic: tuning of sql statement(top 25 emp highst earing)
Next Topic: Need help in tuning
Goto Forum:
  


Current Time: Sat Dec 10 13:00:34 CST 2016

Total time taken to generate the page: 0.10509 seconds