Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Entering a colon in an NDS statement

leave a comment »


An updated version of this blog is on my newer site here.

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

stmt VARCHAR2(2000);

BEGIN

stmt := 'INSERT INTO item 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)';
dbms_output.put_line(stmt);
EXECUTE IMMEDIATE stmt
USING asin, item_type, item_title, item_subtitle, rating, agency, release_date;

END insert_item;
/

Advertisements

Written by maclochlainn

May 10, 2008 at 4:58 am

Posted in Oracle

Tagged with , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: