Hello,
I have one question about fetching variable values in a select statement.
I have a procedure in which I have 8 single inputs and one out variable which is a table type.
I have a cursor which takes value from another table and should return into OUT variable.
Here I also want to return the input values as well.
So I was thinking if I can write the input variables in the select statement so it will be easy to handle for me.
Can anyone please suggest how can I write the select statement in the cursor so that I can get the output with the input variables .
PROCEDURE get_parameter_value_proc( pi_application_name IN VARCHAR2
, pi_name IN VARCHAR2
, pi_reg_cty IN VARCHAR2
, pi_param_field_1 IN VARCHAR2
, pi_param_field_2 IN VARCHAR2
, pi_param_field_3 IN VARCHAR2
, pi_param_field_4 IN VARCHAR2
, pi_param_field_5 IN VARCHAR2
, po_value OUT parameter_value_rec_type
)
IS --Needs to return all the basic columns --- ##
TYPE r_cursor is REF CURSOR;
c_value r_cursor;
BEGIN
OPEN c_value
FOR SELECT :pi_application_name,:pi_name,:pi_reg_cty,:pi_param_field_1,:pi_param_field_2,:pi_param_field_3,:pi_param_field_4,:pi_param_field_5,param_value
FROM parameter_value_t
WHERE parameter_id IN (SELECT parameter_id
FROM default_parameter_t
WHERE UPPER(application_name) = UPPER(pi_application_name)
AND NVL(UPPER(name),'X') = NVL(UPPER(pi_name),NVL(UPPER(name),'X'))
AND NVL(UPPER(region_or_country),'X') = NVL(UPPER(pi_reg_cty),NVL(UPPER(region_or_country),'X'))
AND NVL(UPPER(param_field_1),'X') = NVL(UPPER(pi_param_field_1),NVL(UPPER(param_field_1),'X'))
AND NVL(UPPER(param_field_2),'X') = NVL(UPPER(pi_param_field_2),NVL(UPPER(param_field_2),'X'))
AND NVL(UPPER(param_field_3),'X') = NVL(UPPER(pi_param_field_3),NVL(UPPER(param_field_3),'X'))
AND NVL(UPPER(param_field_4),'X') = NVL(UPPER(pi_param_field_4),NVL(UPPER(param_field_4),'X'))
AND NVL(UPPER(param_field_5),'X') = NVL(UPPER(pi_param_field_5),NVL(UPPER(param_field_5),'X')));
po_value := c_value;
END get_multi_parameter_value_proc;