Re: Calling Oracle stored procedures

[Andrew Barnett <abarnett@ISD.HIH.COM.AU>]

>Hi everybody
>
>Does anybody know if it is possible to call Oracle's stored
>procedures from SQR?
>
>AND if it is possible, how can I do it? And do You have any
>comments or advice on this?
>
>
>Timo
>
>
>timo.kytta@vaasa.wartsila.infonet.com
>http://www.pcuf.fi/~tik
>'reality is for people, who cannot       ////
> cope with science fiction '            (o o)
> -------------------------------------o00=(_)=00o---
>

One thing to watch is that SQR (certainly 2.5) does not allow PL/SQL functions to return null.
This is mildly annoying. If I have a function that does a lookup of some sort and it can't find a
match then I believe that the correct behaviour is to return null. (See "PL/SQL Programming" by
Steven Feuerstein - excellent). It is up to the calling program to decide how to process that
result - whether to raise an error or continue. As things stand I have to use NVL which makes it
harder to programmatically handle the situation.

eg.

<<<
begin-procedure fetch_last_run_date(:$last_run_date)

! do this cos SQR 2.5 does not seem to allow parameters for on-error procedure
let $_procedure_name = 'fetch_last_run_date'

! nvl here cos SQR raises error when function returns null
begin-sql on-error=sql_error
begin
   $last_run_date :=
      to_char(nvl(
              pkg_batch_log.last_run_date('{program_name}', 'SUCCESS'),
              sysdate),
              '{date_format_mask}');;
end;;
end-sql

end-procedure
>>>

Oracle Pro*C programs suffer the same problem.

--------------------------------------------------------
 Andrew Barnett                 abarnett@isd.hih.com.au
 - Wizzard
--------------------------------------------------------