Home » SQL & PL/SQL » SQL & PL/SQL » Explicit Cursor
Explicit Cursor [message #378023] Sat, 27 December 2008 17:52 Go to next message
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 Go to previous message
Barbara Boehmer
Messages: 8636
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.
Previous Topic: logical oprator 'like' & 'in' not working.
Next Topic: login comand (merged)
Goto Forum:
  


Current Time: Sat Dec 10 22:27:51 CST 2016

Total time taken to generate the page: 0.09089 seconds