Archive for the ‘ORA-01006’ tag
Entering a colon in an NDS statement
Somebody asked me how you include a colon in a Native Dynamic SQL (NDS) statement when it’s not related to a placeholder. A colon without a placeholder raises an ORA-00947
error when parsing the statement. The error means you’ve failed to submit enough bind variables. You can substitute a CHR(58) where you need to insert the standalone colon. The NDS or DBMS_SQL
parsing phase ignores a CHR(58), which translates during actual SQL statement parsing as a colon.
Let’s say you want to insert a column value with an ASIN (Amazon Standard Identification Number) code in the format: ASIN: B000VBJEEG
Using NDS, you have two choices. You can let the entry person type the full string and pass that string as a bind variable, or you can substitute CHR(58) for the colon and enter only the ASIN code. The example (Oracle Database 11g PL/SQL Programming, pp. 386-387) implements the latter:
CREATE OR REPLACE PROCEDURE insert_item ( asin VARCHAR2 , item_type VARCHAR2 , item_title VARCHAR2 , item_subtitle VARCHAR2 := '' , rating VARCHAR2 , agency VARCHAR2 , release_date DATE ) IS -- Local variable for a dynamic SQL statement. stmt VARCHAR2(2000); BEGIN -- Create a dynamic statement with bind variables. stmt := 'INSERT INTO item ' || '( item_id' || ', item_barcode' || ', item_type' || ', item_title' || ', item_subtitle' || ', item_desc' || ', item_blob' || ', item_photo' || ', item_rating' || ', item_rating_agency' || ', item_release_date' || ', created_by' || ', creation_date' || ', last_updated_by' || ', last_update_date ) ' || 'VALUES ' || '( item_s1.nextval' || ',''ASIN''||CHR(58)||:asin' || ',(SELECT common_lookup_id' || ' FROM common_lookup' || ' WHERE common_lookup_type = :item_type)' || ', :item_title' || ', :item_subtitle' || ', empty_clob()' || ', NULL, NULL' || ', :rating' || ', :agency' || ', :release_date' || ', 3, SYSDATE, 3, SYSDATE)'; -- Print debug statement. dbms_output.put_line(stmt); -- Execute dynamic statement with bind variables. EXECUTE IMMEDIATE stmt USING asin, item_type, item_title, item_subtitle, rating, agency, release_date; END insert_item; / |
There’s quite a nifty or nasty trick inside the dynamic SQL statement. You’ll notice that the colon is concatenated to the ASIN
and a bind variable. It is critical that you don’t encapsulate the bind variable inside quotes, or you’ll raise an ORA-01006
exception (check Table 11-2 in the Oracle Database 11g PL/SQL Programming book for more detail). You can’t enclose a string in single quotes because the string is substituted as a string, and it trips up the parser.
You can test this dynamic SQL statement with the following anonymous PL/SQL block:
BEGIN insert_item('B00005JPO1' ,'DVD_WIDE_SCREEN' ,'Indiana Jones and the Crystal Skull' ,'2-Disc Special Edition' ,'PG-13' ,'MPAA' ,'14-OCT-08'); END; / |