How to load multiple rows into single column in SQL Loader?

Option 1

Sample Table Script

CREATE TABLE XX_SQLLDR_DEMO_STG

(

   RECORD_ID   NUMBER,

   SENDER      VARCHAR2 (120),

   RECEIVER    VARCHAR2 (120),

   TEXT        VARCHAR2 (2000)

);

Sample Control File

LOAD DATA
CHARACTERSET UTF8
INFILE '$DATA_FILE_NAME' CONTINUEIF LAST <> '|'
INTO TABLE XX_SQLLDR_DEMO_STG
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
RECORD_ID "LTRIM(RTRIM(:RECORD_ID))",
SENDER    "LTRIM(RTRIM(:SENDER))",
RECEIVER  "LTRIM(RTRIM(:RECEIVER))",
TEXT      "LTRIM(RTRIM(:TEXT))"

)

Sample Data File

Need to Add the Data / Fields delimiter at the end of each record in Data File.


1|ABCD@XYZ.COM|XYZ@ABCD.COM|Hi XYZ,

how to load Multiple lines as single record in sqlldr?

Thanks & Regards,
ABCD|
2|XYZ@ABCD.COM|ABCD@XYZ.COM|Hi ABCD,

This is test data file for multiple lines loading into a single field.

Thanks & Regards,
xyz|


It will loads the data (replacing newline character) like this...

Option 2

Sample Control File

LOAD DATA
CHARACTERSET UTF8
INFILE '$DATA_FILE_NAME' "STR '~!~'"
INTO TABLE XX_SQLLDR_DEMO_STG
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
RECORD_ID "LTRIM(RTRIM(:RECORD_ID))",
SENDER    "LTRIM(RTRIM(:SENDER))",
RECEIVER  "LTRIM(RTRIM(:RECEIVER))",
TEXT      "LTRIM(RTRIM(:TEXT))"

)

Sample Data File

Need to Add the Data / Fields delimiter with unused string (like this "~!~") at the end of each record in Data File.


1|ABCD@XYZ.COM|XYZ@ABCD.COM|Hi XYZ,

how to load Multiple lines as single record in sqlldr?

Thanks & Regards,
ABCD|~!~
2|XYZ@ABCD.COM|ABCD@XYZ.COM|Hi ABCD,

This is test data file for multiple lines loading into a single field.

Thanks & Regards,
xyz|~!~


It will loads the data as expected...

How to find the locked tables in Oracle?

Execute this Query

SELECT   owner,
         object_name,
         oracle_username || ' (' || vs.status || ')' ora_user,
         os_user_name os_user,
         machine SYSTEM,
         vlo.process unix,
         '''' || vs.SID || ',' || vs.serial# || '''' sid_serial,
         vr.NAME roll_name,
         TO_CHAR (vs.logon_time, 'YYYY/MM/DD HH24:MI:SS') TIME
    FROM v$locked_object vlo,
         dba_objects doj,
         v$session vs,
         v$transaction vt,
         v$rollname vr
   WHERE vlo.object_id = doj.object_id
     AND vs.SID = vlo.session_id
     AND vs.taddr = vt.addr
     AND vt.xidusn = vr.usn
ORDER BY os_user,
         sid_serial,
         object_name

* Pass / Add condition for Object (Table) Name If required.

How to kill a particular session?

ALTER SYSTEM KILL SESSION 'v$session.sid,v$session.serial';

Reports / Executables attached to Responsibilities List

Run this Query

  SELECT frt.responsibility_name,
         frt.description
    FROM fnd_executables fe,
         fnd_concurrent_programs fcp,
         fnd_concurrent_programs_tl fcpt,
         fnd_request_group_units frgu,
         fnd_request_groups frg,
         fnd_responsibility fr,
         fnd_responsibility_tl frt
   WHERE (fcpt.user_concurrent_program_name = 'USER_CONCURRENT_PROGRAM_NAME'
       OR  fe.execution_file_name = 'EXECUTION_FILE_NAME'
       OR  fe.executable_name = 'EXECUTABLE_NAME')
     AND fcpt.concurrent_program_id = fcp.concurrent_program_id
     AND fcpt.language = USERENV ('Lang')
     AND fcp.executable_id = fe.executable_id
     AND fcp.enabled_flag = 'Y'
     AND fcp.concurrent_program_id = frgu.request_unit_id
     AND frg.request_group_id = frgu.request_group_id
     AND fr.request_group_id = frg.request_group_id
     AND frt.responsibility_id = fr.responsibility_id
     AND frt.language = USERENV ('Lang')
ORDER BY frt.responsibility_name,
         frt.description;

* Pass the Executable Name / Executable File Name / User Concurrent Program Name

How to set ORG_ID for MOAC enabled conc. programs using FND_REQUEST.SUBMIT_REQUEST or CONCSUB

Example

1. FND_REQUEST.SUBMIT_REQUEST
[Oracle Doc ID: 1383266.1]

ORG_ID for a concurrent request can be set by using FND_REQUEST.SET_ORG_ID and needs to be set before submitting request using FND_REQUEST.SUBMIT_REQUEST.

FND_REQUEST.SET_ORG_ID(ORG_ID);
FND_REQUEST.SUBMIT_REQUEST(Parameters);

2. CONCSUB
[Oracle Doc ID: 457519.1]

CONCSUB ${1} AR "Receivables Manager" ${3} WAIT=Y CONCURRENT AR ARLPLB ORG_ID=${25} \"${new_trans}\" \"${transmission_id}\" \"${4}\" \"${trans_name}\" \"${10}\" \"${data_file_path}\" \"${13}\" \"${14}\" \"${15}\" \"${16}\" \"${17}\" \"${18}\" \"${19}\" \"${20}\" \"${21}\" \"${22}\" \"${23}\" \"${24}\" \"${25}\" \"${26}\" \"${27}\"

UTL_FILE Utility Basic Setups

DB and Server Setups

1.       The directory path on the application node will need to be mounted/shared on the database node.

2.      The directory path is readable/writable from the database node.

3.       The directory path should have required permissions.

DB Level - Verification SQL

SELECT directory_name,

       directory_path

  FROM sys.dba_directories

WHERE directory_name = 'CUSTOM_DIR';

SELECT grantee,

       privilege,

       table_name

  FROM sys.dba_tab_privs

WHERE table_name = 'CUSTOM_DIR';

Server Level - Verification Command

ls -altr CUSTOM_DIR_PATH

Basic Code with Important Exceptions

DECLARE

   lf_utl_file_id      UTL_FILE.file_type;

   lv_dba_dir_name     VARCHAR2 (30) := 'CUSTOM_DIR';

   lv_data_file_name   VARCHAR2 (80) := 'outbound_file.txt';

   lv_utl_file_mode    VARCHAR2 (1) := 'w';    -- 'w', 'r'

   lv_data_line        VARCHAR2 (2000);

BEGIN

   IF NOT UTL_FILE.is_open (lf_utl_file_id)

   THEN

      lf_utl_file_id      :=

         UTL_FILE.fopen (lv_dba_dir_name,

                         lv_data_file_name,

                         lv_utl_file_mode,

                         32767);

   END IF;

   lv_data_line   := '';             -- Extract Data

   UTL_FILE.put_line ( lf_utl_file_id, lv_data_line);

   UTL_FILE.fclose (lf_utl_file_id);

EXCEPTION

   WHEN UTL_FILE.invalid_mode

   THEN

      raise_application_error ( -20051, 'Invalid Mode Parameter');

   WHEN UTL_FILE.invalid_path

   THEN

      raise_application_error ( -20052, 'Invalid File Location');

   WHEN UTL_FILE.invalid_filehandle

   THEN

      raise_application_error ( -20053, 'Invalid Filehandle');

   WHEN UTL_FILE.invalid_operation

   THEN

      raise_application_error ( -20054, 'Invalid Operation');

   WHEN UTL_FILE.read_error

   THEN

      raise_application_error ( -20055, 'Read Error');

   WHEN UTL_FILE.internal_error

   THEN

      raise_application_error ( -20057, 'Internal Error');

   WHEN UTL_FILE.charsetmismatch

   THEN

      raise_application_error ( -20058, 'Opened With FOPEN_NCHAR, But Later I/O Inconsistent');

   WHEN UTL_FILE.file_open

   THEN

      raise_application_error ( -20059, 'File Already Opened');

   WHEN UTL_FILE.invalid_maxlinesize

   THEN

      raise_application_error ( -20060, 'Line Size Exceeds 32K');

   WHEN UTL_FILE.invalid_filename

   THEN

      raise_application_error ( -20061, 'Invalid File Name');

   WHEN UTL_FILE.access_denied

   THEN

      raise_application_error ( -20062, 'File Access Denied');

   WHEN UTL_FILE.invalid_offset

   THEN

      raise_application_error ( -20063, 'FSEEK Param Less Than 0');

   WHEN OTHERS

   THEN

      raise_application_error ( -20099, 'Unknown UTL_FILE Error');

END;