Home » SQL & PL/SQL » SQL & PL/SQL » cannot perform fetch on a pl sql statement
cannot perform fetch on a pl sql statement [message #378287] Mon, 29 December 2008 16:12 Go to next message
vijju56
Messages: 22
Registered: December 2008
Junior Member
Hi,

I created a procedure like this:


create or replace
PROCEDURE retrieve_pdfm
  (
    p_invid IN invoice_detail.invd_inv_id%TYPE,
    
       
 
    p_afrmid OUT application_form.afrm_id%TYPE,
    p_desc OUT application_form.AFRM_DESCRIPTION%TYPE,
    p_email OUT application_form.AFRM_AGENCY_E_ADDR%TYPE,
    p_userid OUT GENERATED_AGENCY_APPL_FORM.GAAF_CREATOR_USERID%TYPE,
    p_gaafid OUT GENERATED_AGENCY_APPL_FORM.GAAF_ID%TYPE,
    p_confirmnum OUT invoice_detail.INVD_CEPAS_CONFIRM_NUM%TYPE,
    p_datepaid OUT invoice_detail.INVD_DATE_PAID%TYPE,
    p_creationdate OUT invoice_detail.INVD_CREATION_DATE%TYPE,
    p_invdfee OUT invoice_detail.invd_fee%TYPE,
    p_sku OUT invoice_detail.INVD_SKU_NBR%TYPE,
    p_directory OUT AGENCY_APPLICATION_FORM.AAFRM_DIRECTORY_STORED%TYPE,
    p_code OUT AGENCY_APPLICATION_FORM.AGENCY_AGY_CODE%TYPE,
    p_bizid OUT INVOICE.INV_BIZ_ID%TYPE,
    p_bizname OUT BUSINESS.BIZ_BUSINESS_NAME%TYPE,
   p_pdf OUT GENERATED_AGENCY_APPL_FORM.GAAF_FORM%TYPE)
   
 
IS
 
 
 
    
   e EXCEPTION;
  pragma exception_init(e,100);
  cnt number;
  CURSOR m_cursor
  IS
     SELECT d.afrm_id         ,
      d.afrm_description      ,
      d.afrm_agency_e_addr    ,
      b.gaaf_creator_userid   ,
      b.gaaf_id               ,
      e.invd_cepas_confirm_num,
      e.invd_date_paid        ,
      e.invd_creation_date    ,
      e.invd_fee              ,
      e.invd_sku_nbr          ,
      z.aafrm_directory_stored,
      z.agency_agy_code       ,
      g.inv_biz_id            ,
      f.biz_business_name,
      b.gaaf_form
       FROM generated_agency_appl_form b,
      application_form d                ,
      invoice_detail e                  ,
      invoice g                         ,
      business f                        ,
      agency_application_form z
      WHERE b.gaaf_aafrm_id = d.afrm_id
    AND b.gaaf_id           = e.invd_id
    AND e.invd_inv_id       = p_invid
    AND e.invd_inv_id       = g.inv_id
    AND d.afrm_id           = z.afrm_afrm_id
    AND g.inv_biz_id        = f.biz_id;
BEGIN
  OPEN m_cursor;
  LOOP
    FETCH m_cursor
       INTO       
      
       p_afrmid ,
      p_desc         ,
      p_email        ,
      p_userid       ,
      p_gaafid       ,
      p_confirmnum   ,
      p_datepaid     ,
      p_creationdate ,
      p_invdfee      ,
      p_sku          ,
      p_directory    ,
      p_code         ,
      p_bizid        ,
      p_bizname ,
     p_pdf;
     
     
     
    EXIT
  WHEN m_cursor%NOTFOUND;
          END LOOP;
  
   cnt := m_cursor%ROWCOUNT;
  IF cnt = 0 THEN raise e; END IF;
  CLOSE m_cursor;
  
EXCEPTION
  WHEN e THEN
--WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('wrong value given');
END retrieve_pdfm;



And i am getting an error like this while calling this procedure from jave:


cannot perform fetch on a pl sql statement 



Can any one help me in this, please?

Thanks
Re: cannot perform fetch on a pl sql statement [message #378291 is a reply to message #378287] Mon, 29 December 2008 17:07 Go to previous messageGo to next message
ThomasG
Messages: 3114
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So what is the actual error you get?

Quote:

cannot perform fetch on a pl sql statement



Seems to be neither a real Java nor a real Oracle error.

Also, HOW do you call the procedure form Java? You can of course not use a ResultSet or anything like that, you have to use Out Parameters. Here is an example on how to do do it.
Re: cannot perform fetch on a pl sql statement [message #378324 is a reply to message #378287] Tue, 30 December 2008 00:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you realize what your procedure actually does?
It fetches all rows from the cursor and only "remembers" the last one fetched.
Also, what is the purpose of exception e? The only thing you do if it is raised is comparable to writing a message to /dev/null and continue with what you were doing.
Re: cannot perform fetch on a pl sql statement [message #378436 is a reply to message #378287] Tue, 30 December 2008 07:43 Go to previous messageGo to next message
vijju56
Messages: 22
Registered: December 2008
Junior Member
Hi


It fetches all rows from the cursor and only "remembers" the last one fetched.



Can you please correct me in the code where I went wrong?

Thanks
Re: cannot perform fetch on a pl sql statement [message #378442 is a reply to message #378436] Tue, 30 December 2008 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59791
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nowhere if it is what you want.

Regards
Michel
Re: cannot perform fetch on a pl sql statement [message #378448 is a reply to message #378287] Tue, 30 December 2008 08:13 Go to previous messageGo to next message
vijju56
Messages: 22
Registered: December 2008
Junior Member
Hi,

Nowhere if it is what you want.


What does that mean?

Thanks
Re: cannot perform fetch on a pl sql statement [message #378453 is a reply to message #378448] Tue, 30 December 2008 08:35 Go to previous messageGo to next message
flyboy
Messages: 1779
Registered: November 2006
Senior Member
vijju56 wrote on Tue, 30 December 2008 15:13
What does that mean?

As you posted only code without any description, it may or may not return the required values. However, this is not the most efficient way. FETCH statement fetches cursor values into given variables. When done in LOOP, the formerly fetched values are overwritten with the later ones. In the end, the variables contain the values from last fetched row. As the SELECT statement contains no ORDER BY clause, this row is not deterministic (may be any row from the result set).

Do you really want this? If so, it is ok and you may forget these comments.
Re: cannot perform fetch on a pl sql statement [message #378461 is a reply to message #378453] Tue, 30 December 2008 09:12 Go to previous message
vijju56
Messages: 22
Registered: December 2008
Junior Member
Hi Flyboy,

Thanks for your reply. I got you.

Thanks once again
Previous Topic: subtype INTEGER
Next Topic: user and password in DBURI
Goto Forum:
  


Current Time: Wed Nov 26 09:47:13 CST 2014

Total time taken to generate the page: 0.06190 seconds