MacLochlainns Weblog

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;
/

While writing Oracle Database 11g PL/SQL Programming book last year, I collaborated with Lee Barney on a PHP/AJAX framework. Our Oracle Database AJAX & PHP Web Application Development book covers the framework. You can find a new and improved version at SourceForge.net. Lee maintains the code and continues to improve it. I’m sure he’d welcome suggestions and collaboration. We see it as very useful to AJAX web development, and to the Open Source community.