INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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;
/

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


Try something like this:

CODE

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;
/ 
3eyes

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

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

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

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


It's Friday...Thinking on Happy hourcheers

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close