Oracle Stored Procedures in Crystal Report

[johnsorensen]

I'm trying to write a stored procedure to normalize an Oracle row that contains 12 columns, to 1 column of 12 rows so I can do some reports in CR. This is my first Oracle Stored Proc. What am I doing wrong?

I've created a Oracle temporary table as follows:
CREATE GLOBAL TEMPORARY TABLE GLCONSOL_NORMAL_TEMP (....column specification.)

The table which doesn't work with Crystal very well is like this:
COMPANY
FISCAL_YEAR
ACCOUNT
SUB_ACCOUNT
Then 12 debits and 12 credits, for each month
DB_AMOUNT_01
DB_AMOUNT_02
...
DB_AMOUNT_12
CR_AMOUNT_01
CR_AMOUNT_02
...
CR_AMOUNT_12

My stored procedure inserts 12 records into the temporary table as follows;
COMPANY
FISCAL_YEAR
ACCOUNT
SUB_ACCOUNT
MONTH
DB_AMOUNT
CR_AMOUNT

I'm not even sure this is the way you should go about doing this and returning the temporary table back to the REF CURSOR.

Package specification:

Create GLCONSOL_NORMALIZE_PK
as TYPE GLCONSOL_NORMALIZE_type IS REF CURSOR RETURN GLCONSOL_normal_temp%ROWTYPE;
END GLCONSOL_NORMALIZE_PK;

Stored Procedure specification:

(SELECT_CURSOR IN OUT GLCONSOL_NORMALIZE_PK.GLCONSOL_NORMALIZE_type,
IN_COMPANY IN GLCONSOL.COMPANY%TYPE,
IN_ACCOUNT IN GLCONSOL.ACCOUNT%TYPE,
IN_SUB_ACCOUNT IN GLCONSOL.SUB_ACCOUNT%TYPE,
IN_FISCAL_YEAR IN GLCONSOL.FISCAL_YEAR%TYPE)
AS
/* DECLARATIONS */
i binary_integer := 1;
j binary_integer;
v binary_integer;
GLCONSOL_REC GLCONSOL%ROWTYPE;
CURSOR C1 IS
SELECT * FROM GLCONSOL
WHERE COMPANY = IN_COMPANY AND
ACCOUNT = IN_ACCOUNT AND
SUB_ACCOUNT = IN_SUB_ACCOUNT AND
FISCAL_YEAR = IN_FISCAL_YEAR;
/* Begin the program */
BEGIN
open C1;
/* should only select one record */
FETCH C1 INTO GLCONSOL_REC;
/* temp table - created global temporary table called GLCONSOL_NORMAL_temp with:
company
fiscal_year
account
sub_account
db_amount
cr_amount
month
db_beg_bal
cr_beg_bal
*/
/* Create 12 rows for each GLCONSOL row read. */
/* db_amount first */

insert into glconsol_normal_temp (company,fiscal_year,account,sub_account,db_amount,cr_amount,month,db_beg_bal,cr_beg_bal) values (IN_COMPANY, IN_FISCAL_YEAR, IN_ACCOUNT, IN_SUB_ACCOUNT, glconsol.db_amount_01, glconsol.cr_amount_01, 1 ,glconsol.db_beg_bal,glconsol.cr_beg_bal);

insert into glconsol_normal_temp (company,fiscal_year,account,sub_account,db_amount,cr_amount,month,db_beg_bal,cr_beg_bal) values (IN_COMPANY, IN_FISCAL_YEAR, IN_ACCOUNT, IN_SUB_ACCOUNT, glconsol.db_amount_02, glconsol.cr_amount_02, 2 ,glconsol.db_beg_bal,glconsol.cr_beg_bal);

(... 3-11 ...)

insert into glconsol_normal_temp (company,fiscal_year,account,sub_account,db_amount,cr_amount,month,db_beg_bal,cr_beg_bal) values (IN_COMPANY, IN_FISCAL_YEAR, IN_ACCOUNT, IN_SUB_ACCOUNT, glconsol.db_amount_12, glconsol.cr_amount_12, 12 ,glconsol.db_beg_bal,glconsol.cr_beg_bal);

open select_cursor for select * from glconsol_normal_temp;
/* Now I need to return out of the procedure the temp rows. */
/* SELECT_CURSOR had the 12 rows. */
END GLCONSOL_NORMALIZE_SP;

Thanks to anyone who could help me on this.

 

[lyanch]

You need to create the global temp table outside your stored proc.. don't worry it will be there for you inside the proc.

There is a fairly decent whitepaper from Crystal about what you can and cannot (mostly cannot) do in SP's with oracle. Search their knowledge base for oracle stored procedure.

Lisa

 

[johnsorensen]

Thanks. I did create the global temporary table outside the procedure. Does what I'm doing sound like something a reasonable person would do to 'flatten' a table out. I want to produce a graph of amounts by period or a listing like this:

Month DB Amount CR Amount
----- --------- ---------
1 100.00 200.00
2 200.00 300.00
...
12 1200.00 1200.00

With the original table I can't.

 

[synapsevampire]

I didn't review the SP, but your theory is sound, denormalize the data on the database to fit the reporting requirement.

-k

 

[lyanch]

I have used both your method and a crosstab in Crystal. Your method normally works best, esp with the beginning balance..

Lisa

 

[lyanch]

Ah.. so sorry I didn't read closer in your first post.. you need to create the cursor in a package before hand.. here is an example:

PACKAGE Report_Package
IS TYPE Report_Type IS REF CURSOR;
END Report_Package;

Then it is called like this:

PROCEDURE RSP_Example (
ExampleCursor IN OUT Report_Package.Report_Type)

That should fix your problem!

Lisa