Solved

ORA-00932: inconsistent datatypes

Posted on 2004-10-14
6
5,904 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 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

617 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