Home » SQL & PL/SQL » SQL & PL/SQL » Explicit Cursor
Explicit Cursor [message #378023] |
Sat, 27 December 2008 17:52  |
vijju56
Messages: 22 Registered: December 2008
|
Junior Member |
|
|
Hi,
My table scripts are:
Invoice_detail:
CREATE TABLE "SCOTT"."INVOICE_DETAIL"
( "INVD_ID" NUMBER(4,0) NOT NULL ENABLE,
"INVD_INV_ID" NUMBER(4,0) NOT NULL ENABLE,
"INVD_LP_ID" NUMBER(4,0) NOT NULL ENABLE,
"INVD_FEE" NUMBER(7,0) NOT NULL ENABLE,
"INVD_CEPAS_CONFIRM" VARCHAR2(20 CHAR) NOT NULL ENABLE,
"INVD_DATE_PAID" DATE NOT NULL ENABLE,
"INVD_DELETE_IND" VARCHAR2(1 BYTE) NOT NULL ENABLE,
"INVD_AGSK_SKU_NBR" NUMBER(4,0) NOT NULL ENABLE,
"INVD_CREATOR_USERID" NUMBER(4,0) NOT NULL ENABLE,
"INVD_CREATION_DATE" DATE NOT NULL ENABLE,
"INVD_UPDATE_USERID" NUMBER(4,0) NOT NULL ENABLE,
"INVD_LAST_UPDATE" DATE NOT NULL ENABLE,
CONSTRAINT "INVOICE_DETAIL_PK" PRIMARY KEY ("INVD_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ;
Generated_agency_appl_form:
CREATE TABLE "SCOTT"."GENERATED_AGENCY_APPL_FORM"
( "GAAF_ID" NUMBER(4,0) NOT NULL ENABLE,
"GAAF_AAFRM_ID" NUMBER(4,0) NOT NULL ENABLE,
"GAAF_GENERATION_DT" DATE NOT NULL ENABLE,
"GAAF_CREATOR_USERID" NUMBER(4,0) NOT NULL ENABLE,
"GAAF_CREATION_DATE" DATE NOT NULL ENABLE,
"GAAF_UPDATE_USERID" NUMBER(4,0) NOT NULL ENABLE,
"GAAF_LAST_UPDATE" DATE NOT NULL ENABLE,
CONSTRAINT "GENERATED_AGENCY_APPL_FOR_PK" PRIMARY KEY ("GAAF_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ENABLE,
CONSTRAINT "GAAF_ID_FK" FOREIGN KEY ("GAAF_ID")
REFERENCES "SCOTT"."INVOICE_DETAIL" ("INVD_ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ;
Application_form:
CREATE TABLE "SCOTT"."APPLICATION_FORM"
( "AFRM_ID" NUMBER(4,0) NOT NULL ENABLE,
"AFRM_SHORT_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"AFRM_DESCRIPTION" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"AFRM_CREATOR_USERID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"AFRM_CREATION_DATE" DATE NOT NULL ENABLE,
"AFRM_UPDATE_USERID" NUMBER(4,0) NOT NULL ENABLE,
"AFRM_LAST_UPDATE" DATE NOT NULL ENABLE,
"AFRM_AGENCY_E_ADDR" VARCHAR2(20 BYTE) NOT NULL ENABLE,
CONSTRAINT "APPLICATION_FORM_PK" PRIMARY KEY ("AFRM_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ;
And I wrote Procedure for retrieving date and email as :
CREATE OR REPLACE
PROCEDURE retrieve_pdf
(
p_invid IN invoice_detail.invd_inv_id%TYPE,
p_email OUT application_form.afrm_agency_e_addr%TYPE,
p_date OUT generated_agency_appl_form.gaaf_creation_date%TYPE )
IS
CURSOR date_cursor
IS
SELECT gaaf_creation_date
FROM generated_agency_appl_form
WHERE gaaf_id IN
(SELECT invd_id FROM invoice_detail WHERE invd_inv_id = p_invid
);
CURSOR email_cur(p_invid IN invoice_detail.invd_inv_id%TYPE )
IS
SELECT afrm_agency_e_addr p_email
FROM application_form
WHERE afrm_id IN
(SELECT invd_lp_id FROM invoice_detail WHERE invd_inv_id = p_invid
);
BEGIN
OPEN date_cursor;
LOOP
FETCH date_cursor INTO p_date;
EXIT
WHEN date_cursor%NOTFOUND;
FOR ecur IN email_cur (p_invid)
LOOP
FETCH email_cur INTO p_email;
DBMS_OUTPUT.put_line ( ecur.p_email||p_date);
END LOOP;
END LOOP;
CLOSE date_cursor;
END retrieve_pdf;
And the execution code:
DECLARE
p_invid invoice_detail.invd_inv_id%TYPE;
p_email application_form.afrm_agency_e_addr%TYPE;
p_date generated_agency_appl_form.gaaf_creation_date%TYPE;
BEGIN
p_invid := 105;
retrieve_pdf (p_invid, p_email, p_date);
END;
But I am getting nothing, And there are no errors too....
Can any one help me in this ?
Thanks
|
|
|
Re: Explicit Cursor [message #378026 is a reply to message #378023] |
Sat, 27 December 2008 19:49  |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Change this line:
FETCH email_cur INTO p_email;
to:
p_email := ecur.p_email;
A cursor for loop does an implicit fetch, so just assign the value. That should allow your code to run without error, however it is still inefficient confusing code. You should not use the same names for different things. It makes it hard to tell what is what and Oracle may end up trying to use the wrong one. Also, you could do the whole thing in one cursor, instead of two. I assume that you realize that your out parameters will only return the last values or perhaps you are only expecting one row, in which case you don't need a cursor or a loop at all, just one select statement.
|
|
|
Goto Forum:
Current Time: Tue Feb 11 10:01:52 CST 2025
|