Oracle tricky queries

How to write a CLOB with a length > 40K out to a file?

There is a database limit which only allows at the most 32760 characters to be written at one time.
Any data greater than that size will have to be written in chunks.

Another database limit (with databases version less than 10g) requires a "new line" to be issued
for each 32K of data written to a file.

The following illustrates two examples that write out 84K of data.

The first example can be used within a 9i or 10g database and illustrates using the "new line" for
each 32K of data.

The samples require that you have created the directory alias named 'ORAFILES_OUTPUT' and reference an existing directory with write privileges. 

Sample1

Main Procedure
create or replace PROCEDURE clob_to_file (p_dir IN VARCHAR2, p_file IN VARCHAR2, p_clob IN CLOB) IS
 l_output UTL_FILE.file_type;
 l_amt NUMBER DEFAULT 32000;
 l_offset NUMBER DEFAULT 1;
 l_length NUMBER DEFAULT NVL (DBMS_LOB.getlength (p_clob), 0);
 x varchar2(32760);
BEGIN
 l_output := UTL_FILE.fopen (p_dir, p_file, 'w', 32760);
 WHILE (l_offset < l_length) LOOP
  dbms_lob.read (p_clob, l_amt, l_offset, x);
  UTL_FILE.put (l_output, x);
  UTL_FILE.fflush (l_output);
  UTL_FILE.new_line (l_output);
  l_offset := l_offset + l_amt;
 END LOOP;
 UTL_FILE.fclose (l_output);
END clob_to_file;
/



Anonymous block to create the data and call the above procedure


DECLARE
 v_Clob varchar2(4000);
 t_Clob CLOB;
BEGIN
 dbms_lob.createtemporary(t_clob,true,dbms_lob.session);
 FOR i IN 0 .. 20 LOOP
  select RPAD (i, 4000, i) into v_clob from dual;
  dbms_lob.write(t_clob, 4000, ((4000 * i) + 1), v_clob);
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GETLENGTH(T_CLOB));
 clob_to_file ('ORAFILES_OUTPUT', 'clob_test9i.txt', t_clob);
END;
/

 Results of Sample1

After executing the above code, navigate to the directory location referenced by the directory alias and check for a file named clob_test9i.txt.
> ls -l clob_test9i.txt
-rw-r--r-- 84003 clob_test9i.txt

The total data is 84000, the output file is 84003. This shows there were 3 writes to the file and
the 3 extra characters are due to the NEW_LINE comma

Sample 2 - Main Procedure

Open a file in binary mode is new to the 10g database version and allows the data to be written in
RAW form and eliminates the need for the "new line" as shown in the previous example.


create or replace PROCEDURE clob_to_file (p_dir IN VARCHAR2, p_file IN VARCHAR2, p_clob IN CLOB) IS
 l_output UTL_FILE.file_type;
 l_amt NUMBER DEFAULT 32000;
 l_offset NUMBER DEFAULT 1;
 l_length NUMBER DEFAULT NVL (DBMS_LOB.getlength (p_clob), 0);
 x varchar2(32760);
BEGIN
 l_output := UTL_FILE.fopen (p_dir, p_file, 'wb', 32760);
 WHILE (l_offset < l_length) LOOP
  dbms_lob.read (p_clob, l_amt, l_offset, x);
  UTL_FILE.PUT_raw(l_output, utl_raw.cast_to_raw(x), TRUE);
  UTL_FILE.fflush (l_output);
  l_offset := l_offset + l_amt;
 END LOOP;
 UTL_FILE.fclose (l_output);
END clob_to_file;
/


Anonymous block to create the data and call the above procedure



DECLARE
 v_Clob varchar2(4000);
 t_Clob CLOB;
BEGIN
 dbms_lob.createtemporary(t_clob,true,dbms_lob.session);
 FOR i IN 0 .. 20 LOOP
  select RPAD (i, 4000, i) into v_clob from dual;
  dbms_lob.write(t_clob, 4000, ((4000 * i) + 1), v_clob);
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GETLENGTH(T_CLOB));
 clob_to_file ('ORAFILES_OUTPUT', 'clob_test10.txt', t_clob);
END;
/
RESULTS
After executing the above code, navigate to the directory location referenced by the directory alias and check for a file named clob_test10.txt.
> ls -l clob_test10.txt
-rw-r--r-- 84000 clob_test10.txt

Since there are no "new line" commands added to the file, the database written is exactly the same
as the data originally created. Essentially, no extra character

Transpose of oracle columns 

How to achieve data of the table in transpose format 

Original table data 

SELECT Column1, Column2, Column3, Column4 FROM Temp;

	Column1	Column2	Column3	Column4
1	1	ABC	PQR	1-Jan-13
2	2	DEF	XYZ	1-Feb-13

Required data # Column to Row Transpose

	Column_Name	Column_Value
1	Column1	1
2	Column2	ABC
3	Column3	PQR
4	Column4	1-Jan-13
5	Column1	1
6	Column2	DEF
7	Column3	XYZ
8	Column4	1-Feb-13

Query # 

SELECT

  xmlpvr.attr COLUMN_NAME,

  xmlpvr.val COLUMN_VALUE

  FROM xmltable ('ROWSET/ROW/*' passing dbms_xmlgen.getxmltype(

  'SELECT

  PVR.CREATION_DATE,

  PVR.ASSIGNMENT_ID,

  PVR.EFFECTIVE_START_DATE,

  PVR.EFFECTIVE_END_DATE,

  PVR.SUPERVISOR_ID 

FROM PER_ALL_PEOPLE_F PVA,

  PER_ALL_ASSIGNMENTS_F PVR

WHERE PVA.PERSON_ID = PVR.PERSON_ID

  AND PVR.ASSIGNMENT_ID = '||:P_ASSIGNMENT_ID ||'

  AND PVA.EFFECTIVE_START_DATE BETWEEN PVR.EFFECTIVE_START_DATE AND PVR.EFFECTIVE_END_DATE

  AND TRUNC(PVR.CREATION_DATE) BETWEEN '''||:p_start_date||''' AND '''||:p_end_date||''''

  ) columns attr VARCHAR2(4000) path 'name()', val VARCHAR2(4000) path 'data(.)' ) xmlpvr;

How to retrieve/assign  system timestamp in Oracle 

DECLARE
a   DATE;
b   TIMESTAMP WITH TIME ZONE := systimestamp;
begin
a := b;
dbms_output.put_line(a);
dbms_output.put_line(b);
end;

Result 

09-JAN-08
09-JAN-08 02.37.02.339176 PM +01:00


How to get 10 sequential dates using a single sql query.

SELECT (SYSDATE) +level-1 each_day FROM DUAL CONNECT BY LEVEL <= 10

0/p:

18-DEC-13

19-DEC-13

20-DEC-13

21-DEC-13

22-DEC-13

23-DEC-13

24-DEC-13

25-DEC-13

26-DEC-13

27-DEC-13

        Alternate query ...

select to_date(trunc(sysdate),'dd-mon-yyyy') + rownum -1

      from all_objects

0/p:

18-DEC-13

19-DEC-13

20-DEC-13

21-DEC-13

22-DEC-13

23-DEC-13

24-DEC-13

25-DEC-13

26-DEC-13

27-DEC-13

28-DEC-13

29-DEC-13

30-DEC-13

Calculate age from HIREDATE to system date of system

SELECT HIREDATE||'        '||SYSDATE||'       ' ||

TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) ||' YEARS '||

TRUNC((MONTHS_BETWEEN(SYSDATE,HIREDATE))-(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)*12))||

'MONTHS' AS "AGE  "  FROM EMP;

How to customize the standard Format Payments program while upgrading to Release 12 form 11i(11.5.10.2 or earlier).

How to customize the standard Format Payments program while upgrading to Release 12 form 11i

Payment Processing in Release 11i

Payment Processing in Release 12

Multiple Applications leverage ONE payment processing solution

Oracle Payments:

Oracle Payments is a central engine to disburse and capture payments. 

•For electronic payments, it connects Oracle EBS products like Receivables and Payables to financial institutions like banks and credit card processors.

•Better working capital management by providing real-time visibility to cash inflows and outflows

•Provides a full audit trail and control to a single point of payment processing

•Efficiently Generate Payments for Multiple Organizations, Currencies and Regions

• Lower Costs associated with Transmission Protocol/ Formats

• Interfaces EBS Products with Financial Institutions like Banks and Credit Card Processors

Payments Bank Account Information:

Funds Disbursement:

In R12 Funds Disbursement Moved from Payables to new Payment module, the Payments module has replaced the previous Payables functionality and has re implemented all the templates using XML Publisher.

But from the technical and work-load perspective, the upgrade to Payments needs to re-implement any customizations to your payment instructions and you should know XML Publisher

Creation & validation of payments

Aggregation of payments into files

Format & transmission of files

Selection & approval of invoices remains in

Payables

R12  payment format steps

R12 Oracle Payment Processing Request (PPR) in AP 

PAYMENT PROCESSING REQUEST FUNCTIONALITY-

In 11i we used Payment batches to pay for multiple invoices same time. In R12, PPR is the replacement of Payment batches. R12 PPR process enables payment Administrator to select multiple invoices for payment by selection criteria and he can pause the invoice selection and payment build process. During the invoice selection review, payment manager can review the invoice selected; if the invoices were validated or approved and hence did not get included in the payment process request. He can add or remove the invoices in the Payment process and also can check the cash requirements for the full payment. Payment manager can also dismiss the individual documents or payments if necessary, and restart the payment build process.

Steps in Pay run Process-

Managing a Pay run involves 3 main processes

• Selection of the invoices for payment
• Grouping the invoices into payments
• Building the payment instruction files to either print checks or send instructions to bank.

 There are four steps in the processing of PPR:-

• Document selection – Handled by Payables(AP)
• Build Payments – Handled by Payments(IBY)
• Format Payments – Handled by Payments(IBY)
• Confirm Payments – Handled by Payables(AP)

How to Customize R12 Payments Output (checks, ACH, positive pay, seperate remittance advice) 

In R12 the formatting of payments (Checks, ACH, wires) and the output of related documents (positive pay, Separate Remittance Advice) is done by a Java concurrent programs that generate XML output which is then formatted by BI Publisher.

Oracle Payments provides the IBY_FD_EXTRACT_EXT_PUB extensibility package to construct custom XML element structure that can be added to the payment XML extract generated by Oracle Payments.
The package specification and body definition can be found in files ibyfdxes.pls and ibyfdxeb.pls respectively.

These files are located in the $IBY_TOP/patch/115/sql directory. The package allows custom elements to be created at five levels within the payment XML extract. You cannot customize the package specification, but the package body contains stubbed functions that you can customize.

The five functions are as follows:
Instruction - FUNCTION Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER) RETURN XMLTYPE This function allows XML element to be introduced at instruction level and run only once for the instruction.

Payment - FUNCTION Get_Pmt_Ext_Agg(p_payment_id IN NUMBER) RETURN XMLTYPE This function allows XML element to be introduced at payment level and run once for each payment in the instruction.

Document Payable - FUNCTION Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER) RETURN XMLTYPE This function allows XML element to be introduced at document payable level and run once for each document payable in the instruction.

Document Payable Line -FUNCTION Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER) RETURN XMLTYPE This function allows XML element to be introduced at document payable line level and run once for each document payable line in the instruction.

Payment Process Request - FUNCTION Get_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER) RETURN XMLTYPE This function allows XML element to be introduced at document payable level and run once for each payment process request.

Adding new fields to the R12 Payment 

IBY_FD_EXTRACT_EXT_PUB is a standard PL/SQL package that is used to extend (i.e. add additional tags to) the XML file generated during a R12 Oracle Payments ‘Payment Process Request’:

This XML file is then used as the data source for the XML Publisher cheque or electronic file presentation layout.

To understand how to use IBY_FD_EXTRACT_EXT_PUB, we have to understand the structure of the XML file created by the Payments process request.

There are 4 main levels to the file. These are:

Top Level: Outbound Payment InstructionThis is the top level of the XML File and there is one Outbound Payment Instruction per Payment process request.

Level 2: Outbound Payment:
This is the Payment Level i.e. an individual cheque or BACS payment amount to a supplier. There can be multiple Outbound Payments per Outbound Payment Instruction.

Level 3: Document Payable:
Details the documents (i.e. invoices) being paid. There can be multiple Document Payable tags per Outbound Payment

Level 4: Document Payable Line:This level details the invoice line. There can be multiple Document Payable Line tags per Document Payable.

Additional XML tags can be added at each of these 4 levels by coding different PL/SQL functions in IBY_FD_EXTRACT_EXT_PUB.

The following table lists the functions you need to modify to add additional tags to each level of the XML file:

ML File Level	IBY_FD_EXTRACT_EXT_PUB Function To Modify	Example of Parameter Usage
OutboundPaymentInstruction	Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER)	SELECT * FROM iby_pay_instructions_all WHERE payment_instruction_id = p_payment_instruction_id;
OutboundPayment	Get_Pmt_Ext_Agg(p_payment_id IN NUMBER)	SELECT * FROM iby_payments_all ipa WHERE ipa.payment_id = p_payment_id;
DocumentPayable	Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER)	SELECT * FROM iby_docs_payable_all dp WHERE dp.document_payable_id = P_document_payable_id;
DocumentPayableLine	Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER)
PaymentProcessProfile	Get_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER)	SELECT * FROM iby_pay_service_requests WHERE payment_service_request_id = p_payment_service_request_id;

Sample Code  where function had been extended Get_Pmt_Ext_Agg


FUNCTION Get_Pmt_Ext_Agg(
                          p_payment_id IN NUMBER)
  RETURN XMLTYPE

  L_INS_EXT_AGG XMLTYPE;
  l_inv_string          XMLTYPE         := NULL;
  l_output_str          VARCHAR (3000);
     
 CURSOR pay_docs (
                   p_pmnt_id                           NUMBER
                    )
      IS
         SELECT   sno
                , doc_ref_num
                , payment_id
                , payment_date
                , payment_amount
                , discount_taken
                , invoice_id
                , invoice_num
                , description
                , po_number
             FROM XYZ_TABLE

      lrec_pay_docs   pay_docs%ROWTYPE;            
 
BEGIN

    BEGIN

      FOR lrec IN pay_docs (p_payment_id)
      LOOP
            l_output_str               := rtrim(RPAD (lrec.doc_ref_num, 21)
                                          || LPAD (' ', 1)
                                          || Lpad (To_Char (Lrec.Invoice_Num, 'DD-MON-YY'), 9) || ' '
                                          || RPAD (NVL (lrec.description, ' '), 20) || LPAD (' ', 1)
                                          || RPAD (NVL (lrec.po_number, ' '), 13)|| LPAD (' ', 1)
                                          || LPAD (TRIM (TO_CHAR (lrec.payment_amount, '9,999,999,990.00'))
                                                 , 13
                                                  ));

         IF pay_docs%rowcount = 1
         THEN
            SELECT XMLELEMENT ("ChpPmtDtl", XMLELEMENT ("InvDocDtls", l_output_str))
              INTO l_inv_string
              FROM DUAL;
         ELSE
 
            SELECT XMLCONCAT (l_inv_string
                            , XMLELEMENT ("ChpPmtDtl", XMLELEMENT ("InvDocDtls", l_output_str))
                             )
              INTO l_inv_string
              FROM DUAL;
         END IF;
     
      END LOOP;

      SELECT XMLCONCAT (l_inv_string,l_ins_ext_agg)
        INTO l_ins_ext_agg
        FROM DUAL;    
     END;

  RETURN L_INS_EXT_AGG;
END Get_Pmt_Ext_Agg;

Query to retrieve the XML tags fro the above payment 
SELECT  *  FROM iby_trxn_documents WHERE 1 = 1   AND payment_instruction_id = '133591'
/

Testing e Text  Templates.

You can test eText templates using the ‘Template Viewer’ included with the BIP desktop toolkit.

From the Windows Start menu, click on : All Programs, Oracle BI Publisher Desktop, Template Viewer:


Once the Template Viewer is open:
select your working directory i.e. the folder containing your eText template and preview XML data

Change the output format to eText :

and then click on the button ‘Start Processing’ to view the generated text file :

Will be adding more details very soon ..