Home » Developer & Programmer » Forms » Dynamic Procedure Calling
Dynamic Procedure Calling [message #190972] Sun, 03 September 2006 12:26 Go to next message
dcaptain
Messages: 4
Registered: September 2006
Location: Philippines
Junior Member

Hi,
We will implement calling of procedure based on the value fetch by the database. Is it possible?

sample code..
declare
   v_proc varchar2(30);
begin
   v_proc := :control.proc;   
   if v_proc = 'SP1' then
      SP1;      
   elsif v_proc = 'SP2' then
      SP2;
   end if;
end;

instead..
declare
   v_proc varchar2(30);
begin
   v_proc := :control.proc;   
   call name_in(v_proc);  -- assuming dynamic call 
end;

Any solution like this would greatly appreciated instead of repeating the code depends on how many stored proc to be called.

Many Thanks,
Cap

[Updated on: Mon, 11 September 2006 02:39] by Moderator

Report message to a moderator

Re: Dynamic Procedure Calling [message #191336 is a reply to message #190972] Tue, 05 September 2006 15:12 Go to previous messageGo to next message
RJ.Zijlstra
Messages: 104
Registered: December 2005
Location: Netherlands - IJmuiden
Senior Member
Hi Captain,

I don't think it's possible:
At compile time, how can the compiler know the name of the procedure and therefore is able to decide if this a correct name, if you have rights to execute it, etc etc.

But I might be wrong!

Regards,

Rob Zijlstra
Re: Dynamic Procedure Calling [message #191354 is a reply to message #191336] Tue, 05 September 2006 18:21 Go to previous messageGo to next message
dcaptain
Messages: 4
Registered: September 2006
Location: Philippines
Junior Member

Thanks Rob Zijlstra for your response.

Any insights wil be appreciated.

Best Regards,
Cap Sanchez
Re: Dynamic Procedure Calling [message #191763 is a reply to message #190972] Fri, 08 September 2006 01:26 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Create a database procedure like
CREATE OR REPLACE PROCEDURE exec_Dynamic_Proc_sp
     (Proc_Name  IN VARCHAR2)
IS
BEGIN
  IF Proc_Name IS NOT NULL  THEN
    EXECUTE IMMEDIATE ('Exec '||Proc_Name);
  END IF;
    
EXCEPTION 
  /* write exceptions according to your nead. Like is passed procedure
   does not exists or user do not have access to execute that procedure.
   you can also check the existence of procedure before execute 
   immediate by checking in object table.*/
WHEN OTHERS 
  null;
END;
then call this procedure in your form like.
begin
  Exec_Dynamic_proc_sp(:control.proc); 
end;

Sandy

[Updated on: Mon, 11 September 2006 02:44] by Moderator

Report message to a moderator

Re: Dynamic Procedure Calling [message #191767 is a reply to message #191763] Fri, 08 September 2006 01:44 Go to previous messageGo to next message
dcaptain
Messages: 4
Registered: September 2006
Location: Philippines
Junior Member

Thanks for the reply Sandy..

What if it's not a Backend Procedure..
It's a PROGRAM UNIT..

you want to dynamically called a program unit using Developer Forms...

sample

Program units name as follows;
SP_1
SP_2
SP_3

table tbl_dyn_sp
Code Description
SP_1 Stored procedure Test1
SP_2 Stored procedure Test1
SP_3 Stored procedure Test1


-- code proper --
declare
  v_code varchar2(30);
cursor c1 (p1 varchar2) is
select code
  from tbl_dyn_sp
 where code = p1;
begin
   open c1(<variable>);
   fetch c1 into v_code;
   close c1;

   -- here is my question --
   -- how would I call the Program unit 
   -- depends on the variable passed?
   
   call v_code;  -- any other way to invoke the Program units?

   --
end;
Thanks and God Bless! Smile

Cap

[Updated on: Mon, 11 September 2006 02:46] by Moderator

Report message to a moderator

Re: Dynamic Procedure Calling [message #192130 is a reply to message #191767] Mon, 11 September 2006 02:48 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I would suggest that you don't use program units to do 'work'. It is inefficient in terms of doing database processing. Program units and program libraries should be used for doing 'forms' work, not database work.

David
Previous Topic: Manipulate files and folders with Oracle Forms
Next Topic: Display Word Document
Goto Forum:
  


Current Time: Mon Dec 05 12:37:30 CST 2016

Total time taken to generate the page: 0.07068 seconds