developers

    Forum Stats

  • 3,874,062 Users
  • 2,266,668 Discussions
  • 7,911,710 Comments

Discussions

Oracle PLSQL wrong number or types of arguments in call to 'BIND_ARRAY' in DBMS_SQL

gg
gg Member Posts: 156 Bronze Badge


I've written the below PLSQL block. However when i execute it , i am getting following error. Kindly tell me how to pass nested tables in the DBMS_SQL to

execute and generate the report. Can we use DBMS_SQL to pass array value to bind parameters ? Or else do we have to use Execute Immediate only. We are using DBMS_SQL for generating all the reports.

Error report -
ORA-06550: line 34, column 2:
PLS-00306: wrong number or types of arguments in call to 'BIND_ARRAY'
ORA-06550: line 34, column 2:
PL/SQL: Statement ignored
ORA-06550: line 35, column 2:
PLS-00306: wrong number or types of arguments in call to 'BIND_ARRAY'
ORA-06550: line 35, column 2:

declare

 l_cursor number := dbms_sql.open_cursor;
 l_ignore number;
 q varchar2(32000);
 o_desc_tab  DBMS_SQL.desc_tab;
 l_col_cnt number(10);
 l_val            VARCHAR2 (32767);
 l_rec_no NUMBER;
 l_col_sep VARCHAR2(25) := ',';
 l_text               VARCHAR2 (32767);
 l_value              VARCHAR2 (4000);
 TYPE T_dc_code IS TABLE OF VARCHAR2(25);
 A_dc_code T_dc_code;
  TYPE T_bin_type IS TABLE OF VARCHAR2(25);
 A_bin_type T_bin_type;
begin
q :='select col1, col2, col3, col4
     from tabl1 wbm
          inner join table2 wam
          on wbm.dc_code=wam.dc_code
          and wbm.dc_area=wam.dc_area
    where wbm.dc_code  MEMBER OF :P_DC_CODE
     and wbm.BIN_TYPE MEMBER OF :P_BIN_TYPE
    )';
   
   dbms_sql.parse( l_cursor,q,dbms_sql.native );
  A_dc_code.EXTEND(2);
  A_dc_code(1) := '888';
  A_dc_code(2) := '902';
    A_bin_type.EXTEND(2);
 A_bin_type(1) := 'R';
 A_bin_type(2) := 'P';
 dbms_sql.bind_array( l_cursor, ':P_DC_CODE', A_bin_type );
 dbms_sql.bind_array( l_cursor, ':P_BIN_TYPE', A_dc_code );
 
 DBMS_SQL.describe_columns (l_cursor, l_col_cnt, o_desc_tab);

 FOR l_cl_cnt IN 1 .. o_desc_tab.COUNT
 LOOP
 DBMS_SQL.define_column (l_cursor, l_cl_cnt, l_val, 32767);
 END LOOP; 

 l_ignore := dbms_sql.execute( l_cursor );  
 
 WHILE (DBMS_SQL.fetch_rows (l_cursor) > 0)
            LOOP            
             DBMS_OUTPUT.PUT_LINE('COMING IN LOOP');
                FOR l_rec_no IN 1 .. o_desc_tab.COUNT
                   LOOP
                      DBMS_SQL.COLUMN_VALUE (l_cursor, l_rec_no, l_value);
                      l_text := l_text || l_col_sep || l_value;            
                END LOOP;
            END LOOP;
  dbms_sql.close_cursor( l_cursor );            
 DBMS_OUTPUT.PUT_LINE('THE VALUE IS ---- '||l_text);           
end;
/

Thanks

Tagged:

Answers

developers