Multiple File upload thru HOST Concurrent Program

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),

  EMP_DATE       DATE,

  CREATION_DATE  DATE,

  ORG_ID     NUMBER

)

2. Create .CTL file Syntax:-

OPTIONS (SKIP=1)

LOAD DATA

APPEND

INTO TABLE XXSD.XXSD_HOST_PROGRAM_TL

FIELDS TERMINATED BY ',' 

OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

EMP_NAME ":EMP_NAME" ,

EMP_DEPT ":EMP_DEPT",

EMP_DATE ":EMP_DATE",

HDR_ID  "XXSD.XXSD_HOST_PROGRAM_SEQ.NEXTVAL",

CREATION_DATE         SYSDATE  

)

3. Create .prog file Syntax:-

FCP_LOGIN=$1

v_file_dir=/u08/STG_APP/apps/apps_st/appl/xxsd/12.0.0/Custome_Folder

v_arch_dir=/u08/STG_APP/apps/apps_st/appl/xxsd/12.0.0/Custome_Folder/Archive

echo "***********************************************************"

echo "                   INPUT PARAMETERS                        "

echo "***********************************************************"

echo " File Directory"$v_file_dir

echo " Archive Directory"$v_arch_dir

echo " " 

cd  $XXSD_TOP/CustReferral

echo `pwd`

#Checking number of files in directory

#Clearing contents of check.txt

> check.txt

v_file_count=`ls XXSD_MULTI_FILE*.csv |wc -l`

echo "Count is $v_file_count"

if [[ $v_file_count -gt 0 ]]; then

#writing the file name in directory to text file 

#

echo "Inside If"

ls XXSD_MULTI_FILE* > $v_file_dir/test.txt

#

for file in $(cat test.txt)

do

echo "File_Name: $file"

L_TODAY_DATE=`date +%d-%m-%Y_%T`

L_LOG_FILE=`basename $file`

L_BKUP_FILE=`basename $file`

L_LOG_FILE="$file"_"$L_TODAY_DATE.log"

L_LOG_FILE="$v_file_dir/$L_LOG_FILE"

L_BKUP_FILE="$file:$L_BKUP_FILE_$L_TODAY_DATE"

L_BKUP_FILE="$v_arch_dir/$L_BKUP_FILE"

sqlldr userid=$FCP_LOGIN control=$XXSD_TOP/bin/XXSD_MULTI_FILE_CONTROL.ctl log=$L_LOG_FILE silent=feedback bindsize=100000 data=$file

 if [ $? -eq 0 ]

then

 

  echo "  "

  tail $L_LOG_FILE

  echo "  "

  echo "***SUCCESS***  SQL*LOADER FINISHED SUCCESSFULLY"

  echo "  "

  echo " Archiving before Exit !!"

  mv  $v_file_dir/$file $L_BKUP_FILE

  echo "File Archived at Path $L_BKUP_FILE"

else

echo "Error***  SQL*Loader finished with some error. See below log for more information"

echo " Error during Load of  file : $file"

echo "`cat $L_LOG_FILE`"

l_exis_status=1

fi

done

echo "Remove Contents of Directory $v_file_dir and Move to $v_file_dir/Archive"

mv $v_file_dir/XXSD_MULTI_FILE*.csv $v_file_dir/Archive

echo "Script Ends"

else

echo "inside else"

echo "X" > $v_file_dir/check.txt 

exit 0;

fi

4. Login Putty:-
5. Go to :-  cd /u08/STG_APP/apps/apps_st/appl/xxsd/12.0.0/bin

5.1. Run This for Sfot Link:-    ln -s $FND_TOP/bin/fndcpesr  XXSD_MULTI_FILE_UPLOAD

6. File transfer:-

7. Provide Full GRANT:- just right click on file and provide full permission

8. Run Concurrent Program

10. Check File is moved in Archive folder 

11. check into the table