Hi Masters,
I have a procedure which is having 3 parameters with sys_refcursor. But requirement is instead of sys_refcursor I need to write a procedures with table type parameter. How can I change ? Please advise..!!!
But if you ask why ? I don't have proper answer. Org. need that ...!!!
I am working on Oracle 10.2.1 version.
PROCEDURE sp_adm_get_srvinfo(p_part_no VARCHAR2 ,
p_sdlr_state NUMBER,
p_srvinfo_cursor OUT SYS_REFCURSOR)
IS
v_subclass_id NUMBER(22);
v_subclass_name VARCHAR2(100);
v_srv_subclass_id NUMBER(22);
V_PF_MOD_FLG CHAR(1) :='N';
v_Platform_ID NUMBER(22);
v_Batch_id NUMBER(22);
v_change_id NUMBER(22);
v_a_rev_id NUMBER(22);
v_x_rev_id NUMBER(22);
v_part_id NUMBER(22);
v_sw_proj_affected VARCHAR2(1000);
v_ph_subclass_id NUMBER(22);
v_ph_proj_affected VARCHAR2(1000);
TYPE x_ref_cursor IS REF CURSOR;
ph_ref_cursor x_ref_cursor;
srv_ref_cursor x_ref_cursor;
v_bom_level NUMBER(22) := 64;
g_srv_counter NUMBER(22) ;
srvinfo_excp EXCEPTION;
srvcur_excp EXCEPTION;
sdlr_excp EXCEPTION;
invalid_part_excp exception;
v_stmt VARCHAR2(8000);
v_ph_stmt VARCHAR2(200);
v_component_id NUMBER(22);
v_inp_part_id NUMBER(22);
v_inp_chg_id NUMBER(22);
v_item_number varchar2(100);
v_rev_number varchar2(100);
v_desc VARCHAR2(4000);
v_proj_affected VARCHAR2(500);
v_lifecycle_phase VARCHAR2(500);
v_region_affected VARCHAR2(500);
v_lob_affected VARCHAR2(500);
v_product_name VARCHAR2(500);
v_vendor VARCHAR2(500);
v_sw_type VARCHAR2(500);
v_langauge VARCHAR2(500);
v_media_type VARCHAR2(500);
v_version VARCHAR2(500);
v_payment_type VARCHAR2(500);
v_source VARCHAR2(500);
v_blank_media VARCHAR2(500);
v_ccat_num VARCHAR2(500);
v_dept_comm_review VARCHAR2(500);
v_encrypt_bit_level VARCHAR2(500);
v_sw_contains_encrypt VARCHAR2(500);
v_num_of_lic VARCHAR2(500);
v_num_of_media VARCHAR2(500);
v_os_comp VARCHAR2(500);
v_packaging VARCHAR2(500);
v_part_sub_type VARCHAR2(500);
v_resource_required VARCHAR2(500);
v_resource_supplied VARCHAR2(500);
BEGIN
g_srv_part_info_rec := srv_par_info_tab_type();
v_proc_name := 'SP_ADM_Get_SRVInfo';
adm.plog.info('Starting Procdedure ' || v_proc_name );
BEGIN
SELECT id,subclass_id,default_change_id
INTO v_inp_part_id,v_subclass_id,v_inp_chg_id
FROM ODM_ITEM_PARTS
WHERE ITEM_NUMBER=p_part_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_err_code := SQLCODE;
v_err_message := v_err_code || '- Error Message:' || SQLERRM;
INSERT INTO adm_aic_log_error (SNO,proc_name,
sql_error_message,
custom_message)
VALUES (adm_aic_err_seq.NEXTVAL,
v_proc_name,v_err_message,
'Error while fetching subclass of the item');
COMMIT;
raise invalid_part_excp;
END;
dbms_output.put_line('2222');
BEGIN
SELECT subclass
INTO v_subclass_name
FROM odm_class_metadata
WHERE subclass_id=v_subclass_id;
EXCEPTION
WHEN OTHERS THEN
v_err_code := SQLCODE;
v_err_message := v_err_code || '- Error Message:' || SQLERRM;
INSERT INTO adm_aic_log_error (SNO,proc_name,sql_error_message,custom_message) VALUES (adm_aic_err_seq.NEXTVAL,v_proc_name,v_err_message,'Error while fetching subclass name');
COMMIT;
END;
dbms_output.put_line('3333');
BEGIN
IF (v_subclass_name ='Platform Hierarchy') THEN
v_pf_mod_flg :='P';
ELSIF ( instr(upper(v_subclass_name),'MOD')>0 ) THEN
dbms_output.put_line('Inside MMM');
v_pf_mod_flg :='M';
ELSIF (v_subclass_name ='Software Bundle') THEN
v_pf_mod_flg :='S';
ELSE
INSERT INTO adm_aic_log_error (SNO,proc_name,sql_error_message,custom_message) VALUES (adm_aic_err_seq.NEXTVAL,v_proc_name,v_err_message,'The part should be one of these three PH,SWB or MODO/S');
COMMIT;
raise srvinfo_excp;
END If;
EXCEPTION
WHEN srvinfo_excp THEN
raise srvinfo_excp;
WHEN OTHERS THEN
v_err_code := SQLCODE;
v_err_message := v_err_code || '- Error Message:' || SQLERRM;
INSERT INTO adm_aic_log_error (SNO,proc_name,sql_error_message,custom_message) VALUES (adm_aic_err_seq.NEXTVAL,v_proc_name,v_err_message,'Error while fetching target dim table');
COMMIT;
raise;
END;
dbms_output.put_line('4444');
v_srv_subclass_id := pkg_aic_gen_utilities.PROC_FETCH_SUBCLASS_ID('Service Install');
BEGIN
v_a_rev_id := pkg_aic_gen_utilities.func_getLifeCyclePhaseID('A Revision');
v_x_rev_id := pkg_aic_gen_utilities.func_getLifeCyclePhaseID('X Revision');
EXCEPTION
WHEN OTHERS THEN
v_err_code := SQLCODE;
v_err_message := v_err_code || '- Error Message:' || SQLERRM;
INSERT INTO adm_aic_log_error (SNO,proc_name,sql_error_message,custom_message) VALUES (adm_aic_err_seq.NEXTVAL,v_proc_name,v_err_message,'Error while fetching IDs for XREV and AREV in SRV Info Proc');
COMMIT;
END;
dbms_output.put_line('5555'||v_pf_mod_flg);
IF (v_pf_mod_flg='P') THEN
v_ph_stmt := 'SELECT id,default_change_id,id as component_id FROM ODM_PARTS_SUM WHERE item_number ='||''''||p_part_no||'''';
dbms_output.put_line('The ph stmt is:'||v_ph_stmt);
OPEN ph_ref_cursor FOR v_ph_stmt;
END IF;
dbms_output.put_line('Before Fetch');
v_proj_affected := 'pkg_aic_gen_utilities.func_getColValue('||'''*Proj-Platform Affected''' ||',PRODUCT_LINES'||','||'item.id,' ||'NULL'||')';
v_lifecycle_phase := 'pkg_aic_gen_utilities.func_get_list_gen_col_name_val('||'''REV'''||','||'''List'''||','||'''RELEASE_TYPE'''||','||'item.id' ||')';
v_region_affected := 'pkg_aic_gen_utilities.func_getColValue('||'''*Regions Affected'''||',item.MULTILIST01,'|| 'NULL' ||')';
v_lob_affected := 'pkg_aic_gen_utilities.func_getColValue('||'''*LOBs Affected'''||',item.MULTILIST02'||','||'NULL' ||')';
v_product_name := 'pkg_aic_gen_utilities.func_getColValue('||'''*Product Name'''||',LIST38_ID'||','||v_srv_subclass_id||')';
v_vendor := 'pkg_aic_gen_utilities.func_getColValue('||'''*Vendor'''||',LIST33_ID'||','||v_srv_subclass_id||')';
v_sw_type := 'pkg_aic_gen_utilities.func_getColValue('||'''*Software Type'''||',LIST37_ID'||','||v_srv_subclass_id||')';
v_langauge := 'pkg_aic_gen_utilities.func_getColValue('||'''*Language'''||',MULTILIST33'||','||v_srv_subclass_id||')';
v_media_type := 'pkg_aic_gen_utilities.func_getColValue('||'''*Media Type'''||',LIST32_ID'||','||v_srv_subclass_id||')';
v_version := 'pkg_aic_gen_utilities.func_getColValue('||'''*Version'''||',LIST36_ID'||','||v_srv_subclass_id||')';
v_payment_type := 'pkg_aic_gen_utilities.func_getColValue('||'''*Payment Type'''||',LIST34_ID'||','||v_srv_subclass_id||')';
v_source := 'pkg_aic_gen_utilities.func_getColValue('||'''*Source'''||',LIST35_ID'||','||v_srv_subclass_id||')';
v_blank_media := 'pkg_aic_gen_utilities.func_getColValue('||'''BlankMedia'''||',LIST40_ID'||','||v_srv_subclass_id||')';
v_ccat_num := 'pkg_aic_gen_utilities.func_getColValue('||'''CCAT#(number)'''||',LIST41_ID'||','||v_srv_subclass_id||')';
v_dept_comm_review := 'pkg_aic_gen_utilities.func_getColValue('||'''DeptOfCommerceReview'''||',LIST42_ID'||','||v_srv_subclass_id||')';
v_encrypt_bit_level := 'pkg_aic_gen_utilities.func_getColValue('||'''EncryptionBitLevel(bits)'''||',LIST43_ID'||','||v_srv_subclass_id||')';
v_sw_contains_encrypt := 'pkg_aic_gen_utilities.func_getColValue('||'''SWContainsEncryption'''||',LIST47_ID'||','||v_srv_subclass_id||')';
v_num_of_lic := 'pkg_aic_gen_utilities.func_getColValue('||'''NumberOfLIC'''||',LIST44_ID'||','||v_srv_subclass_id||')';
v_num_of_media := 'pkg_aic_gen_utilities.func_getColValue('||'''NumberOfMediaType(number)'''||',LIST45_ID'||','||v_srv_subclass_id||')';
v_os_comp := 'pkg_aic_gen_utilities.func_getColValue('||'''OSCompatibility'''||',LIST39_ID'||','||v_srv_subclass_id||')';
v_packaging := 'pkg_aic_gen_utilities.func_getColValue('||'''Packaging'''||',LIST46_ID'||','||v_srv_subclass_id||')';
v_part_sub_type := 'pkg_aic_gen_utilities.func_getColValue('||'''PartSub Type'''||',LIST31_ID'||','||v_srv_subclass_id||')';
v_resource_required := 'pkg_aic_gen_utilities.func_getColValue('||'''ResourceRequired''' ||',MULTILIST31'||','|| v_srv_subclass_id ||')';
v_resource_supplied := 'pkg_aic_gen_utilities.func_getColValue('||'''ResourceSupplied''' ||',MULTILIST32'||','|| v_srv_subclass_id ||')';
v_stmt := 'SELECT * FROM (SELECT DISTINCT ITEM.ITEM_NUMBER,item.description, REV.REV_NUMBER, ';
v_stmt := v_stmt || v_proj_affected || ' as Project_Platfrom_affected, '||v_lifecycle_phase || ' as Life_Cycle_Phase, '|| v_region_affected || ' as Region_Affected,';
v_stmt := v_stmt || v_lob_affected || ' as LOB_Affected,'||v_product_name || ' as Product_Name, '|| v_vendor || ' as Vendor, '|| v_sw_type || ' as Software_Type, ';
v_stmt := v_stmt || v_langauge || ' as Language,'|| v_media_type || ' as Media_Type, '||v_version || ' as Version, '||v_payment_type || ' as Payment_Type,'|| v_source || ' as Source,';
v_stmt := v_stmt || v_blank_media || ' as Blank_Media,'|| v_ccat_num || ' as CCAT_Number,'||v_dept_comm_review || ' as Dept_of_commerice_Review,'||v_encrypt_bit_level||' as Encrypt_Bit_Level,';
v_stmt := v_stmt || v_sw_contains_encrypt || ' as SW_Contains_Encryption,'||v_num_of_lic || ' as Number_of_LIC,'||v_num_of_media|| ' as Number_Of_Media_Type,'||v_os_comp || ' as OSCompatibility,';
v_stmt := v_stmt || v_packaging || ' as Packaging,'||v_part_sub_type|| ' as Part_Sub_Type,'|| v_resource_required || ' as Resource_Required,'||v_resource_supplied|| ' as ResourceSupplied';
IF (v_pf_mod_flg IN ('P') ) THEN
LOOP
FETCH ph_ref_cursor INTO v_part_id,v_change_id,v_component_id;
EXIT WHEN ph_ref_cursor%NOTFOUND;
dbms_output.put_line('After Fetch');
dbms_output.put_line('6666');
delete from aic_bom_result where id >0;
pkg_aic_gen_utilities.sp_adm_bom_populate_fly(v_part_id);
INSERT INTO aic_bom_result(assembly_id,component_id,change_id,batch_id,platform_id)
SELECT item_id,component_id,change_id,batch_id,platform_id FROM agilert_bom_result_fly;
v_stmt := 'select * from ('||v_stmt || ' FROM aic_bom_result BR , ODM_REV REV, ODM_PARTS_SUM ITEM WHERE BR.COMPONENT_ID= REV.ITEM_ID ';
v_stmt := v_stmt || ' AND BR.CHANGE_ID = REV. CHANGE_ID AND REV.DELETE_FLAG=0 AND BR.COMPONENT_ID = ITEM.ID AND item.id = rev.item_id AND ITEM.SUBCLASS_id= '||v_srv_subclass_id;
v_stmt :=v_stmt || ' AND BR.PLATFORM_ID = '|| v_part_id;
v_stmt := v_stmt|| ' AND BR.batch_id = '||v_change_id;
dbms_output.put_line('7777');
END LOOP;
ELSIF (v_pf_mod_flg IN ('M') ) THEN
dbms_output.put_line('Inside Mod');
delete from aic_bom_result where id >0;
pkg_aic_gen_utilities.sp_adm_bom_populate_fly(v_inp_part_id);
INSERT INTO aic_bom_result(assembly_id,component_id,change_id,batch_id,platform_id)
SELECT item_id,component_id,change_id,batch_id,platform_id FROM agilert_bom_result_fly;
dbms_output.put_line('After populating BOM for Mod');
END IF;
--OPEN srv_ref_cursor for v_stmt;
dbms_output.put_line('The stmt is:'||v_stmt);
--END IF;
IF (v_pf_mod_flg='M') THEN
dbms_output.put_line('88888');
v_stmt := 'select * from ('||v_stmt || ' FROM aic_bom_result BR , ODM_REV REV, ODM_PARTS_SUM ITEM WHERE BR.COMPONENT_ID= REV.ITEM_ID ';
v_stmt := v_stmt || ' AND BR.CHANGE_ID = REV. CHANGE_ID AND REV.DELETE_FLAG=0 AND BR.COMPONENT_ID = ITEM.ID AND item.id = rev.item_id AND ITEM.SUBCLASS_id= '||v_srv_subclass_id;
v_stmt :=v_stmt || ' AND BR.PLATFORM_ID = '|| v_inp_part_id;
v_stmt := v_stmt|| ' AND BR.batch_id = '||v_inp_chg_id;
ELSIF (v_pf_mod_flg='S') THEN
dbms_output.put_line('Before calling SRV procedure for input part:'||v_inp_part_id);
delete aic_gtt_srv;
pkg_aic_gen_utilities.sp_adm_get_whereused_for_srv (v_inp_part_id);
dbms_output.put_line('After calling SRV procedure');
v_stmt := 'select * from ('|| v_stmt ||' from odm_parts_sum item,odm_rev rev where item.id in (select id from aic_gtt_srv) and item.id=rev.item_id and item.default_change_id=rev.change_id AND REV.DELETE_FLAG=0 ';
END IF;
IF (p_SDLR_state = 0) THEN
v_stmt := v_stmt|| ' AND (REV.RELEASE_TYPE IN ('|| v_x_rev_id ||','||v_a_rev_id ||' ) OR REV.RELEASE_TYPE IS NULL ) )) WHERE item_number is NOT NULL ORDER BY ITEM_NUMBER';
ELSIF (p_SDLR_state = 1) THEN
v_stmt := v_stmt || 'AND ( REV.RELEASE_TYPE IN (' ||v_a_rev_id|| ') OR REV.RELEASE_TYPE IS NULL )) ) WHERE item_number is NOT NULL ORDER BY ITEM_NUMBER';
ELSE
raise sdlr_excp;
END IF;
dbms_output.put_line('The statement is:'||v_stmt);
adm.plog.info(v_stmt);
OPEN srv_ref_cursor for v_stmt;
g_srv_counter := 0;
dbms_output.put_line('1515151515');
LOOP
--FETCH srv_ref_cursor INTO v_item_number,v_desc,v_rev_number;
FETCH srv_ref_cursor INTO v_srv_part_info_rec ;
EXIT WHEN srv_ref_cursor%NOTFOUND;
dbms_output.put_line('1717171717171');
g_srv_counter := g_srv_counter + 1;
g_srv_part_info_rec.extend;
IF (trim(v_srv_part_info_rec.item_number) IS NOT NULL) THEN
g_srv_part_info_rec(g_srv_counter).ITEM_NUMBER := v_srv_part_info_rec.item_number;
g_srv_part_info_rec(g_srv_counter).description := v_srv_part_info_rec.description;
g_srv_part_info_rec(g_srv_counter).revision := v_srv_part_info_rec.revision;
g_srv_part_info_rec(g_srv_counter).Project_Affected := v_srv_part_info_rec.Project_Affected ;
g_srv_part_info_rec(g_srv_counter).Life_cycle_Phase := v_srv_part_info_rec.Life_cycle_Phase ;
g_srv_part_info_rec(g_srv_counter).Regions_Affected := v_srv_part_info_rec.Regions_Affected ;
g_srv_part_info_rec(g_srv_counter).LOBs_Affected := v_srv_part_info_rec.LOBs_Affected ;
g_srv_part_info_rec(g_srv_counter).Product_Name := v_srv_part_info_rec.Product_Name ;
g_srv_part_info_rec(g_srv_counter).Vendor := v_srv_part_info_rec.Vendor ;
g_srv_part_info_rec(g_srv_counter).Software_Type := v_srv_part_info_rec.Software_Type ;
g_srv_part_info_rec(g_srv_counter).Language := v_srv_part_info_rec.Language ;
g_srv_part_info_rec(g_srv_counter).Media_Type := v_srv_part_info_rec.Media_Type ;
g_srv_part_info_rec(g_srv_counter).Version := v_srv_part_info_rec.Version ;
g_srv_part_info_rec(g_srv_counter).Payment_Type := v_srv_part_info_rec.Payment_Type ;
g_srv_part_info_rec(g_srv_counter).Source := v_srv_part_info_rec.Source ;
g_srv_part_info_rec(g_srv_counter).BlankMedia := v_srv_part_info_rec.BlankMedia ;
g_srv_part_info_rec(g_srv_counter).CCAT# := v_srv_part_info_rec.CCAT# ;
g_srv_part_info_rec(g_srv_counter).Dept_Of_Commerce_Review := v_srv_part_info_rec.Dept_Of_Commerce_Review;
g_srv_part_info_rec(g_srv_counter).Encryption_Bit_Level := v_srv_part_info_rec.Encryption_Bit_Level ;
g_srv_part_info_rec(g_srv_counter).SW_Contains_Encryption := v_srv_part_info_rec.SW_Contains_Encryption ;
g_srv_part_info_rec(g_srv_counter).Number_Of_LIC := v_srv_part_info_rec.Number_Of_LIC ;
g_srv_part_info_rec(g_srv_counter).Number_Of_MediaType := v_srv_part_info_rec.Number_Of_MediaType ;
g_srv_part_info_rec(g_srv_counter).OS_Compatibility := v_srv_part_info_rec.OS_Compatibility ;
g_srv_part_info_rec(g_srv_counter).Packaging := v_srv_part_info_rec.Packaging ;
g_srv_part_info_rec(g_srv_counter).Part_Sub_Type := v_srv_part_info_rec.Part_Sub_Type ;
g_srv_part_info_rec(g_srv_counter).Resource_Required := v_srv_part_info_rec.Resource_Required ;
g_srv_part_info_rec(g_srv_counter).Resource_Supplied := v_srv_part_info_rec.Resource_Supplied ;
END IF;
END LOOP;
dbms_output.put_line('161616161616');
IF (g_srv_part_info_rec.count>0) THEN
dbms_output.put_line ('Before Opening cursor from collection');
OPEN p_srvinfo_cursor FOR SELECT * FROM TABLE(get_srv_info) WHERE ITEM_NUMBER IS NOT NULL;
dbms_output.put_line ('After Opening cursor from collection');
ELSE
OPEN p_srvinfo_cursor FOR select NULL AS ITEM_NUMBER,NULL AS DESCRIPTION,NULL AS REVISION,NULL AS Project_Affected,NULL AS Life_cycle_Phase,NULL AS Regions_Affected,
NULL AS LOBs_Affected,NULL AS Product_Name,NULL AS Vendor,NULL AS Software_Type,NULL AS Language,NULL AS Media_Type,NULL AS Version,
NULL AS Payment_Type,NULL AS Source,NULL AS BlankMedia,NULL AS CCAT#,NULL AS Dept_Of_Commerce_Review,NULL AS Encryption_Bit_Level,NULL AS SW_Contains_Encryption,
NULL AS Number_Of_LIC,NULL AS Number_Of_MediaType,NULL AS OS_Compatibility,NULL AS Packaging,NULL AS Part_Sub_Type,
NULL AS Resource_Required,
NULL AS Resource_Supplied FROM DUAL where 1=2;
-- raise srvcur_excp;
END IF;
--g_srv_part_info_rec.delete;
EXCEPTION
WHEN sdlr_excp THEN
RAISE_APPLICATION_ERROR(-20019,' For SDLR Information pass either 1 or 0');
WHEN srvcur_excp THEN
RAISE_APPLICATION_ERROR(-20001,'The SRV parts are not available for part:'||p_part_no);
when invalid_part_excp then
RAISE_APPLICATION_ERROR(-20022,'Invalid Part number passed for getting SRV information:' ||p_part_no);
when srvinfo_excp then
RAISE_APPLICATION_ERROR(-20023,'The input part should be one of these three PH,SWB or MODO/S');
WHEN OTHERS THEN
dbms_output.put_line (SQLERRM);
v_err_code := SQLCODE;
v_err_message := v_err_code || '- Error Message:' || SQLERRM;
INSERT INTO adm_aic_log_error (SNO,proc_name,sql_error_message,custom_message) VALUES (adm_aic_err_seq.NEXTVAL,v_proc_name,v_err_message,'Error in the program');
COMMIT;
raise;
END sp_adm_get_srvinfo;
Thanks In advi
Regards
Ar
Edited by: 845299 on Jul 23, 2012 7:05 AM