1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Parameter passing in where clause

Discussion in 'SQL PL/SQL' started by Tariq Bashir Malhi, Jan 27, 2010.

  1. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    This is a part of a procedure, i am having problem when passing hard coded value in where clause, i try in following ways

    1. Save value in variable and use in where clause
    2. Give value in single & double quotes

    but either is not working, help is required.

    CREATE OR REPLACE PROCEDURE core_business.region_achivements

    IS

    tmpvar NUMBER;

    p_meter1 VARCHAR2 (5);

    p_meter2 VARCHAR2 (5);

    BEGIN

    BEGIN

    p_meter1 := 'R';
    p_meter2 := 'A';

    EXECUTE IMMEDIATE 'create table core_business.t_set0 as

    SELECT ROW_NUMBER () OVER (ORDER BY r.region_fo_code) SR_NO,r.region_fo_code rgn, r.NAME AS NAME, t.targeted_employer AS employer_target, t.targeted_ip AS employees_target, t.current_contribution AS contribution_target,
    t.arrear_preceding_year AS arrear_target
    FROM general_information.cb_region_fo r, core_business.cb_region_target t
    WHERE r.region_fo_id = p_meter1
    AND r.status = p_meter2
    AND r.region_fo_code <> 9999
    AND r.region_fo_code = t.region_fo_code
    ORDER BY r.region_fo_code';
    EXCEPTION
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.put_line (SQLERRM || ' ' || SQLCODE);
    END;

    BEGIN

    EXECUTE IMMEDIATE 'DROP TABLE core_business.t_set01';

    EXCEPTION

    WHEN OTHERS

    THEN

    NULL;

    END;



    BEGIN

    EXECUTE IMMEDIATE 'create table core_business.t_set01 as

    SELECT r.region_fo_code rgn,COUNT (*) AS pr02a_entered

    FROM core_business.cb_contr_card e,

    core_business.cb_employer i,general_information.cb_region_fo r

    WHERE TRUNC (e.created_date) BETWEEN TO_DATE ('7/1/2009', 'MM/DD/YYYY' ) AND TRUNC (SYSDATE)

    AND i.emp_area_code = e.emp_area_code

    AND i.emp_reg_serial_no = e.emp_reg_serial_no

    AND i.emp_sub_area_code = e.emp_sub_area_code

    AND i.emp_sub_serial_no = e.emp_sub_serial_no

    AND i.region_fo_code = r.region_fo_code group by r.region_fo_code';

    EXCEPTION

    WHEN OTHERS

    THEN

    NULL;

    END;
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Please use the SQL Button or Code (#) button from editor and format your code for readability
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Here you need to put an extra Single quote for for each quote in the string.

    Like,

    Code (SQL):


    EXECUTE immediate 'Insert into table_name(col1,col2,col3) Values ( sysdate,'''||v_col2||''','''||v_col3||''')'

     
     
  4. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    Thanks. Problem has been resolved.