oracle execute anonymous block with ptOutput parameter

[volkerp]

Hello,

how can I access ptOutput parameter from an anonymous block?
this simple code

Code: Select all

ZQuery1.SQL.Text := 'begin :result ::= 42; end;';
 ZQuery1.Params.CreateParam(ftInteger,'result',ptOutput);
 ZQuery1.ExecSQL;
 fortytwo := ZQuery1.ParamByName('result').AsString;

doesn't work. I get the error

Code: Select all

SQL Error: OCI_ERROR: ORA-06502: PL/SQL: numerischer oder Wertefehler: Zeichenfolgenpuffer zu klein
ORA-06512: in Zeile 1

If I try to use the assignment ":=" without double ":", this error occurs:

Code: Select all

Incorrect token followed by ":"

[marsupilami]

Hello Volker,

I courrently have no working Oracle instance. What happens if you try the same using a TZSQLProcessor component?
Best regards,

Jan

[marsupilami]

Hello Volker,

one more hint: if you use the : in your sql statement for something that is no parameter, you need to change the ParamChar property. So your code should look something like this:

Code: Select all

 ZQuery1.ParamChar := '$';                                 // change the paramchar 
 ZQuery1.SQL.Text := 'begin $result ::= 42; end;';         // note the use of the changed param char
 ZQuery1.Params.CreateParam(ftInteger,'result',ptOutput);  // this line usually should not be necessary. 
                                                           // Zeos should create the parameter automatically
 ZQuery1.ExecSQL;
 fortytwo := ZQuery1.ParamByName('result').AsString;

Does this work?

Best regards,

Jan

[EgonHugeist]

Hello volkerp,
that's supported since 7.3 and up.. Any feedback is welcome. Have fun.