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
