Re: PL/SQL error SP2-0552: Bind Variable MI not decalred

I don't know if this is indeed the problem but it seems that you are missing a ')' around this line:

              || ' (PARTITION main VALUES LESS THAN (  TO_DATE(''2000-01-01 00:00:00'',''yyyy-mm-dd hh24:mi.ss'') )'

avsrk_at_mailcity.com (Subrahmanyam Arya) wrote in message news:<25c1993e.0306020756.7e275d72_at_posting.google.com>...
> All,
>
> I dont understand why i am getting the above error. In the seocnd part
> when i am creating the views, it worked previously when i did create
> or replace view as select * from <tablename>
>
> I am pasting below my SQL code
>
> --
> -- 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);
> create_str VARCHAR2(1024);
> BEGIN
>
> FOR HITER in 1..10 loop
> FOR SITER in 1..10 loop
> VIEWNAME := HOST || HITER || '_' || SWITCH || SITER || '_' ||
> TBLNAME;
> create_str := 'CREATE OR REPLACE VIEW ' || VIEWNAME ||
> ' AS SELECT * FROM HOST_SWITCH_PEGCOUNT';
> execute immediate create_str;
>
> END LOOP;
> END LOOP;
> END;
> /
> commit;
>
> your help is greatly appreciated
>
> -thx,
> avsrk
Received on Tue Jun 03 2003 - 00:35:28 CEST