Load the data in Oracle Database

[kalai arasan]

Hi ,

I am trying to load the data in Oracle Database with Oracle SQL Developer, but i could not

any one has any idea about this query

=======================================================================

OPTIONS ( SKIP=1)
LOAD DATA
TRUNCATE INTO
TABLE AS_OWNER.CLIENT_POLICY
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
( SYSTEM_ID "TRIM(:SYSTEM_ID)"
, PRODUCT_TYPE "TRIM(:PRODUCT_TYPE)"
, SERVICE_TYPE "TRIM(:SERVICE_TYPE)"
, CUSTOMER_TYPE "TRIM(:CUSTOMER_TYPE)"
, MARKET_UNIT "TRIM(:MARKET_UNIT)"
, TROUBLE_TYPE "TRIM(:TROUBLE_TYPE)"
, SERVICE_CLASS "TRIM(:SERVICE_CLASS)"
, DISPATCH_REQ "TRIM(ISPATCH_REQ)"
, APPOINTMENT_REQ "TRIM(:APPOINTMENT_REQ)"
, COMMITMENT_HOURS "TRIM(:COMMITMENT_HOURS)"
, A_TIME_FIRST "TO_DATE(:A_TIME_FIRST,'HH12:MI AM')"
, A_TIME_LAST "TO_DATE(:A_TIME_LAST,'HH12:MI AM')"
, APPT_DURATION "TRIM(:APPT_DURATION)"
, C_TIME "TO_DATE(:C_TIME,'HH12:MI AM')"
, DATE_CREATED "TO_DATE(:C_TIME,'HH12:MI AM')"
, DATE_UPDATED "TO_DATE(:C_TIME,'HH12:MI AM')"
)

====================================================



this is the data which already inserted in database

==========================================================================================================
"AS",POTS,INSTALL,RETAIL,RESIDENTIAL,NA,STANDARD,OUT,Y,72,08:00 AM,01:00 PM,4,08:00 PM
"AS",POTS,INSTALL,WHOLESALE,RESIDENTIAL,NA,STANDARD,OUT,Y,72,08:00 AM,01:00 PM,4,08:00 PM
"AS",POTS,INSTALL,RETAIL,BUSINESS,NA,STANDARD,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",POTS,INSTALL,WHOLESALE,BUSINESS,NA,STANDARD,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,RETAIL,RESIDENTIAL,NA,STANDARD,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,RETAIL,BUSINESS,NA,STANDARD,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,RETAIL,RESIDENTIAL,NA,STANDARD+,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,RETAIL,BUSINESS,NA,STANDARD+,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,RETAIL,RESIDENTIAL,NA,PREMIUM,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,RETAIL,BUSINESS,NA,PREMIUM,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,RETAIL,RESIDENTIAL,NA,PREMIUM+,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,RETAIL,BUSINESS,NA,PREMIUM+,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,WHOLESALE,RESIDENTIAL,NA,STANDARD,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,WHOLESALE,BUSINESS,NA,STANDARD,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,WHOLESALE,RESIDENTIAL,NA,STANDARD+,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,WHOLESALE,BUSINESS,NA,STANDARD+,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,WHOLESALE,RESIDENTIAL,NA,PREMIUM,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,WHOLESALE,BUSINESS,NA,PREMIUM,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,WHOLESALE,RESIDENTIAL,NA,PREMIUM+,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,WHOLESALE,BUSINESS,NA,PREMIUM+,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",VIDEO,INSTALL,RETAIL,RESIDENTIAL,NA,PREMIUM+,OUT,Y,72,08:00 AM,01:00 PM,4,08:00 PM
"AS",VIDEO,INSTALL,RETAIL,BUSINESS,NA,PREMIUM+,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",VIDEO,INSTALL,WHOLESALE,RESIDENTIAL,NA,PREMIUM+,OUT,Y,72,08:00 AM,01:00 PM,4,08:00 PM
"AS",VIDEO,INSTALL,WHOLESALE,BUSINESS,NA,PREMIUM+,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",POTS,INSTALL,RETAIL,RESIDENTIAL,NA,MANDATE,OUT,Y,72,08:00 AM,01:00 PM,4,08:00 PM
"AS",POTS,INSTALL,RETAIL,BUSINESS,NA,MANDATE,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",POTS,INSTALL,WHOLESALE,RESIDENTIAL,NA,MANDATE,OUT,Y,72,08:00 AM,01:00 PM,4,08:00 PM
"AS",POTS,INSTALL,WHOLESALE,BUSINESS,NA,MANDATE,OUT,Y,48,08:00 AM,01:00 PM,4,08:00 PM
"AS",HSI,INSTALL,RETAIL,RESIDENTIAL,NA,STANDARD,OUT,N,48,,,,08:00 PM
"AS",HSI,INSTALL,RETAIL,BUSINESS,NA,STANDARD,OUT,N,48,,,,08:00 PM
"AS",HSI,INSTALL,WHOLESALE,RESIDENTIAL,NA,STANDARD,OUT,N,48,,,,08:00 PM
"AS",HSI,INSTALL,WHOLESALE,BUSINESS,NA,STANDARD,OUT,N,48,,,,08:00 PM
"AS",HSI,INSTALL,RETAIL,RESIDENTIAL,NA,STANDARD+,OUT,N,48,,,,08:00 PM
"AS",HSI,INSTALL,RETAIL,BUSINESS,NA,STANDARD+,OUT,N,48,,,,08:00 PM
"AS",HSI,INSTALL,WHOLESALE,RESIDENTIAL,NA,STANDARD+,OUT,N,48,,,,08:00 PM
"AS",HSI,INSTALL,WHOLESALE,BUSINESS,NA,STANDARD+,OUT,N,48,,,,08:00 PM

=========================================================================================

So i tried to load bellow data,

with file name as data.ctl or data.txt
===========================================================================================================
SYSTEM_NAME,PRODUCT_TYPE,SERVICE_TYPE,CUSTOMER_TYPE,MARKET_UNIT,TROUBLE_TYPE,SERVICE_CLASS,DISPATCH_REQ,APPOINTMENT_REQ,COMMITMENT_HOURS,A_TIME_FIRST,A_TIME_
LAST,APPT_DURATION,C_TIME
"AAAS",POTSA,INSTALAL,RETAILS,RESIDENTIALA,NA,STANDARDA,OUT,N,73,07:00 pM,02:00 PM,7,01:00 AM

=============================================================================================================


i am not able to load the data through Oracle SQL Developer, i am getting invalid SQL Statement Error.

please correct me where i did the mistake.what needs to be changed.

I am done with mysql database,i want to do that in Oracle DataBase.


With Regards,
Kalai.

[Martin Vajsar]

Kalai,

which tool did you use to obtain the data?

There is no LOAD DATA command in Oracle's SQL, and as far as I know, neither in SQL Developer. Oracle has SQLLoader utility for loading up values from text files, or much better external tables feature from version 10 on (maybe in Oracle 9 too, don't remember clearly), and you might be able to reformat the data to conform to these tools, but it'll be some work (lots of work if you're not familiar with Oracle) and you should look it up in the documentation first.

For smaller data sets usually plain scripts (with CREATE TABLE and lots of INSERT statements) are generated. If you can generate these form MySQL, it might be the easiest way to go. The second option would be to export data to some intermediate format - eg. CSV or XML, SQL Developer should be able to import this, if you create the table(s) first.