Script for Download file from FND_LOBS Table

Alternative for FNDGFU utility



DECLARE
  v_inv_image_dir VARCHAR2(100) := 'MYDIR';
  CURSOR cur_new_attmt
  IS
    SELECT fl.file_id,
      fl.file_name,
      fl.file_data,
      dbms_lob.getlength(fl.file_data) file_length
    FROM fnd_lobs fl
    WHERE fl.file_id = 3293571;
  v_start    NUMBER DEFAULT 1;
  v_bytelen  NUMBER DEFAULT 32000;
  v_len_copy NUMBER;
  v_raw_var RAW(32000);
  v_output utl_file.file_type;
  v_inv_file_name VARCHAR2(100);
  v_position      NUMBER;
  v_first_rec     BOOLEAN DEFAULT TRUE;
BEGIN
  v_position := 10;
  FOR rec_inv IN cur_new_attmt
  LOOP
    BEGIN
      v_inv_file_name := NULL;
      v_inv_file_name := rec_inv.file_name;
      v_position      := 20;
      -- define output directory AND OPEN THE file IN WRITE BYTE MODE
      v_output   := utl_file.fopen(p_inv_image_dir, v_inv_file_name, 'wb', 32760);
      v_position := 30;
      -- maximum size OF buffer parameter IS 32767 BEFORE
      -- which you have TO flush  your buffer
      IF rec_inv.file_length < 32760 THEN
        utl_file.put_raw(v_output, rec_inv.file_data);
        utl_file.fflush(v_output);
      ELSE
        v_position   := 40;
        v_start      := 1;
        v_bytelen    := 32000;
        v_len_copy   := rec_inv.file_length;
        WHILE v_start < rec_inv.file_length AND v_bytelen > 0
        LOOP
          v_position := 50;
          dbms_lob.READ(rec_inv.file_data, v_bytelen, v_start, v_raw_var);
          v_position := 60;
          utl_file.put_raw(v_output, v_raw_var);
          v_position := 70;
          utl_file.fflush(v_output);
          v_start      := v_start    + v_bytelen;
          v_len_copy   := v_len_copy - v_bytelen;
          IF v_len_copy < 32000 THEN
            v_bytelen  := v_len_copy;
          END IF;
        END LOOP;
        v_position := 80;
        utl_file.fclose(v_output);
      END IF;
      v_position := 90;
      dbms_output.put_line(' File_name :' || rec_inv.file_name);
    EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(rpad(NVL(v_inv_file_name,'NA'),31) || rpad('ERROR', 21) || 'POSITION: ' || v_position || 'Error :' || SUBSTR(SQLERRM,1,100));
    END;
  END LOOP;
END;

How to List All Tables and Describe Tables in Oracle DB

Connect to the database:

sqlplus username/password@database-name
To list all tables owned by the current user, type:

select tablespace_name, table_name from user_tables;
To list all tables in a database:

select tablespace_name, table_name from dba_tables;
To list all tables accessible to the current user, type:

select tablespace_name, table_name from all_tables;
You can find more info about views all_tables, user_tables, and dba_tables in Oracle Documentation. To describe a table, type:

desc <table_name>

Oracle Apps Receivables (AR) Tables

Transactions
RA_CUSTOMER_TRX_ALL	Transaction Header table
RA_CUSTOMER_TRX_LINES_ALL	Transaction Lines table along with Tax lines.
RA_CUST_TRX_LINE_GL_DIST_ALL	Distribution for Transaction Lines
RA_CUST_TRX_LINE_SALESREPS_ALL	Salesrep information for Transaction Lines
Transaction Interface Tables
RA_INTERFACE_LINES_ALL	Transaction Lines interface
RA_INTERFACE_SALESCREDITS_ALL	Transaction Sales credit information
RA_INTERFACE_DISTRIBUTIONS_ALL	Transaction Distribution information
RA_INTERFACE_ERRORS_ALL	Transaction errors table
AR_PAYMENTS_INTERFACE_ALL	Interface table to import receipts
AR_INTERIM_CASH_RECEIPTS_ALL	Lockbox transfers the receipts that pass validation to the interim tables
AR_INTERIM_CASH_RCPT_LINES_ALL	Lockbox transfers the receipts that pass validation to the interim tables
Receipts tables
AR_CASH_RECEIPTS_ALL	Cash Receipt Header tables
AR_RECEIVABLE_APPLICATIONS_ALL	stores Receipt Application details
AR_PAYMENT_SCHEDULES_ALL	This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable
AR_CASH_RECEIPT_HISTORY_ALL	This table stores all of the activity that is contained for the life cycle of a receipt. • Each row represents one step. • The status field for that row tells you which step the receipt has reached. • Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.
Customer Tables
HZ_PARTIES	A party is an entity that can enter into a business relationship.
HZ_CUST_ACCOUNTS	This table stores information about customer/financial relationships established between a Party and the deploying company.
HZ_PARTY_SITES	This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
HZ_CUST_ACCT_SITES_ALL	This table stores information about customer/financial account sites information.
HZ_CUST_SITE_USES_ALL	This table stores information about the business purposes assigned to a customer account site
HZ_LOCATIONS	A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.
Setup tables
RA_CUST_TRX_TYPES_ALL	This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
AR_RECEIPT_CLASSES	This table stores the different receipt classes that you define.
AR_RECEIPT_METHODS	This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications

Oracle External Tables

External tables can read the flat files as though they are normal oracle tables. Therefore it is convenient to use external tables to load flat files in to DB

SQL Tuning Advisor - DBMS_SQLTUNE

-- Create a tuning task
DBMS_SQLTUNE.create_tuning_task

-- Execute a task
DBMS_SQLTUNE.execute_tuning_task

-- Report of a task
DBMS_SQLTUNE.report_tuning_task

-- Interrupt a tuning task.
DBMS_SQLTUNE.interrupt_tuning_task

-- Resume a tuning task.
DBMS_SQLTUNE.resume_tuning_task

-- Cancel a tuning task.
DBMS_SQLTUNE.cancel_tuning_task

-- Reset a tuning task allowing it to be re-executed.
DBMS_SQLTUNE.reset_tuning_task

-- Cancel a tuning task.
 DBMS_SQLTUNE.cancel_tuning_task

--Check the status of a task
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';

TASK_NAME                      STATUS
------------------------------ -----------
emp_dept_tuning_task           COMPLETED

1 row selected.

Optimisation

When we go for optimisation, we need to think about

1. Index
2. Explain Plan
3. Analyse table
4. Response time etc

DBMS_METADATA.GET_DDL

Select DBMS_METADATA.GET_DDL('object type', 'object name', ' owner')
From dual;

Try this