This discussion is archived
5 Replies Latest reply: Jul 3, 2013 2:24 AM by dariyoosh RSS

A question about the bind_array procedure in sys.dbms_sql package

dariyoosh Journeyer
Currently Being Moderated

Dear all,

 

I have a problem with the bind_array procedure of sys.dbms_sql package and I would appreciate if you could kindly show where I have made mistake(s) in my code.

 

Parameter
Parameter value
Oracle versionEnterprise Edition Release 11.2.0.1.0 - 64bit
OSLinux Fedora Core 17 (X86_64)

 

Test case:

For the purpose of this exercice (if you wish to have the data at your disposal) I use the employee table of the hr sample schema.

 

Problem:

Consider the following SQL query:

SELECT    t1.first_name fname
FROM        hr.employees t1
WHERE     t1.employee_id IN (197, 179, 130, 116);

 

This gives the following result

 

FNAME

--------------------

Mozhe

Shelli

Kevin

Charles

 

SQL>

 

Just an exercice I wanted to use dbms_sql package to do the same thing (there is absolutely no need to do so as nothing is dynamic here, but as I said, the purpose was to do some exercice with sys.dbms_sql package in order to better understand how those procedure/functions work)

 

Here is what I wrote (the following was run directly in SQL*Plus):

 

SET SQLBLANKLINES ON;
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE mytestProc
AUTHID DEFINER
IS   
BEGIN
    <<bk>>
    DECLARE
        curId       PLS_INTEGER;  -- The cursor ID
        rowCnt      PLS_INTEGER;  
       
        fname       hr.employees.first_name%TYPE;
        FNAME_LNG   CONSTANT PLS_INTEGER := 20;
       
        empIds      sys.dbms_sql.NUMBER_TABLE;
       
        SQLSTMT     CONSTANT VARCHAR2(100) :=
            'SELECT t1.first_name '                 ||
            'FROM   hr.employees t1 '               ||
            'WHERE  t1.employee_id IN :b_empIdTab';
    BEGIN
        -- We chose 4 arbitrary employee_id for
        -- the purpose of this exercise from
        -- the employee table of oracle sample
        -- 'hr' schema
        empIds(1) := 197;
        empIds(2) := 179;
        empIds(3) := 130;
        empIds(4) := 116;
       
        bk.curId := sys.dbms_sql.open_cursor(security_level=>2);
       
        sys.dbms_sql.parse(bk.curId, 
                            bk.SQLSTMT, 
                           sys.dbms_sql.NATIVE
                          );
                          
        sys.dbms_sql.define_column(bk.curId, 
                                   1, 
                                   bk.fname,
 bk.FNAME_LNG
                                  );
       
        -- So normally here the 4 employees id are provided
        -- to the SQL statement
        sys.dbms_sql.bind_array(bk.curId, ':b_empIdTab', bk.empIds);
       
        rowCnt := sys.dbms_sql.execute(bk.curId);
       
        WHILE (sys.dbms_sql.fetch_rows(bk.curId) > 0) LOOP
            sys.dbms_sql.column_value(bk.curId, 1, bk.fname);
            sys.dbms_output.put_line('fname = ' || bk.fname);
        END LOOP;
       
        sys.dbms_sql.close_cursor(bk.curId);
    END;
END mytestProc;
/
SHOW ERRORS;
BEGIN
    mytestProc();
END;
/
DROP PROCEDURE mytestProc;
SET SERVEROUTPUT OFF;

 

 

And here is the output of the above code:

 

fname = Shelli

 

PL/SQL procedure successfully completed.

 

SQL>

 

What I don't understand is why instead of four first names (fname), I get only one?

 

Thanks in advance,

 

Regards,

Dariyoosh

  • 1. Re: A question about the bind_array procedure in sys.dbms_sql package
    ActiveSomeTimes Explorer
    Currently Being Moderated

    bind_array seems to be working only with insert , update & delete..!

  • 2. Re: A question about the bind_array procedure in sys.dbms_sql package
    dariyoosh Journeyer
    Currently Being Moderated

    Hi,

     

    Thanks for your answer.

     

    Actually I checked the online documentation before creating this thread and I saw that all examples provided for bind_array() concerned in fact only DML (insert, update, delete). Yet, the documentation doesn't officially state that non DML statements are not working.

     

    But if as you say, bind_array can be used only with DML, then how can we bind values to IN clause inside a WHERE clause for a given SQL statement using sys.dbms_sql package?

     

    Something like

     

    SELECT . . . FROM . . . WHERE <a column value> IN (<list of values>)

     

    Is there any way to specify (<list of values>) as a bind variable (of a collection type) by using sys.dbms_sql package?

     

    Thanks,

     

    Regards,

    Dariyoosh

  • 3. Re: A question about the bind_array procedure in sys.dbms_sql package
    ActiveSomeTimes Explorer
    Currently Being Moderated

    You may use 'or' condition like Field = :bind1 or Field = :bind2 as a workaround.

  • 4. Re: A question about the bind_array procedure in sys.dbms_sql package
    padders Pro
    Currently Being Moderated

    To be honest the evolution of DBMS_SQL is somewhat confusing but suffice to say the DBMS_SQL.BIND_ARRAY procedure is not what you are looking for.

     

     

    The DBMS_SQL.BIND_ARRAY procedure binds each value of the array in turn to *separate* executions of the SQL statement, similar to the FORALL statement in native dynamic SQL. While this works well for INSERT/UPDATE/DELETE and works in principle with SELECT statements what I believe you are in fact fetching is the results of only one of those executions which is clearly not what you want.

     

     

    Instead what you are looking for is one of the overloads of the DBMS_SQL.BIND_VARIABLE procedure which supports binding of collection types but this is not going to work with the SQL you have now which uses a literal IN clause, you would have to use either MEMBER OF (which would afaik not use indexes) or treat the collection as a table via the TABLE () function syntax and either join or use as an IN subquery.

  • 5. Re: A question about the bind_array procedure in sys.dbms_sql package
    dariyoosh Journeyer
    Currently Being Moderated

    Thanks for your suggestion. In fact the I had to use TABLE function by the given collection. So the corrected version of the script is provided below (for those who might be interested.

     

    SET SQLBLANKLINES ON;
    ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
    SET SERVEROUTPUT ON;
    
    
    CREATE OR REPLACE TYPE empIdVarr_ty IS VARRAY(10) OF NUMBER
    /
    SHOW ERRORS;
    
    
    CREATE OR REPLACE PROCEDURE mytestProc
    AUTHID DEFINER
    IS   
    BEGIN
        <<bk>>
        DECLARE
            empIds      empIdVarr_ty := empIdVarr_ty(197,179,130,116);
            
            curId       PLS_INTEGER;
            rowCnt      PLS_INTEGER;
     
            fname       hr.employees.first_name%TYPE;
            FNAME_LNG   CONSTANT PLS_INTEGER := 20;
            
            SQLSTMT     CONSTANT VARCHAR2(500) :=
                'SELECT t1.first_name '                 ||
                'FROM hr.employees t1 '               ||
                'WHERE t1.employee_id IN '             ||
                    '(SELECT t2.column_value '          ||
                    ' FROM TABLE(:b_empIdTab) t2)';
        BEGIN
            bk.curId := sys.dbms_sql.open_cursor(security_level=>2);
            
            sys.dbms_sql.parse(bk.curId, 
                               bk.SQLSTMT, 
                               sys.dbms_sql.NATIVE
                              );
                               
            sys.dbms_sql.define_column(bk.curId, 
                                       1, 
                                       bk.fname, 
                                       bk.FNAME_LNG
                                      );
       
            sys.dbms_sql.bind_variable(bk.curId, ':b_empIdTab', bk.empIds);
            
            rowCnt := sys.dbms_sql.execute(bk.curId);
            
            WHILE (sys.dbms_sql.fetch_rows(bk.curId) > 0) LOOP
                sys.dbms_sql.column_value(bk.curId, 1, bk.fname);
                sys.dbms_output.put_line('fname = ' || bk.fname);
            END LOOP;
            
            sys.dbms_sql.close_cursor(bk.curId);
        END;
    END mytestProc;
    /
    SHOW ERRORS;
    
    
    BEGIN
        mytestProc();
    END;
    /
    
    
    DROP PROCEDURE mytestProc;
    SET SERVEROUTPUT OFF;

     

    Regards,

    Dariyoosh

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points