Shell script

#!/bin/sh

ls -l *.xml|nl|awk '{print ""$10","$1",\x27"$10"\x27"}' > xml_files.dat

sqlldr username/password(for database)@localhost:1521/xe control='xml.ctl'

sqlplus -s username/password(for database)@localhost:1521/xe<<END
execute  xml_dataloader_p
delete from xml_files where file_name is not null;
Commit;
exit;
END
>xml_files.dat


ls -l ----> To display all directories.
nl ----> To display line numbers.
awk ----> awk formating text in required format in a file.

$10 ----> 10 th column of response from "  ls -l *.xml |nl "

[karunan@eappnodejs anand]$ ls -l *.xml|nl

1  -rw-rw-r--. 1 karunan karunan  8747 Jun 12 15:0noshipment1_2017_03_30_02_55_18_013.xml

count of above line like 1 for 1st and .xml file name is 10th 

$1 ---> sequence
\x27 ---> To display single code " ' "

http://www.unix.com/shell-programming-and-scripting/130984-how-call-oracle-stored-procedure-unix-shell.html

Downloading ldts through script :

#!/bin/sh
while read -r line;
do
FNDLOAD sqlusername/password O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct $line.ldt PROGRAM APPLICATION_SHORT_NAME="XXXX" CONCURRENT_PROGRAM_NAME="$line"
mv *.ldt /home/oracle/sampleldts/ldts
mv *.log /home/oracle/sampleldts/logs
done < file.txt
 

     

Loading xml elements from multiple xml files into oracle table.

How to transform xml data from multiple xml files into oracle database tables.

      1.       Extracting xml file names from the directory into .dat file. This .dat file will be referred

             by control file.

      2.       Processing xml files in the dat file through sql loader with the following command.

                   SQL >  host sqlldr control = ‘control file name.ctl’

      3.       We can querry the result set from the table “xml_files”.

            SQL>  SELECT     replace(file_name,'''','')

                                      ,    EXTRACTVALUE (xmldata, '//AirwayBillNumber/text()')

                                      ,     EXTRACTVALUE (xmldata, '//ShipperID/text()')

                            FROM     xml_files ;

       4.       Processing xml data elements from xml type table “xml_type” into base                                          table"xxrp_acct_header”.

        5.       Execute the following procedure “accountHEADER” to load required xml elements into              “xxrp_acct_header” with the following command and also empty the table “xml_files”

          exec accountHEADER;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The following syntax should be followed while extracting xml file names into .dat file. Filename “'xml_files.dat'”

                          "      Xmlfilename1.xml,1,’xmlfilename1.xml'

                                  Xmlfilename2.xml,2,’xmlfilename2.xml'

                                  Xmlfilename3.xml,3,’xmlfilename3.xml'    ”

The .dat file will be referenced by control file which locks as follows. File name “xml.ctl”

                           “          load data

                                       infile 'xml_files.dat'

                                       into table XML_FILES append

                                       fields terminated by ','

                                        (

                                        xml_filler filler char

                                        ,xmldata lobfile(xml_filler) terminated by EOF

                                         ,file_id

                                         ,file_name

                                         )               ”

Create the following tables must be created.

                             SQL>  create table XML_FILES(

                                         XMLDATA     sys.XMLTYPE        NOT NULL,

                                         FILE_ID          NUMBER (10)        NOT NULL,

                                         FILE_NAME  VARCHAR2 (255)   NOT NULL

                                         );

                              SQL> create table xxrp_acct_header (

                                         file_name                    varchar2(100),

                                         AirwayBillNumber     varchar2(100),

                                         ShipperID                    varchar2(100)

                                         );

Compile and Execute the following procedure.

“set SERVEROUTPUT ON

/

CREATE OR REPLACE PROCEDURE accountHEADER AS

BEGIN

insert into xxrp_acct_header (file_name,AirwayBillNumber,ShipperID)

SELECT replace(file_name,'''',''), EXTRACTVALUE (xmldata, '//AirwayBillNumber/text()'),

EXTRACTVALUE (xmldata, '//ShipperID/text()')

FROM xml_files ;

delete from xml_files where file_name is not null;

COMMIT;

END accountHEADER;

/

show errors

/

exec accountHEADER;

/”

SELECT XMLTYPE(t.response_xml).EXTRACT('//PieceNumber/text()').getStringVal() as PieceNumber
from  inv_shipment_requests t
where SERVICE_TYPE='SHIPMENT'
and   STATUS='Success'
and   AIRWAY_BILLNUMBER = 111;

OR

SELECT
EXTRACTVALUE(xmltype(t.response_xml), '//PieceNumber/text()') as PieceNumber
from  inv_shipment_requests t
where SERVICE_TYPE='SHIPMENT'
and   STATUS='Success'
and   AIRWAY_BILLNUMBER = 111;