How to use cursor properly?

From: C Chang <cschang_at_maxinter.net>
Date: Sat, 05 Jan 2002 00:35:51 -0500
Message-ID: <3C3690B7.48F5@maxinter.net>

I tried to write a procedure to qurey one row of record back. However, I got different errors at different syntax of cursor as following.

PROCEDURE GetTheQuote

      (p_orderID      IN  purchase_requests.order_id%TYPE,
       o_quoteID      IN OUT spotbuy_quotes.quote_id%TYPE,
       o_quotedate    IN OUT spotbuy_quotes.quote_date%TYPE,
       o_fsc          IN OUT spotbuy_quotes.fsc%TYPE,
       o_niin         IN OUT spotbuy_quotes.niin%TYPE,
       o_partNumber   IN OUT spotbuy_quotes.part_number%TYPE,
       o_nomen        IN OUT spotbuy_quotes.nomen%TYPE,
       o_locid        IN OUT spotbuy_quotes.loc_id%TYPE,
       o_qty          IN OUT spotbuy_quotes.qty%TYPE,
       o_ui           IN OUT spotbuy_quotes.qty_unit%TYPE,
       o_remarks      IN OUT spotbuy_quotes.remarks%TYPE,
       o_errFlag        OUT  NUMBER,
       o_advMsg         OUT  VARCHAR2  ) IS
    TYPE c_quote_type IS REF CURSOR RETURN spotbuy_quotes%ROWTYPE;
     quote_cur c_quote_type;
  
  BEGIN
     OPEN quote_cur FOR
        SELECT quote_id, fsc, niin, part_number, nomen, loc_id, qty,
qty_unit, quote_date, approved, remarks
        FROM spotbuy_quotes
        WHERE order_id = p_orderID;
        FETCH quote_cur INTO v_quote_id, v_fsc, v_niin,v_partNum,
v_nomen, v_locid, v_qty, v_qtyUnit,
           v_quotedate, v_approved, v_remarks;

....

  END;
In above syntax, I got ERROR at compile error PL/SQL: SQL Statement ignored
PLS-00382: expression is of wrong type
PLS-00394: wrong number of values in the INTO list of a FETCH statement PL/SQL: SQL Statement ignored

If I replace above with

    ...

        CURSOR quote_cur  IS
         SELECT quote_id, fsc, niin, part_number, nomen, loc_id, qty,
qty_unit, quote_date, approved, remarks
         FROM spotbuy_quotes
         WHERE order_id = p_orderID;
         quote_rec   quote_cur%ROWTYPE;
        

BEGIN     OPEN quote_cur;

         FETCH quote_cur INTO v_quote_id, v_fsc, v_niin, v_partNum, v_nomen, v_locid, v_qty, v_qtyUnit,

            v_quotedate, v_approved, v_remarks;
        IF quote_cur%NOTFOUND THEN
            o_errFlag:= 1;
        ELSE
        o_quoteID := v_quote_id;
            o_quotedate := v_quotedate;
            o_fsc := v_fsc;
            o_niin := v_niin;
            o_partNumber := v_partNum;
            o_nomen := v_nomen;
            o_locid := v_locid;
            o_qty := v_qty;
            o_ui := v_qtyUnit;
            o_remarks := v_remarks;
        END IF;

   CLOSE quote_cur;
....

  END;
OR
..

  CURSOR quote_cur IS

         SELECT quote_id, fsc, niin, part_number, nomen, loc_id,
                qty, qty_unit, quote_date, approved, remarks
         FROM spotbuy_quotes
         WHERE order_id = p_orderID;

         quote_rec   quote_cur%ROWTYPE;
       
   BEGIN
      
      OPEN quote_cur; 
         FETCH quote_cur INTO quote_rec;

         IF quote_cur%NOTFOUND THEN
            o_errFlag:= 1;
         ELSE
            o_quoteID := quote_rec.quote_id;
            o_quotedate := quote_rec.quote_date;
            o_fsc := quote_rec.fsc;
            o_niin := quote_rec.niin;
            o_partNumber := quote_rec.part_Number;
            o_nomen :=quote_rec.nomen;
            o_locid := quote_rec.loc_id;
            o_qty := quote_rec.qty;
            o_ui := quote_rec.qty_Unit;
            o_remarks := quote_rec.remarks;
            o_errFlag:= 0;
         END IF;

        CLOSE quote_cur;

    END; I got different error at run time:

Cause error - the ORA-02005: Implicit(-1) length not valid for this bind or define datatype in run time

I have tried following and I got the same error. PROCEDURE GetTheQuote

      (p_orderID      IN  purchase_requests.order_id%TYPE,
       o_quoteID      IN OUT tbl_quote_id,
       o_quotedate    IN OUT tbl_reqDate,
       o_fsc          IN OUT tbl_fsc,
       o_niin         IN OUT tbl_niin,
       o_partNumber   IN OUT tbl_partNum,
       o_nomen        IN OUT tbl_nomen,
       o_locid        IN OUT tbl_loc_id,
       o_qty          IN OUT tbl_qty,
       o_ui           IN OUT tbl_qtyUnit,
       o_remarks      IN OUT tbl_remarks,
       o_errFlag        OUT  NUMBER,
       o_advMsg         OUT  VARCHAR2  ) IS

      v_count       BINARY_INTEGER DEFAULT 1;
      v_errCode     NUMBER;
      v_errText     VARCHAR2(200);

      CURSOR quote_cur  IS
         SELECT quote_id, fsc, niin, part_number, nomen, loc_id,
                qty, qty_unit, quote_date, approved, remarks
         FROM spotbuy_quotes
         WHERE order_id = p_orderID;

         quote_rec   quote_cur%ROWTYPE;
       
   BEGIN
      
      OPEN quote_cur;
         
         FETCH quote_cur INTO quote_rec;
         IF quote_cur%NOTFOUND THEN
            o_errFlag:= 1;
   
         ELSE
          
            o_quoteID(v_count) := quote_rec.quote_id;
            o_quotedate(v_count) := quote_rec.quote_date;
            o_fsc(v_count) := quote_rec.fsc;
            o_niin(v_count) := quote_rec.niin;
            o_partNumber(v_count) := quote_rec.part_Number;
            o_nomen(v_count) :=quote_rec.nomen;
            o_locid(v_count) := quote_rec.loc_id;
            o_qty(v_count) := quote_rec.qty;
            o_ui(v_count) := quote_rec.qty_Unit;
            o_remarks(v_count) := quote_rec.remarks;
            o_errFlag:= 0;
         END IF;
      CLOSE quote_cur;

..

   END; My ASP page :
sbPak = "{call IPV$vd2_spotbuy.GetTheQuote(?, {resultset 1, o_quoteID," &_

             " o_quotedate,  o_fsc, o_niin, o_partNumber, o_nomen, " &_
             " o_locid, o_qty, o_ui, o_remarks, o_errFlag, o_advMsg})}"
	               
  Set theQuote = Server.CreateObject("ADODB.Command")
	...
	theQuote(0) = orderID	
	

  Set rsSBquote = Server.CreateObject("ADODB.Recordset")     ...

        rsSBquote.Open theQuote

All my cursor syntax follows the standard syntax from PL/SQL programming book. Why it the static cause the error? Does that mean I have to use dynamic cursor? ( I have a similar case using dynamic cursor and works) From the book, it said that static is better than dynamic. Any guru can give some tip here? Thanks ahead.

C Chang Received on Fri Jan 04 2002 - 23:35:51 CST