Using where clause in Insert statement while in CURSOR

Question

I am running this code and am getting this error ORA-00933 SQL command not properly ended

How would you write this code so this error does not exist? member_addresses_to_remove is a CURSOR.

 FOR curr_element IN member_addresses_to_remove 
  LOOP
    INSERT INTO schema.tablename(ID,NAME, PHONE_NUMBER)
    VALUES (curr_element.ID, curr_element.NAME,curr_element.PHONE_NUMBER)
    WHERE NOT EXISTS (SELECT 1
                      FROM SCHEMA.OTHERTABLE OT
                      WHERE OT.ID = curr_element.ID);                        
  END LOOP;
COMMIT;

change the VALUES for SELECT [your columns] from dual .... It should work — Jorge Campos, Feb 01 '18 at 02:30
and maybe you just forgot to remove the WHERE condition in the INSERT statement, — eifla001, Feb 01 '18 at 02:32
check this link : https://stackoverflow.com/questions/25969/insert-into-values-select-from?rq=1 — Ramki, Feb 01 '18 at 03:30
@JorgeCampos I did that and now it is saying ORA-00907 missing right parantheses. I have all closing parantheses. Is this something you are familiar with? Also why does from dual work? What is it doing there? — Troy Reynolds, Feb 01 '18 at 03:34
Well, I think you didn't remove the parenthesis from your columns? I wasn't clear enough, I wanted to say SELECT your columns without the parenthesis .... :) — Jorge Campos, Feb 01 '18 at 12:58

score 2 · Answer 1 · answered Feb 01 '18 at 03:52

2

Your syntax is incorrect because you are combining a values clause with a where clause.

Try this:

 FOR curr_element IN member_addresses_to_remove 
  LOOP
    INSERT INTO schema.tablename(ID,NAME, PHONE_NUMBER)
    SELECT curr_element.ID, curr_element.NAME,curr_element.PHONE_NUMBER
    FROM DUAL
    WHERE NOT EXISTS (SELECT 1
                      FROM SCHEMA.OTHERTABLE OT
                      WHERE OT.ID = curr_element.ID);                        
  END LOOP;
COMMIT;

answered Feb 01 '18 at 03:52

WW.

23,793
13
94
121

1

It's unlikely you need this loop at all. You probably write a single INSERT INTO statement instead of having the cursor at all. But you haven't shown that code and this should fix your immediate problem. – WW. Feb 01 '18 at 03:54
Thank you! This works. Can you explain to me why dual works? How is that grabbing my data from the current element in the cursor? – Troy Reynolds Feb 01 '18 at 03:59
About dual: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries009.htm – WW. Feb 01 '18 at 07:53
You can't put anything after a values clause, so you need a table to select from. Dual has one row. – WW. Feb 01 '18 at 07:55
Hi @WW. as you already commented I would also add to your answer the insert select statement so the OP can choose the right approach :) +1 anyways – Jorge Campos Feb 01 '18 at 13:00

score 0 · Answer 2 · answered Feb 01 '18 at 03:28

0

Insert statement syntax is wrong, pls check below example.

INSERT INTO trg_tbl (col1, col2, col3)
SELECT col1,
       col2,
       col3
FROM src_tbl
WHERE col4 = myvale;

test SQL by executing single SQL statement in SQLPlus then add it procedure

answered Feb 01 '18 at 03:28

Ramki

453
2
7

I am trying to insert data from a CURSOR with the where clause going to look into another table to see if one one the values exists there or not. If it does then I do not want to insert that data. – Troy Reynolds Feb 01 '18 at 03:38
Hi, Ok I got it. but I think you can make it all in single SQL statement without using a cursor. – Ramki Feb 01 '18 at 04:05

Using where clause in Insert statement while in CURSOR

2 Answers2