Home » SQL & PL/SQL » SQL & PL/SQL » 'Dynamic' Records with Dynamic Cursors
'Dynamic' Records with Dynamic Cursors [message #200574] Tue, 31 October 2006 06:58 Go to next message
krili
Messages: 3
Registered: October 2006
Location: Austria
Junior Member
Hi there,
my jobs are running monte-carlos. each job stores it's results in a result table:

job mc subsample result
================
1 1 0 123
1 1 1 234
...
1 1 961 345
1 2 0 567
...
2 1 0 678
...
2 1 256 789
(for simplicity jobs are numbers)

To evaluate the results I need a pivot query:

subsamples
job mc 0 1 2 ... 961
===== ==========
1 1 results
1 2
...
2 1
(first 2 rows are not displayed)

To do this, I created a stored procedure that I want to call from my math software to fetch the results of a specific job:

=============================================
create or replace PROCEDURE GET_DATA

( jobName IN VARCHAR2, --class io jobs
nos IN NUMBER, --Number of Subsamples for this class of jobs
jobCount IN NUMBER DEFAULT 0 --1 for just counting finished jobs
) IS

sql_stmt LONG;

TYPE c_type IS REF CURSOR;
c_cur c_type;

TYPE r_type IS RECORD ???;
c_data r_type;

BEGIN
--Create SQL
sql_stmt := 'SELECT';
FOR i IN 0..nos LOOP
sql_stmt := sql_stmt ||', max(decode(subsample,' || i ||', value, NULL)) sub_' || i;
END LOOP;
sql_stmt := sql_stmt || ' FROM t_results GROUP BY job, mc WHERE job LIKE jobName;

OPEN c_cur FOR sql_stmt;
LOOP
FETCH c_cur INTO c_data;
EXIT WHEN c_cur%NOTFOUND;
/* in this loop, check if all subsamples of this run are done*/
/* if not, don't store this row in c_data*/
END LOOP;
CLOSE c_cur;

/* IF jobcount=1 -> just count rows else export data */
END;
=============================================

Obviously I have a problem with defining the record type (because of the dynamic sql). Of course I know, how much columns I have, but I don't want to define a maximum of 961 columns...

Is there a smarter way to do this?

Thank you very much,
krili
Re: 'Dynamic' Records with Dynamic Cursors [message #200590 is a reply to message #200574] Tue, 31 October 2006 07:43 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
It's not clear from your procedure how you are planning to return the data. All it does is fetch the results and then does nothing with it. Were you going to use a cursor, dbms_Output statements, pipeline table or some other mechanism ?
Re: 'Dynamic' Records with Dynamic Cursors [message #200598 is a reply to message #200590] Tue, 31 October 2006 07:59 Go to previous messageGo to next message
krili
Messages: 3
Registered: October 2006
Location: Austria
Junior Member
Hi Cthulhu,

thank you very much for your reply. You're absolutely right. I haven't thought about returning the data. I've to check, which is the best way for Matlab. First I want the preprocessing to be done. Do you think this is the wrong way?
Re: 'Dynamic' Records with Dynamic Cursors [message #200605 is a reply to message #200574] Tue, 31 October 2006 08:16 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
It does depend on how you are returning the data. If you have to open a cursor to return a data set with 96 columns, then your approach is the best one. If the output structure is more flexible (like a long string representing each record), then there might be some alternatives.

A possible alternative approach would be to fetch the cursor with an order by e.g.

select job, mc, subsample, sum(result) FROM t_results
group by job, mc, subsample
order by job, mc, subsample

You could then loop through the result set until job and mc changes and build up the output record. Once job/mc changes, you know you have processed all the data for the previous job/mc and you can output the record.
Re: 'Dynamic' Records with Dynamic Cursors [message #200622 is a reply to message #200605] Tue, 31 October 2006 09:18 Go to previous message
krili
Messages: 3
Registered: October 2006
Location: Austria
Junior Member
Well, my result set should be a NxM matrix with N monte-carlos and M results of subsamples respectively. This is done by the pivot query. When I ignored the error checking, I couldn't return this cursor. I don't know how to assing the rowtype for 961 columns... (TYPE c_cur IS REF CURSOR RETURN ???%(ROW)TYPE or TYPE c_cur IS REF CURSOR RETURN (sub_1 NUMBER, sub_2 NUMBER ... sub_961 NUMBER ?). So I tried to fetch the cursor into a record-type. But here I have the same problem.

Is there a way to declare the RETURN-type dynamic?
Previous Topic: Sequence.currval problem
Next Topic: SQL QUERY
Goto Forum:
  


Current Time: Sat Dec 03 07:57:37 CST 2016

Total time taken to generate the page: 0.07519 seconds