Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1691
  • Last Modified:

Oracle dbms_sql error - ORA-06562: type of out argument must match type of column or bind variable

Hi,
      I am using the dbms_sql package to dynamically build reports.
I have a column, below, that I need to format on the fly..
The code is from the package I created in Oracle APEX > SQL Workshop
Oracle APEX does not allow a to_date() without the format of existing being known.

CREATION_DATE      TIMESTAMP(6) WITH LOCAL TIME ZONE

The first value of this column             10-JUN-14 12.36.08.199178 PM
Desired format                        DD-MON-RR HH:MIPM

I tried
ld_date            DATE;
ld_date            TIMESTAMP(6) WITH LOCAL TIME ZONE;
ld_date            TIMESTAMP(6);

ORA-20111: ERROR: [Formatting for the data row col_type[231] Name[CREATION_DATE] Format[DD-MON-RR HH:MIPM]
ORA-06562: type of out argument must match type of column or bind variable
ORA-01861: literal does not match format string

-1861 is my custom exception number but the errmsg is not

pdebug OUTPUT from below code.
XXXXXXX - 0
XXXXXXX - 1

     ......
     pdebug('XXXXXXX - 0'); 
     if ( l_dtbl(i).col_type in ( 12, 178, 179, 180, 181, 231 ) ) then
	pdebug('XXXXXXX - 1');   
      
        dbms_sql.column_value(l_cur,i, ld_date );

	pdebug('XXXXXXX - 2');         
        l_val:= to_char( ld_date, l_FORMAT_MASK);

	pdebug('XXXXXXX 3 - '||l_val);
     else
         dbms_sql.column_value(l_cur,i,l_val);
     end if;
     ......

Open in new window


I was following Ask Ask Tom  Link

Thank you, Bill
0
bcarlis
Asked:
bcarlis
  • 7
  • 6
2 Solutions
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
1. Have you been able to debug & pin down the "values of interest" (ld_date and l_FORMAT_MASK) that raised the exception?!

2. I'm just curious: do you mind trying this (you might have to adept it to fit into your code):
          l_anydata    anydata;
          l_date           date;
          l_date_char  varchar2(100);
          l_ret              number;
...
          dbms_sql.column_value(l_cur, i, l_date);
          l_anydata := anydata.convertdate(l_date);
          l_date_char := to_char(l_date, l_FORMAT_MASK);
          l_anydata := anydata.convertvarchar2(l_date_char);
          l_ret := l_anydata.getvarchar2(l_val);

Open in new window

0
 
bcarlisAuthor Commented:
Yes,
The first value of this column             10-JUN-14 12.36.08.199178 PM

ld_date according to Tom's example is DATE
ld_date DATE;

l_FORMAT_MASK = 'DD-MON-RR HH:MIPM';

This is where it gives the error:
dbms_sql.column_value(l_cur, i, l_date);

it would never make it to
l_anydata := anydata.convertdate(l_date);
in your example.

thank you for the help... Bill
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
in that case, what exactly is the data/column type?!
if ( l_dtbl(i).col_type in ( 12, 178, 179, 180, 181, 231 ) ) then

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Official docs says:
Oracle raises exception ORA-06562, inconsistent_type, if the type of this output parameter differs from the actual type of the value, as defined by the call to DEFINE_COLUMN.

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sql.htm#ARPLS68245

So, how is that column being defined beforehands?!
0
 
bcarlisAuthor Commented:
Here are the facts
From the table
CREATION_DATE      TIMESTAMP(6) WITH LOCAL TIME ZONE

during loop when it hits column CREATION_DATE  the col_type is 231
l_FORMAT_MASK = 'DD-MON-RR HH:MIPM';

I tried
ld_date            DATE;
ld_date            TIMESTAMP(6) WITH LOCAL TIME ZONE;
ld_date            TIMESTAMP(6);

But each time it hit
        dbms_sql.column_value(l_cur,i, ld_date );
it would get the error      

if the type of this output parameter differs ld_date

if I don't try to format the date all is fine..
        dbms_sql.column_value(l_cur,i, l_val);
l_val is a VARCHAR2(200);

So, the output of
is 10-JUN-14 12.36.08.199178 PM a VARCHAR2
so now, if I start there then how do I convert the VARCHAR2 to a date?
Because this is dynamic..  without knowing the DATE format of the table
0
 
bcarlisAuthor Commented:
CUSTOMER_NAME ----- 1
ADDRESS_LINE_1 ----- 1
CITY ----- 1
STATE ----- 1
COUNTRY ----- 1
POSTAL_CODE ----- 1
NOTES ----- 1
IS_ACTIVE ----- 1
CREATION_DATE ----- 231
CREATED_BY ----- 1
STATUS ----- 1
STSTUS_MSG ----- 1
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
I've just tested it with the following setup:
create table t1
(
  CUSTOMER_NAME varchar2(100),
  ADDRESS_LINE_1 varchar2(100),
  CITY varchar2(100),
  STATE varchar2(100),
  COUNTRY varchar2(100),
  POSTAL_CODE varchar2(100),
  NOTES varchar2(100),
  IS_ACTIVE varchar2(100),
  CREATION_DATE timestamp(6) with local time zone,
  CREATED_BY varchar2(100),
  STATUS varchar2(100),
  STSTUS_MSG varchar2(100)
);

insert into t1 (CUSTOMER_NAME, ADDRESS_LINE_1, CITY, STATE, COUNTRY, POSTAL_CODE, NOTES, IS_ACTIVE, CREATION_DATE, CREATED_BY, STATUS, STSTUS_MSG)
values ('AAAAAA', 'AAAAAA', 'AAAAAA', 'AAAAAA', 'AAAAAA', 'AAAAAA', 'AAAAAA', 'AAAAAA', '10.07.14 23:15:51,058000', 'AAAAAA', 'AAAAAA', 'AAAAAA');

insert into t1 (CUSTOMER_NAME, ADDRESS_LINE_1, CITY, STATE, COUNTRY, POSTAL_CODE, NOTES, IS_ACTIVE, CREATION_DATE, CREATED_BY, STATUS, STSTUS_MSG)
values ('BBBBBB', 'BBBBBB', 'BBBBBB', 'BBBBBB', 'BBBBBB', 'BBBBBB', 'BBBBBB', 'BBBBBB', '10.06.14 12:36:08,199178', 'BBBBBB', 'BBBBBB', 'BBBBBB');

insert into t1 (CUSTOMER_NAME, ADDRESS_LINE_1, CITY, STATE, COUNTRY, POSTAL_CODE, NOTES, IS_ACTIVE, CREATION_DATE, CREATED_BY, STATUS, STSTUS_MSG)
values ('CCCCCC', 'CCCCCC', 'CCCCCC', 'CCCCCC', 'CCCCCC', 'CCCCCC', 'CCCCCC', 'CCCCCC', '09.07.14 23:18:15,161000', 'CCCCCC', 'CCCCCC', 'CCCCCC');

insert into t1 (CUSTOMER_NAME, ADDRESS_LINE_1, CITY, STATE, COUNTRY, POSTAL_CODE, NOTES, IS_ACTIVE, CREATION_DATE, CREATED_BY, STATUS, STSTUS_MSG)
values ('DDDDDD', 'DDDDDD', 'DDDDDD', 'DDDDDD', 'DDDDDD', 'DDDDDD', 'DDDDDD', 'DDDDDD', '10.06.14 23:18:30,113000', 'DDDDDD', 'DDDDDD', 'DDDDDD');

Open in new window


Then, I ran following block with NO PROBLEMS at all?!?
declare

  l_cur      number;
  l_rows     number;
  l_cols     number;
  l_desc_tab dbms_sql.desc_tab2;

  l_date date;
  l_char varchar2(1000);

begin

  l_cur := dbms_sql.open_cursor();
  dbms_sql.parse(l_cur, 'select CUSTOMER_NAME, CREATION_DATE from t1 order by CUSTOMER_NAME', dbms_sql.native);

  dbms_sql.define_column(l_cur, 1, l_char, 100);
  dbms_sql.define_column(l_cur, 2, l_date);

  l_rows := dbms_sql.execute(l_cur);
  dbms_sql.describe_columns2(l_cur, l_cols, l_desc_tab);

  while dbms_sql.fetch_rows(l_cur) > 0 loop
  
    for i in 1 .. l_cols loop
    
      dbms_output.put_line('col type: ' || l_desc_tab(i).col_type);
    
      if l_desc_tab(i).col_type = 1 then
        dbms_sql.column_value(l_cur, i, l_char);
        dbms_output.put_line(l_char);
      else
        dbms_sql.column_value(l_cur, i, l_date);
        dbms_output.put_line(l_date);
        l_char := to_char(l_date, 'DD-MON-RR HH:MIPM');
        dbms_output.put_line(l_char);
      end if;
    
    end loop;
  
    dbms_output.put_line('');
  
  end loop;

  dbms_sql.close_cursor(l_cur);

exception
  when others then
    dbms_output.put_line(dbms_utility.format_error_stack());
    dbms_sql.close_cursor(l_cur);
end;

Open in new window

0
 
bcarlisAuthor Commented:
Hi Alex,
This part of your code made me realize I had a problem

  dbms_sql.define_column(l_cur, 1, l_char, 100);
  dbms_sql.define_column(l_cur, 2, l_date);

Open in new window


This is what I had:
 
 dbms_sql.describe_columns(l_cur,l_cnt,l_dtbl);
 for i in 1..l_cnt loop
    dbms_sql.define_column(l_cur,i,l_val,30);
  end loop;

Open in new window



This is what I needed I believe..
 
   dbms_sql.describe_columns(l_cur,l_cnt,l_dtbl);
  for i in 1..l_cnt loop
    if ( l_dtbl(i).col_type in ( 12, 178, 179, 180, 181, 231 ) ) then
      dbms_sql.define_column(l_cur,i,ld_date);
    else
      dbms_sql.define_column(l_cur,i,l_val,30);
    end if;
  end loop;

Open in new window


I will see tomorrow..
Thank you! Bill
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
This is what I needed I believe..
Yes, yes yes :-)
If you define all columns of your query as VARCHAR2, a call to "COLUMN_VALUE" with a date type parameter will fail (as mentioned in the official docs, see link above), resulting in an ORA-06562 error.
0
 
bcarlisAuthor Commented:
Yes, that did it.. You da' man!
Define Columns
  ls_errmsg := 'Dbms_sql Describing Columns.';
  dbms_sql.describe_columns(l_cur,l_cnt,l_dtbl);
  for i in 1..l_cnt loop
    if ( l_dtbl(i).col_type in ( 2 ) ) then
      dbms_sql.define_column(l_cur,i,ln_val_number);
    elsif ( l_dtbl(i).col_type in ( 12, 178, 179, 180, 181, 231 ) ) then
      dbms_sql.define_column(l_cur,i,ld_date);
    else
      dbms_sql.define_column(l_cur,i,ls_val_varchar,30);
    end if;
  end loop;

Open in new window


Get the values in the loop
      if( l_FORMAT_MASK is null ) then
        if ( l_dtbl(i).col_type in ( 2 ) ) then
          dbms_sql.column_value(l_cur,i,ln_val_number);    
          ls_val_varchar := to_char(ln_val_number);   
        elsif ( l_dtbl(i).col_type in ( 12, 178, 179, 180, 181, 231 ) ) then
          dbms_sql.column_value(l_cur,i,ld_date);
          ls_val_varchar := to_char(ld_date, 'DD-MON-RR'); -- default (want figure how to switch it to the NLS_DATE as default)
        else
          dbms_sql.column_value(l_cur,i, ls_val_varchar);  
        end if;
      else
        if(l_COLUMN_TYPE = 'NUMBER') then
          dbms_sql.column_value(l_cur,i, ln_val_number);  
          ls_val_varchar := to_char(ln_val_number,l_FORMAT_MASK);
        elsif ( l_dtbl(i).col_type in ( 12, 178, 179, 180, 181, 231 ) ) then
          dbms_sql.column_value(l_cur,i,ld_date);
          ls_val_varchar := to_char(ld_date, l_FORMAT_MASK);
        else
          dbms_sql.column_value(l_cur,i, ls_val_varchar);
        end if;
      end if;

Open in new window

0
 
bcarlisAuthor Commented:
thanks a lot Alex for the help!
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
This is how EE should work ;-)
You're welcome! Rock on :-))

Cheers,
Alex
0
 
bcarlisAuthor Commented:
Cheers!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now