How pass array to oracle procedure using in-parameter?

[alciakng]

I try passing array to oracle procedure in node.js.
below is my code. but failure. help me..

var BIGINBLOCK ="declare "
       +"p_seatcodes dbms_sql.varchar2a; "
       +"begin "
       +"p_seatcodes := :1; " 
       +"RESERVEPROC(:p_email,:p_class,:p_bookingcode,:p_timecode,:p_screencode,:p_moviecode,:p_totalprice,:p_seatcount,p_seatcodes); "
       +"end;"

and my procedure is

CREATE OR REPLACE PROCEDURE RESERVEPROC
( p_email in varchar2,
  p_class in varchar2,
  p_bookingcode in varchar2,
  p_timecode in varchar2,
  p_screencode in varchar2,
  p_moviecode in varchar2,
  p_totalprice in integer,
  p_seatcount in integer,
  p_seatcodes in dbms_sql.varchar2a
 )
 IS 
    I INTEGER;
 BEGIN
    --멤버 마일리지 업데이트
    UPDATE MEMBER
    SET MILEAGE = CASE 
                   WHEN class = 'D' THEN MILEAGE+p_totalprice*0.02
                   WHEN class = 'C' THEN MILEAGE+p_totalprice*0.04
                   WHEN class = 'B' THEN  MILEAGE+p_totalprice*0.06
                   WHEN class = 'A' THEN  MILEAGE+p_totalprice*0.08
                   WHEN class = 'S' THEN  MILEAGE+p_totalprice*0.1
                   END
    WHERE EMAIL = p_email;

    --BOOKING테이블 예약레코드 삽입               
    INSERT
    INTO BOOKING 
    VALUES (p_bookingcode,p_email,p_timecode,p_screencode,p_moviecode,p_totalprice,p_seatcount);               

    --seatcodearr을 루프 돌면서 BOOKED_SEATS에 삽입하고 PERFORMANCE_SEAT의 사용여부 업데이트.  
    FOR I IN 1 .. p_seatcodes.COUNT
    LOOP
        --BOOKED_SEATS에 INSERT
        INSERT
        INTO BOOKED_SEATS 
        VALUES(p_bookingcode,p_seatcodes(I));

        --PERFORMANCE_SEAT UPDATE
        UPDATE
        PERFORMANCE_SEAT 
        SET SEATSTATUS=1 
        WHERE SEATCODE= p_seatcodes(I)
        and TIMECODE= p_timecode;
    END LOOP;
    --커밋.
    commit;
END;

[cjbj]

Support for binding PL/SQL arrays has not yet been implemented.

[krish0803]

Is this feature implemented in v0.7?

I need to pass an array of strings as input parameter.
Below is the sample of ARRAY defined in DB.

CREATE OR REPLACE
TYPE VAR_ARRAY AS TABLE OF VARCHAR2(100);

[cjbj]

@krish0803 The list of changes is in https://github.com/oracle/node-oracledb/blob/master/CHANGELOG.md

Your request is not on the roadmap for <= 1.0. We'll evaluate priorities again after that release.
[The things I personally want to have in by 1.0 are LOBs, some basic type mapping for queries, a review of date handling, and various NLS issues fixed]

[lewisbarber]

This would be a great feature to get in there. From an app development perspective, it significantly enables me to improve performance, if I can send 10k entries to the database for insert / update in one go. Considering porting a Java app to Node (with Oracle DB), and this missing functionality might be enough to stop me from doing so until it's implemented. If only I knew C++ well enough, I'd try and implement it myself 👍

[carloscasalar]

+1
This feature would be awesome.

[doberkofler]

This would be the most important missing feature for me and in fact prevents me to switch from my own nodejs driver to the "official" one.

[lewisbarber]

@doberkofler do you have a Nodejs driver that supports this functionality? If you do I'd love it if you could share it with me whilst we're waiting for this functionality to be implemented. Thanks

[doberkofler]

@lewisbarber Yes, I do actually have a driver that supports this functionality, but it is not a generic one but rather specialized for my https://github.com/doberkofler/node_plsql project.
I'm currently evaluating switching to to oracledb and would most likely then open a pull request with this functionality.

[cjbj]

@doberkofler we'd be glad to see a PR. Before you start work check the CONTRIBUTING file and post a draft plan so we can see where it is headed.

[hg-smart]

Thats exactly what I need for performing a bulk insert into the database, passing an array to a stored procedure in the database from node.js and then performing a bulk insert using a forall insert construct...

[doberkofler]

@cjbj I started analyzing the existing code base and am currently preparing a prototype for passing arrays to stored procedures. Because I'm using node 4, all my work is currently based on the nan 2 pull request #227, so I will not be able to create a pull request before #227 has been merged.
My current plan is to enhance the object bindings and simply allow to set the 'val' property to an array of values instead of just a plain value.

An example for the binding would look as follows:

var bindvars = {
  'strings': {type: oracledb.STRING, dir: oracledb.BIND_IN, maxSize: 30, val: ['John', 'Doe']},
  'numbers': {type: oracledb.NUMBER, dir: oracledb.BIND_IN, val: [1, 2, 3, 4, 5]}
};
connection.execute('BEGIN testproc(:strings, :numbers); END;', bindvars, function (err, result) {
  if (err) {
    console.error(err.message);
    return;
  }
  console.log(result.outBinds);        
});

[lewisbarber]

@doberkofler - this would be great but it would also be useful to be able to pass an ARRAY of OBJECTS to the database, for instance, using the code sample above I was thinking:

var bindvars = {
  'objects': {type: oracledb.OBJECT, dir: oracledb.BIND_IN, maxSize: 30, val: [{name:'John', age:20}, {name:'Doe',age:50}]}
};
connection.execute('BEGIN testproc(:objects); END;', bindvars, function (err, result) {
  if (err) {
    console.error(err.message);
    return;
  }
  console.log(result.outBinds);        
});

@cjbj - should we open another issue for support for passing objects (custom types) to stored procs or can this issue be used for this as well?

Thanks