Solved

ORA-00932: inconsistent datatypes

Posted on 2004-10-14
6
5,886 Views
Last Modified: 2012-05-05
Hi,

I have a procedure that that should allow users to select any table from the top frame, pass this table name into the bottom frame and display the column headings and data.

It works fine up until

OPEN cv FOR lv_sql;
                        LOOP
                        FETCH cv INTO out_tab(1).lv_col,
                                               out_tab(2).lv_col,
                                               out_tab(3).lv_col,
                                            out_tab(4).lv_col,
                                            out_tab(5).lv_col,
                                            out_tab(6).lv_col,
                                            out_tab(7).lv_col,
                                            out_tab(8).lv_col,
                                            out_tab(9).lv_col,
                                            out_tab(10).lv_col,
                                            out_tab(11).lv_col,
                                               out_tab(12).lv_col,
                                               out_tab(13).lv_col,
                                            out_tab(14).lv_col,
                                            out_tab(15).lv_col,
                                            out_tab(16).lv_col,
                                            out_tab(17).lv_col,
                                            out_tab(18).lv_col,
                                            out_tab(19).lv_col,
                                            out_tab(20).lv_col,
                                            out_tab(21).lv_col,
                                               out_tab(22).lv_col,
                                               out_tab(23).lv_col,
                                            out_tab(24).lv_col,
                                            out_tab(25).lv_col,
                                            out_tab(26).lv_col,
                                            out_tab(27).lv_col,
                                            out_tab(28).lv_col,
                                            out_tab(29).lv_col,
                                            out_tab(30).lv_col;

                                    EXIT WHEN cv%NOTFOUND;
                                           lv_no_rows := lv_no_rows +1;
                                          htp.tabledata(htf.tablerowopen);
                                            FOR x IN 1..lv_no_cols LOOP
                                                    htp.tabledata(htf.bold(NVL(out_tab(x).lv_col,CHR(0160)), cattributes => 'style="font-size:10" align-center'));
                                            END LOOP;
                                            htp.tabledata(htf.tablerowclose);

                              END LOOP;

it returns a:
ORA-00932: inconsistent datatypes: expected - got -

the variables are:
TYPE rec_column IS RECORD (lv_col VARCHAR2(32767));
TYPE tab_column IS TABLE OF rec_column INDEX BY BINARY_INTEGER;


TYPE cv_type IS REF CURSOR;
cv cv_type;

out_tab tab_column;

and the table Im selecting from is:

 LOAD_DATE                DATE,
  REGION                       VARCHAR2(60 BYTE),
  END_MARKET               VARCHAR2(60 BYTE),
  END_MARKET_DESC          VARCHAR2(60 BYTE),
  TRADE_CHANNEL            VARCHAR2(60 BYTE),
  SOLD_TO_PARTY            VARCHAR2(60 BYTE),
  SPOT_ORDER               VARCHAR2(60 BYTE),
  PLANT                        VARCHAR2(60 BYTE),
  POINT_OF_MF              VARCHAR2(60 BYTE),
  BRAND_FAMILY             VARCHAR2(60 BYTE),
  BRAND_VARIANT            VARCHAR2(60 BYTE),
  MATERIAL                 VARCHAR2(60 BYTE),
  ORDER_NUMBER             VARCHAR2(60 BYTE),
  LINE_NUMBER              VARCHAR2(60 BYTE),
  ORIGINAL_REQUEST_DATE    DATE,
  END_MARKET_REQUEST_DATE  DATE,
  GOODS_ISSUE_DATE         DATE,
  SHIPPING_ETA             DATE,
  DAYS_LATE                NUMBER(6),
  QTY_REQUESTED            NUMBER(9),
  QTY_SHIPED               NUMBER(9),
  QTY_OTIF                 NUMBER(9),
  QTY_SHIPPED_ON_TIME      NUMBER(6,2),
  UNIT                     VARCHAR2(2 BYTE),
  REASON_CODE              VARCHAR2(60 BYTE),
  REASON_TEXT              VARCHAR2(4000 BYTE),
  WEEK_NUMBER              NUMBER(5),
  YEAR                     NUMBER(7),
  EXCLUDE                  VARCHAR2(1 BYTE),
  LAST_UPDATED_BY          VARCHAR2(40 BYTE),
  REGION_DESCRIPTION       VARCHAR2(20 BYTE),
  FINISHED_TYPE            VARCHAR2(1 BYTE)


any ideas?
0
Comment
Question by:joehodge
  • 4
6 Comments
 
LVL 9

Expert Comment

by:konektor
ID: 12306651
change variables, which u r fething into   to be the same as selected values
or change cursor and convert each date and number values to characters
0
 

Author Comment

by:joehodge
ID: 12306727
Hi,

If the type is varchar2(32767) then wont this be ok for all values being fetched into it?
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 125 total points
ID: 12307037
No, it won't.

there are two problems here.

For one, it's the data type mismatch.
for two, it's the NUMBER of fields mismatch.

the  rec_column  TYPE has to have the SAME number of columns as the cursor table, and SAME data type for each field.


Period.


To completely work around it ,

you can do this , but this is not what you want most likely:

concatenate all the columns in the Cursor CV...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:joehodge
ID: 12346388
Hi,

Firstly apologies, I've been busy so have not responded to this thread for a couple of days.

I dont agree with the two suggestions (unless I've got the wrong end of the stick). I created a view of the table which selects one column (varchar2) and got the same error message.

Again I'm copying the work of people who have implemented this (and now moved on) before and it should work for any table/view with columns < 30 containing any type of data e.g. date/number/varchar etc.

The conversion/formatting of the datatypes is done in a vbs program after the columns have been selected.
0
 

Author Comment

by:joehodge
ID: 12401385
Hi,

I tried out seazodiacs suggestions and I retract my earlier comment (I dont agree with the two suggestions)

when I add 1 extra column:

OPEN cv FOR lv_sql;
                    LOOP
                    FETCH cv INTO out_tab(1).lv_col,
                                        out_tab(2).lv_col,
                                        out_tab(3).lv_col,
                                     out_tab(4).lv_col,
                                     out_tab(5).lv_col,
                                     out_tab(6).lv_col,
                                     out_tab(7).lv_col,
                                     out_tab(8).lv_col,
                                     out_tab(9).lv_col,
                                     out_tab(10).lv_col,
                                     out_tab(11).lv_col,
                                        out_tab(12).lv_col,
                                        out_tab(13).lv_col,
                                     out_tab(14).lv_col,
                                     out_tab(15).lv_col,
                                     out_tab(16).lv_col,
                                     out_tab(17).lv_col,
                                     out_tab(18).lv_col,
                                     out_tab(19).lv_col,
                                     out_tab(20).lv_col,
                                     out_tab(21).lv_col,
                                        out_tab(22).lv_col,
                                        out_tab(23).lv_col,
                                     out_tab(24).lv_col,
                                     out_tab(25).lv_col,
                                     out_tab(26).lv_col,
                                     out_tab(27).lv_col,
                                     out_tab(28).lv_col,
                                     out_tab(29).lv_col,
                                     out_tab(30).lv_col,
                                     out_tab(31).lv_col;

it works?

lv_sql is the columns from the view/or table and then concatented with null_column null (outputing the sql showed 31 columns). Im not sure why I have to add this one extra column in this incarnation of the code as it has worked in >3 other systems.
0
 

Author Comment

by:joehodge
ID: 12401391
the type doesn't seem to be the issue though....
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now