Re: SP2-0552: Bind variable "MI" not declared on this code

avsrk_at_mailcity.com (Subrahmanyam Arya) wrote in message news:<25c1993e.0306020801.3266bb1a_at_posting.google.com>...

> 'TO_CHAR(CreationTime,'mm/dd/yyyy') AS
> CREATION_DATE,'||
> 'TO_CHAR(CreationTime,'hh24:mi:ss') AS
> CREATION_TIME,'||
Looks like hh24:mi:ss is not properly enclosed inside single quotes '... which is why Oracle thinks :mi is a bind variable. Count the number of ' and enclose properly.

Suggestion:
When building a long sql statement for dynamic plsql, try it by declaring a stmt varchar2(??) variable... like you did in the other post, and build it incrementally e.g.

stmt_ := 'CREATE OR REPLACE VIEW ' || MYVIEW;
stmt_ := stmt_ || ' AS SELECT .....;
stmt_ stmt_ ||  ' .....';

It will be much more manageable and easy to build. Also try using CHR(39) to pass ' as a literal, I find it to be much easier that way since you only need to count ' at the beginning and end of a statement.

Regards
/Rauf Sarwar Received on Mon Jun 02 2003 - 23:38:22 CEST