Home » Developer & Programmer » Forms » invalid cursor using SYS_REFCURSOR (Oracle Forms Builder 10.1.2.3.0, IBM-AIX 5.3)
| invalid cursor using SYS_REFCURSOR [message #552436] |
Wed, 25 April 2012 15:47  |
wtolentino
Messages: 404
Registered: March 2005
|
Senior Member |
|
|
i have created a procedure to open a query using a reference sys_refcursor as output parameter. i referenced the cursor in a pl/sql and is working correctly. however it throws error "ORA-01001: invalid cursor ORA-01403: no data found" when use on the oracle forms builder.
create or replace procedure mrch.prc_proj_list (p_add_clause in varchar2, p_rec_set out sys_refcursor) as
begin
Open p_rec_set for
'Select mp2.REGION region, '||
' mp2.ID proj_id, '||
' mppm2.PHASE phase, '||
' mm2.muni muni_id, '||
' mm2.MUNINAME muni_name, '||
' nvl((select Sum(ma.AMOUNT) From MRCH.MRC_AGREEMENT ma '||
' Where ma.PROJECT_ID = mp2.ID '||
' And ma.PHASE = mppm2.phase '||
' And ma.MUNI = mm2.MUNI),0) agreement, '||
' nvl((Select Sum(NVL(ma.GREEN_BOOK_AMOUNT,0) + NVL(ma.GB_ADJUST,0)) From MRCH.MRC_ALLOCATION ma '||
' Where ma.PROJECT_ID = mp2.ID '||
' And ma.PHASE = mppm2.phase '||
' And ma.MUNI = mm2.MUNI),0) allocation, '||
' nvl((SELECT SUM((NVL(FA_AMOUNT,0) * STATE_PCT) * NVL(SPLIT_PCT,1)) '||
' FROM MRCH.MRC_PROJ_PHASE_MUNI_COST A, MRCH.MRC_COST B '||
' WHERE A.PROJECT_ID = mp2.ID '||
' AND A.PHASE = mppm2.phase '||
' AND A.MUNI = mm2.MUNI '||
' AND A.PROJECT_ID = B.PROJECT_ID '||
' AND A.PINSUB = B.PINSUB),0) cost, '||
' 0 payment, '|| -- see function fnc_proj_payment
' decode(NVL(mppm2.MCLOSED,''N''),''Y'',''C'',null) proj_status_code '||
' From MRCH.MRC_PROJECT mp2, '||
' MRCH.MRC_PROJ_PHASE_MUNI mppm2, '||
' MRCH.MRC_MUNI mm2 '||
' Where mp2.id in (select mrc_proj2.proj_id '||
' from (select mp1.id proj_id, '||
' mp1.region, '||
' decode(NVL(mppm1.mclosed,''N''),''Y'',''Closed'',''Open'') proj_status, '||
' max(decode(NVL(mppm1.mclosed,''N''),''Y'',''Closed'',''Open'')) '||
' over (partition by mp1.id order by mp1.region, mp1.id) max_proj_status_flag '||
' from mrch.mrc_project mp1, '||
' mrch.mrc_proj_phase_muni mppm1 '||
' where mp1.id = mppm1.project_id) mrc_proj2 '||
' where mrc_proj2.proj_status <> mrc_proj2.max_proj_status_flag) '||
' And mp2.ID = mppm2.PROJECT_ID '||
' And mppm2.MUNI = mm2.MUNI '|| p_add_clause ||' '||
'UNION '||
'SELECT mp2.REGION region, '||
' mp2.ID proj_id, '||
' mppm2.PHASE phase, '||
' mm2.muni muni_id, '||
' mm2.MUNINAME muniname, '||
' nvl((select Sum(ma.AMOUNT) From MRCH.MRC_AGREEMENT ma '||
' Where ma.PROJECT_ID = mp2.ID '||
' And ma.PHASE = mppm2.phase '||
' And ma.MUNI = mm2.MUNI),0) agreement, '||
' nvl((Select Sum(NVL(ma.GREEN_BOOK_AMOUNT,0) + NVL(ma.GB_ADJUST,0)) From MRCH.MRC_ALLOCATION ma '||
' Where ma.PROJECT_ID = mp2.ID '||
' And ma.PHASE = mppm2.phase '||
' And ma.MUNI = mm2.MUNI),0) allocation, '||
' nvl((SELECT SUM((NVL(FA_AMOUNT,0) * STATE_PCT) * NVL(SPLIT_PCT,1)) '||
' FROM MRCH.MRC_PROJ_PHASE_MUNI_COST A, MRCH.MRC_COST B '||
' WHERE A.PROJECT_ID = mp2.ID '||
' AND A.PHASE = mppm2.phase '||
' AND A.MUNI = mm2.MUNI '||
' AND A.PROJECT_ID = B.PROJECT_ID '||
' AND A.PINSUB = B.PINSUB),0) cost, '||
' 0 payment, '||
' decode(NVL(mppm2.MCLOSED,''N''),''Y'',''C'',null) proj_status_code '||
' FROM MRCH.MRC_PROJECT mp2, '||
' MRCH.MRC_PROJ_PHASE_MUNI mppm2, '||
' MRCH.MRC_MUNI mm2 '||
' WHERE mp2.ID = mppm2.PROJECT_ID '||
' AND mppm2.MUNI = mm2.MUNI '||
' AND mp2.id in (select mrc_proj2.proj_id '||
' from (select mp1.id proj_id, '||
' mp1.region, '||
' decode(NVL(mppm1.mclosed,''N''),''Y'',''Closed'',''Open'') proj_status, '||
' max(decode(NVL(mppm1.mclosed,''N''),''Y'',''Closed'',''Open'')) '||
' over (partition by mp1.id order by mp1.region, mp1.id) max_proj_status_flag '||
' from mrch.mrc_project mp1, '||
' mrch.mrc_proj_phase_muni mppm1 '||
' where mp1.id = mppm1.project_id) mrc_proj2 '||
' where mrc_proj2.proj_status = mrc_proj2.max_proj_status_flag) '||
' AND NVL(mppm2.MCLOSED,''N'') = ''N'' '|| p_add_clause ||
' order by 1,2,3,4 ';
end;
PL/SQL code:
-- Created on 25-Apr-12 by WTOLENTINO
declare
-- define the variables to hold the file attributes
vLineBuf varchar2(4000);
v_cursor SYS_REFCURSOR;
-- define the variables to hold the record group from the system reference cursor
vRegion MRCH.MRC_PROJECT.region%Type;
vProjId MRCH.MRC_PROJECT.id%Type;
vPhase MRCH.MRC_PROJ_PHASE_MUNI.PHASE%Type;
vMuniId MRCH.MRC_MUNI.MUNI%Type;
vMuniName MRCH.MRC_MUNI.MUNINAME%Type;
vAgreement number;
vAllocation number;
vCost number;
vPayment number;
vProjStatusCode MRCH.MRC_PROJ_PHASE_MUNI.MCLOSED%Type;
BEGIN
-- prc_proj_list is a database procedure
mrch.prc_proj_list ('and 1 = 1', v_cursor);
loop
fetch v_cursor
into vRegion, vProjId, vPhase, vMuniId, vMuniName,
vAgreement, vAllocation, vCost, vPayment, vProjStatusCode;
exit when v_cursor%NotFound;
vLineBuf := '="'||vRegion||'",="'||
vProjId||'",="'||
vPhase||'","'||
vMuniName||'","'||
to_char(vAgreement)||'","'||
to_char(vAllocation)||'","'||
to_char(vCost)||'","'||
to_char(vPayment)||'","'||
vProjStatusCode||'"';
dbms_output.put_line(vLineBuf);
end loop;
close v_cursor;
END;
forms package code:
Procedure extract_proj_list (pClause varchar2)is
-- define the variables to hold the file attributes
vFileName varchar2(80);
vDataType varchar2(80);
vLineBuf varchar2(4000);
vHdrBuf varchar2(4000);
vOutFile text_io.file_type;
vHardPath varchar2(200);
vVirtualPath varchar2(200);
v_cursor SYS_REFCURSOR;
-- define the variables to hold the record group from the system reference cursor
vRegion MRCH.MRC_PROJECT.region%Type;
vProjId MRCH.MRC_PROJECT.id%Type;
vPhase MRCH.MRC_PROJ_PHASE_MUNI.PHASE%Type;
vMuniId MRCH.MRC_MUNI.MUNI%Type;
vMuniName MRCH.MRC_MUNI.MUNINAME%Type;
vAgreement number;
vAllocation number;
vCost number;
vPayment number;
vProjStatusCode MRCH.MRC_PROJ_PHASE_MUNI.MCLOSED%Type;
BEGIN
-- filename format in [Mon][YYYY][mcrpt014][db name].csv
-- e.g. Apr2012mcrpt014ORP2.csv
vFileName := to_char(sysdate,'Mon')||to_char(sysdate,'yyyy')||'mcrpt014'||
upper(sys_context('USERENV','DB_NAME'))||'.csv';
/* determine file paths and open file */
Tool_Env.Getvar('MRCH_OUTPUT_DOC_DIR', vHardPath);
if vHardPath is null then
alertme('stop','undefined MRCH_OUTPUT_DOC_DIR');
end if;
vHardPath := vHardPath||'/'||vFileName;
vVirtualPath := get_virtual_path;
vVirtualPath := vVirtualPath||vFileName;
begin
vOutFile := text_io.fopen(vHardPath,'w');
exception
when others then
alertme('stop','unable to open '||vHardPath);
end;
-- header for the file
vHdrBuf := '"Region"'||','||
'"Project ID"'||','||
'"Phase"'||','||
'"Agreement"'||','||
'"Allocation"'||','||
'"Cost"'||','||
'"Payment"'||','||
'"Status Code"';
text_io.put_line(vOutFile,vHdrBuf);
-- prc_proj_list is a database procedure
mrch.prc_proj_list ('and 1 = 1', v_cursor);
loop
fetch v_cursor
into vRegion, vProjId, vPhase, vMuniId, vMuniName,
vAgreement, vAllocation, vCost, vPayment, vProjStatusCode;
exit when v_cursor%NotFound;
vPayment := pkg_data_extract.fnc_proj_payment(vProjId, vPhase, vMuniId);
vLineBuf := '="'||vRegion||'","'||
vProjId||'","'||
vPhase||'","'||
vMuniName||'","'||
to_char(vAgreement)||'","'||
to_char(vAllocation)||'","'||
to_char(vCost)||'","'||
to_char(vPayment)||'","'||
vProjStatusCode||'"';
text_io.put_line(vOutFile,vLineBuf);
end loop;
close v_cursor;
text_io.fclose(vOutFile);
web.show_document(vVirtualPath,'_blank');
synchronize;
EXCEPTION
when others then
text_io.fclose(vOutFile);
raise;
END;
END;
i found out that the problem starts in this line of code in forms:
fetch v_cursor
into vRegion, vProjId, vPhase, vMuniId, vMuniName,
vAgreement, vAllocation, vCost, vPayment, vProjStatusCode;
exit when v_cursor%NotFound;
please help. thank you in advance.
|
|
|
|
| Re: invalid cursor using SYS_REFCURSOR [message #552441 is a reply to message #552436] |
Wed, 25 April 2012 17:17  |
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
|
Senior Member |
|
|
Oracle/PLSQL: ORA-01001 Error
------------------------------------------
Error: ORA-01001: invalid cursor
Cause: You tried to reference a cursor that does not yet exist.
This may have happened because:
1.You've executed a FETCH cursor before OPENing the cursor.
2.You've executed a CLOSE cursor before OPENing the cursor.
3.You've executed a FETCH cursor after CLOSING the cursor.
Action: The options to resolve this Oracle error are:
1.Make sure you haven't CLOSEd the cursor and are still referencing it in your code.
2.Make sure you've OPENed the cursor before calling a FETCH cursor or CLOSE cursor.
3.If everything else is fine, you may need to increase the AREASIZE and MAXOPENCURSORS options.
regards
baba
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jul 05 22:40:28 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
