CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
Yvonne Nnaji
Yvonne Nnaji

Reputation: 3

Using 'Insert Into' with an 'update' keyword in an Explicit Cursor

Running the script below, produces the error:

*"Error report - ORA-06550: line 19, column 15: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 19, column 1: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. Action: "

DECLARE
h_d_regionID departments.region%TYPE;
h_l_countryID locations.country_id%TYPE;

CURSOR DCursor IS
SELECT  d.region --,l.country_id
FROM locations l 
inner join departments d
on d.location_id = l.location_id
FOR UPDATE;
BEGIN
OPEN DCursor;
LOOP
FETCH DCursor INTO h_l_countryID;
EXIT WHEN DCursor%NOTFOUND;
/* Delete the current tuple: */
--DELETE FROM departments.region WHERE CURRENT OF DCursor;
/* Insert the reverse tuple: */
INSERT INTO d.region VALUES(h_d_regionID);

END LOOP;
/* Free cursor used by the query. */
CLOSE DCursor;
END;

What am I missing here?

Upvotes: 0

Views: 252

Answers (1)

Popeye
Popeye

Reputation: 35920

You have used the table name in the INSERT INTO statement with an alias (D).

The Following code should work (See inline comment for solution)

DECLARE
    H_D_REGIONID    DEPARTMENTS.REGION%TYPE;
    H_L_COUNTRYID   LOCATIONS.COUNTRY_ID%TYPE;
    CURSOR DCURSOR IS
    SELECT D.REGION --,l.country_id
      FROM LOCATIONS     L
     INNER JOIN DEPARTMENTS   D
    ON D.LOCATION_ID = L.LOCATION_ID
    FOR UPDATE;

BEGIN
    OPEN DCURSOR;
    LOOP
        FETCH DCURSOR INTO H_L_COUNTRYID;
        EXIT WHEN DCURSOR%NOTFOUND;
/* Delete the current tuple: */
--DELETE FROM departments.region WHERE CURRENT OF DCursor;
/* Insert the reverse tuple: */
        --INSERT INTO D.REGION VALUES ( H_D_REGIONID ); --issue is with this line
        INSERT INTO REGION VALUES ( H_D_REGIONID ); -- removed the D. before table name
    END LOOP;
/* Free cursor used by the query. */

    CLOSE DCURSOR;
END;

Also, Please note that you are using the H_D_REGIONID in the INSERT statement but it is not initialized or assigned any value.

Upvotes: 1

Related Questions