2

I'm fairly new to the procedural language side of PL/SQL, so forgive me if this is basic.

I'm trying to put values in a table I've previously created outside of this code block. This code is currently getting an error on the sixth line. Any idea why?

BEGIN
  FOR c IN (SELECT name FROM clients) LOOP
    FOR d IN (SELECT customer_id, alt_name FROM customers) LOOP 
      IF d.alt_name LIKE '%' || c.name || '%'
      THEN
            INSERT INTO previously_made_table(name, customer_id, alt_name, customer_code) VALUES(c.name, d.customer_id, d.alt_name, '101');
            COMMIT;
       END IF;
    END LOOP;
END LOOP;
END;
4
  • 4
    Please post the error. Thanks. Commented Aug 8, 2013 at 2:56
  • The error is PL/SQL: ORA-00904: "CUSTOMER_CODE": invalid identifier Commented Aug 8, 2013 at 12:28
  • I should assure you that the table definitely has this field on it. Commented Aug 8, 2013 at 12:35
  • 1
    Can you try to run just the INSERT statement from SQLPlus (or you favourite Oracle tool)? Maybe you got the column name wrong... Commented Aug 8, 2013 at 19:32

2 Answers 2

4

You don't need pl\sql here

insert into previously_made_table
  (name, customer_id, alt_name, customer_code) 
 select c.name, d.customer_id, d.alt_name, '101'
 from   clients c , customers d
 where  d.alt_name LIKE '%' || c.name || '%'
Sign up to request clarification or add additional context in comments.

3 Comments

True, but I'd prefer to know how to do it in the PL/SQL format I delivered the question in.
you probably don't have a column named customer_code in previously_made_table.
It's there, as mentioned in my comment above.
-1

This worked.

INSERT INTO previously_made_table VALUES(c.name, d.customer_id, d.alt_name, '101');

I cut the column names. Hope that helps.

1 Comment

Please, always use a column list in your INSERT INTO statements.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.