Re: DBMS_SQL.COLUMN_VALUE_CHAR **NOT A** Bug

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 01 Nov 1998 14:32:11 GMT
Message-ID: <363e6f90.2998761_at_192.86.155.100>


A copy of this was sent to "Dmitry G. Bezgodov" <dima_at_profit.maginfo.net> (if that email address didn't require changing) On Sat, 31 Oct 1998 05:59:19 +0000, you wrote:

>Hello,
>I'm trying to useDBMS_SQL package in form 4.5 of Dev2k v 1.6.
>Can anyone explain me, why the example does not work:

You used define_column with a VARCHAR2. You called column_value_char (a CHAR type). CHAR <> VARCHAR2. The error you are getting states:

06562, 00000, "type of out argument must match type of column or bind variable"

// *Cause:  Attempting to get the value of a column or
//          a bind variable by calling procedure COLUMN_VALUE or
//          VARIABLE_VALUE of package DBMS_SQL but the type of
//          the given out argument where to place the value is different
//          from the type of the column or bind variable that was previously
//          defined by calling procedure DEFINE_COLUMN (for defining a column)
//          or BIND_VARIABLE (for binding a bind variable) of package DBMS_SQL.
// *Action: Pass in an out argument of the correct type when calling
//          procedure COLUMN_VALUE or VARIABLE_VALUE.  The right
//          type is the type that was provided when defining the column or
//          binding the bind variable.

So you changed the datatype between the bind and the getting of the value. You can either call:

define_column_char() instead of define_column.

or

column_value() instead of column_value_char....

either will make your block of code work. However.... If you want this to work fast in a client server environment you will investigate the functions:

create_group_from_query,
populate_group_from_query

and record groups in general. You see, each and every call to dbms_sql in the following block will create a server round trip. Every row you fetch will cause a server round trip. This little block of code itself will generate at least 9 round trips for each call to dbms_sql. If you use create_group_from-query and populate_group_from_query, you will dramatically reduce the round trips and forms will automatically ARRAY fetch 100 rows at a time into the record group (removing 99 out of 100 round trip calls while fetching, this can be dramatic as far as performance goes)....

>-- WHEN_BUTTON_PRESSED TRIGGER
>-- TABLE is abc( id integer, name varchar2(140))
>
>DECLARE
>
>c_id number;
>
>id_val number;
>
>name_val varchar2(140);
>
>flag number;
>
>BEGIN
>
>c_id:=DBMS_SQL.OPEN_CURSOR;
>
>DBMS_SQL.PARSE(c_id,'select id, name from abc',2);
>
>DBMS_SQL.DEFINE_COLUMN(c_id,1,id_val);
>
>DBMS_SQL.DEFINE_COLUMN(c_id,2,id_name,140);
>
>flag:=DBMS_SQL.EXECUTE(c_id);
>
>flag:=DBMS_SQL.FETCH_ROWS(c_id);
>
>IF flag > 0
>
>THEN
>
> DBMS_SQL.COLUMN_VALUE(c_id,1,id_val); --Works fine, because id_val is integer
>
> DBMS_SQL.COLUMN_VALUE_CHAR(c_id,2,name_val); --Failed! exception ora-6562!
>
>END IF;
>
>DBMS_SQL.CLOSE_CURSOR(c_id);
>
>END;
>
>------------------
>
>If I use integer(or number) type of column for DBMS_SQL.COLUMN_VALUE, then it works fine,
>
>else if I use varchar2 comumn type, then I get the error ora-06562.
>
>Is it bug?
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.
Received on Sun Nov 01 1998 - 15:32:11 CET

Original text of this message