You are not logged in.
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)
Offline
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?
Offline
It seems that I need BindCursor, but how to work with it? Where can I find an example?
Offline
Offline
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
Offline
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.
Offline
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
Offline
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
Offline
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.
Offline