SQL Loader in ORACLE

-

Download Oracle Instant Client tools :instantclient-tools-windows.x64-21.12.0.0.0dbru 

SQL*Loader Features

SQL*Loader loads data from external files into tables of an Oracle database.

It has a powerful data parsing engine that puts little limitation on the format of the data in the data file. You can use SQL*Loader to do the following:

  • Load data across a network if your data files are on a different system than the database.

  • Load data from multiple data files 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 (you can load records based on the records' values).

  • Manipulate the data before loading it, using SQL functions.

  • Generate unique sequential key values in specified columns.

  • Use the operating system's file system to access the data files.

  • Load data from disk, tape, or named pipe.

  • Generate sophisticated error reports, which greatly aid troubleshooting.

  • Load arbitrarily complex object-relational data.

  • Use secondary data files for loading LOBs and collections.

You can use SQL*Loader in two ways: with or without a control file. A control file controls the behavior of SQL*Loader and one or more data files used in the load. Using a control file gives you more control over the load operation, which might be desirable for more complicated load situations.

The output of SQL*Loader is an Oracle database (where the data is loaded), a log file, a bad file if there are rejected records, and potentially, a discard file.


Steps in  Unix:

Write the loader command to a file as follows :

echo "load data infile '/data/cifs/fs/freightcron_stg/gsa/Temp/files/<FileName>.txt'
  into table <tablename>
  fields terminated by '~' TRAILING NULLCOLS
  (NAME, ADDRESS, CITY, STATE)" >> /data/cifs/fs/freightcron_stg/gsa/Temp/files/controlfile_oracle_new.ctl

Execute the SQL Loader : 

 /usr/lib/oracle/21/client64/bin/sqlldr userid=username/password@hostname:1526/sid control='/Temp/files/controlfile_oracle_new.ctl'  direct=true


Comments

Post a Comment

Popular posts from this blog

-
Image
EASY DEPLOYMENT IN TOMCAT SERVER TAKE A BACKUP OF WAR FILE FROM PROD TO Desktop 1) sudo -i 2) systemctl stop tomcat_ratepro_app [ CHECK APPLICATION IS WORKIKNG] 3) cp -r /apps/ratepro/ratepro_app/webapps/RatePro-App-1.0.0.war /home/gehc-cloud/Bkp/Prod_bkp_on_18Jan2025/ 4) cp -r /apps/ratepro/ratepro_app/webapps/RatePro-App-1.0.0 /home/gehc-cloud/Bkp/Prod_bkp_on_18Jan2025/ 3) rm -rf /apps/ratepro/ratepro_app/webapps/RatePro-App-1.0.0.war;rm -rf /apps/ratepro/ratepro_app/webapps/RatePro-App-1.0.0 4) cp -r /home/gehc-cloud/GSA_To_Deploy_Files/RatePro-App-1.0.0.war /apps/ratepro/ratepro_app/webapps 5) chmod -R 777 /apps/ratepro/ratepro_app/webapps/RatePro-App-1.0.0.war 6) systemctl start tomcat_ratepro_app [RatePro-App-1.0.0 FOLDER WILL BE CREATED] 7) tail -100f /apps/ratepro/ratepro_app/logs/catalina.out 8) \cp -r /home/gehc-cloud/GSA_To_Deploy_Files/dist/* /apps/ratepro/ratepro_app/webapps/RatePro-App-1.0.0 9) chmod -R 777 /apps/ratepro/ratepro_app/webapps/RatePro-App-1.0.0 ...
Read more
-
  Like the King commands... When thinking of commands in Linux, I am reminded of the old stories of kings and military leaders who issue orders to their subordinates, expecting them to be executed promptly and accurately. Just like the king's command, a Linux command must be carried out in a timely and precise manner to achieve the desired result. Similarly, when we use commands to instruct our computers, we are telling them to perform specific tasks quickly and accurately, with results we are awaiting. Commands like "ls" or "ps -f | grep '.txt'" are common examples we use daily. These commands simplify our lives, and just like the orders of a king, they are often crucial and lawful. I’m referring to the way we use Linux commands to execute tasks and view their results. Linux is a powerful and wonderful operating system that presents its results in a structured and informative manner. However, mastering these commands and understanding their outputs requ...
Read more

Why to do a POC (Proof Of Concept)

-
 Proof Of Concept or POC POC or Proof Of Concept is done for the following reasons : 1) Less Waste Of Resources: POC's prevent you from dumping resources into projects that are unlikely to succeed. 2) More Funding from Clients : Investors are more likely to fund a project that has been proven viable. 3) Better Planning : A POC will make you think about project logistics before you even get started. 4) Fewer Road Blocks : Challenges to viability will stick out to you when writing a POC and can inform project direction Some snippets on POC's  1) POC  helps us to determine whether an idea is feasible before developing it in to a product. 2) Prototype : Prototype is a model of your product that can be tested to provide an idea of how it will function  3) Minimum viable product : An MVP is refined version of your prototype that's tested to identify minor issues and create the final product.
Read more