| Calling procedures dynamically [message #38673] |
Mon, 06 May 2002 02:08  |
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  |
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...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
