Home » SQL & PL/SQL » SQL & PL/SQL » utl_file (oracle 11 g)
utl_file [message #592612] Fri, 09 August 2013 18:48 Go to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
Hi,
I have a question.
The problem is I have newline characters in my varchar column.And when I write them in .csv file,it doesn't come in single cell .Instead it splits into multiple lines where ever it finds new line character.How should I resolve this?I want the result in single cell.Please help.
Re: utl_file [message #592614 is a reply to message #592612] Fri, 09 August 2013 19:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You can use the REPLACE function to replace the newline character with a space or whatever you like.
Re: utl_file [message #592621 is a reply to message #592614] Fri, 09 August 2013 23:12 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
How can I do that in my utl_file procedure before I write my data to the csv file?

CREATE OR REPLACE PROCEDURE owb_prc_file_mult_column_test(
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;
         dbms_output.put_line(l_print_text);
         --UTL_FILE.PUT (l_file_handle, l_print_text);
         UTL_FILE.new_line (l_file_handle, l_print_text);
         l_current_col := l_rec_tab.NEXT(l_current_col);
         dbms_output.put_line(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;
dbms_output.put_line(l_print_text);
       l_current_line := l_current_line || l_column_value || p_delimiter;
       dbms_output.put_line(l_current_line);
      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;


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

[Updated on: Fri, 09 August 2013 23:21] by Moderator

Report message to a moderator

Re: utl_file [message #592622 is a reply to message #592621] Fri, 09 August 2013 23:24 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
The "flexible" design GREATLY complicates adding the new requirement into the exist procedure.

Can the data within the DB be permanently changed prior to invoking the posted procedure?

If not, how does the posted code know/decide in which column the offending newline character resides?
Re: utl_file [message #592623 is a reply to message #592622] Fri, 09 August 2013 23:29 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
That is my problem.I am reading 200 tables so wherever in the data I have new line character,can I replace it with some other character so that while writing it into the csv file it doesnt create multiple lines for the offending new line characters.And then in sql loader I replace the character back to the new line character to load the data into the tables?
Re: utl_file [message #592624 is a reply to message #592623] Fri, 09 August 2013 23:36 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Do not allow newline characters to be stored within the DB & the problem is solved.
Re: utl_file [message #592625 is a reply to message #592624] Fri, 09 August 2013 23:43 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
I cannot change the data.Its a production data and it has to be like that only.Lets say we have a person's address so it is entered this way
1234 south lane
detroit
michigan
123456

I want these four lines of address column in just one cell of excel sheet.
Re: utl_file [message #592626 is a reply to message #592625] Fri, 09 August 2013 23:52 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Mandeep2910 wrote on Fri, 09 August 2013 21:43
I cannot change the data.Its a production data and it has to be like that only.Lets say we have a person's address so it is entered this way
1234 south lane
detroit
michigan
123456

I want these four lines of address column in just one cell of excel sheet.


conceptually the problem is a simple one.
REPLACE all the newline characters with a space character.

The challenge is that REPLACE requires you to identify the the table_name & column_name which contains the newline character.
Can you produce a hardcoded list of table names & column names which can contain newline characters?
If not, then you live with what you have now!
Re: utl_file [message #592627 is a reply to message #592626] Fri, 09 August 2013 23:55 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
Ok.I can have the list of all those tables and columns which have this offending new line character.So can you tell me how and where I should apply this replace function in my above program.Lets say I have address table with address column which has this offending new line character.
Re: utl_file [message #592628 is a reply to message #592627] Sat, 10 August 2013 00:01 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
l_print_text := l_column_value || p_delimiter;

->
l_print_text := replace(l_column_value,chr(10),' ') || p_delimiter;

Regards
Michel
Re: utl_file [message #592630 is a reply to message #592627] Sat, 10 August 2013 00:05 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
When table with newline is being processed by owb_prc_file_mult_column_test, then do REPLACE against column_name that contains newline.
Re: utl_file [message #592633 is a reply to message #592628] Sat, 10 August 2013 00:51 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
I have replaced it as
l_current_line := l_current_line ||replace(l_column_value,chr(10),'***') || p_delimiter;

but still it appears in different line with the value as

0000037179|1|00000000|00000000|H|I|N| | | | | |2840 Riderwood Dr| | | |Decatur|GA|30033-1533| | | | |11-OCT-12|N|0||| |00000000|0|UNI| | |N|V| |2840 Riderwood Dr|Decatur, GA 30033-1533| | | | | | |phone number is home phone in Decatur GA
***This address is the preferred address from 4/1/0000 to 12/31/0000
***Phone listed previously was 941-492-9258|01-JAN-55|11-OCT-12|griff387|S5||

Now what should I do?
Re: utl_file [message #592634 is a reply to message #592633] Sat, 10 August 2013 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check if you have not another character like chr(13).

Regards
Michel
Re: utl_file [message #592635 is a reply to message #592634] Sat, 10 August 2013 01:08 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
Since it is replacing chr(10) with ***,doesnt that mean that it has only chr(10) character?Is there any way to find what characters in hex are stored in the database column contents?
Re: utl_file [message #592636 is a reply to message #592635] Sat, 10 August 2013 01:10 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
I found it.It is .. character.So how should I replace this now?
Re: utl_file [message #592637 is a reply to message #592636] Sat, 10 August 2013 01:14 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
I have seen the hexa decimal for .. is "0D0A".Now how should I replace it?
Re: utl_file [message #592638 is a reply to message #592637] Sat, 10 August 2013 01:34 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
Ok.So its resolved now.It was a concatenation of chr(13) and chr(10).Thanks for help.
Re: utl_file [message #592877 is a reply to message #592621] Mon, 12 August 2013 16:29 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
Ok.So the problem is resolved.But when I generate a csv file using the below code..my dates format gets changed.It displays wrong date..eg. 1/1/1855 changes to '01-JAN-55'.And when I load this csv agn using sql loader ,it gets loaded as '1/1/1955'.Please help.





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;


[Updated on: Mon, 12 August 2013 16:30]

Report message to a moderator

Re: utl_file [message #592885 is a reply to message #592877] Mon, 12 August 2013 17:50 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
>Ok.So the problem is resolved.But when I generate a csv file using the below code..my dates format gets changed.
>It displays wrong date..eg. 1/1/1855 changes to '01-JAN-55'.And when I load this csv agn using sql loader ,it gets loaded as '1/1/1955'.Please help.

Why are you writing a CSV file simply to use SQLLOADER to load back into a database?
Why not just transfer the data via plain SQL from source into the target?
Re: utl_file [message #592891 is a reply to message #592885] Tue, 13 August 2013 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It displays wrong date..eg. 1/1/1855 changes to '01-JAN-55'.


Use TO_CHAR with a format mask to get the date in the format you want.

Regards
Michel
Re: utl_file [message #592977 is a reply to message #592885] Tue, 13 August 2013 07:56 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
Because I have to load 150 tables from one database to another.I dont have database links and I have to apply complex joins to fetch my data fro all the tables.So I am applying this logic and passing the final query for all the tables to the utl_file package to write to the csv files first and then load it to another database using sql loader.

I have 150 queries,dynamic ones.So I cannot specify the date columns explicitly.alter session for nfs ..is it the right approach before running my utl procedure?
Re: utl_file [message #592979 is a reply to message #592977] Tue, 13 August 2013 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Right, maybe not, but if it works, why not...

Regards
Michel
Re: utl_file [message #592998 is a reply to message #592977] Tue, 13 August 2013 08:59 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
I have checked my date format .It is correct..
select sysdate from dual;
8/13/2013 9:58:02 AM

My source row is as following:
source row
YR_END_RCPT_START_DATE YR_END_RCPT_STOP_DATE DATE_ADDED DATE_MODIFIED OPERATOR_NAME

0 0 10/8/0007 9:52:45 AM 1/30/2012 8:56 straitd



In excel the dates are displayed as: 8-Oct-07 30-Jan-12




YR_END_RCPT_START_DATE YR_END_RCPT_STOP_DATE DATE_ADDED DATE_MODIFIED OPERATOR_NAME

source row 0 0 1/1/1855 6/15/2009 13:06 pungbroo


excel 1-Jan-55 15-Jun-09



Please help.
Re: utl_file [message #593000 is a reply to message #592998] Tue, 13 August 2013 09:01 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
forget what excel shows. If you open up the csv in a text editor what does it show?
Re: utl_file [message #593079 is a reply to message #593000] Tue, 13 August 2013 22:38 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
Ok.So now that problem is resolved..I have another thing coming up..
My control file is:
OPTIONS (SKIP=1) load data
infile "/mnt/oraclefra/adis_etl_dir/ALLOCATION.csv"
INSERT preserve blanks INTO TABLE owbrep.ALLOCATION


TRUNCATE
FIELDS TERMINATED BY '~'


TRAILING NULLCOLS


(


ALLOCATION_CODE,
STATUS_CODE,
ACCOUNT,
SHORT_NAME,
LONG_NAME,
LONG_NAME2,
CFAE_PURPOSE_CODE,
CFAE_SUB_CODE,
RESTRICT_CODE,
FUND_NAME,
OWNERS,
PROGRAM_CODE,
ACCOUNT_2,
ATHLETICS_IND,
ALLOC_DEPT_CODE,
DEPARTMENT_NOTIFY,
ANNUAL_SW,
ALLOC_SCHOOL,
ALLOC_SUB_DEPT,
ALPHA_SORT,
GOAL_AMOUNT,
START_DATE,
STOP_DATE,
FISCAL_YEAR_TYPE,
CAMPUS,
AGENCY,
ALLOC_DIVISION,
ALLOC_PURPOSE,
ALLOC_FEE_IND,
ALLOC_NAMING_CODE,
ADMINISTRATOR_ID_NUMBER,
STEWARD_ID_NUMBER,
ENDOW_POOL_CODE,
ENDOW_TYPE_CODE,
MIN_AMT,
MIN_ACHIEVED_DATE,
DIST_START_DATE,
STEWARD_REPORTING_CODE,
XREF,
description "REPLACE (:description, '^', CHR(10))",
xcomment "REPLACE (:xcomment, '^', CHR(10))",
EXTRAMURAL_ATHLETICS,
ACCOUNTING_SCHOOL,
ACCOUNTING_DEPT,
GIFT_GRANT_TYPE,
CATEGORY1_CODE,
CATEGORY2_CODE,
CATEGORY3_CODE,
ALLOC_FEE_MIN_AMT,
ALLOC_FEE_ALT_CODE,
USE_FOR_AWC_IND,
date_added "to_date(:date_added,'MM/DD/YYYY HH:MI:SS AM')",
date_modified "to_date(:date_modified,'MM/DD/YYYY HH:MI:SS AM')",
OPERATOR_NAME,
USER_GROUP,
TRANSFER_USER_GROUP,
LOCATION_ID
)


And my log file says:

Record 1: Rejected - Error on table OWBREP.ALLOCATION, column LONG_NAME2.
ORA-00936: missing expression


Record 2: Rejected - Error on table OWBREP.ALLOCATION, column LONG_NAME2.
ORA-00936: missing expression



Please sugest what needs to be done.

Re: utl_file [message #593080 is a reply to message #593079] Tue, 13 August 2013 22:55 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
post CREATE TABLE owbrep.ALLOCATION statement
Re: utl_file [message #593129 is a reply to message #593080] Wed, 14 August 2013 07:54 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
-- Create table
create table ADVANCE.ALLOCATION
(
allocation_code VARCHAR2(16 BYTE) default ' ' not null,
status_code CHAR(1 BYTE) default ' ' not null,
account VARCHAR2(30 BYTE) default ' ' not null,
short_name VARCHAR2(40 BYTE) default ' ' not null,
long_name VARCHAR2(255 BYTE) default ' ' not null,
long_name2 VARCHAR2(255 BYTE) default ' ' not null,
cfae_purpose_code VARCHAR2(2 BYTE) default ' ' not null,
cfae_sub_code VARCHAR2(2 BYTE) default ' ' not null,
restrict_code VARCHAR2(3 BYTE) default ' ' not null,
fund_name VARCHAR2(10 BYTE) default ' ' not null,
owners VARCHAR2(10 BYTE) default ' ' not null,
program_code VARCHAR2(3 BYTE) default ' ' not null,
account_2 VARCHAR2(30 BYTE) default ' ' not null,
athletics_ind CHAR(1 BYTE) default ' ' not null,
alloc_dept_code VARCHAR2(7 BYTE) default ' ' not null,
department_notify CHAR(1 BYTE) default ' ' not null,
annual_sw CHAR(1 BYTE) default ' ' not null,
alloc_school VARCHAR2(4 BYTE) default ' ' not null,
alloc_sub_dept VARCHAR2(7 BYTE) default ' ' not null,
alpha_sort VARCHAR2(120 BYTE) default ' ' not null,
goal_amount NUMBER(14,2) default 0 not null,
start_date DATE,
stop_date DATE,
fiscal_year_type CHAR(1 BYTE) default ' ' not null,
campus VARCHAR2(3 BYTE) default ' ' not null,
agency VARCHAR2(3 BYTE) default ' ' not null,
alloc_division VARCHAR2(7 BYTE) default ' ' not null,
alloc_purpose VARCHAR2(5 BYTE) default ' ' not null,
alloc_fee_ind CHAR(1 BYTE) default ' ' not null,
alloc_naming_code VARCHAR2(3 BYTE) default ' ' not null,
administrator_id_number VARCHAR2(10 BYTE) default ' ' not null,
steward_id_number VARCHAR2(10 BYTE) default ' ' not null,
endow_pool_code VARCHAR2(3 BYTE) default ' ' not null,
endow_type_code VARCHAR2(3 BYTE) default ' ' not null,
min_amt NUMBER(14,2) default 0 not null,
min_achieved_date DATE,
dist_start_date DATE,
steward_reporting_code VARCHAR2(3 BYTE) default ' ' not null,
xref VARCHAR2(20 BYTE) default ' ' not null,
description VARCHAR2(255 BYTE) default ' ' not null,
xcomment VARCHAR2(255 BYTE) default ' ' not null,
extramural_athletics CHAR(1 BYTE),
accounting_school VARCHAR2(4 BYTE) default ' ' not null,
accounting_dept VARCHAR2(7 BYTE) default ' ' not null,
gift_grant_type VARCHAR2(3 BYTE) default ' ' not null,
category1_code VARCHAR2(3 BYTE) default ' ' not null,
category2_code VARCHAR2(3 BYTE) default ' ' not null,
category3_code VARCHAR2(3 BYTE) default ' ' not null,
alloc_fee_min_amt NUMBER(14,2) default 0 not null,
alloc_fee_alt_code CHAR(1 BYTE) default ' ' not null,
use_for_awc_ind CHAR(1 BYTE) default ' ' not null,
date_added DATE not null,
date_modified DATE default SYSDATE not null,
operator_name VARCHAR2(32 BYTE) default ' ' not null,
user_group VARCHAR2(2 BYTE) default ' ' not null,
transfer_user_group VARCHAR2(2 BYTE) default ' ' not null,
location_id NUMBER
)
tablespace ADVANCE_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create unique index ADVANCE.ALLOCATION_KEY0 on ADVANCE.ALLOCATION (ALLOCATION_CODE, LOCATION_ID)
tablespace ADVANCE_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Grant/Revoke object privileges
grant select on ADVANCE.ALLOCATION to OWBREP;
Re: utl_file [message #593135 is a reply to message #593129] Wed, 14 August 2013 08:31 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
Any solutions for the above errors?
Re: utl_file [message #593138 is a reply to message #593135] Wed, 14 August 2013 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
>infile "/mnt/oraclefra/adis_etl_dir/ALLOCATION.csv"
if those are double quote marks, then are not correct.
Re: utl_file [message #593145 is a reply to message #593138] Wed, 14 August 2013 10:01 Go to previous messageGo to next message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
I have double quotation marks but still it is working fine.I resolved all the issues.Everything is resolved now.Thanks.
Re: utl_file [message #593147 is a reply to message #593145] Wed, 14 August 2013 10:11 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
what was changed to make it work?
Re: utl_file [message #593149 is a reply to message #593147] Wed, 14 August 2013 10:35 Go to previous message
DIPRINKS
Messages: 57
Registered: July 2013
Location: Michigan
Member
There were plenty of problems with the control file.I had to apply to_date on the two fields START_DATE, STOP_DATE.The control file had "nvl(:LONG_NAME2, )" embedded in front of the field long_name2.So,I corrected this and everything is good.
Previous Topic: check constraint
Next Topic: alternative for pivot stmt
Goto Forum:
  


Current Time: Wed Sep 30 15:09:24 CDT 2020