Stubborn Error - Procedure to Insert and Update records at the same time 1

[AlStl]

Following are my objects. Problem desc is at the bottom of my post. I am close, but need little help at the end.

CREATE SEQUENCE P_TEXTBODYID
START WITH 360000
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

CREATE TABLE TEXTBODY_TMP
(
TEXTBODY_ID INTEGER NOT NULL,
A_ID NUMBER(10) NOT NULL,
TEXTBODY CLOB,
CREATE_DATE DATE
);

Insert into TEXTBODY_TMP (TEXTBODY_ID, A_ID, TEXTBODY, CREATE_DATE) Values (401, 62, 'Life is great STANDARD 1459, SECOND EDITION', TO_DATE('12/17/2004 10:46:22', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEXTBODY_TMP (TEXTBODY_ID, A_ID, TEXTBODY, CREATE_DATE) Values (403, 62, 'Event Horizon.', TO_DATE('10/17/2003 11:46:22', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEXTBODY_TMP (TEXTBODY_ID, A_ID, TEXTBODY, CREATE_DATE) Values (404, 62, 'Saturn Rings.', TO_DATE('02/17/2005 08:46:22', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEXTBODY_TMP (TEXTBODY_ID, A_ID, TEXTBODY, CREATE_DATE) Values (405, 62, 'NONE.', TO_DATE('06/17/2001 07:46:22', 'MM/DD/YYYY HH24:MI:SS'));

CREATE TABLE DETAIL_TMP
(
A_ID INTEGER NOT NULL,
SUB_SYSTEM VARCHAR2(72 BYTE),
CREATE_DATE DATE,
FLAG CHAR(1 BYTE) DEFAULT 'N' NOT NULL
);

Insert into DETAIL_TMP (A_ID, SUB_SYSTEM, CREATE_DATE, FLAG) Values(21909,'2000 Series', TO_DATE('12/07/2005 11:00:06', 'MM/DD/YYYY HH24:MI:SS'), 'N');
Insert into DETAIL_TMP (A_ID, SUB_SYSTEM, CREATE_DATE, FLAG) Values(19444,'800 Series', TO_DATE('11/07/2006 12:00:06', 'MM/DD/YYYY HH24:MI:SS'), 'N');
Insert into DETAIL_TMP (A_ID, SUB_SYSTEM, CREATE_DATE, FLAG) Values(18765,'1000 Series', TO_DATE('04/07/2007 10:00:06', 'MM/DD/YYYY HH24:MI:SS'), 'N');
Insert into DETAIL_TMP (A_ID, SUB_SYSTEM, CREATE_DATE, FLAG) Values(55555,'7700 Series', TO_DATE('09/07/2008 08:00:06', 'MM/DD/YYYY HH24:MI:SS'), 'Y');

CREATE TABLE PC_TEXT
(
A_ID INTEGER NOT NULL,
SUB_SYSTEM INTEGER,
COMMENTS INTEGER
);

Insert into PC_TEXT (A_ID, SUB_SYSTEM, COMMENTS) Values(21909,NULL, 100);
Insert into PC_TEXT (A_ID, SUB_SYSTEM, COMMENTS) Values(19444,NULL, 200);
Insert into PC_TEXT (A_ID, SUB_SYSTEM, COMMENTS) Values(18765,NULL, 900);
Insert into PC_TEXT (A_ID, SUB_SYSTEM, COMMENTS) Values(19000,NULL, 500);

---- This procesure works with no issues.

CREATE OR REPLACE PROCEDURE MIGRATE_SUB_SYSTEM IS
BEGIN
FOR i IN (SELECT P_TEXTBODYID.nextval "TEXTBODY_ID" , A_ID,SUB_SYSTEM,CREATE_DATE FROM DETAIL_TMP)
LOOP
INSERT INTO TEXTBODY_TMP
(TEXTBODY_ID, A_ID, TEXTBODY, CREATE_DATE )
VALUES
(i.TEXTBODY_ID, i.A_ID, i.SUB_SYSTEM,i.CREATE_DATE);
END LOOP;
COMMIT;
END MIGRATE_SUB_SYSTEM;
/

Then this came along:

update PC_TEXT.SUBSYSTEM and set it to P_TEXTBODYID.nextval "TEXTBODY_ID" where PC_TEST.A_ID=DETAIL_TMP.A_ID. Insert and update need to work in concert.

So, I added an update statment to above procedure and it does not work. Gives me error PL/SQL: ORA 00942 table or view does not exists. Can some help?


CREATE OR REPLACE PROCEDURE MIGRATE_SUB_SYSTEM IS
BEGIN
FOR i IN (SELECT P_TEXTBODYID.nextval "TEXTBODY_ID" , A_ID,SUB_SYSTEM,CREATE_DATE FROM DETAIL_TMP)
LOOP
INSERT INTO TEXTBODY_TMP
(TEXTBODY_ID, A_ID, TEXTBODY, CREATE_DATE )
VALUES
(i.TEXTBODY_ID, i.A_ID, i.SUB_SYSTEM,i.CREATE_DATE);

UPDATE PC_TEXT.SUB_SYSTEM SET PC_TEXT.SUB_SYSTEM = i.TEXTBODY_ID WHERE PC_TEXT.A_ID=i.A_ID;

END LOOP;
COMMIT;
END MIGRATE_SUB_SYSTEM;
/

 

[LKBrwnDBA]

Try something like this:

CREATE OR REPLACE PROCEDURE migrate_sub_system
IS
BEGIN
  FOR i
    IN (SELECT p_textbodyid.NEXTVAL "TEXTBODY_ID"
             , a_id, sub_system, create_date
          FROM detail_tmp)
  LOOP
    INSERT INTO textbody_tmp (
                              textbody_id
                            , a_id
                            , textbody
                            , create_date
                             )
         VALUES (
                 i.textbody_id
               , i.a_id
               , i.sub_system
               , i.create_date
                );

    UPDATE pc_text
       SET sub_system   = i.textbody_id
     WHERE a_id = i.a_id;
  END LOOP;

  COMMIT;
END migrate_sub_system;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[AlStl]

LKBrwnDBA, thanks! What a silly mistake I was making.

 

[LKBrwnDBA]

It's Friday...Thinking on Happy hour


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb