CODEWITHSUNDEEP
sqloracle-databaseplsql
Kuvalya
Kuvalya

Reputation: 1094

INSERT ALL INTO throws ORA-01722: invalid number

I want to copy a view into a table. Below procedure throws ORA-01722: invalid number

BEGIN
   INSERT ALL
     INTO DB_LOCATIONS
      SELECT * FROM DB_LOCVIEW ; 
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line  ('SQLCODE: ' || SQLCODE||'  -SQLERRM: '||SQLERRM);
      END;
END;

On the other hand, If I try it with for loop, everything is fine:

DECLARE
    currow integer := 1;
BEGIN
    DBMS_OUTPUT.put_line ('starting.....');
    for xxinserteddata in (select * from DB_LOCVIEW )
    LOOP  
        BEGIN           
            DBMS_OUTPUT.put_line('currow: ' || currow);
            INSERT INTO DB_LOCATIONS ( 
                DB_DATE,
                LOCATIONID,
                MX_ET,
                MX_MT,
                BAND,
                SITE) 
            VALUES (  
                xxinserteddata.DB_DATE,
                xxinserteddata.LOCATIONID,
                xxinserteddata.MX_ET,
                xxinserteddata.MX_MT,
                xxinserteddata.BAND,
                xxinserteddata.SITE );
            currow := currow + 1;
        END;
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.put_line ('end.');
EXCEPTION
   WHEN OTHERS
   THEN
      BEGIN
         DBMS_OUTPUT.put_line  ('-currow: ' || currow || '  -SQLCODE: ' || SQLCODE||'  -SQLERRM: '||SQLERRM);
      END;
END;

Both view and table has only 5 same columns. Any idea why insert all throws exception?

EDIT: Adding create procedures:

View:

CREATE OR REPLACE FORCE VIEW DB_LOCVIEW ("LOCATIONID", "SITE", "MX_ET", "MX_MT", "DB_DATE", "BAND") AS 
      SELECT 
        l.LOCATIONID,
        l.SITE,
        l.MX_ET,
        l.MX_MT,
        l.CHANGE_DATE AS db_date,
        d.BAND
    FROM ALL_LOCATIONS l
    INNER JOIN LOC_DETATILS d ON (l.location = d.location)
    WHERE l.status = 'ACTIVE' ;

Table:

CREATE TABLE DB_LOCATIONS 
   (    "LOCATIONID" NUMBER, 
    "DB_DATE" DATE, 
    "MX_ET" NUMBER(10,2), 
    "MX_MT" NUMBER(10,2), 
    "SITE" VARCHAR2(8), 
    "BAND" NUMBER
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DB_DATA" ;

Upvotes: 1

Views: 597

Answers (1)

MT0
MT0

Reputation: 168001

You do not specify the order of the columns in the INSERT ALL statement and if the columns of the tables are in different orders then you will find that using SELECT * and not specifying the columns will attempt to put the column from one table into the wrong column in the other table.

For example:

CREATE TABLE DB_LOCVIEW (DB_DATE, MX_ET, MX_MT, LOCATIONID, BAND, SITE) AS
SELECT SYSDATE, 'ABC', 'DEF', 1, 23, 'GHI' FROM DUAL;

-- Create the table with the columns in a different order
CREATE TABLE DB_LOCATIONS (DB_DATE, LOCATIONID, MX_ET, MX_MT, BAND, SITE) AS
SELECT DB_DATE, LOCATIONID, MX_ET, MX_MT, BAND, SITE FROM DB_LOCVIEW WHERE 1 = 0;

Then running your INSERT ALL query fails with the error:

SQLCODE: -1722  -SQLERRM: ORA-01722: invalid number

This is because the statement is effectively expanded to:

BEGIN
  INSERT ALL
    INTO DB_LOCATIONS (DB_DATE, LOCATIONID, MX_ET, MX_MT, BAND, SITE)
    SELECT DB_DATE, MX_ET, MX_MT, LOCATIONID, BAND, SITE FROM DB_LOCVIEW; 
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line  ('SQLCODE: ' || SQLCODE||'  -SQLERRM: '||SQLERRM);
END;
/

And you can see that the columns do not match. So, even though the columns have identical types, the order of the columns matters.

Instead, you can specify the columns and their order:

BEGIN
  INSERT ALL
    INTO DB_LOCATIONS (DB_DATE, MX_ET, MX_MT, LOCATIONID, BAND, SITE)
    SELECT DB_DATE, MX_ET, MX_MT, LOCATIONID, BAND, SITE FROM DB_LOCVIEW; 
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line  ('SQLCODE: ' || SQLCODE||'  -SQLERRM: '||SQLERRM);
END;
/

Or use INSERT INTO ... SELECT and specify the columns:

BEGIN
   INSERT INTO DB_LOCATIONS (DB_DATE, LOCATIONID, MX_ET, MX_MT, BAND, SITE)
   SELECT DB_DATE, LOCATIONID, MX_ET, MX_MT, BAND, SITE FROM DB_LOCVIEW;
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.put_line  ('SQLCODE: ' || SQLCODE||'  -SQLERRM: '||SQLERRM);
END;
/

Note: You do not need to catch the exception; if there is an error then it will stop the PL/SQL block, implicitly rollback the transaction and print the error trace. Additionally, catching OTHERS is considered bad practice. If you do want to catch the exception then you should catch specific expected exceptions then you know when there is an uncaught exception that something unexpected has occurred and you can debug it; if you catch all exceptions then you lose the ability to identify unexpected behaviours.

Or, without PL/SQL:

INSERT INTO DB_LOCATIONS (DB_DATE, LOCATIONID, MX_ET, MX_MT, BAND, SITE)
SELECT DB_DATE, LOCATIONID, MX_ET, MX_MT, BAND, SITE FROM DB_LOCVIEW;

COMMIT;

db<>fiddle here

Upvotes: 3

Related Questions