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!

Bind parameter is not working

Discussion in 'SQL PL/SQL' started by jagadekara, Jul 18, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    Q1) SELECT organization_code
    FROM ORG_ORGANIZATION_DEFINITIONS;

    ORGANIZATION_CODE
    ---------------------
    AU1
    C01
    D01
    DE1
    DE2

    Q2) SELECT organization_code
    FROM ORG_ORGANIZATION_DEFINITIONS
    WHERE organization_code IN (select :p_ORG from dual); -- Giving 'AU1','C01','D01' for parameter

    ORGANIZATION_CODE
    ---------------------

    No Rows...

    Q3) select :p_ORG from dual; -- Giving 'AU1','C01','D01' for parameter

    :p_ORG
    ----------------
    'AU1','C01','D01'

    Q4) SELECT organization_code
    FROM ORG_ORGANIZATION_DEFINITIONS
    WHERE organization_code IN ('AU1','C01','D01');

    ORGANIZATION_CODE
    ---------------------
    C01
    D01
    AU1

    So why Q2 is not working?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Everything is correct, this query(2) shan't return result.
    Because:
    "select :p_ORG from dual r" returns one rows ( variable value :p_ORG ), with elements the separated of commas.
    you look for single value of a column, instead of search on several value (in your case on a rows).

    For example :
    Code (SQL):

    SET serveroutput ON
    DECLARE
     v varchar2 (100) := 'A1,A2,A3';
    BEGIN
        dbms_output.put_line ('Result Query1');  
        FOR z IN (
                    WITH your_data AS
                        (
                             SELECT 'A1' str FROM dual UNION ALL
                             SELECT 'A2' str FROM dual UNION ALL
                             SELECT 'A4' str FROM dual UNION ALL                        
                             SELECT 'A3' str FROM dual    
                        )        
                    SELECT
                         yd.*
                    FROM your_data yd
                    WHERE
                     v LIKE '%'||yd.str||'%'
                 )
        loop
            dbms_output.put_line(Z.STR);
        END loop;              
        dbms_output.put_line(NULL);
        dbms_output.put_line ('Result Query2');  
        dbms_output.put_line('Var V = '||v);
        FOR z IN (
                    WITH your_data AS
                        (
                             SELECT 'A1' str FROM dual UNION ALL
                             SELECT 'A2' str FROM dual UNION ALL
                             SELECT 'A4' str FROM dual UNION ALL                        
                             SELECT 'A3' str FROM dual    
                        )        
                    SELECT
                         yd.*
                    FROM your_data yd
                    WHERE
                     yd.str IN (SELECT v FROM dual)
                 )
        loop
            dbms_output.put_line(Z.STR);
        END loop;            
         
        dbms_output.put_line(NULL);    
        dbms_output.put_line ('Result Query3');  
        dbms_output.put_line('Var V = '||v);
        FOR z IN (
                    WITH your_data AS
                        (
                             SELECT 'A1' str FROM dual UNION ALL
                             SELECT 'A2' str FROM dual UNION ALL
                             SELECT 'A4' str FROM dual UNION ALL                        
                             SELECT 'A3' str FROM dual    
                        )        
                    SELECT
                         yd.*
                    FROM your_data yd
                    WHERE
                     yd.str IN (SELECT
                                    regexp_substr(v,'[^,]+',1,level) str
                                FROM dual
                                CONNECT BY
                                    regexp_substr(v,'[^,]+',1,level) IS NOT NULL
                               )    
                 )
        loop
            dbms_output.put_line(Z.STR);
        END loop;              
       
    END;  
    /

    SQL>
     
    RESULT Query1
    A1
    A2
    A3
     
    RESULT Query2
    Var V = A1,A2,A3
     
    RESULT Query3
    Var V = A1,A2,A3
    A1
    A2
    A3


     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You can also use the MEMBER OF operator:


    SELECT organization_code
    FROM ORG_ORGANIZATION_DEFINITIONS
    WHERE organization_code member of :)P_ORG);


    See here:


    http://dfitzjarrell.wordpress.com/2008/09/26/how-dynamic/


    The MEMBER OF function is discussed and used toward the bottom of the post.
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi David,

    Your query giving the following error...

    ORA-00932: inconsistent datatypes: expected UDT got CHAR