Re: SQR/Oracle Error

["O'Dorisio, Steve" <Steven.O'Dorisio@MMS.GOV>]

Can you change your database function to a database stored procedure where
you can specify the return variable?

Example:
 let $date_stor_proc = 'rpt.storprocedurename'

 begin-sql
   begin
   [$date_stor_proc]($option,$return_variable);;
   end;;
 end-sql

You can even use "$return_variable" directly, without having to move it to
another variable.  Or you can move it to another variable if you would
prefer (ex.  let $business_date = $return_variable).

Here is an example of how we are using stored procedures (dynamically):
-----------------------------------------------------------------
MOVE 1 TO #in_input
MOVE 'test_function' TO $xxx

begin-sql
begin
[$xxx](#in_input, $return_test);;
end;;
end-sql

show $return_test

Here is the code from my test_function:
----------------------------------------

CREATE OR REPLACE PROCEDURE test_function(
   in_input IN number,
   return_var OUT varchar2)
AS
BEGIN
   IF    in_input = 1 THEN
           return_var:= 'A';
   ELSIF in_input = 2  THEN
           return_var := 'B';
   ELSE
           return_var := 'C';
   END IF;
END test_function;

GOOD LUCK,
Steve


-----Original Message-----
From: Kevin Kroen [mailto:kevin.kroen@FXALL.COM]
Sent: Tuesday, October 02, 2001 3:45 PM
To: SQR-USERS@list.iex.net
Subject: SQR/Oracle Error


We had some reports set up with a stored procedure that returned values in
the following format:
(DD-Mon-YYYY, DD-Mon-YYYY)

We added a new option that in certain instances the stored procedure would
return:
(DD-Mon-YYYY HH:MI:SS, DD-Mon-YYYY HH:MI:SS)

We ran the new stored procedure in an SQL Client and it worked properly.

When I run this through SQR, I get the following error:

(SQR 5528) ORACLE OEXEC error -6502 in cursor 1: ORA-06502: PL/SQL: numeric
or value error: character string buffer too small ORA-06512: at line 1 Error
on line XX: (SQR 3735) Could not execute SQL. SQR: Program Aborting.

This is how the SP is called:

 let $date_stor_proc = 'rpt.storprocedurename'
 begin-sql
   begin
     $business_date := [$date_stor_proc]($option);;
   end;;
 end-sql


We tried having the function only return (DD-Mon-YYYY HH, DD-Mon-YYYY HH)
and it worked with no problem, so I tend to think this problem in on the
Brio side with the $business_date string. We are using SQR 6.0 going against
an Oracle DB on Solaris.

Any ideas?

Kevin Kroen

=========================================================================
This message is intended for the named addressee(s) only and contains
information that may be confidential, non-public or legally privileged.  If
you are not the intended recipient, you must not copy this message or
attachment or disclose the contents to any other person.  If you received
this email in error, please contact the sender and permanently delete the
email and any attachments.

Unless expressly stated, opinions in this email are those of the individual
sender and not of FX Alliance, LLC or its affiliates.
=========================================================================