Home » RDBMS Server » Server Utilities » sql loader
sql loader [message #592840] Mon, 12 August 2013 12:09 Go to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
I have a scenario where the data file has values separated by ^.While loading this data into the table using sql loader I want to convert it into multiple lines.eg:

data file:

1|1013 park ridge~12345~irving|

2|2013 park ridge~12345~irving|

3|1013 park ridge|




while loading it into table i want the data like




ID ADDRESS

1 "1013 park ridge

12345

irving"

2 "2013 park ridge

12345

irving"

.....

My control file says:


load data
infile "/usr2/home2/adistest/h91ftp/temp/owb_test/owb_test1.csv"
preserve blanks INTO TABLE owbrep.owb_test1

TRUNCATE
fields terminated by '|' TRAILING NULLCOLS
(
id,
address
)



Please suggest what should I do?

[Updated on: Mon, 12 August 2013 12:10]

Report message to a moderator

Re: sql loader [message #592841 is a reply to message #592840] Mon, 12 August 2013 12:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQLLOADER does not manipulate the data being loaded.
Consider doing the same via EXTERNAL TABLE REPLACE the tilde character with a newline charater

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: sql loader [message #592844 is a reply to message #592841] Mon, 12 August 2013 12:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I posted the following on the OTN forums:

https://forums.oracle.com/thread/2568886

You can use the REPLACE function in your control file to replace whatever character, like ~ in your sample data, is where the newline should be with whatever your newline is on your system, like chr(10) as in the example below.

SCOTT@orcl12c_11gR2> host type owb_test1.csv
1|1013 park ridge~12345~irving|
2|2013 park ridge~12345~irving|
3|1013 park ridge|

SCOTT@orcl12c_11gR2> host type test.ctl
load data
infile "owb_test1.csv"
preserve blanks INTO TABLE owb_test1
TRUNCATE
fields terminated by '|' TRAILING NULLCOLS
(
id,
address "REPLACE (:address, '~', CHR(10))"
)

SCOTT@orcl12c_11gR2> create table owb_test1
  2    (id       number,
  3     address  varchar2(60))
  4  /

Table created.

SCOTT@orcl12c_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SQL*Loader: Release 12.1.0.1.0 - Production on Mon Aug 12 10:38:10 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 3

Table OWB_TEST1:
  3 Rows successfully loaded.

Check the log file:
  test.log
for more information about the load.

SCOTT@orcl12c_11gR2> select * from owb_test1
  2  /

        ID ADDRESS
---------- ------------------------------------------------------------
         1 1013 park ridge
           12345
           irving

         2 2013 park ridge
           12345
           irving

         3 1013 park ridge

3 rows selected.


[Updated on: Mon, 12 August 2013 12:43]

Report message to a moderator

Re: sql loader [message #592878 is a reply to message #592841] Mon, 12 August 2013 17:05 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
My problem is not with the sql loader.It is behaving correctly.My problem is the above procedure using utl_file which writes wrong date format to the csv file and it also truncates the time from the date.Please suggest.
Re: sql loader [message #592880 is a reply to message #592878] Mon, 12 August 2013 17:11 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
Similarly..
source table had:10/8/0007 9:52:45 AM
utl_file wrote in csv file:08-OCT-07(so utl_file is not behaving correctly)
So my sql loader treated as 10/8/2007


Re: sql loader [message #592881 is a reply to message #592878] Mon, 12 August 2013 17:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Mandeep2910 wrote on Mon, 12 August 2013 15:05
My problem is not with the sql loader.It is behaving correctly.My problem is the above procedure using utl_file which writes wrong date format to the csv file and it also truncates the time from the date.Please suggest.


I guess I need new glasses since I can't see any procedure; above or elsewhere.

>source table had:10/8/0007 9:52:45 AM
How are we supposed to know/see such details?


Re: sql loader [message #592882 is a reply to message #592881] Mon, 12 August 2013 17:20 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
Sorry.I pasted but it didnt get submitted earlier.
CREATE OR REPLACE PROCEDURE owb_prc_file_mult_column_gen(
p_file_dir         VARCHAR2, -- mandatory (Oracle directory name)
p_file_name   VARCHAR2, -- mandatory
p_sql_query        VARCHAR2, -- Multiple column SQL SELECT statement that needs to be executed and processed
p_delimiter   CHAR    -- column delimiter
)
AS

l_cursor_handle  INTEGER;
l_dummy          NUMBER;
l_col_cnt        INTEGER;
l_rec_tab          DBMS_SQL.DESC_TAB;
l_current_col      NUMBER(16);
l_current_line   VARCHAR2(2047);
l_column_value   VARCHAR2(32767);

l_file_handle      UTL_FILE.FILE_TYPE;
l_print_text       VARCHAR2(32767);
l_record_count   NUMBER(16) := 0;

BEGIN

   /* Open file for append*/
   l_file_handle := UTL_FILE.FOPEN(p_file_dir, p_file_name, 'w', 32767); --Append Mode, 2047 chars per line max, possibly increasable

   l_cursor_handle := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(l_cursor_handle, p_sql_query, DBMS_SQL.native);
   l_dummy := DBMS_SQL.EXECUTE(l_cursor_handle);

   /* Output column names and define them for latter retrieval of data */
   DBMS_SQL.DESCRIBE_COLUMNS(l_cursor_handle, l_col_cnt, l_rec_tab); -- get column names

   /* Append to file column headers */
   l_current_col := l_rec_tab.FIRST;
   IF (l_current_col IS NOT NULL) THEN
      LOOP
         DBMS_SQL.DEFINE_COLUMN(l_cursor_handle, l_current_col, l_column_value, 32767);
         l_print_text := l_rec_tab(l_current_col).col_name || p_delimiter;
         UTL_FILE.PUT (l_file_handle, l_print_text);
         l_current_col := l_rec_tab.NEXT(l_current_col);
         EXIT WHEN (l_current_col IS NULL);
      END LOOP;
   END IF;
   UTL_FILE.PUT_LINE (l_file_handle,' ');

   /* Append data for each row */
   LOOP
      EXIT WHEN DBMS_SQL.FETCH_ROWS(l_cursor_handle) = 0; -- no more rows to be fetched

      l_current_line := '';
      /* Append data for each column */
      FOR l_current_col IN 1..l_col_cnt LOOP
         DBMS_SQL.COLUMN_VALUE (l_cursor_handle, l_current_col, l_column_value);
         l_print_text := l_column_value || p_delimiter;

       l_current_line := l_current_line || replace(l_column_value,chr(13)||chr(10),'^') || p_delimiter;
      END LOOP;
      l_record_count := l_record_count + 1;
      UTL_FILE.PUT_LINE (l_file_handle, l_current_line);
   END LOOP;

   UTL_FILE.FCLOSE (l_file_handle);
   DBMS_SQL.CLOSE_CURSOR(l_cursor_handle);

EXCEPTION
   WHEN OTHERS THEN

   -- Release resources
   IF DBMS_SQL.IS_OPEN(l_cursor_handle) THEN
      DBMS_SQL.CLOSE_CURSOR(l_cursor_handle);
   END IF;

   IF UTL_FILE.IS_OPEN (l_file_handle) THEN
      UTL_FILE.FCLOSE (l_file_handle);
   END IF;

   --RAISE ;
   DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_stack);

END owb_prc_file_mult_column_gen;


*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Mon, 12 August 2013 17:34] by Moderator

Report message to a moderator

Re: sql loader [message #592883 is a reply to message #592882] Mon, 12 August 2013 17:21 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
So the problem is


source table had:10/8/0007 9:52:45 AM
utl_file wrote in csv file:08-OCT-07(so utl_file is not behaving correctly)
So my sql loader treated as 10/8/2007
Re: sql loader [message #592884 is a reply to message #592883] Mon, 12 August 2013 17:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


Since we don't have your tables or data, we can't run, test, debug, or improve posted code.
Re: sql loader [message #592886 is a reply to message #592884] Mon, 12 August 2013 23:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The problem is probably in the value of the p_sql_query parameter that is passed. If it selects a date column, then the default nls_date_format is probably being applied, instead of using to_char to get the desired format.
Re: sql loader [message #592887 is a reply to message #592886] Mon, 12 August 2013 23:41 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The second problem has nothing to do with the first and was apparently posted on the wrong thread and refers to the problem and procedure in this thread:

http://www.orafaq.com/forum/t/189006/0/unread/43710/#msg_592877
Previous Topic: IMPDP struck in 11g @ VIEW
Next Topic: About RESUMABLE, RESUMABLE_NAME parameter
Goto Forum:
  


Current Time: Thu Mar 28 14:56:00 CDT 2024