ORA-00932: inconsistent datatypes

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?
joehodgeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
seazodiacConnect With a Mentor Commented:
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
 
konektorCommented:
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
 
joehodgeAuthor Commented:
Hi,

If the type is varchar2(32767) then wont this be ok for all values being fetched into it?
0
Get expert help—faster!

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

 
joehodgeAuthor Commented:
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
 
joehodgeAuthor Commented:
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
 
joehodgeAuthor Commented:
the type doesn't seem to be the issue though....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.