Execute Stored Procedure directly

[altink]

Is it possible to execute from DOA a stored procedure which is not encapsulated inside a package ?
something like Schema.proc_name.exec ??
If yes how ?

regards
Altin

 

[Marco Kalter]

Yes, this is possible. You need to execute an Anonymous PL/SQL Block with the call. For example:

begin
  schema.proc_name(:param1, :param2, false);
end;

See also this FAQ .

 

[altink]

Thank You very much Sir. It was exactly what I was looking for.
But since I got to this only now I have used a workaround until now based on component TOracleScript which I use to call procedures from application. Pls see the following code:

=================================================
dm.p_exec.Lines.Clear;
dm.p_exec.Lines.Add('EXEC SQL EXECUTE ');
dm.p_exec.Lines.Add('BEGIN');
dm.p_exec.Lines.Add('-- Name = RUN1');
dm.p_exec.Lines.Add( 'P_UNW_POLICY_HLH_DUP('

// UNW_POLICY
+ p_policy_id_old + ', '
+ p_dup_serial_new
+ '); ' ) ;

dm.p_exec.Lines.Add('END;');
dm.p_exec.Lines.Add('/');
//memo1.Lines := dm.p_exec.lines;

dm.p_exec.Execute;
if dm.p_exec.CommandByName('RUN1').ErrorCode 0 then
begin
Messagedlg_alb(MyDOAOraErrMsg(dm.p_exec.CommandByName('RUN1').ErrorMessage) , 'Gabim', mtError, [mbYes], 0);
dm.ImsL.Rollback;
exit;
end;
dm.ImsL.Commit; // commit if no error
=================================================

where p_exec is the TOracleScript component inside the DataModule. As you see I call a procedure (P_UNW_POLICY_HLH_DUP) with two parameters whose values are represented by the two variables (p_policy_id_old, p_dup_serial_new ).

Question: Can this solution lead to any potential problem ? Is OK what I am doing ?

Thanks and Regards
Altin

 

[Marco Kalter]

It should work, but it is generally better and more efficient to call procedures as described in the FAQ. The use of actual typed variables for parameters is less error prone.

 

[altink]

Thank you very much Sir

You said
"The use of actual typed variables for parameters is less error prone."
Can you specify to what kind of error this (my way) can potentially lead ?

I have a problem in one Oracle Procedure I call this way. I SELECT one field INTO a variable and if not found I use NO_DATA_FOUND to treat the problem. Code is OK, begin-exception-end, but I know that the query MUST return one row, instead it raises NO_DATA_FOUND. I preview the TOracleScript text by putting its debug property to True and see that my given values are OK.
Do you see any potential threat in this way especially combined with NO_DATA_FOUND usage ?
The error I have is not persistent, I can execute the procedure several times and occasionally does this. It looks like Oracle (or my application ) goes unstable.

I have used OracleScript this way successfully before, but I was not using NO_DATA_FOUND before, I was first using Count to evaluate my row presence first.

Thanks and regards
Altin

 

[Marco Kalter]

Can you specify to what kind of error this (my way) can potentially lead ?

You build a SQL text and add parameter values in the text. The text representation of a parameter value can be affected by:

- The decimal point: should it be 2.5 or 2,5?
- The oracle date format: dd-mm-yyyy, dd-mon-yyyy, ...
- Quotes in strings: you need to convert abc'def to abc''def

With actual typed variables these things do not apply. You simply pass the actual number/date/string value.

Do you see any potential threat in this way especially combined with NO_DATA_FOUND usage ?

It could be, but I'm sure you checked the SQL text that is executed.

 

[altink]

I have worked this way and never had probles. I have the same logic in all my program, but error is only at this part. I am currently following it in Oracle Global Support Metalink.
Thankyou very much for your effort and dedication.
If I get anything new I will notify you again in this thread. Until then
Alles Gute Herr Kalter und danke schoen

regards
Altin

 

[altink]

OK for calling an Oracle procedure like

begin
schema.proc_nameparam1, aram2, aram3);
end;

if param1 is string then
DoaQuery.setvariable('PARAM1', 'ALTIN');

if param2 is integer then
DoaQuery.setvariable('PARAM2', 200);

how would I do if PARAM3 is Oracle Date ? Of course being sure that the right format is set in Delphi and not interpreted by Oracle defualt datetime format ? The same for float param.

thanks
Altin

 

[Marco Kalter]

Just declare them as otFloat and otDate, and pass a Delphi Double and TDateTime. There are no format issues whatsoever.