Q about returning cursor values after in-cursor processing

[CraigBest]

Hoping someone can help...

I have a stored proc I'm writing (part of a package, actually) that will return a number of records. Within the results, there are two types of records, which must be handled differently but appear on the same report. The cursor is returned and dumped to an excel file for accounting type to check over.

I have approached this by creating a cursor in the procedure that reads the record data, and examines a series of data items, including a currency value, in the record to determine if the record should count towards one total or the other. What I want to do is return two separate curency fields in the cursor record, with one or the other filled depending on how the evaluation goes.

Unfortunately, it appears from the texts I have that if you want to return a cursor with different fields from the ones originally supplied by the initial query, you need to have a separate table in the DB and run specific insert statements to write the cursor data out. I would prefer not to have to create a dedicated table for this, and just return the cursor so it can get dumped into Excel.

Can anyone suggest a path as to how I can di this without a new dedicated table in my DB?

Thanks


CraigHartz

 

[Dagon]

You might want to consider using a pipeline procedure. These have a lot more flexibility than cursors and can be called just like regular table selects. Details can be found in the Oracle manuals:

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/08_subs.htm#19677

 

[CraigBest]

Hi Dagon

Thanks... To be honest I'm not sure how this will help me, it's a bit over my head. I'll spend some time looking at it and trying to figure it out. But after spending the last 20 minutes trying to follow what's going on in the example, I don't really get how this applies.

Not fishing for more help, I just don't get it.

CraigHartz

 

[jimirvine]

Can you post the code that you are currently using to give a clearer picture of exactly what you are trying to do. It sounds to me like you are over working this and unnecessarily using cursors

 

[ChrisHunt]

If you want to follow the "read some data, process it, return an array of values" approach, you might want to investigate returning a PL/SQL table instead of a cursor.

If you need to retrun a cursor (I don't know how you're doing this "dump to excel" bit), then one way to do it would be to rethink your approach:

Write two functions, one for each total type, to which you pass the currency value and the fields which you need to check. If the function decides that the data's of the right type, it returns the value, otherwise it returns null.

Generating your cursor then becomes trivial. It's just

SELECT f_type1(curr_value,fielda,fieldb,fieldc),
       f_type2(curr_value,fieldd,fielde,fieldf)
FROM your_table

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[CraigBest]

Hi Chris, thanks. You and Jim are probably right, and the cursor may not be the right approach. For me it is sort of a "When the only tool you have is a hammer, you tend to think of all problems as nails" kind of thing. I'm still pretty new at this and I'm trying to use the limited tools I understand to make it work. But your function idea sounds like it might be the right answer.

In a pseudocode sort of manner, can I do it this way?

SELECT
Function1(Curr_Val, FieldA, FieldB, FieldC, FieldD as Null);
Function2(Curr_Val, FieldA, FieldB, FieldC as Null, FieldD);

FROM Table_List...

If I could do this it would probably be enough to make it work.

Thanks, looking forward to your response.




CraigHartz