Tom, Thanks for the lightning response.
This is required by the Front-end team. Whenever they pass a parent run_id, dependent run_ids and their dependents should be displayed. They need a simple select where they pass the parent run_id and they get all the dependents. So, I thought of putting them in a Table Type and return it from a function. The main query gets the parent run_id as input and returns all the dependent run_ids. Here is the simplified part of it with codes:
create table PROCESS_DEPENDENCY
(
RUN_ID NUMBER not null,
DEPENDENT_RUN_ID NUMBER not null
);
insert into process_control values (1337,0);
insert into process_control values (1338,1337);
insert into process_control values (1339,1338);
insert into process_control values (1340,1339);
insert into process_control values (1341,1340);
insert into process_control values (1342,1340);
insert into process_control values (1343,1340);
insert into process_control values (1344,1340);
insert into process_control values (1345,1287);
insert into process_control values (1346,0);
insert into process_control values (1347,1346);
insert into process_control values (1348,1347);
insert into process_control values (1349,1348);
insert into process_control values (1350,1340);
insert into process_control values (1351,1349);
insert into process_control values (1352,1351);
insert into process_control values (1353,0);
insert into process_control values (1354,1353);
insert into process_control values (1355,1354);
insert into process_control values (1356,1355);
insert into process_control values (1357,0);
insert into process_control values (1358,1357);
insert into process_control values (1359,1358);
insert into process_control values (1360,1359);
create or replace type ty_dep_run_id as object
(
run_id number,
dependent_run_id number
);
create or replace type tab_dep_run_id is table of ty_dep_run_id;
CREATE OR REPLACE FUNCTION fn_get_dependent_run (ip_run_id in number) RETURN
tab_dep_run_id
AS
v_dep_tab tab_dep_run_id := tab_dep_run_id();
v_sql VARCHAR2(4000);
BEGIN
v_sql := 'SELECT pd.run_id,
pd.dependent_run_id
FROM cpm_data.process_dependency pd
START WITH run_id = '||ip_run_id||'
CONNECT BY prior pd.dependent_run_id = pd.run_id';
execute immediate ' SELECT cast(multiset('||v_sql||') as tab_dep_run_id) from dual'
bulk collect into v_dep_tab;
return v_dep_tab;
END;I donot want to display them in a dbms_output. I wanted it in a select as an output.
select * from table(fn_get_dependent_run(1352));
The above should return the hierarchy from top to bottom. Please help.
July 19, 2013 - 12:58 pm UTC
why do you believe you need dynamic sql for this??????????????????????????????????????????????????????????????
ops$tkyte%ORA11GR2> CREATE OR REPLACE FUNCTION fn_get_dependent_run (ip_run_id in number)
2 RETURN tab_dep_run_id
3 AS
4
5 v_dep_tab tab_dep_run_id;
6
7 BEGIN
8 select ty_dep_run_id( pd.run_id, pd.dependent_run_id)
9 bulk collect into v_dep_tab
10 FROM process_dependency pd
11 START WITH run_id = fn_get_dependent_run.ip_run_id
12 CONNECT BY prior pd.dependent_run_id = pd.run_id;
13
14 return v_dep_tab;
15 END;
16 /
Function created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> declare
2 l_data tab_dep_run_id;
3 begin
4 l_data := fn_get_dependent_run( 1352 );
5 dbms_output.put_line( l_data.count || ' entries' );
6 for i in 1 .. l_data.count
7 loop
8 dbms_output.put_line( l_data(i).run_id || ', ' || l_data(i).dependent_run_id );
9 end loop;
10 end;
11 /
6 entries
1352, 1351
1351, 1349
1349, 1348
1348, 1347
1347, 1346
1346, 0
PL/SQL procedure successfully completed.
you do not need, want, nor desire dynamic sql.
you have a query, it has inputs (run id). Just code static sql using bind variables like I've been demonstrating.