Menu
I am writing a PL/SQL Procedure that performs a select based on input variables and then inserts a row for each result in the select. I am having trouble debugging what is wrong with my query due my newness to PL/SQL. I know this must be easy, but I am stuck here for some reason. Thanks for your help!
CREATE OR REPLACE PROCEDURE setup_name_map(ranking_id IN NUMBER, class_string IN VARCHAR2) IS BEGIN FOR rec IN (SELECT NAME_ID FROM PRODUCT_NAMES WHERE NAME = class_string) LOOP EXECUTE IMMEDIATE 'INSERT INTO NAME_RANKING (NAME_ID, RANKING_ID) VALUES (' || rec.NAME_ID || ', ' || ranking_id || ')'; END LOOP; END; According to the Oracle Developer Compiler... 'NAME_ID' is an invalid identifier. I've tried putting it in quotes but no dice. It also complains that loop index variables 'REC' use is invalid. Any help is much appreciated.
732
DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY , defined to be VARCHAR2(1) , and contains one row with a value X .
Single Row Insert You can add one row at a time to a table with the insert clause. This takes the form: insert into <table_name> ( col1, col2, ... ) values ( 'value1', 'value2', ... )
The first row selected has a ROWNUM of 1, the second has 2, and so on. You can use ROWNUM to limit the number of rows returned by a query, as in this example: SELECT * FROM employees WHERE ROWNUM < 10; If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause.
In your case, SELECT 1 FROM DUAL; will simply returns 1 . You need it because the INSERT ALL syntax demands a SELECT clause but you are not querying the input values from a table.
There is no need for dynamic SQL here:
BEGIN FOR rec IN (SELECT NAME_ID FROM PRODUCT_NAMES WHERE NAME = class_string) LOOP INSERT INTO NAME_RANKING (NAME_ID, RANKING_ID) VALUES (rec.NAME_ID, ranking_id); END LOOP; END; Better still you can avoid a slow row-by-row cursor approach like this:
BEGIN INSERT INTO NAME_RANKING (NAME_ID, RANKING_ID) SELECT NAME_ID, ranking_id FROM PRODUCT_NAMES WHERE NAME = class_string; END; If you really did need the dynamic SQL you should not be concatenating values into it, but using bind variables:
BEGIN FOR rec IN (SELECT NAME_ID FROM PRODUCT_NAMES WHERE NAME = class_string) LOOP EXECUTE IMMEDIATE 'INSERT INTO NAME_RANKING (NAME_ID, RANKING_ID) VALUES (:b1, :b2) USING rec.NAME_ID, ranking_id; END LOOP; END; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
