Re: How to use cursor properly?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 5 Jan 2002 07:45:39 -0800
Message-ID: <a1773301mpu@drn.newsguy.com>

In article <3C3690B7.48F5_at_maxinter.net>, C says...
>
>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
>

that is because you defined the ref cursor variable to be:

> TYPE c_quote_type IS REF CURSOR RETURN spotbuy_quotes%ROWTYPE;

and spotby_quotes (the table) has more columns in it then you are selecting. The table has columns (a,b,c,d,e), your query only selects (a,b,c) and hence the ref cursor definition doesn't match the query you used.

This is a really bad example of when to use a REF CURSOR by the way. You should use a ref cursor ONLY when you HAVE to, not as a standard practice. They are not nearly as efficient as "regular" or "implicit" cursors.

Your plsql body should SIMPLY be:

> 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;

begin

   select quote_id, fsc, niin, part_number, nomem, loc_id, qty,

          qty_unit, quote_date, approved, remarks
     into o_quote_id, o_fsc, o_niin, o_partNum, o_nomen, o_locid, o_qty, 
          o_qtyUnit, o_quotedate, o_approved, o_remarks
     from spotbuy_quotes

    where order_id = p_orderId;
exception

    when no_data_found
    then

        o_errFlag := 1;
end;

As for the error:

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

I'll have to GUESSS since this error is caused by the CLIENT calling this procedure, not the procedure itself. I'll guess the client is PRO*C, you are binding a VARCHAR host variable and have not set the .len attribute.

If you have code like this:

exec sql begin declare section;

   varchar host_var[25];
exec sql end declare section;

   exec sql execute
   begin

       some_procedure( :host_var );
   end;
   end-exec;

and host_var is mapped to an OUT parameter, you should code:

   host_var.len = sizeof(host_var.arr);
   exec sql execute
   begin

      some_procedure( :host_var );
   end;
   end-exec;

as it is, the .len attribute is uninitialized and is not valid and you are getting that invalid bind length. initialize it and it should go away.

So

o use select into
o select into the OUT parameters directly (its the correct way to do it, you   can even READ the out parameters in the procedure, no need to add overhead   of temporaries)
o catch the exception to set your error flag (this I would actually SKIP, i would
  just let the error propagate back to the caller) o set the .len attribute of your varchar host variables in the client

>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

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Jan 05 2002 - 09:45:39 CST