Oracle sproc for CR10 1

[thisisboni]

Hi,

I am relatively new to Oracle as compared to Crystal.
I am using Crystal Enterprise 10 with Oracle 9i as backend.

Trying to report off a stored procedure -
the sproc is calculating values off tables BUT they(hte calculated values) are NOT stored into the dB
Theese (the calculated values in the sproc) are the values I would want in my report.

say I am calculating

total_time_in_state1 , total_time_in_state2 from values stored in the dB

How do I get total_time_in_state1 , total_time_in_state2 in the report ?

How do I include these values into the REF CURSOR

Thanx
/Sam

 

[synapsevampire]

In the final select, include them.

select table.field, total_time_in_state1 , total_time_in_state2 from table

I don't understand what the issue is.

-k

 

[thisisboni]

Hi,

the thing is total_time_in_state1 , total_time_in_state2
are NOT stored in a table but calculated values in the stored procedure

/sam

 

[Turkbear]

Hi,
Your OUT Cursor needs to have those values Selected Into..

( Looking at your code in the other forum it appears that you are not populating the OUT Ref Cursor with any data..)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[thisisboni]

Hi,

thnx for the reply

inserting the following before I come out of the loop will do ?

OPEN Test_Cursor FOR
SELECT fts_case_id, assign_time, pending_time, wip_time
FROM DUAL;

 

[thisisboni]

Hi,

I made a few changes to include only one select stmt. in the sproc BUT how do I retireve multiple rows into the REF CURSOR - here is the bones of the structure so far
in this Crystal is only picking up the last row - thats coz I fancy, every time its overwriting the previous value - any suggections/help ? /thnx


PACKAGE BODY TEST_PACKAGE
IS
PROCEDURE Test_Procedure (Test_Cursor IN OUT Test_Type)
IS
/* Variables & Cursor Declarations */
BEGIN
OPEN CURSOR1;
LOOP
/* do stuff in here */

OPEN CURSOR2(argv, argc);
LOOP
/* do more stuff in here to get values--v1..v4 */
END LOOP;
CLOSE CURSOR2;

dbms_output.put_line (' v1 = ' || v1 ||
' v2 = ' || v2 ||
' v3 = ' || v3 ||
' v4 = ' || v4 ||
);

/* WHERE DO I DO THIS ? */
OPEN Test_Cursor FOR
SELECT v1, v2, v3, v4
FROM DUAL;


/*reset variables for next iteration*/
v1:=0; v2:=0; v3:=0; v4:=0;

dbms_output.put_line ('*********************************************');

END LOOP;
CLOSE CURSOR1;

END Test_Procedure;
END Test_Package;

 

[synapsevampire]

Might want to review the requirements for Oracle SPs in Crystal:

http://support.businessobjects.com/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf.asp

-k

 

[Turkbear]

Hi,
In it simplest form it world be something like:

CREATE OR REPLACE MY_SP
(  p_return_cur OUT SYS_REFCURSOR)
IS

BEGIN
  OPEN p_return_cur FOR
  SELECT salesmanID,sum(sales),sum(returns) from Sales_performance
group by salesmanID;
END;

You process will vary, but try not to combine DBMS_OUTPUT and REFCURSOR creation in the same Proc..This code will select all the records from the table and compute the sums for each salesmanID..
If you used this as the data source for a Crystal Report you would have 3 fields to select from to display--Logicaly, in your report, you would group on SalemanID and show the sums in the GH...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[thisisboni]

the problem is I have 2 loops
and I need the values after the first loop however if I throw in the "OPEN p_return_cur FOR" after then every time it gets over written by the second loop

LOOP
LOOP
/* do calculation*/
END LOOP
/* retrieve values into REF CUR*/
END LOOP

thnx
/Sam

 

[thisisboni]

I fancy, one way to do this is dumping the values in a temp table and then doing
OPEN ref_cur FOR

However, I do not have write access in the prod schema - and getting that might be an issue !
hence any other suggetions ?

thnx
/Sam

 

[Turkbear]

Hi,
Create variables ( maybe arrays, if multiple rows) to hold the values created by the first loop then insert them into the OUT REFCURSOR in the second loop..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,'
On further thought,the above might not work if multiple rows are involved since the REFCURSOR needs a basic select statement to populate its rows...

Given your needs, a temp table may be the only solution ( or a view created in the first loop).










To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Since you're sharing technical information such as:

/* do more stuff in here to get values--v1..v4 */

It's hard to advise you, in future posts try including technical information instead of junk comments.

Turk is correct though, if you're bulding out a single row, strore the values to variables, the reference the variables in the outer loop.

-k