This post is in continuation to the problem solved about generating dynamic SQL. for loop inside a cursor oracle
Below is the PL/SQL I am executing which generates a dynamic sql by looping through the TYPE created as mentioned in the post above.
PROCEDURE p_XXX_XX(
p_id_i IN VARCHAR2,
p_error_code_o OUT VARCHAR2,
p_error_message_o OUT VARCHAR2,
pcur_XXX OUT SYS_REFCURSOR )
AS
l_array siebel.intl_crm.t_array;
strSelect_statement VARCHAR2(20000);
BEGIN
l_array := f_Split_String(p_company_id_i);
strSelect_statement :=
'SELECT contact.row_id AS contact_id,
contact.fst_name AS contact_first_name,
contact.last_name AS contact_last_name,
contact.cell_ph_num AS cell_phone,
contact.email_addr AS email_address,
party.party_id AS company_id,
addr.addr AS address_line_1,
addr.addr_line_2 AS address_line_2,
addr.addr_line_3 AS address_line_3,
addr.city,
addr.state AS state_province,
addr.zipcode,
addr.country,
NULL AS raw_most_recent_activity_dt,
contact.work_ph_num AS work_phone,
contact.alt_email_addr AS alternate_email,
contact.x_preferred_phone AS preferred_phone,
contact.x_preferred_email AS preferred_email,
contact.suppress_email_flg AS email_flag,
contact.fax_ph_num AS fax_phone,
NULL AS most_recent_activity_dt,
NULL AS mra_company_id,
NULL AS mra_company_name,
NULL AS accnt_val_cd
FROM s_party_per party
JOIN s_contact contact
ON party.person_id = contact.row_id
LEFT JOIN siebel.s_addr_per addr
ON contact.pr_per_addr_id = addr.row_id
WHERE party.reference_type_cd ='''||'Current Employee'
||'''AND UPPER(TRIM(contact.cust_stat_cd)) ='''||'CURRENT'
||'''AND party.party_id IN (';
FOR i IN l_array.FIRST .. l_array.LAST LOOP
strSelect_statement := strSelect_statement ||
'''' || l_array(i) || ''',';
END LOOP;
-- Get rid of the unwanted trailing comma
strSelect_statement := SUBSTR(strSelect_statement, 1,
LENGTH(strSelect_statement)-1);
-- Add a right parentheses to close the IN list
strSelect_statement := strSelect_statement || ')';
dbms_output.put_line('query: '||strSelect_statement);
-- Open the cursor
OPEN pcur_company_contacts_new FOR strSelect_statement;
p_error_code_o := SQLCODE;
p_error_message_o := SQLERRM;
END p_company_contact_new;
Below is the SQl generated after looping through the array
SELECT contact.row_id AS contact_id,
contact.fst_name AS contact_first_name,
contact.last_name AS contact_last_name,
contact.cell_ph_num AS cell_phone,
contact.email_addr AS email_address,
party.party_id AS company_id,
addr.addr AS address_line_1,
addr.addr_line_2 AS address_line_2,
addr.addr_line_3 AS address_line_3,
addr.city,
addr.state AS state_province,
addr.zipcode,
addr.country,
NULL AS raw_most_recent_activity_dt,
contact.work_ph_num AS work_phone,
contact.alt_email_addr AS alternate_email,
contact.x_preferred_phone AS preferred_phone,
contact.x_preferred_email AS preferred_email,
contact.suppress_email_flg AS email_flag,
contact.fax_ph_num AS fax_phone,
NULL AS most_recent_activity_dt,
NULL AS mra_company_id,
NULL AS mra_company_name,
NULL AS accnt_val_cd
FROM s_party_per party
JOIN s_contact contact
ON party.person_id = contact.row_id
LEFT JOIN siebel.s_addr_per addr
ON contact.pr_per_addr_id = addr.row_id
WHERE party.reference_type_cd ='Current Employee'AND UPPER(TRIM(contact.cust_stat_cd)) ='CURRENT'AND party.party_id IN ('3-9TTCJ2','1-19G1TZ','1-B3-4924','1-B1-2288','1-160JF','1-1QFF2L',
'1-AZ-1282','1-B5-2052','1-UJHQ9','3-C75CSW','3-C7GDYR',);
When I run the generated SQL in my editor, it fetches the resultset for all the id's passed. But when I run the PL/SQL block, it only returns a portion of the resultset but not the complete set. I am calling this procedure from java and when I test those ids separately, I see data in the database. I am guessing the issue is with returning CURSOR. Did anyone face similar kind of issue?
Thanks in advance!
