Datatype Limits

Datatypes	Limit	Comments
BFILE	Maximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see Comments	The maximum number of BFILEs is limited by the value of theSESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.
BLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Foot 1 ).
CHAR	Maximum size: 2000 bytes	None
CHAR VARYING	Maximum size: 4000 bytes	None
CLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1).
Literals (characters or numbers in SQL or PL/SQL)	Maximum size: 4000 characters	None
LONG	Maximum size: 2 GB - 1	Only one LONG column is allowed per table.
NCHAR	Maximum size: 2000 bytes	None
NCHAR VARYING	Maximum size: 4000 bytes	None
NCLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1).
NUMBER	999...(38 9's) x10125 maximum value -999...(38 9's) x10125 minimum value	Can be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa)
Precision	38 significant digits	None
RAW	Maximum size: 2000 bytes	None
VARCHAR	Maximum size: 4000 bytes	None
VARCHAR2	Maximum size: 4000 bytes	None

Footnote 1 The absolute maximum number of columns in a table is 1000. However, when you create an object table (or a relational table with columns of object, nested table, varray, or REF type), Oracle maps the columns of the user-defined types to relational columns, creating in effect hidden columns that count toward the 1000-column limit. For details on how Oracle calculates the total number of columns in such a table, refer to Oracle Database Administrator's Guide.

for reference - 

https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits001.htm

How to Use FNDLOAD?

LDT (Data Loader Files)- This files are Used to upload & download setup data on different instances.
LCT (Data Config Files)- This files are used to create LDT Files. There are difference lct files for different sets of data.
Eg: Request Set- afcpreqg.lct, Concurrent Program- afcpprog.lct, Value Set- afffload.lct..etc..
** Now lets take a simple example where you want to migrate 1 concurrent program from Development (DEV) instance to Production (PROD).
Step 1: Login to Unix Development server and change the directory using following Command where you can store your LDT files:cd $CUST_TOP/ patch/115/import/US/ (where CUST_TOP is your custom top on server)
Step 2: Run the following command by supplying the parameters given in {} as per your needFNDLOAD {userid/password@connect_string} O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct {file_name.ldt} PROGRAM APPLICATION_SHORT_NAME="{your_application_short_name}" CONCURRENT_PROGRAM_NAME="{your_concurrent_name}"The above command will generate ldt file in your custom top directory ($CUST_TOP/ patch/115/import/US/ )
Step 3: Copy the LDT file from Development Server to your local desktop
Step 4: Login to Unix Production Server and copy the LDT files from local desktop to Production Server on following path:$CUST_TOP/ patch/115/import/US/ {file_name.ldt}
Step 5: Finally Run the following command by supplying all parameters in {} to upload concurrent program definition on production application server.$ FNDLOAD {userid/password@connect_string} 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $CUST_TOP/patch/115/import/US/{file_name}.ldt
Note: 1) All above FNDLOAD commands are single line commands.2) All above steps will remain exactly same for all kind of setup data (Eg: Value Set, CP, Req.group, DFF…etc) except the name of LCT file (.lct). LCT file will be different for every different component because the setup data is different.

Generate table DDL

set heading off;

set echo off;

Set pages 999;

set long 90000;

 

spool ddl_list.sql

 

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

 

select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;

 

spool off;

Update Contingent Workers Assignment/Criteria

--
declare
   --
   l_mode                      varchar2(10) := 'CRITERIA'; --'ASG'
   --- DECLARE variables for HR_ASSIGNMENT_API.update_emp_asg
   --- IN variables
   l_assignment_id             number := 123456;
   l_normal_hours              number := 37;
   --per_assignment_status_types.assignment_status_type_id%TYPE;
   l_assignment_status_type_id number := 1;
   l_sob_id                    number := 1;
   l_default_expense_ccid      number := 1234;

   --- OUT variables
   l_org_now_no_manager_warning   boolean := false;
   l_no_managers_warning          boolean := false;
   l_other_manager_warning        boolean := false;
   l_hourly_salaried_warning      boolean := false;
   l_comment_id                   number;
   l_effective_start_date         date;
   l_effective_end_date           date;
   l_soft_coding_keyflex_id       number;
   l_concatenated_segments        varchar2(100);

   --- DECLARE variables for HR_ASSIGNMENT_API.update_emp_asg_criteria
   --- IN variables
   l_payroll_id                    number := 125;
   l_organization_id               number := 5678;
   l_position_id                   number := 9904567;
   l_job_id                        number := 3456;
   l_location_id                   number := 12345;
   l_grade_id                      number := 12;

   --- OUT variables
   l_spp_delete_warning           boolean := false;
   l_entries_changed_warning      varchar2(100) default null;
   l_tax_district_changed_warning boolean := false;
   l_people_group_id              number;
   l_people_group_name            varchar2(100);
   --
begin
    --
    if l_mode = 'ASG' then

       /* This API updates contingent worker assignment.
       * The assignment must exist as of the effective date, and must be a contingent
       * worker assignment.
       * updating the record. You must set to either UPDATE, CORRECTION,
       * UPDATE_OVERRIDE or UPDATE_CHANGE_INSERT.
       */
      
       hr_assignment_api.update_cwk_asg
                      (p_validate                     => false --in boolean  default false
                      ,p_effective_date               => trunc(sysdate)            
                      ,p_datetrack_update_mode        => 'CORRECTION'      
                      ,p_assignment_id                => l_assignment_id     
                      ,p_assignment_status_type_id    => l_assignment_status_type_id
                      ,p_default_code_comb_id         => l_default_expense_ccid 
                      ,p_set_of_books_id              => l_sob_id 
                      ,p_normal_hours                 => l_normal_hours                         
                       --In/Out
                      ,p_object_version_number  => l_asg_object_version_number
                       --Out
                      ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
                      ,p_effective_start_date        => l_effective_start_date      
                      ,p_effective_end_date          => l_effective_end_date        
                      ,p_comment_id                  => l_comment_id                
                      ,p_no_managers_warning         => l_no_managers_warning       
                      ,p_other_manager_warning       => l_other_manager_warning     
                      ,p_soft_coding_keyflex_id      => l_soft_coding_keyflex_id    
                      ,p_concatenated_segments       => l_concatenated_segments     
                      ,p_hourly_salaried_warning     => l_hourly_salaried_warning   
                      );  
      
       if (l_org_now_no_manager_warning= true or l_no_managers_warning = true  or l_other_manager_warning = true or l_hourly_salaried_warning = true) then
           dbms_output.put_line('Warning validating API: hr_assignment_api.update_cwk_asg');
           rollback;
       else
          dbms_output.put_line('Success ');
          commit;
       end if;
      
    elsif l_mode = 'CRITERIA' then
    
       /* This API updates attributes of a contingent worker assignment that may
       * affect the entitlement to element entries - currently Contingent Workers may
       * not have element entries so this API is reserved for future use.
       * The API updates the attributes of assignment.
       */
       hr_assignment_api.update_cwk_asg_criteria
                            ( p_effective_date                =>  trunc(sysdate)
                            , p_datetrack_update_mode         =>  'CORRECTION'
                            , p_assignment_id                 =>  l_assignment_id
                            , p_validate                      =>  false --lb_validate_mode
                            , p_called_from_mass_update       =>  false
                            , p_position_id                   =>  l_position_id
                            , p_job_id                        =>  l_job_id
                            , p_location_id                   =>  l_location_id
                            , p_organization_id               =>  l_organization_id
                            -- In/Out
                            , p_object_version_number =>  l_asg_object_version_number
                            -- Out
                            ,p_people_group_name            => l_people_group_name        
                            ,p_effective_start_date         => l_effective_start_date     
                            ,p_effective_end_date           => l_effective_end_date       
                            ,p_people_group_id              => l_people_group_id          
                            ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
                            ,p_other_manager_warning        => l_other_manager_warning    
                            ,p_spp_delete_warning           => l_spp_delete_warning       
                            ,p_entries_changed_warning      => l_entries_changed_warning  
                            ,p_tax_district_changed_warning => l_tax_district_changed_warning
                           );
      
       if (l_org_now_no_manager_warning= true or l_other_manager_warning = true or l_spp_delete_warning = true
          or l_tax_district_changed_warning= true or l_entries_changed_warning is not null) then
          dbms_output.put_line('Warning validating API: hr_assignment_api.update_cwk_asg_criteria');
          rollback;
       else
          dbms_output.put_line('Success ');
          commit;
       end if;
    
    end if ;
        
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);                          
end;
--   

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;