ODI Object Queries

Following query prints mapping folder path:

set serveroutput on

declare
mapping_name varchar2(200):='MAPPING_NAME';
folder_id int;
parent_folder_id int;
folder_name varchar(200);
begin
DBMS_OUTPUT.ENABLE(1000000);
dbms_output.put_line('Mapping: '||mapping_name);
select i_folder into folder_id from SNP_MAPPING where name=mapping_name;
select i_folder,folder_name,par_i_folder into folder_id, folder_name,parent_folder_id from snp_folder where i_folder=folder_id;
while parent_folder_id is not null
loop
dbms_output.put_line('Folder: '||folder_name);
select i_folder,folder_name,par_i_folder into folder_id, folder_name,parent_folder_id from snp_folder where i_folder=parent_folder_id;
end loop;
dbms_output.put_line('Folder: '||folder_name);
end;


Following query prints package folder path:


set serveroutput on
declare
pck_name varchar2(200):='PACKAGE_NAME';
folder_id int;
parent_folder_id int;
folder_name varchar(200);
begin
DBMS_OUTPUT.ENABLE(1000000);
dbms_output.put_line('Pck: '||pck_name);
select i_folder into folder_id from SNP_PACKAGE where pack_name=pck_name;
select i_folder,folder_name,par_i_folder into folder_id, folder_name,parent_folder_id from snp_folder where i_folder=folder_id;
while parent_folder_id is not null
loop
dbms_output.put_line('Folder: '||folder_name);
select i_folder,folder_name,par_i_folder into folder_id, folder_name,parent_folder_id from snp_folder where i_folder=parent_folder_id;
end loop;
dbms_output.put_line('Folder: '||folder_name);
end;