Is it possible to insert a record with rowtype X into a table with rowtype Y?

Question

I'm stuck with something really weird. Yesterday I was able to produce a procedure like this:

create or replace PROCEDURE proc
IS
  CURSOR CUR
  IS
    SELECT * FROM PROVA
    WHERE STATUS = 'X';

BEGIN
  FOR H IN CUR
  LOOP
    BEGIN
      INSERT INTO PROVA2 VALUES H;
      DELETE FROM PROVA WHERE ID = H.ID;
      COMMIT;
    END;
  END LOOP;
END;

Where PROVA is defined as:

CREATE TABLE PROVA
   (    "ELEMENTO" VARCHAR2(20 BYTE), 
    "DATO" VARCHAR2(20 BYTE), 
    "NUMERO_TENTATIVI" NUMBER(8,0), 
    "STATUS" VARCHAR2(1000 BYTE), 
    "ID" NUMBER(*,0)
   )

and PROVA2 is defined as:

CREATE TABLE PROVA
   (    "ELEMENTO" VARCHAR2(20 BYTE), 
    "DATO" VARCHAR2(20 BYTE), 
    "NUMERO_TENTATIVI" NUMBER(8,0), 
    "STATUS" VARCHAR2(1000 BYTE), 
    "ID" NUMBER(*,0)
    "DATE_TIME" TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP
   )

Unfortunately, my mistake, I didn't save and commit the correct procedure so now I'm stuck with the old one (where, when I do the insert, I have to specify every column...

INSERT INTO PROVA2(bla,bla,bla...) 
      VALUES (bla,bla,bla...);

I'd like the INSERT part to abstract from the table structure and I searched everywhere but I didn't find any evidence on the error that the first PROCEDURE I posted gives me, which is:

 ORA-00947: not enough values

So my question is: is it possible to insert a complete record from a table X into a table Y, which has the same columns except DATE_TIME that has a default value (and I don't want to modify...).

I hope that this isn't messy and I searched everywhere on the internet about this with no luck.

Thanks.

EDIT: To summarize: given a table A that has foo,bar,foobar as columns, where foobar has a default value: can I insert a record 'x' from a table B that has foo,bar as columns by using:

insert into A values x

Thanks

Answer 1

You can use a view to do that:

create table tmp(
id number
);
create table tmp1(
id number,
dt date default sysdate
);
create view tmp1_vw as select id from tmp1;
insert into tmp1_vw values (1);

result:

table TMP created.
table TMP1 created.
view TMP1_VW created.
1 rows inserted.

Your procedure works as well:

declare
  CURSOR CUR
  IS
    SELECT * FROM tmp;
BEGIN
  FOR H IN CUR
  LOOP
    BEGIN
      INSERT INTO tmp1_vw VALUES H;
    END;
  END LOOP;
END;

And don't do commit after every statement.

---

Follow up after 1st comment.

You have 2 tables with slightly different structure.

To solve the problem, you make a view on a second table to look exactly like first one. Then your "record types", that are derived from first table, will work on a view as well.