Re: [sqr-users] Return a user defined PL/SQL table to Brio?

["Dwight Schrute" <schrute.dwight@hotmail.com>]

The Oracle version works like this:
   EXECUTE ON-ERROR=PROCEDURE-YOU-CREATE-FOR-ERROR
      DO=PROCEDURE-EXECUTED-FOR-EACH-ROW-RETURNED
      @$RETURN_VARIABLE=STORED_PROCEDURE_NAME
      @PARAMETER=$SQR-VARIABLE, @PARAMETER=#SQR-VARIABLE OUTPUT
      INTO &SQR-COLUMN-VARIABLE INT

So, a stored procedure "get_products" with two parameters that selects data 
into five column variables and calls the SQR procedure "print_products" for 
each row retrieved would look like this:

execute
   do=print_products                               !SQR Procedure called for 
each row
   @#proc_return_status=get_products     !Stored Proc and the return status 
of the stored proc
   @prodcode=&code,                             !Param of stored proc use 
'@'
   @max=#maximum                               !Variables from SQR use '&', 
'#', '$'
   INTO &prod_code   int,                        !SQR column variables '&' 
which the
           &description   char(45),                !Oracle values are 
returned to the column
           &discount      float,                      !variables but must 
declare variables like
           &restock       char,                       !Oracle types (float, 
int, etc.)
           &expire_date datetime

You can add the ON-ERROR to check and handle errors.

If the stored proc returns values (not rows), then you use the OUTPUT flag.  
For example, I have a stored proc named "SETVERSION" that updates a row with 
the values passed but it checks to ensure all of the values are passed.  If 
a value is missing, it returns a message stating that a value is missing 
(not an Oracle Error Message but a user defined one); if it is successful, 
it returns a message "Update Successful".  So if the stored proc requires 
'version_nbr' and 'report_desc' and returns 'err_msg', it would be called 
like this:

EXECUTE
   @#proc_return_status=SETVERSION
   @version_nbr=#version_number,
   @report_desc=$report_description,
   @err_msg=$proc_error_message OUTPUT

or to be more condensed it could be like this:

EXECUTE SETVERSION #version_number $report_description $proc_error_message 
OUTPUT

~DWK


>From: Ray Ontko <rayo@ontko.com>
>Reply-To: "This list is for discussion about the SQR database 
>reportinglanguage from Hyperion Solutions." <sqr-users@sqrug.org>
>To: "This list is for discussion about the SQR database reporting 
>languagefrom Hyperion Solutions." <sqr-users@sqrug.org>
>Subject: Re: [sqr-users] Return a user defined PL/SQL table to Brio?
>Date: Fri, 15 Sep 2006 15:10:05 -0400
>
>The only way I have found is to use a PL/SQL block (begin-sql ... end-sql 
>and double-up on the semicolons) and return each array element through a 
>variable.  This means that if you want to return 10 rows, you use 10 
>variables.  If you want to do an arbitrary number of rows, you'll need to 
>be clever with your PL/SQL so that you can return it by making multiple 
>PL/SQL calls, one per row.
>
>Ray
>
>Kevin Laroche wrote:
>>Sorry for not being precise, yes, I want to return a PL/SQL array using 
>>SQR 6.0
>>
>>I looked at the EXECUTE command and it seems to be what is needed only it 
>>claims
>>to be for SQL Server and Sybase.  Guess I'll have to find out if it works 
>>on Oracle.
>>
>>Thanks
>>
>>Kevin LaRoche
>>SQR 6, Oracle 8i, Forms 5
>>Ottawa, Ontario
>>
>>On Sep 15, "Dwight Schrute" <schrute.dwight@hotmail.com> wrote:
>>
>>>Question: Are you referring to SQR or Brio?
>>>
>>>If it is SQR, try using EXECUTE instead of BEGIN-SQL.
>>>
>>>~DKS
>>>
>>>
>>>
>>>>From: "Kevin Laroche" <bydesign@magma.ca>
>>>>Reply-To: "This list is for discussion about the SQR database 
>>>>reportinglanguage from Hyperion Solutions." <sqr-users@sqrug.org>
>>>>To: <sqr-users@sqrug.org>
>>>>Subject: [sqr-users] Return a user defined PL/SQL table to Brio?
>>>>Date: Fri, 15 Sep 2006 13:10:36 -0400
>>>>
>>>>Hi to all
>>>>I have a query function in PL/SQL which returns a table composed of a 
>>>>series of
>>>>records of the form (number, text, number, number, text).
>>>>
>>>>I have successfully used PL/SQL procedures to return single values to 
>>>>Brio
>>>>with statements like this
>>>>
>>>>BEGIN-SQL
>>>>BEGIN
>>>>CMS.WORKLOAD.GET_NEW_ASSIGNMENTS($officer, #v_count_new,#v_SE_report2 , 
>>>>#v_IV_report2 );;
>>>>END;;
>>>>END-SQL
>>>>
>>>>Can I return the PL/SQL table values into a Brio Array or a list?
>>>>
>>>>If that is not possible then I guess I could write the table to a 
>>>>temporary table
>>>>and then query it from Brio.
>>>>
>>>>Comments?
>>>>
>>>>Thanks
>>>>
>>>>Kevin LaRoche
>>>>Brio 6, Oracle 8i, Forms 5
>>>>Ottawa, Ontario
>>>>
>>
>>
>>
>>_______________________________________________
>>sqr-users mailing list
>>sqr-users@sqrug.org
>>http://www.sqrug.org/mailman/listinfo/sqr-users
>>
>>
>
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org
>http://www.sqrug.org/mailman/listinfo/sqr-users

_________________________________________________________________
Windows Live Spaces is here! It’s easy to create your own personal Web site. 
  http://spaces.live.com/signup.aspx


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users