| In a quandry [message #10675] |
Mon, 09 February 2004 09:07  |
Paul Bankston
Messages: 12
Registered: February 2004
|
Junior Member |
|
|
I am in a quandry. Here is what I need to do:
I have several joined tables. I need to loop through the tables, manipulate the data, summarize it and then store it. I then need to select * into a ref cursor that I have defined as an OUT parameter so I can pass the data to a report writer.
I know how to get the data in using cursors. I cannot store the results of my calculations in another table as all this is happening on a replicated "reporting" database that is read only. I thought I could use a table of records defined as following:
TYPE tActTableType IS TABLE OF mms_old_owner.MmsActivity%ROWTYPE INDEX BY BINARY_INTEGER;
tActTable tActTableType;
I can get the data into the table of records fine - the problem is getting it into the ref cursor. I tried using the TABLE() function as follows:
OPEN oRefCur FOR SELECT * FROM TABLE(tActTable);
All I get is this error:
PLS-00382: expression is of wrong type
Any suggestions?
Thanks!
|
|
|
|
| Re: In a quandry [message #10676 is a reply to message #10675] |
Mon, 09 February 2004 09:19  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
The TABLE function requires a type defined at the SQL level (create or replace type) - your example is a type at the PL/SQL level.
|
|
|
|
| Re: In a quandry [message #10677 is a reply to message #10675] |
Mon, 09 February 2004 09:23 |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
You cannot SELECT from PL/SQL table.
But you can LOOP through it, evaluate data, and you can pass it as an output parameter instead of REF CURSOR. But with very fiew detail you gave it is difficult to determine the best way to accomplish your task.
|
|
|
|
| Re: In a quandry [message #10678 is a reply to message #10676] |
Mon, 09 February 2004 11:33 |
Paul Bankston
Messages: 12
Registered: February 2004
|
Junior Member |
|
|
OK
I created a object type in SQL -
CREATE OR REPLACE TYPE oActivity AS OBJECT
(
fiscalYr CHAR(4),
section CHAR(10),
actNo NUMBER(6,2),
description CHAR(30),
rate NUMBER(10)
);
Then I create a collection type based on oActivity -
CREATE OR REPLACE TYPE tActTableType AS TABLE OF oActivity;
I then run the procedure -
CREATE OR REPLACE PROCEDURE DynamicSqlTest2
(
iActList IN VARCHAR2,
iFyear IN VARCHAR2,
iSec IN NUMBER,
oRefCur OUT mms_old_owner.brio_package.ref_cur
)
AS
CURSOR cActCur IS
SELECT fiscalyr, section, actno, description,rate, unit,
unitper, notes, isreport, strucactivity, changedate,
groupno, isinactive, fiscalyear
FROM mms_old_owner.MmsActivity
WHERE fiscalYr = iFyear AND
section = iSec AND
actNo IN (SELECT * FROM TABLE(CAST(ufStr2Tbl(iActList) as myTableType)));
lnIndex NUMBER := 1;
tActTable tActTableType :=tActTableType();
BEGIN
FOR rActRec IN cActCur LOOP
tActTable.EXTEND;
tActTable(lnIndex).fiscalyr := rActRec.fiscalyr;
tActTable(lnIndex).section := rActRec.section;
tActTable(lnIndex).actNo := rActRec.actNo;
tActTable(lnIndex).description := rActRec.description;
tActTable(lnIndex).rate := rActRec.rate;
lnIndex := lnIndex + 1;
END LOOP;
OPEN oRefCur FOR
SELECT * FROM TABLE(tActTable);
END DynamicSqlTest2;
/
I get the following error -
ORA-06530: Reference to uninitialized composite
ORA-06512: at "BANKSTONP.DYNAMICSQLTEST2", line 25
ORA-06512: at line 9
|
|
|
|
|
|
| Re: In a quandry [message #10680 is a reply to message #10678] |
Mon, 09 February 2004 13:30 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Paul, why wouldn't the select statement associated with the ref cursor just use the select statement associated with the cActCur cursor? In other words, why an intermediate collection at all? Why not just:
create or replace procedure ... (...)
as
open orefcur for
select fiscalyr, section, actno, description, rate
from mms_old_owner.mmsactivity
where fiscalyr = ifyear
and section = isec
and actno in (select * from table(cast(ufstr2tbl(iactlist) as mytabletype)));
end dynamicsqltest2; |
|
|
|
| Re: In a quandry [message #10695 is a reply to message #10680] |
Tue, 10 February 2004 06:07 |
Paul Bankston
Messages: 12
Registered: February 2004
|
Junior Member |
|
|
Todd,
The example script I posted is just a simplified test script. Normally I would do what you suggested and have done that in other procedures. However, this is a complicated problem. I outlined it briefly in my first posting. The user will supply a optional list of 14 different parameters, some of them lists of items. Some of these parameters may be null every time the procedure is run. I have to take the parameters and use them to pull data from 5 different joined tables and using cursors and arrays filter the data down to the exact result set I need. Once there I need to perform calculations and summarized the data down even more. I will then have a number of rows of summarized data. I have to store these somewhere. I can't store them in a table because I am working on a read-only reporting database. So I was going to put the data into a table of records. The problem is that now I have to somehow pass the summarized records to my report writer. The only way I know how to do this is using a ref cursor as an OUT parameter.
Paul
|
|
|
|
|
|
| Re: In a quandry [message #11927 is a reply to message #10678] |
Thu, 22 April 2004 09:23 |
kenii
Messages: 1
Registered: April 2004
|
Junior Member |
|
|
CREATE OR REPLACE PROCEDURE DynamicSqlTest2
(
iActList IN VARCHAR2,
iFyear IN VARCHAR2,
iSec IN NUMBER,
oRefCur OUT mms_old_owner.brio_package.ref_cur
)
AS
CURSOR cActCur IS
SELECT fiscalyr, section, actno, description,rate, unit, unitper, notes, isreport, strucactivity, changedate, groupno, isinactive, fiscalyear
FROM mms_old_owner.MmsActivity
WHERE fiscalYr = iFyear AND section = iSec AND
actNo IN (SELECT * FROM TABLE(CAST(ufStr2Tbl(iActList) as myTableType)));
lnIndex NUMBER := 1;
tActTable tActTableType :=tActTableType();
vdata oActivity;
BEGIN
FOR rActRec IN cActCur LOOP
tActTable.EXTEND;
vdata := oActivity(rActRec.fiscalyr, rActRec.section, rActRec.actNo, rActRec.description£¬ rActRec.rate);
tActTable(lnIndex) = vData;
lnIndex := lnIndex + 1;
END LOOP;
OPEN oRefCur FOR
SELECT * FROM TABLE(tActTable);
END DynamicSqlTest2;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
