| URGENT:-Creating flat files for the tables [message #124235] |
Fri, 17 June 2005 01:29  |
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
|
Member |
|
|
Hi Gurus,
I need your urgent help. I was assigned with the task of creating flat files. I have followed the way of creating a function in the schema where the table is situated. The function is called by the procedure and the flat file that should be in .csv format was created. I had to modify the UTL_FILE_DIR parameter for that. I tried this on test database and was successful.
But, when i sent this approach to the production DBA's they said they will not allow any modification in init.ora parameters and will not allow any function or procedure to be created for this.
Now, I am stuck and could not think of anything else as they want an approach from me that there should be a single SQL statement that will select for the DB and does not require any modification and will create files in .csv format.
Is there anything or single SQL statement that will create the flat files for the tables specified and will not require any modfication on production DB.
If there is any thing please sent it to me stepwise as soon as possible as this is urgent.
My version is Oracle 9.2.0.5 and OS is AIX UNIX.
Sample table structure:-
Name Null? Type
----------------------------------------- -------- ------------
AFFL_ID NOT NULL NUMBER(18)
AFFL_NAME NOT NULL VARCHAR2(350)
AFFL_NAME_CMPRSD NOT NULL VARCHAR2(350)
RENEWAL_MONTH NUMBER(2)
FISCAL_YEAR_END NUMBER(2)
XSOP_BILLING_PERIOD_CD NOT NULL NUMBER(9)
COMBINE_XSOP_INVOICE NOT NULL CHAR(1)
CREATED_BY NOT NULL VARCHAR2(25)
CREATED_DATE NOT NULL DATE
LAST_MODIFIED_BY NOT NULL VARCHAR2(25)
LAST_MODIFIED_DATE NOT NULL DATE
IS_DELETED NOT NULL CHAR(1)
i am also attatching the sample flat file which i created using the function and the procedure. In this format the .csv has to be created.
Please, help me gurus as i need your urgent help on this.
Thanks
Milind.
|
|
|
|
| Re: URGENT:-Creating flat files for the tables [message #124237 is a reply to message #124235] |
Fri, 17 June 2005 01:38  |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
HI
Checked the sample flat file.
Hey, I hv a suggestion.. why dont u use "," as ur column separator or replace "|" with "," in the flat file u r generating.
Probably that should resolve the issue.
Also, if u need to create a number of flat files, why not use some scripting language like perl for doing this.
Regds
Girish
|
|
|
|
| Re: URGENT:-Creating flat files for the tables [message #124243 is a reply to message #124237] |
Fri, 17 June 2005 01:48 |
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
|
Member |
|
|
Hi Girish,
Thanks for your quick response. I think you did not get my point. My problem is not with the seperator. I want an SQL statement or any other way that when fired on production DB on UNIX will create flat files for the tables without modifying any parameter or creating any objects on DB.
If you have any way out of this Kindly suggest.
Thanks
Milind.
|
|
|
|
| Re: URGENT:-Creating flat files for the tables [message #124257 is a reply to message #124243] |
Fri, 17 June 2005 02:28 |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
Sir
even this process wont create any object in DB and will not change any parameter. All u r doing is firing a select * statement on the table.
All its gonna do is direct the output into a spooled file.
One more way is to use a anonymous pl/sql block. But that, without saving the file handling function, will amount of large code.
Dont save the procedure in database.
Regds
Girish
|
|
|
|
| Re: URGENT:-Creating flat files for the tables [message #124259 is a reply to message #124257] |
Fri, 17 June 2005 02:32 |
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
|
Member |
|
|
Hi Girish,
Thanks again for your reply.
Can u send me the PL/SQL block if possible.
I will try with the spool file. But, what about the seperator.
Can u give me the SQL statement for the table which i have shown in my first post.
Please, if you can send me.
Thanks
Milind.
|
|
|
|
| Re: URGENT:-Creating flat files for the tables [message #124264 is a reply to message #124259] |
Fri, 17 June 2005 02:56 |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
Sri
As for fetching data from table... simple select statement will do.
the flow of PL block will look like this:
Decalation section:
Cursor with select query (containing all fields as required by u)
file handler
Executable block section:
Open cursor
Open file
While data is there in cursor write that in file.
Close ur cursor and file.
For more details on PL/SQL
http://www.orafaq.com/faq/plsql
Regds
Girish
[EDIT]: Small typo
|
|
|
|
| Re: URGENT:-Creating flat files for the tables [message #124310 is a reply to message #124264] |
Fri, 17 June 2005 08:15 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
there is something you must be aware of.
UTL_FILE_DIR is serverside. an pl/sql package can create the file only in the server.
Any other methods (spooling the output etc), can be written in the client side and NOT on the serverside (unless, you call this spooling pl/sql body from the server directly.ie. sqlplus installed in server).
What is your need?
[Updated on: Fri, 17 June 2005 08:16] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
