I am not able to pass two bind variables into a dynamic sql clause. When I use one variable, I get the result I expect. However, if I have two variables, then there no data found. Could it be that appending an AND clause to the main query and using a second bind variable at that point is problematic? I can't see why this won't work. AskTom and TechontheNet examples suggest that this shouldn't be a problem.
DECLARE
l_rcpt VARCHAR2(10);
l_allocation allocation.allocation_code%TYPE;
TYPE cur_typ IS REF CURSOR;
c cur_typ;
query_str VARCHAR2(1000);
gift_query VARCHAR2(800) :=
'SELECT gift_donor_id,
gift_receipt_number,
gift_sequence,
gift_account,
gift_alloc_dept_code,
gift_program_credit_code,
gift_associated_purpose,
gift_annual_sw,
gift_receipt_date,
gift_associated_fund_name,
gift_alloc_school,
gift_alloc_division,
allocation_code,
account,
alloc_dept_code,
program_code,
annual_sw,
alloc_purpose,
fund_name,
alloc_school,
alloc_division
FROM gift,
allocation a
WHERE gift_associated_allocation = :alloc
AND allocation_code = gift_associated_allocation';
append_gift_query VARCHAR2(200) :=
' AND gift_receipt_number = :rcpt';
-- just here to set the cursor type up
CURSOR temp_cursor IS
SELECT gift_donor_id,
gift_receipt_number,
gift_sequence,
gift_account,
gift_alloc_dept_code,
gift_program_credit_code,
gift_associated_purpose,
gift_annual_sw,
gift_receipt_date,
gift_associated_fund_name,
gift_alloc_school,
gift_alloc_division,
allocation_code,
account,
alloc_dept_code,
program_code,
annual_sw,
alloc_purpose,
fund_name,
alloc_school,
alloc_division
FROM gift,
allocation a
WHERE allocation_code = gift_associated_allocation;
trans_rec temp_cursor%rowtype;
BEGIN
l_rcpt := '164567';
l_allocation := '14201';
query_str := gift_query|| CASE WHEN l_rcpt IS NOT NULL THEN append_gift_query ELSE NULL END;
dbms_output.put_line(query_str);
OPEN c FOR query_str USING l_allocation, l_rcpt;
LOOP
FETCH c INTO trans_rec;
dbms_output.put_line('in loop');
EXIT WHEN c%NOTFOUND;
dbms_output.put_line(trans_rec.gift_donor_id);
END LOOP;
END LOOP;
CLOSE c;
COMMIT;
END;
It appears that the query is being setup correctly with the appended AND clause.
The query sting from dbms_output is:
SELECT gift_donor_id,
gift_receipt_number,
gift_sequence,
gift_account,
gift_alloc_dept_code,
gift_program_credit_code,
gift_associated_purpose,
gift_annual_sw,
gift_receipt_date,
gift_associated_fund_name,
gift_alloc_school,
gift_alloc_division,
allocation_code,
account,
alloc_dept_code,
program_code,
annual_sw,
alloc_purpose,
fund_name,
alloc_school,
alloc_division
FROM gift,
allocation a
WHERE gift_associated_allocation = :alloc
AND allocation_code = gift_associated_allocation AND gift_receipt_number = :rcpt