CODEWITHSUNDEEP
oracle-databaseplsqlora-00932ora-06512
Rachel
Rachel

Reputation: 103397

Oracle: Inconsistent Datatype Issue

I am getting inconsistent datatype error message and I am not sure why. I need some guidance to figure this out.

I am creating two types as:

My universe table have following columns with column type:

Column Name                          Data Type

PON                                  VARCHAR2(25 BYTE)
RPON                             VARCHAR2(25 BYTE)
SUPPLIER_NAME                    VARCHAR2(255 BYTE)
SUB_SUPPLIER_NAME                    VARCHAR2(255 BYTE)
SOURCE_NO                            VARCHAR2(40 BYTE)
CKR                                  VARCHAR2(200 BYTE)
LEC_ID                           VARCHAR2(200 BYTE)
ICSC                             VARCHAR2(10 BYTE)
ACTL_ST                          VARCHAR2(10 BYTE)
ADW_ST                           VARCHAR2(10 BYTE)
PROJ_ID                          VARCHAR2(100 BYTE)
MOVE_TO_INV_DT                        DATE
IE_DT                                 DATE
DDD_DT                                DATE
EFF_BILL_DT                               DATE
ACTION                           VARCHAR2(10 BYTE)
SERVICE                          VARCHAR2(10 BYTE)
AFP                                  VARCHAR2(10 BYTE)
ACNA                             VARCHAR2(10 BYTE)
SERVICE_NAME                     VARCHAR2(255 BYTE)
UPLOAD_DT                                 DATE
PROGRAM                          VARCHAR2(50 BYTE)
INITIATIVE_ID                        NUMBER
ACOST                                NUMBER
ACOST_IND                            VARCHAR2(25 BYTE)
MAPFILE                          VARCHAR2(100 BYTE)

Row Type

   create or replace
TYPE test_COMP_REPORT_ROW_TYPE AS OBJECT (
  PON   VARCHAR2(25 BYTE),
  RPON  VARCHAR2(25 BYTE),
  VENDOR VARCHAR2(255 BYTE),
  SUB_SUPPLIER VARCHAR2(255 BYTE),
  SOURCE_NO VARCHAR2(40 BYTE),
  ATT_CKT_ID VARCHAR2(200 BYTE),
  LEC_ID VARCHAR2(200 BYTE),
  ICSC VARCHAR2(10 BYTE),
  STATE VARCHAR2(10 BYTE),
  PROJECT_ID VARCHAR2(100 BYTE),
  ACTION VARCHAR2(10 BYTE),
  SERVICE_SPEED VARCHAR2(10 BYTE),
  SERVICE_NAME VARCHAR(255 BYTE),
  INEFFECT_DATE DATE,
  EVENT_DATE DATE, 
  DUE_DATE DATE, 
  ACOST NUMBER
  )

Tab Type

create or replace type test_COMP_REPORT_TAB_TYPE 
           AS TABLE OF test_COMP_REPORT_ROW_TYPE

Here is the Function which is using this type:

create or replace
FUNCTION test_comp_report_func
  (
    start_dt_h IN VARCHAR2 DEFAULT NULL,
    end_dt_h   IN VARCHAR2 DEFAULT NULL,
    year_h     IN VARCHAR2 DEFAULT NULL )
  RETURN test_comp_report_tab_type pipelined
  IS
  e_sql LONG;
  program_v VARCHAR2(10);

v_row test_comp_report_row_type := test_comp_report_row_type(NULL,
                                                             NULL,
                                                             NULL,
                                                             NULL,
                                                             NULL,
                                                             NULL,
                                                             NULL,
                                                             NULL,
                                                             NULL,
                                                             NULL,
                                                             NULL,
                                                             NULL,
                                                             NULL,
                                                             NULL,
                                                             NULL,
                                                             NULL,
                                                             NULL
                                                             );

  TYPE rectyp IS REF CURSOR;
    rrc_rectyp rectyp;
TYPE recordvar IS RECORD
( 
  PON   VARCHAR2(25 BYTE),
  RPON  VARCHAR2(25 BYTE),
  VENDOR VARCHAR2(255 BYTE),
  SUB_SUPPLIER VARCHAR2(255 BYTE),
  SOURCE_NO VARCHAR2(40 BYTE),
  ATT_CKT_ID VARCHAR2(200 BYTE),
  LEC_ID VARCHAR2(200 BYTE),
  ICSC VARCHAR2(10 BYTE),
  STATE VARCHAR2(10 BYTE),
  PROJECT_ID VARCHAR2(100 BYTE),
  ACTION VARCHAR2(10 BYTE),
  SERVICE_SPEED VARCHAR2(10 BYTE),
  SERVICE_NAME VARCHAR(255 BYTE),
  INEFFECT_DATE DATE,
  EVENT_DATE DATE, 
  DUE_DATE DATE, 
  ACOST NUMBER
);
  res_rec recordvar;

BEGIN  

    e_sql := e_sql || 'SELECT 
                      PON,
                      RPON,
                      SUPPLIER_NAME VENDOR, 
                      SUB_SUPPLIER_NAME SUB_SUPPLIER, 
                      SOURCE_NO,
                      CKR,
                      LEC_ID,
                      ICSC, 
                      ACTL_ST,
                      ADW_ST STATE, 
                      PROJ_ID,
                      ACTION,
                      SERVICE SPEED, 
                      AFP,
                      ACNA,
                      SERVICE_NAME,
                      IE_DT, 
                      MOVE_TO_INV_DT EVENTDAT,
                      DDD_DT DUEDATE, 
                      EFF_BILL_DT,
                      ACOST
                    FROM UNIVERSE
                    WHERE to_date(IE_DT) between to_date(nvl(''01/01/2000'', ''01/01/2000''), ''MM/DD/YYYY'') 
                      and to_date(nvl(''12/31/2009'', to_char(trunc(add_months(sysdate, 12),''year'')-1,''MM/DD/YYYY'')), ''MM/DD/YYYY'')
                    AND PROGRAM = ''T45sONNET''
                    AND nvl(trim(ACOST_IND), ''NULL'') not in (''INVALID-2005'')
                    ORDER BY ACTION';

dbms_output.put_line(e_sql);
OPEN rrc_rectyp FOR e_sql;
  LOOP
      FETCH rrc_rectyp INTO res_rec;
      EXIT WHEN rrc_rectyp%NOTFOUND;
      v_row.PON            := res_rec.PON;
      v_row.RPON           := res_rec.RPON;
      v_row.VENDOR         := res_rec.VENDOR;
      v_row.SUB_SUPPLIER   := res_rec.SUB_SUPPLIER;
      v_row.SOURCE_NO      := res_rec.SOURCE_NO;
      v_row.ATT_CKT_ID     := res_rec.ATT_CKT_ID;
      v_row.LEC_ID         := res_rec.LEC_ID;
      v_row.ICSC           := res_rec.ICSC;
      v_row.STATE          := res_rec.STATE;
      v_row.PROJECT_ID     := res_rec.PROJECT_ID;
      v_row.ACTION         := res_rec.ACTION;
      v_row.SERVICE_SPEED  := res_rec.SERVICE_SPEED;
      v_row.SERVICE_NAME   := res_rec.SERVICE_NAME;
      v_row.INEFFECT_DATE  := res_rec.INEFFECT_DATE;
      v_row.EVENT_DATE     := res_rec.EVENT_DATE;
      v_row.DUE_DATE       := res_rec.DUE_DATE;
      v_row.ACOST          := res_rec.ACOST;
      pipe ROW(v_row);
  END LOOP;
  return;

end test_comp_report_func;

I have tried to debug issue but still am not able to find my way out and would appreciate if SO Community can guide.

Upvotes: 1

Views: 4272

Answers (2)

Luke Woodward
Luke Woodward

Reputation: 64949

You're getting the error because the SQL query in e_sql is returning four more values than are in res_rec. The cursor returns 21 columns of data but your recordvar record type only contains 17 fields.

It looks to me like the columns ACTL_ST, AFP, ACNA and EFF_BILL_DT don't map to anything in res_rec, and if you remove these from the query you should find that your function no longer reports the inconsistent datatypes error.

I would probably have implemented the function something like the following:

CREATE OR REPLACE FUNCTION test_comp_report_func_2 (
  start_dt_h IN VARCHAR2 DEFAULT NULL,
  end_dt_h   IN VARCHAR2 DEFAULT NULL,
  year_h     IN VARCHAR2 DEFAULT NULL
) RETURN test_comp_report_tab_type PIPELINED
IS
  CURSOR cur_res_rec IS
    SELECT PON,
           RPON,
           SUPPLIER_NAME VENDOR, 
           SUB_SUPPLIER_NAME SUB_SUPPLIER, 
           SOURCE_NO,
           CKR ATT_CKT_ID,
           LEC_ID,
           ICSC, 
           ACTL_ST,
           ADW_ST STATE, 
           PROJ_ID AS PROJECT_ID,
           ACTION,
           SERVICE SERVICE_SPEED, 
           AFP,
           ACNA,
           SERVICE_NAME,
           IE_DT INEFFECT_DATE, 
           MOVE_TO_INV_DT EVENT_DATE,
           DDD_DT DUE_DATE, 
           EFF_BILL_DT,
           ACOST
      FROM UNIVERSE
     WHERE TO_DATE(IE_DT) BETWEEN TO_DATE(NVL('01/01/2000', '01/01/2000'), 'MM/DD/YYYY') 
                              AND TO_DATE(NVL('12/31/2009', TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, 12),'year') - 1,'MM/DD/YYYY')), 'MM/DD/YYYY')
       AND PROGRAM = 'T45sONNET'
       AND NVL(TRIM(ACOST_IND), 'NULL') NOT IN ('INVALID-2005')
     ORDER BY ACTION;

    v_row test_comp_report_row_type := test_comp_report_row_type(NULL, NULL, NULL, NULL,
        NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

BEGIN  
  FOR res_rec IN cur_res_rec
  LOOP
      v_row.PON            := res_rec.PON;
      v_row.RPON           := res_rec.RPON;
      v_row.VENDOR         := res_rec.VENDOR;
      v_row.SUB_SUPPLIER   := res_rec.SUB_SUPPLIER;
      v_row.SOURCE_NO      := res_rec.SOURCE_NO;
      v_row.ATT_CKT_ID     := res_rec.ATT_CKT_ID;
      v_row.LEC_ID         := res_rec.LEC_ID;
      v_row.ICSC           := res_rec.ICSC;
      v_row.STATE          := res_rec.STATE;
      v_row.PROJECT_ID     := res_rec.PROJECT_ID;
      v_row.ACTION         := res_rec.ACTION;
      v_row.SERVICE_SPEED  := res_rec.SERVICE_SPEED;
      v_row.SERVICE_NAME   := res_rec.SERVICE_NAME;
      v_row.INEFFECT_DATE  := res_rec.INEFFECT_DATE;
      v_row.EVENT_DATE     := res_rec.EVENT_DATE;
      v_row.DUE_DATE       := res_rec.DUE_DATE;
      v_row.ACOST          := res_rec.ACOST;
      PIPE ROW(v_row);
  END LOOP;

  RETURN;

END test_comp_report_func_2;
/

Firstly, I can't honestly see the reason you're using dynamic SQL. The function above uses a 'static' SQL query, and it has the advantage that Oracle will check that this query is valid when it compiles the function. If there's an error with the query, the function won't compile. On the other hand, if you have an error with a dynamic SQL query, you won't find out that there's a problem until you run your function.

Dynamic SQL is useful if you want to change the structure of a query, e.g. to run it on different tables or change the columns used in a WHERE clause. However, most of the time you don't need to do this. Dynamic SQL is one of those things you really shouldn't use if you don't need to use it.

Also, by using FOR some_record IN some_cursor, I don't have to fiddle around with opening and closing a cursor, nor do I need to check whether there's any more data left and exit the loop if so. It also cuts out having to declare a variable for the row record (res_rec) or getting the type of this variable wrong. That is all done automatically for me.

Upvotes: 1

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

I first wrote an answer trying to reproduce your error but you've changed your question quite a bit so I'm starting again from scratch.

First a few remarks:

  • By your own account you're quite new to PL/SQL yet you're using pretty advanced features: dynamic SQL, pipelined functions, SQL Objects. Let's try to begin with something simpler at first (I'll show you how you can work with static SQL, this would be sufficient 99.9% of the time).
  • When you hit a problem you need to decompose your code to see what is working and what is not. That usually means simplifying your code until it is so simple it starts to work, then bring back the complex elements of your code one by one until you hit the problem again.
  • When you provide a test case, try to make it as simple as possible :) It'll be easier for people to help you, but more importantly in most of the cases, building the test case will help you find the solution yourself since this will force you to decompose your complex code (see previous point). My rule of thumb (FWIW) is that code that is displayed with a scroll bar (either horizontal or vertical) in SO is too big for a test case and needs to be trimmed if possible.

I ran your code and got the ORA-00932 on the fetch line. When I replace the SQL with static SQL the error is more explicit:

SQL> CREATE OR REPLACE FUNCTION test_comp_report_func
  2     RETURN test_comp_report_tab_type
  3     PIPELINED IS
  4     TYPE recordvar IS RECORD(
  5        PON           VARCHAR2(25 BYTE),
           (...snip...)
 21        ACOST         NUMBER);
 22     res_rec recordvar;
 23     v_row   test_COMP_REPORT_ROW_TYPE;
 24     CURSOR rrc_rectyp IS
 25        SELECT PON,
                  (...snip...)
 45               ACOST
 46          FROM UNIVERSE;
 48  BEGIN
 49     OPEN rrc_rectyp;
 50     LOOP
 51        FETCH rrc_rectyp
 52           INTO res_rec;
 54        EXIT WHEN rrc_rectyp%NOTFOUND;
 55        /*...*/
 56        PIPE ROW(v_row);
 57     END LOOP;
 58     RETURN;
 59  END test_comp_report_func;
 60  /

Warning: Function created with compilation errors.
LINE/COL ERROR
-------- -----------------------------------------------------------------
51/7     PL/SQL: SQL Statement ignored
52/15    PLS-00386: type mismatch found at 'RES_REC' between FETCH cursor
         and INTO variables

Here the problem comes from the fact that your select statement doesn't have the same number of columns as the number of fields in your record. You can use %rowcount to prevent this:

CREATE OR REPLACE FUNCTION test_comp_report_func
   RETURN test_comp_report_tab_type
   PIPELINED IS
   v_row   test_COMP_REPORT_ROW_TYPE;
   CURSOR rrc_rectyp IS
      SELECT PON, RPON, SUPPLIER_NAME VENDOR, SUB_SUPPLIER_NAME SUB_SUPPLIER,
             SOURCE_NO, CKR, LEC_ID, ICSC, ACTL_ST, ADW_ST STATE, PROJ_ID,
             ACTION, SERVICE SPEED, AFP, ACNA, SERVICE_NAME, IE_DT,
             MOVE_TO_INV_DT EVENTDAT, DDD_DT DUEDATE, EFF_BILL_DT, ACOST
        FROM UNIVERSE;
   res_rec rrc_rectyp%ROWTYPE;
BEGIN
   OPEN rrc_rectyp;
   LOOP
      FETCH rrc_rectyp
         INTO res_rec;
      EXIT WHEN rrc_rectyp%NOTFOUND;
      v_row.pon := res_rec.pon;
      /*...*/
      PIPE ROW(v_row);
   END LOOP;
   RETURN;
END test_comp_report_func;

You can even fetch the SQL object directly (with an implicit cursor):

CREATE OR REPLACE FUNCTION test_comp_report_func
   RETURN test_comp_report_tab_type
   PIPELINED IS
BEGIN
   FOR res_rec IN (SELECT test_comp_report_row_type(PON, RPON, SUPPLIER_NAME,
                                                     SUB_SUPPLIER_NAME,SOURCE_NO,
                                                     CKR, LEC_ID, ICSC, ACTL_ST,
                                                     PROJ_ID, ACTION, SERVICE,
                                                     SERVICE_NAME, IE_DT, DDD_DT,
                                                     EFF_BILL_DT, ACOST)my_object
                     FROM UNIVERSE) LOOP
      PIPE ROW(res_rec.my_object);
   END LOOP;
   RETURN;
END test_comp_report_func;

Upvotes: 4

Related Questions