OraFAQ Forum: Client Tools » sql table does not exist error

Home » Other » Client Tools » sql table does not exist error (SQL*PLUS, 10.2.0.2.0, windows XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql table does not exist error [message #341329]

Mon, 18 August 2008 06:56

josephsjrmurrell
Messages: 8
Registered: August 2008

Junior Member

Hi all its the noob again.

I am using a batch file to create 5 sql table from 5 text files and then join all 5 tables into one. When i run the .bat file i get an error saying that the 5 individual tables required for joining does not exist. below are the code from the .sql, .ctl, .bat, files. each table has a control file and a sql file. i will just post the code from one each, since they are all structured the same way but just different field names...

CONTROL FILE CODE

load data
infile 'R:\CIS_LEGACY_FILES\BILLING.txt' 
badfile 'bad_file_BILLING.log'
        replace
        into table legacy_cis.BILLING
        fields terminated by "|"
           (
BILL_KEY,
SERV_KEY,
SERVICE_NO,
CUSTOMER_NO,
CURR_DATE_TIME,
DATE_BILLED,
CURR_TIME,
CYCLE_NO,
TARIFF,
METER_NO_BILLED,
CUST_MULT,
READ_CODE,
PREVIOUS_DATE,
PREVIOUS_READING
)

SQL FILE CODE

DROP  TABLE LEGACY_CIS.BILLING;
CREATE TABLE LEGACY_CIS.BILLING (
BILL_KEY VARCHAR2(30),
SERV_KEY VARCHAR2(14),
SERVICE_NO VARCHAR2(7),
CUSTOMER_NO VARCHAR2(7),
CURR_DATE_TIME VARCHAR2(16),
DATE_BILLED VARCHAR2(10),
CURR_TIME VARCHAR2(12),
CYCLE_NO VARCHAR2(3),
TARIFF VARCHAR2(2),
METER_NO_BILLED VARCHAR2(6),
CUST_MULT VARCHAR2(5),
READ_CODE VARCHAR2(2),
PREVIOUS_DATE VARCHAR2(10),
PREVIOUS_READING VARCHAR2(16))
nologging;

.BAT FILE CODE

call 00_setenv.bat

REM create initial tables
sqlplus LEGACY_CIS/VAX_CIS07@MTEST <R:\CIS_LEGACY_FILES\table_creation_scripts\BILLING.sql  >logs\BILLING_tables.log
sqlplus LEGACY_CIS/VAX_CIS07@MTEST <R:\CIS_LEGACY_FILES\table_creation_scripts\BILLING_2.sql  >>logs\BILLING_tables.log
sqlplus LEGACY_CIS/VAX_CIS07@MTEST <R:\CIS_LEGACY_FILES\table_creation_scripts\BILLING_3.sql  >>logs\BILLING_tables.log
sqlplus LEGACY_CIS/VAX_CIS07@MTEST <R:\CIS_LEGACY_FILES\table_creation_scripts\BILLING_4.sql  >>logs\BILLING_tables.log
sqlplus LEGACY_CIS/VAX_CIS07@MTEST <R:\CIS_LEGACY_FILES\table_creation_scripts\BILLING_5.sql  >>logs\BILLING_tables.log

REM load BILLING.txt
SQLLDR userid=LEGACY_CIS/VAX_CIS07@MTEST  Control=R:\CIS_LEGACY_FILES\SQL_LOADER\control\BILLING.ctl, LOG=logs\BILLING.log  

REM load BILLING-2.txt
SQLLDR userid=LEGACY_CIS/VAX_CIS07@MTEST  Control=R:\CIS_LEGACY_FILES\SQL_LOADER\control\BILLING_2.ctl, LOG=logs\BILLING_2.log  

REM load BILLING-3.txt
SQLLDR userid=LEGACY_CIS/VAX_CIS07@MTEST  Control=R:\CIS_LEGACY_FILES\SQL_LOADER\control\BILLING_3.ctl, LOG=logs\BILLING_3.log  

REM load BILLING-4.txt
SQLLDR userid=LEGACY_CIS/VAX_CIS07@MTEST  Control=R:\CIS_LEGACY_FILES\SQL_LOADER\control\BILLING_4.ctl, LOG=logs\BILLING_4.log  

REM load BILLING-5.txt
SQLLDR userid=LEGACY_CIS/VAX_CIS07@MTEST  Control=R:\CIS_LEGACY_FILES\SQL_LOADER\control\BILLING_5.ctl, LOG=logs\BILLING_5.log  

REM Join tables and clean up
sqlplus LEGACY_CIS/VAX_CIS07@MTEST <R:\CIS_LEGACY_FILES\table_joining_scripts\BILLING_tab_join.sql >logs\BILLING_tab_sql_join.log
exit

SQL JOINING FILE CODE

CREATE TABLE LEGACY_CIS.BILLING_ALL
AS
SELECT
A.BILL_KEY,
A.SERV_KEY,
A.SERVICE_NO,
A.CUSTOMER_NO,
A.CURR_DATE_TIME,
A.DATE_BILLED,
A.CURR_TIME,
A.CYCLE_NO,
A.TARIFF,
A.METER_NO_BILLED,
A.CUST_MULT,
A.READ_CODE,
A.PREVIOUS_DATE,
A.PREVIOUS_READING,
B.PREV_COM_KWHS,
B.PREV_AVERAGE_KWHS,
B.PREV_DATE_BILLED,
B.PREV_KWHS,
B.PREV_DAYS_OF_SERVICE,
B.PREV_LAST_ACTUAL_DATE,
B.PREV_LAST_ACTUAL_READ,
B.READING_DATE,
B.READ_YRMO,
C.READING,
C.KWHS,
C.ENERGY,
C.FIXED_CHARGE,
C.FUEL_CHARGE,
C.VALUE_ADDED_TAX,
C.VALUE_ADDED_TAX_NET,
C.OTHER_CHARGES,
C.OTHER_CHARGES_CODE,
D.PERMANENT_OTH_CHGS,
D.ARREARS,
D.DEMAND_KVA_READ,
D.DEMAND_KVA_BILL,
D.PEAK_MARK_DATE,
D.MIN_BILL_CODE,
D.BLOCK_1_KWH,
D.BLOCK_2_KWH,
D.BLOCK_3_KWH,
E.DAYS_OF_SERVICE,
E.COM_KWHS,
E.BILL_PRINT_DIGIT,
E.TYPE_BILL,
E.CATEGORY,
E.BILL_FILL,
E.DISCOUNT_AMOUNT,
E.DATE_OF_STAT,
E.CURRENTDATE,
E.CURRENT_TIME,
E.OPERATOR,
E.CHANGE_TYPE,
E.PROGRAM_NAME
FROM BILLING A , BILLING_2 B , BILLING_3 C ,BILLING_4 D , BILLING_5 E
WHERE 
      A.BILL_KEY=B.BILL_KEY
  AND B.BILL_KEY=C.BILL_KEY
  AND C.BILL_KEY=D.BILL_KEY
  AND D.BILL_KEY=E.BILL_KEY
  AND A.SERV_KEY=B.SERV_KEY
  AND B.SERV_KEY=C.SERV_KEY
  AND C.SERV_KEY=D.SERV_KEY
  AND D.SERV_KEY=E.SERV_KEY
  AND A.SERVICE_NO=B.SERVICE_NO
  AND B.SERVICE_NO=C.SERVICE_NO
  AND C.SERVICE_NO=D.SERVICE_NO
  AND D.SERVICE_NO=E.SERVICE_NO
  AND A.CUSTOMER_NO=B.CUSTOMER_NO
  AND B.CUSTOMER_NO=C.CUSTOMER_NO
  AND C.CUSTOMER_NO=D.CUSTOMER_NO
  AND D.CUSTOMER_NO=E.CUSTOMER_NO
  AND A.DATE_BILLED=B.DATE_BILLED
  AND B.DATE_BILLED=C.DATE_BILLED
  AND C.DATE_BILLED=D.DATE_BILLED
  AND D.DATE_BILLED=E.DATE_BILLED;

DROP TABLE BILLING;
DROP TABLE BILLING_2;
DROP TABLE BILLING_3;
DROP TABLE BILLING_4;
DROP TABLE BILLING_5;

RENAME  BILLING_ALL   TO BILLING;

is there something wrong with my table creation statement?

i used that statement structure to create 99 other tables and it worked, what am i doing wrong.

Report message to a moderator