Re: DBMS_SQL.COLUMN_VALUE_CHAR **NOT A** Bug
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