Hi asktom,

As per the oracle standard we can not use the select, insert statement directly in pl/sql. Hence we use the "execute immediate" command to execute select and insert command in oracle pl/sql below code.


The code worked to load the xml file data into a table.

For this we have used the XML table function . To get the data we have used xml tags as it is in the file.

So while executing we have faced the below error.

When we execute the procedure . It is asking enter bind variables value for .

for
1):Amt
2):SrcCcy
3):v1_0

and so on

how to avoid this, it is treating bind variables, actually those are not bind variables those are xml tags inside the xml file.
we require help on this.

execute immediate q'{ your insert and select }';

create table xml_tab (
 xml_doc xmltype
);
create table orders (
  order_id integer,
  customer varchar2(10)
);

insert into xml_tab values (
  xmltype('<orders>
 <order>
   <order_id>1234</order_id>
   <customer_name>Customer</customer_name>
 </order>
 <order>
   <order_id>5678</order_id>
   <customer_name>Customer</customer_name>
 </order>
</orders>')
);

commit;

create or replace procedure p as 
begin
  insert into orders
    select x.order_id, x.customer
    from   xml_tab t,
           xmltable('/orders/order'
             passing t.xml_doc
             columns 
               order_id integer path 'order_id',
               customer varchar2(10) path 'customer_name'
           ) x;
end;
/

select * from orders;

no rows selected

exec p;

select * from orders;

  ORDER_ID CUSTOMER 
---------- ----------
      1234 Customer  
      5678 Customer 

Hi Tom,

we are going to create the procedure in sample schema, but we are inserting the data in to sample1 schema. The tables are present in sample schema, and we are using the some sequences in the code. those sequences are in the sample schema. We we run the code without procedure in sample. it is executing successfully. but when we make this as procedure, it showing error like "No more data to read from socket" error.

We are executing this in the sqldeveloper 3.4 version.

below is the code we are using.

create or replace procedure XML_Parsing
is
begin
INSERT
INTO sample1.LTE_TRANSACTION COLUMNS
(
LTTR_TRANSACTION_ID,
LTTR_TRANSACTION_TYPE_CODE,
LTTR_AGMNT_ID,
LTTR_PRODUCT_ID,
LTTR_XML_FILE,
LTTR_CORE_TX_ID,
LTTR_REFERENCES,
EXT_LTTR_ORG_ID,
LTTR_CURRENCY_CODE_LOC,
LTTR_CURRENCY_CODE_ORG,
LTTR_CURRENCY_CODE_ACCT,
EXT_LTTR_ACCT_NUMBER,
LTTR_BOOK_DATE,
LTTR_TRANS_VALUE_DATE,
LTTR_TRANSACTION_AMOUNT_ORG,
LTTR_TRANSACTION_AMOUNT_ACCT,
LTTR_TRANSACTION_AMOUNT_LOC,
LTTR_EXCHANGE_RATE,
LTTR_CORRECTION_CODE,
-- LTTR_EXECUTION_TIME,
LTTR_TRANS_CHANNEL_TYPE,
LTTR_TRANS_SERVICE_TYPE,
LTTR_CONTRA_ACCOUNT,
LTTR_EXECUTION_STATUS,
LTTR_REPORT_TO_PAYMENT,
LTTR_ACCOUNT_OPEN_DATE,
LTTR_WITHDRAWAL_CHRG
)
(SELECT LTE_TRANSACTION_SEQ.NEXTVAL AS LTTR_TRANSACTION_ID,
LTE_TRANSACTION_SEQ.NEXTVAL AS LTTR_TRANSACTION_TYPE_CODE,
LTE_TRANSACTION_SEQ.NEXTVAL AS LTTR_AGMNT_ID,
LTE_TRANSACTION_SEQ.NEXTVAL AS LTTR_PRODUCT_ID,
LTE_TRANSACTION_SEQ.NEXTVAL AS LTTR_XML_FILE ,
LTE_TRANSACTION_SEQ.NEXTVAL AS LTTR_CORE_TX_ID,
'InstrId'
||':'
||InstrId
|| ','
||'EndToEndId'
||':'
||EndToEndId
||','
||'TxId'
||':'
||TxId
||','
||'ResId'
||':'
||ResId
||','
||'MhId'
||':'
||MhId
||','
||'PwhId'
||':'
||PwhId
||','
||'TgId'
||':'
||TgId
||','
||'Tp'
||':'
||Tp
||','
||'Ref'
||':'
|| Ref AS LTTR_REFERENCES,
org_id AS EXT_LTTR_ORG_ID,
loc_cur_code AS LTTR_CURRENCY_CODE_LOC ,
org_cur_code AS LTTR_CURRENCY_CODE_ORG,
acct_cur_code AS LTTR_CURRENCY_CODE_ACCT,
acct_number AS EXT_LTTR_ACCT_NUMBER,
book_date AS LTTR_BOOK_DATE,
value_date AS LTTR_TRANS_VALUE_DATE ,
tran_amt_org AS LTTR_TRANSACTION_AMOUNT_ORG,
tran_amt_acct AS LTTR_TRANSACTION_AMOUNT_ACCT,
tran_amt_loc AS LTTR_TRANSACTION_AMOUNT_LOC,
exchang_rate AS LTTR_EXCHANGE_RATE,
correction_code AS LTTR_CORRECTION_CODE,
-- TO_TIMESTAMP( execution_time, 'HH24MI')AS LTTR_EXECUTION_TIME,
transction_ch_type AS LTTR_TRANS_CHANNEL_TYPE ,
transction_service_type AS LTTR_TRANS_SERVICE_TYPE,
contra_acct AS LTTR_CONTRA_ACCT,
execution_status AS LTTR_EXECUTION_STATUS,
report_to_payment AS LTTR_REPORT_TO_PAYMENT ,
acct_open_date AS LTTR_ACCOUNT_OPEN_DATE,
withdrawal_charge AS LTTR_WITHDRAWAL_CHRG

FROM XMLTable(XMLNAMESPACES(default 'urn:trg.payment.fs.evry.com:domain:trg-booking:v1_0','urn:trg.payment.fs.evry.com:domain:trg-booking:v1_0' AS "p",
'urn:trg.payment.fs.evry.com:ws:trg-internal-booking-ws-types:v1_0' AS "tns",
'urn:trg.payment.fs.evry.com:domain:trg-booking-iso:v1_0' AS "i",
' http://edb.com/ws/WSCommon_v21' AS "a",'urn:trg.payment.fs.evry.com:domain:trg-common:v1_0' AS "c"),
'/tns:bookingExecuteRequest/tns:SnglPstg' passing xmltype( bfilename('DUMP','test2.xml') , nls_charset_id('AL32UTF8') ) COLUMNS -- LTTR_TRANSACTION_ID VARCHAR2(20) LTE_TRANSACTION_SEQ.NEXTVAL
InstrId VARCHAR2(200) path 'p:RefId/InstrId',
EndToEndId VARCHAR2(200) path 'p:RefId/EndToEndId',
TxId VARCHAR2(200) path 'p:RefId/TxId',
ResId VARCHAR2(200) path 'p:RefId/ResId',
MhId VARCHAR2(200) path 'p:RefId/MhId',
PwhId VARCHAR2(200) path 'p:RefId/PwhId',
TgId VARCHAR2(200) path 'p:RefId/TgId',
Tp VARCHAR2(200) path 'p:RefId/ExtRef/Tp',
Ref VARCHAR2(200) path 'p:RefId/ExtRef/Ref',
org_id VARCHAR2(20) path 'p:AcctHldgAgt/i:FinInstnId/i:Othr/i:Id',
loc_cur_code VARCHAR2(20) path 'p:AmtDtls/p:TxAmt/i:CcyXchg/i:SrcCcy',
org_cur_code VARCHAR2(20) path 'p:AmtDtls/p:TxAmt/i:CcyXchg/i:TrgtCcy',
acct_cur_code VARCHAR2(20) path 'p:AmtDtls/p:TxAmt/i:CcyXchg/i:UnitCcy',
acct_number VARCHAR2(20) path 'p:Acct/p:Id/p:Othr/i:Id',
book_date TIMESTAMP DEFAULT sysdate,
value_date TIMESTAMP path 'p:DtTm/p:ValDt',
tran_amt_org NUMBER(20) path 'p:AmtDtls/p:TxAmt/i:Amt',
tran_amt_acct NUMBER(20) path 'AmtDtls/CntrValAmt/Amt',--
tran_amt_loc NUMBER(20) path 'AmtDtls/PrtryAmt/Amt ', --
exchang_rate NUMBER(20) path 'p:AmtDtls/p:TxAmt/i:CcyXchg/i:XchgRate',
correction_code VARCHAR2(20) path 'p:Corr/p:Cd',
execution_time VARCHAR2(20) path 'DtTm/TxDtTm/Tm',--
transction_ch_type VARCHAR2(20) DEFAULT 'OTHER',
transction_service_type VARCHAR2(20) DEFAULT 'APPL',
contra_acct VARCHAR2(20) DEFAULT '123',
execution_status VARCHAR2(20) DEFAULT 'A',
report_to_payment VARCHAR2(20) DEFAULT 'Y',
acct_open_date TIMESTAMP DEFAULT sysdate,
withdrawal_charge VARCHAR2(20) DEFAULT 'Y' ));
EXCEPTION
WHEN OTHERS THEN
Raise;
End;


Error report -
No more data to read from socket

Hi Tom,

We have one function and procedure, these things i have put in package and executing .. The function returns the return value 1 or 0 .. When the function returns 1 the procedure should execute other wise should not execute.

Please provide me an example on this.

SQL> create or replace
  2  package PKG is
  3    procedure P;
  4    function F return number;
  5  end;
  6  /

Package created.

SQL>
SQL>
SQL> create or replace
  2  package body PKG is
  3    procedure P is
  4    begin
  5       if F = 1 then
  6          dbms_output.put_line('I am doing some work');
  7       else
  8          dbms_output.put_line('I skipped some work');
  9       end if;
 10    end;
 11    function F return number is
 12    begin
 13       if to_char(sysdate,'SS') < 30 then
 14          return 1;
 15       else
 16          return 0;
 17       end if;
 18    end;
 19  end;
 20  /

Package body created.

SQL>
SQL>
SQL> set serverout on
SQL> exec pkg.p;
I am doing some work

PL/SQL procedure successfully completed.

[then I waited 30 seconds]

SQL> exec pkg.p;
I skipped some work

PL/SQL procedure successfully completed.

SQL>