SQL Loader - Basics
Many times we need to load the data in to Peoplesoft Loading tables or other tables for conversion and interface and bulk load for minor data migrations.
SQL Loader is very fast and easy way for loading the data. It is not used much in real-time with productions instance, but with BPEL you can automate the loading of data in to tables in real-time. SQL Loader fast and very efficient.
There is some of the Basics of SQL Loader.
SQL Loader:
SQL*Loader can be used to do the following:
SQL*Loader is the primary method for quickly populating Oracle tables with data from external files. It has a powerful data-parsing engine that puts little limitation on the format of the data in the datafile. SQL*Loader is invoked when you specify the sqlldr command.
- Load data across a network. This means that a SQL*Loader client can be run on a different system from the one that is running the SQL*Loader server.
- Load data from multiple datafiles during the same load session
- Load data into multiple tables during the same load session
- Specify the character set of the data
- Selectively load data
- Load data from disk, tape, or named pipe
- Specify the character set of the data
- Generate sophisticated error reports, which greatly aid troubleshooting
- Load arbitrarily complex object-relational data
- Use either conventional or direct path loading.
SQL*Loader Control File: The control file is a text file written in a language that SQL*Loader understands. The control file tells SQL*Loader where to find the data, how to parse and interpret the data, and where to insert the data.
Input Data and Datafiles:
SQL*Loader reads data from one or more files specified in the control file. From SQL*Loader's perspective, the data in the datafile is organized as records. A particular datafile can be in fixed record format, variable record format, or stream record format. The chosen format depends on the data and depends on the flexibility and performance necessary for the job.
SQL*Loader Discards:
Loading into Empty Tables:
Input Data and Datafiles:
SQL*Loader reads data from one or more files specified in the control file. From SQL*Loader's perspective, the data in the datafile is organized as records. A particular datafile can be in fixed record format, variable record format, or stream record format. The chosen format depends on the data and depends on the flexibility and performance necessary for the job.
SQL*Loader Discards:
· As SQL*Loader executes, it may create a file called the discard file. This file is created only when it is needed, and only if you have specified that a discard file should be enabled .The discard file contains records that were filtered out of the load because they did not match any record-selection criteria specified in the control file.
· The discard file therefore contains records that were not inserted into any table in the database. You can specify the maximum number of such records that the discard file can accept. Data written to any database table is not written to the discard file.
· The discard file is written in the same format as the datafile. The discard data can be loaded with the existing control file, after any necessary editing or correcting.
Loading into Empty Tables:
If the tables you are loading into are empty, use the INSERT option. Loading into Non-Empty Tables:
If the tables you are loading into already contain data, you have three options: APPEND REPLACE TRUNCATE Warning: When the REPLACE or TRUNCATE keyword is specified, the entire table is replaced, not just individual rows. After the rows are successfully deleted, a commit is issued. You cannot recover the data that was in the table before the load, unless it was saved with Export or a comparable utility. APPEND:
If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn't already exist, the new rows are simply loaded. You must have SELECT privilege to use the APPEND option. REPLACE:
All rows in the table are deleted and the new data is loaded. The table must be in your schema, or you must have DELETE privilege on the table. The row deletes cause any delete triggers defined on the table to fire. If DELETE CASCADE has been specified for the table, then the cascaded deletes are carried out, as well. Updating Existing Rows:
The REPLACE method is a table replacement, not a replacement of individual rows. SQL*Loader does not update existing records, even if they have null columns. To update existing rows, use the following procedure: - Load your data into a work table.
- Use the SQL language UPDATE statement with correlated subqueries.
- Drop the work table.
TRUNCATE:
Using this method, SQL*Loader uses the SQL TRUNCATE command to achieve the best possible performance. For the TRUNCATE command to operate, the table's referential integrity constraints must first be disabled. If they have not been disabled, SQL*Loader returns an error. Once the integrity constraints have been disabled, DELETE CASCADE is no longer defined for the table. If the DELETE CASCADE functionality is needed, then the contents of the table must be manually deleted before the load begins. The table must be in your schema, or you must have the DELETE ANY TABLE privilege. Notes: Unlike the SQL TRUNCATE option, this method re-uses a table's extents. INSERT is SQL*Loader's default method. It requires the table to be empty before loading. SQL*Loader terminates with an error if the table contains rows. Steps to Run a SQL loader:
- Open Command prompt.
- Then enter the Database details in the below mentioned format.
SQLLDR Database Username/Password@Database Name. Eg: SQLLDR SYSADM/SYSADM&HRMS90DM - Press enter.
- You will see
Control= (in the Next Line, Enter the Control file Path.) Eg: Control=X:\appserv\prcs\HRMS90DM\files\Course_Catalog_Control.CTL (And Enter)- System takes the input from the file path mentioned in the control file and start loading the file with data accordingly.
Sample CTL (Control) File:
File name should be stored with the extension *.CTL.
LOAD DATA INFILE 'X:\appserv\prcs\HRMS90DM\files\Course_Catalog.txt' BADFILE 'X:\appserv\prcs\HRMS90DM\files\Course_Catalog.BAD' DISCARDFILE 'X:\appserv\prcs\HRMS90DM\files\Course_Catalog.DIS' APPEND INTO TABLE PS_PMR_AD_CRS_CATL fields terminated by "|" TRAILING NULLCOLS (INSTITUTION, ACAD_CAREER, CU_SUBJECT, CU_CATALOG_NUM, EFFDT DATE, EFF_STATUS, CU_DESCR_CRSE, CU_ACAD_GROUP, CU_DEPT_ACAD, CU_CRS_TYPE_CD, CU_SCHEDULE_SEQ_NM, CU_ACAD_LVL_CD, CU_CREDIT, CU_HOUR, CU_EQ_CREDS, CU_VR_CRED_INDC, CU_LST_TERM_OFF, CU_GRADE_MTH_CD, CU_FIRST_TERM_OFFR, CU_BHE_APPROVAL_DT, CU_CREDT_ONLY_INDC, CU_ATTND_REQ_CD, CU_SPL_FEES, CU_ROOM_REQ_CD, CU_ESL_INDC, CU_VAR_DESR_INDC, CU_UPDAT_TIME, CU_LIBERAL_ARTS_IN, CU_REPEAT_ALLOW_IN, CU_REQUISIT_OF_IND, CU_REQUISITE_FLAG, CU_BLCK_PRG_OFF_CD, CU_SAT_LIB_ART, CU_CPI_SUBJECT, CU_CPI_UNIT, CU_CPI_ELECTIVE, CU_UP_LOW_CD, CU_SPC_EFF_EXP_DTE, CU_WAIT_LST_ALW_IN, CU_PRE_REG_ALW_IND, CU_BLK_BOARD_ALLOW, CU_SCH_CLS_BLK_IND )
URL’s you can refer:
The contents written above were taken from the following mentioned URLs.Please go through them for the details.
If the tables you are loading into already contain data, you have three options:
APPEND
REPLACE
TRUNCATE
Warning: When the REPLACE or TRUNCATE keyword is specified, the entire table is replaced, not just individual rows. After the rows are successfully deleted, a commit is issued. You cannot recover the data that was in the table before the load, unless it was saved with Export or a comparable utility.
APPEND:
If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn't already exist, the new rows are simply loaded. You must have SELECT privilege to use the APPEND option.
REPLACE:
All rows in the table are deleted and the new data is loaded. The table must be in your schema, or you must have DELETE privilege on the table.
The row deletes cause any delete triggers defined on the table to fire. If DELETE CASCADE has been specified for the table, then the cascaded deletes are carried out, as well.
Updating Existing Rows:
The REPLACE method is a table replacement, not a replacement of individual rows. SQL*Loader does not update existing records, even if they have null columns. To update existing rows, use the following procedure:
- Load your data into a work table.
- Use the SQL language UPDATE statement with correlated subqueries.
- Drop the work table.
TRUNCATE:
Using this method, SQL*Loader uses the SQL TRUNCATE command to achieve the best possible performance. For the TRUNCATE command to operate, the table's referential integrity constraints must first be disabled. If they have not been disabled, SQL*Loader returns an error.
Once the integrity constraints have been disabled, DELETE CASCADE is no longer defined for the table. If the DELETE CASCADE functionality is needed, then the contents of the table must be manually deleted before the load begins.
The table must be in your schema, or you must have the DELETE ANY TABLE privilege.
Notes:
Unlike the SQL TRUNCATE option, this method re-uses a table's extents.
INSERT is SQL*Loader's default method. It requires the table to be empty before loading. SQL*Loader terminates with an error if the table contains rows.
Steps to Run a SQL loader:- Open Command prompt.
- Then enter the Database details in the below mentioned format.
SQLLDR Database Username/Password@Database Name.
Eg: SQLLDR SYSADM/SYSADM&HRMS90DM
- Press enter.
- You will see
Control= (in the Next Line, Enter the Control file Path.)
Eg: Control=X:\appserv\prcs\HRMS90DM\files\Course_Catalog_Control.CTL (And
Enter)
- System takes the input from the file path mentioned in the control file and start loading the file with data accordingly.
File name should be stored with the extension *.CTL.
LOAD DATA
INFILE 'X:\appserv\prcs\HRMS90DM\files\Course_Catalog.txt'
BADFILE 'X:\appserv\prcs\HRMS90DM\files\Course_Catalog.BAD'
DISCARDFILE 'X:\appserv\prcs\HRMS90DM\files\Course_Catalog.DIS'
APPEND
INTO TABLE PS_PMR_AD_CRS_CATL
fields terminated by "|"
TRAILING NULLCOLS
(
INSTITUTION,
ACAD_CAREER,
CU_SUBJECT,
CU_CATALOG_NUM,
EFFDT DATE,
EFF_STATUS,
CU_DESCR_CRSE,
CU_ACAD_GROUP,
CU_DEPT_ACAD,
CU_CRS_TYPE_CD,
CU_SCHEDULE_SEQ_NM,
CU_ACAD_LVL_CD,
CU_CREDIT,
CU_HOUR,
CU_EQ_CREDS,
CU_VR_CRED_INDC,
CU_LST_TERM_OFF,
CU_GRADE_MTH_CD,
CU_FIRST_TERM_OFFR,
CU_BHE_APPROVAL_DT,
CU_CREDT_ONLY_INDC,
CU_ATTND_REQ_CD,
CU_SPL_FEES,
CU_ROOM_REQ_CD,
CU_ESL_INDC,
CU_VAR_DESR_INDC,
CU_UPDAT_TIME,
CU_LIBERAL_ARTS_IN,
CU_REPEAT_ALLOW_IN,
CU_REQUISIT_OF_IND,
CU_REQUISITE_FLAG,
CU_BLCK_PRG_OFF_CD,
CU_SAT_LIB_ART,
CU_CPI_SUBJECT,
CU_CPI_UNIT,
CU_CPI_ELECTIVE,
CU_UP_LOW_CD,
CU_SPC_EFF_EXP_DTE,
CU_WAIT_LST_ALW_IN,
CU_PRE_REG_ALW_IND,
CU_BLK_BOARD_ALLOW,
CU_SCH_CLS_BLK_IND
)
URL’s you can refer:
The contents written above were taken from the following mentioned URLs.Please go through them for the details.
0 comments:
Subscribe to:
Post Comments (Atom)
Posts
