SQL & PL/SQL

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.

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