Oracle Shooter

Multiple File upload thru HOST Concurrent Program Requirement :- A FTP location having multiple file and we need to upload all files in system or any staging Table. based on that we can write some validation/program/interface anything. Solution :- 1. Create   table for data upload 2. Create   .ctl Control File 3. Create .Prog File 4. Login to Putty 5.  Create a Soft Link for Prof file for host program 6.    Login in Filezilla or Winscp for file movement 7.  Transfer all files 8.    Provide Full Grants to .prog file 9.  Register a concurrent HOST type program in Oracle ERP. 10   Run that program 11.  Check all file moved or not to destination location 12. check into the TABLE.  See my video on my you tube Channel " ORACLE  TECHNICAL SHOOTER " 1.  Create Table Syntax :- CREATE TABLE XXSD . XXSD_SQL_LOADER_TEST_TL (   HDR_ID         NUMBER ,   EMP_NAME       VARCHAR2 ( 100 BYTE),   EMP_DEPT       VARCHAR2 ( 100 BYTE),   E

Multiple File upload thru Oracle Loader (OR) External Table Concept Requirement :- A FTP location having multiple file and we need to upload all files in system or any staging Table. based on that we can write some validation/program/interface anything. Restrictions :- 1. Only First File contain the heading or columns name. because we skip first row from first file. Solution :- 1. Create a DB Directory 2. Give Full Grant to this directory 3. Create a External TABLE with proper Syntax 3. Move that control file and excel files to particular $TOP/ .../Bin  Folder or directories 4. Run the SELECT Command on table and check all data comes or not. See my video on my you tube Channel " ORACLE  TECHNICAL SHOOTER " 1.  Create Directory Syntax :- CREATE OR REPLACE DIRECTORY  XYZ  AS ' /var/tmp '; 2.  Grant to Directory Syntax :- GRANT READ,WRITE ON DIRECTORY  XYZ  TO  PUBLIC; 3. We can check whether our directory is created or not:- select * from a

Oracle Loader (OR) External Table Concept Requirement :- A FTP location having one file and we need to upload this files in system or any staging Table. based on that we can write some validation/program/interface anything. Restrictions :- 1. Only First File contain the heading or columns name. because we skip first row from first file. Solution :- 1. Create a DB Directory 2. Give Full Grant to this directory 3. Create a External TABLE with proper Syntax 3. Move that control file and excel files to particular $TOP/ .../Bin  Folder or directories 4. Run the SELECT Command on table and check all data comes or not. See my video on my you tube Channel " ORACLE  TECHNICAL SHOOTER " 1.  Create Directory Syntax :- CREATE OR REPLACE DIRECTORY XYZ AS ' /var/tmp '; 2.  Grant to Directory Syntax :- GRANT READ,WRITE ON DIRECTORY XYZ TO PUBLIC; 3. We can check whether our directory is created or not:- select * from all_objects where object_type =

Oracle SQL*Loader Requirement :- A FTP location having multiple file and we need to upload all files in system or any staging Table. based on that we can write some validation/program/interface anything. Restrictions :- 1. All file name must be known 2. All file must be exists on FTP location Solution :- 1. Create A TABLE 2. Write a control file 3. Move that control file and excel files to particular $TOP/ .../Bin  Folder or directories 4. Register Concurrent program     Type :- SQL* Loader 5. Run That program and check the data in to the table See my video on my you tube Channel " ORACLE  TECHNICAL SHOOTER " 1. Create Table Syntax :- CREATE TABLE XXSD.XXSD_SQL_LOADER_TEST_TL (   HDR_ID          NUMBER ,   EMP_NAME        VARCHAR2 (100 BYTE),   EMP_DEPT        VARCHAR2 (100 BYTE),   EMP_DATE        VARCHAR2 (100 BYTE),   CREATION_DATE  DATE ,   ORG_ID          NUMBER ) 2. File :- .CTL OPTIONS (SKIP=1) LOAD DATA INFILE '$XXSD_TOP