mORMot Open Source

Sargon

Member

Registered: 2018-02-09

Posts: 10

How to pass array as parameter to stored procedure in Oracle

Hello, I need help please?

I'm newby in mORMot, and i can't find, how to pass array as parameter to stored procedure in Oracle (i'm try using synDBOracle)?

Can someone help me with the source code or link to an article?

Many thanks!

Last edited by Sargon (2018-02-09 16:59:29)

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,544

Website

Re: How to pass array as parameter to stored procedure in Oracle

See bindArray and comment above TSQLDBOracleStatement  declaration. Limited to array of int64/string

Sargon

Member

Registered: 2018-02-09

Posts: 10

Re: How to pass array as parameter to stored procedure in Oracle

See bindArray and comment above TSQLDBOracleStatement  declaration. Limited to array of int64/string

Many thanks!!!! It works! Oh, and one more question, can I pass the cursor (or the table) as a parameter, for example, in ODAC?

Sargon

Member

Registered: 2018-02-09

Posts: 10

Re: How to pass array as parameter to stored procedure in Oracle

It seems that I need BindCursor, but how to work with it? Where can I find an example?

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,544

Website

Re: How to pass array as parameter to stored procedure in Oracle

Why you need to create cursor on delphi side and pass it back to oracle?  Much better to do this inside pl sql VM.
In any case bindCursor is for read data AFAIR

Sargon

Member

Registered: 2018-02-09

Posts: 10

Re: How to pass array as parameter to stored procedure in Oracle

Why you need to create cursor on delphi side and pass it back to oracle?  Much better to do this inside pl sql VM.
In any case bindCursor is for read data AFAIR

maybe I did not put the question correctly, I want to try to do it like this

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,544

Website

Re: How to pass array as parameter to stored procedure in Oracle

I understand what you need. In SynDBOracle BindCursor work only for Out params. When I wrote " Much better to do this inside pl sql VM" I mean - better to create a PLSQL procedure what create a cursor inside and call your target procedure what take a cursor as a parameter. It will be easier and faster.

But you can try to implement binding for IN cursor params in SynDBOracle - see how we bind a arrays. We use a build-in types ODCINUMBERLIST and ODCIVARCHAR2LIST source. In your case you need to create your own type.

Sargon

Member

Registered: 2018-02-09

Posts: 10

Re: How to pass array as parameter to stored procedure in Oracle

I understand what you need. In SynDBOracle BindCursor work only for Out params. When I wrote " Much better to do this inside pl sql VM" I mean - better to create a PLSQL procedure what create a cursor inside and call your target procedure what take a cursor as a parameter. It will be easier and faster.

But you can try to implement binding for IN cursor params in SynDBOracle - see how we bind a arrays. We use a build-in types ODCINUMBERLIST and ODCIVARCHAR2LIST source. In your case you need to create your own type.

Many thanks, i'm try it

ehkhalid

Member

Registered: 2018-05-16

Posts: 12

Re: How to pass array as parameter to stored procedure in Oracle

Hi,
   I'm opening this topic, as i'm facing the same problem, passing an array to an oracle stored procedure, i tried bindarray but without success ! i'm getting all the time the error : number or type of argument are invalid ! i tried also with integer array and the same error ! i'm working with oracle 19c but getting the same with 12c

   Here is the code i use :

   - Oracle procedure

TYPE TArrChar IS VARRAY(2) OF VARCHAR2(50);

PROCEDURE TestPLSQLArray(ATable in out TArrChar) IS
BEGIN
    for i in ATable.First .. ATable.Last loop
      ATable(i) := '*' || ATable(i) || '*';
    end loop;
END; 

- Delphi code

var
  Props: TSQLDBOracleConnectionProperties;
  stmt : ISQLDBStatement;
  query : string;
  warrayUtf8 : TRawUTF8DynArray;
begin
  Props := TSQLDBOracleConnectionProperties.Create('localdatabase','','user','pass');
  try
    query :=  'begin Pkg_TFA.TestPLSQLArray(?); end;';
    stmt := Props.NewThreadSafeStatementPrepared(query, false);
    try
      setlength(warrayUtf8,2);
      warrayUtf8[0] := 'toto1';
      warrayUtf8[1] := 'toto2';
      stmt.BindArray(1, warrayUtf8);
      stmt.ExecutePrepared;
    finally
      stmt := nil;
    end;
  finally
    Props.Free;
  end;

Thank you for your help

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,544

Website

Re: How to pass array as parameter to stored procedure in Oracle

Current implementation supports only IN params array binding and only for types ODCINUMBERLIST/ODCIVARCHAR2LIST.

ehkhalid

Member

Registered: 2018-05-16

Posts: 12

Re: How to pass array as parameter to stored procedure in Oracle

Current implementation supports only IN params array binding and only for types ODCINUMBERLIST/ODCIVARCHAR2LIST.

Thank you, so it's my inout parameter which wasn't supported ! , thank you for your help.