Go Premium for a chance to win a PS4. Enter to Win

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

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?
0
joehodge
Asked:
joehodge
  • 4
1 Solution
 
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
 
seazodiacCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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