?
Solved

ORA-00932: inconsistent datatypes

Posted on 2004-10-14
6
Medium Priority
?
5,913 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 375 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

719 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