aspose file tools*
The moose likes Oracle/OAS and the fly likes Load the data in Oracle Database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Load the data in Oracle Database" Watch "Load the data in Oracle Database" New topic
Author

Load the data in Oracle Database

kalai arasan
Greenhorn

Joined: Aug 18, 2009
Posts: 26
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
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Load the data in Oracle Database