Home » Other » Client Tools » Calling procedures dynamically
Calling procedures dynamically [message #38673] Mon, 06 May 2002 02:08 Go to next message
Deepak Khemani
Messages: 5
Registered: April 2002
Junior Member
Hi..
I have a number of stored procrdures in my schema. I have stored the names of the procedures in a table 'Glb_Proc'. What I want to do is select the name of the procedure from the table and execute the procedure. For e.g I write the code 'Select proc_name from glb_proc where proc_id = 1' this gives a procedure name 'Test'. Now I want to know how I can execute this procedure.
Re: Calling procedures dynamically [message #38674 is a reply to message #38673] Mon, 06 May 2002 02:29 Go to previous message
Rico
Messages: 7
Registered: April 2002
Junior Member
In the PL/SQL Skript concat the execute string:

vcSQL_Mapping := ('Begin MAPPINGS.'|| vcAktuell_Mapping_Name || '; end;');
...
execute immediate vcSQL_Mapping;

Complete skript: (sorry I have not the time to translate ist or take out my comments)

Procedure Start_Load_ab_MappingID is

cursor curMappingID_Namen is
select MappingID, MAPPING_NAME
from WT_ABZUG_MAPPING_NAME where BENUTZEN= 'J' order by MappingID;

recMappingID_Namen curMappingID_Namen%ROWTYPE;

nStart_MappingID number(10) := - 1;
nAktuell_MappingID number(10) := - 1;
vcAktuell_Mapping_Name varchar2(40) := '';

vcSQL_Mapping varchar2(4000) := '';

Begin
-- Hole MappingID aus Statustabelle ABZUG_STATUS
nReturn_main := LOGGING( 5, 'Hole die aktuelle Mapping ID aus der Status Tabelle...');
nStart_MappingID := ABZUG_HELPER.Hole_MAPPING_ID;
nReturn_main := LOGGING( 5, 'Die aktuelle MappingID ist: ' || nStart_MappingID);
-- Hole Mappingnamen aus WT_ABZUG_MAPPING_NAME
open curMappingID_Namen;
loop
fetch curMappingID_Namen into nAktuell_MappingID, vcAktuell_Mapping_Name;
exit when curMappingID_Namen%NOTFOUND;
-- Beginnt ab der in derStatustabelle zuletzt bearbeiteten Mapping ID
if nAktuell_MappingID >= nStart_MappingID then
vcSQL_Mapping := ('Begin MAPPINGS.'|| vcAktuell_Mapping_Name || '; end;');
nReturn_main := LOGGING ( 0, CHR(10) || 'Starte Mapping: '|| vcAktuell_Mapping_Name);
nReturn_main := LOGGING ( 5, '...mit folgender Anweisung: '|| vcSQL_Mapping);
ABZUG_HELPER.Schreibe_MAPPING_ID(nAktuell_MappingID);
execute immediate vcSQL_Mapping;
end if;
end loop;
close curMappingID_Namen;

exception...
Previous Topic: Re: Problem with SQL Trace Command
Next Topic: Re: rollback segment
Goto Forum:
  


Current Time: Fri Mar 29 01:10:23 CDT 2024