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

If you search for 'MI' in your code, the error will become apparent. Try to play with putting a single quote inside a string.

SQL> desc t1
ERROR:
ORA-04043: object t1 does not exist

SQL> declare
  2 x varchar2(500);
  3 begin
  4 x:='create table t1(c1) as select
to_char(created,'||''''||'ddmmyy hh24:mi:ss'||''''||')'   5 ||'from user_objects where rownum=1';   6 dbms_output.put_line(x);
  7 execute immediate x;
  8 end;
  9 /
create table t1(c1) as select to_char(created,'ddmmyy hh24:mi:ss')from user_objects where rownum=1

PL/SQL procedure successfully completed.

SQL> desc t1

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 C1                                                 VARCHAR2(15)

SQL>

• Jusung Yang

avsrk_at_mailcity.com (Subrahmanyam Arya) wrote in message news:<25c1993e.0306020801.3266bb1a_at_posting.google.com>...
> Hi,
>
> I dont know why i am getting the error mentioned above. This is the
> correct sql code which is giving this error. the previous one that got
> posted did not error
>
> --
> -- Create PEGCOUNT table if not already existing
> --
> DECLARE
> tbl varchar2(255);
> SCHEMA_OWNER CONSTANT VARCHAR2 (30) := 'test';
> STATS_TABLESPACE CONSTANT VARCHAR2 (30) := 'stats';
> BEGIN
> select table_name into tbl from user_tables where
> table_name='HOST_SWITCH_PEGCOUNT';
> EXCEPTION
> WHEN NO_DATA_FOUND
> THEN
> execute immediate 'CREATE TABLE ' || SCHEMA_OWNER || '.' ||
> 'HOST_SWITCH_PEGCOUNT' || '('
> || ' Id NUMBER NOT NULL,'
> || ' SwitchNumber INTEGER NOT NULL,'
> || ' Aid VARCHAR2(64) NOT NULL,'
> || ' CreationTime DATE NOT NULL,'
> || ' Version VARCHAR2(8) NOT NULL,'
> || ' RecordType INTEGER NOT NULL,'
> || ' TypeId INTEGER NOT NULL,'
> || ' OpMode INTEGER NOT NULL,'
> || ' OpState INTEGER NOT NULL,'
> || ' Medium INTEGER NOT NULL,'
> || ' PegCount INTEGER NOT NULL,'
> || ' Circuits INTEGER NOT NULL )'
> || ' PARTITION BY RANGE (CREATIONTIME) '
> || ' (PARTITION main VALUES LESS THAN (
> TO_DATE(''2000-01-01 00:00:00'',''yyyy-mm-dd hh24:mi.ss'') )'
> || ' TABLESPACE ' || STATS_TABLESPACE || ')' ;
>
> END;
> /
> commit;
>
> --
> -- create helper pegcount views. Assume 10 statservers and 10 switches
> per
> -- stats server
> --
> --
> DECLARE
> HOST CONSTANT VARCHAR2(10) := 'HOST';
> SWITCH CONSTANT VARCHAR2(10) := 'SWITCH';
> TBLNAME CONSTANT VARCHAR2(10) := 'PEGCOUNT';
> HITER INTEGER(5);
> SITER INTEGER(5);
> VIEWNAME VARCHAR2(64);
> BEGIN
>
> FOR HITER in 1..10 loop
> FOR SITER in 1..10 loop
> VIEWNAME := HOST || HITER || '_' || SWITCH || SITER || '_' ||
> TBLNAME;
> execute immediate 'CREATE OR REPLACE VIEW' || VIEWNAME ||
> ' AS SELECT SwitchNumber,Aid,' ||
> 'TO_CHAR(CreationTime,'mm/dd/yyyy') AS
> CREATION_DATE,'||
> 'TO_CHAR(CreationTime,'hh24:mi:ss') AS
> CREATION_TIME,'||
> 'RecordType, TypeId, OpMode, OpState, Medium,'||
> 'PegCount, Circuits FROM HOST_SWITCH_PEGCOUNT';
>
> END LOOP;
> END LOOP;
> END;
> /
> commit;
Received on Tue Jun 03 2003 - 00:16:54 CEST