Procedure for the " multiple in" parameter  in the query

Hello , Good evening
in Orcale 10g,
i am trying to write a dynamic query that sends a multple in paratemter to a Query ,
i will be using these query further in package  and execute it thru vb6.

please refer below , "pin_AircraftStatus" can have more then one value,
 currently i am trying to only print it it as the Expected output( i want the output given below at end)

declare

vstr_sql_query    VARCHAR2 (2500);
pin_AgreementId  VARCHAR2 (200);
pin_AircraftStatus VARCHAR2 (200);

begin    
    pin_AgreementId :='1';
    pin_AircraftStatus := 'FIR,PRO';
   
 vstr_sql_query :=  'SELECT cic.bcag_standard_designator |'|| '|-|' ||'|cic.common_customer_name as Customer,
                aag.adv_account_number as "Account Number",
                aag.purchase_agreement_num|'||'| - SA |'||'|arv.adv_sa_num as "Agreement Number"                
            FROM adv_aircraft_status aas,
                 customer_info_ccid cic,
                 adv_agreement aag,
                 adv_revision arv,
                 adv_aircraft aac,
                 adv_block abk
            WHERE cic.customer_reference_number = aag.customer_reference_number
             AND aas.adv_aircraft_status = aac.adv_aircraft_status
             AND aag.adv_agreement_id = aac.adv_agreement_id
             AND arv.adv_agreement_id = aac.adv_agreement_id
             AND aag.adv_agreement_id ='|| pin_AgreementId ||
             'AND aas.adv_aircraft_status IN ('|| pin_AircraftStatus || ')';
           
            dbms_output.put_line(vstr_sql_query);
end;
----------------------------------------------------------------------------------------------
Expected output
check the 1st and third line i want single quotes  around the "-" , example
cic.bcag_standard_designator || ' - ' ||cic.common_customer_name
Finallly the output Query also should execute in the normal Editor , you can try to by putting comma instead of "-"  for temporary. hope this explanation is satisfactory
----------------------------------------------------------------------------------------------

SELECT cic.bcag_standard_designator ||-||cic.common_customer_name as Customer,
                aag.adv_account_number as "Account Number",
                aag.purchase_agreement_num|| - SA ||arv.adv_sa_num as "Agreement Number"                
            FROM adv_aircraft_status aas,
                 customer_info_ccid cic,
                 adv_agreement aag,
                 adv_revision arv,
                 adv_aircraft aac,
                 adv_block abk
            WHERE cic.customer_reference_number = aag.customer_reference_number
             AND aas.adv_aircraft_status = aac.adv_aircraft_status
             AND aag.adv_agreement_id = aac.adv_agreement_id
             AND arv.adv_agreement_id = aac.adv_agreement_id
             AND aag.adv_agreement_id =1
            AND aas.adv_aircraft_status IN (FIR,PRO) ------> probelm lies here i cannot run the query with multiple input

Start Free Trial