Home » SQL & PL/SQL » Client Tools » Create directory in UNIX (oracle 10.2)
Create directory in UNIX [message #588766] Fri, 28 June 2013 04:07 Go to next message
eshwar401
Messages: 17
Registered: June 2013
Location: India
Junior Member
Hi Team,

What is the script for creating SQL directory in UNIX.

Create or replace directory CONFIG as 'c:\Config\'
Please reply ASAP.

Thanks,
Eshwar.
Re: Create directory in UNIX [message #588767 is a reply to message #588766] Fri, 28 June 2013 04:11 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It doesn't depend on operating system; statement is just the same. However, the operating system path to that directory differs. Obviously, you don't have "c:\Config\" on Unix (and we don't know which directories you do have).
Re: Create directory in UNIX [message #588768 is a reply to message #588767] Fri, 28 June 2013 04:13 Go to previous messageGo to next message
eshwar401
Messages: 17
Registered: June 2013
Location: India
Junior Member
let say config is the directory i have created in my root directory then how should the statement should be?
Re: Create directory in UNIX [message #588770 is a reply to message #588768] Fri, 28 June 2013 04:24 Go to previous messageGo to next message
John Watson
Messages: 4515
Registered: January 2010
Location: Global Village
Senior Member
A matter of etiquette: phrases such as Quote:
Please reply ASAP.
sometimes annoy people on forums, who answer in their free, unpaid, time only because they are nice people who want to contribute to the total of global happiness.
LF has already told you what needs to be done, and I have to say that if you cannot work out the command (and have the directory with appropriate permissions) you probably should not be executing it. But anyway,
create directory d1 as '/home/oracle';

works for me.
Re: Create directory in UNIX [message #588771 is a reply to message #588768] Fri, 28 June 2013 04:25 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't use Unix, but that might look like
create directory config as '/user/config'

I *think* that Unix is case-sensitive so take care about that (i.e. Config <> CONFIG).

[EDIT: Missing slash]

[Updated on: Fri, 28 June 2013 04:25]

Report message to a moderator

Re: Create directory in UNIX [message #588774 is a reply to message #588771] Fri, 28 June 2013 05:11 Go to previous messageGo to next message
eshwar401
Messages: 17
Registered: June 2013
Location: India
Junior Member
thanks everyone for the replies..

The issue here is i don't have DBA permissions on the db to create the folder and getting a dba help in executing this statement takes a lot of time.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:88212348059

Have used above link for taking data from tables and create csv files.

But i cannot use create directory statement because of the permissions issue. With the directory object it is not allowing to execute the stored procedure if i hard code the value of the path it throws the following error:
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at "VEMS.MAIL_ATTACH", line 27
ORA-06512: at line 1

Any suggestions?

Thanks,Eshwar.

[Updated on: Fri, 28 June 2013 05:12]

Report message to a moderator

Re: Create directory in UNIX [message #588776 is a reply to message #588774] Fri, 28 June 2013 05:18 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If possible, store data into CSV files on your own computer (for example, SPOOL result of the SELECT statement). UTL_FILE creates files on a database server, so - in order to access these - you do need a directory object.

Therefore, pick one:
- UTL_FILE => directory => call your DBA as long as necessary
- spool => file stored locally
Re: Create directory in UNIX [message #588781 is a reply to message #588776] Fri, 28 June 2013 06:15 Go to previous messageGo to next message
eshwar401
Messages: 17
Registered: June 2013
Location: India
Junior Member
Ok is it possible to change the script to incorporate the spool functionality in the link provided above?

Basically i am trying to load all the tables data to different files.

Thanks,Eshwar.

[Updated on: Fri, 28 June 2013 06:20]

Report message to a moderator

Re: Create directory in UNIX [message #588784 is a reply to message #588781] Fri, 28 June 2013 06:44 Go to previous messageGo to next message
eshwar401
Messages: 17
Registered: June 2013
Location: India
Junior Member
I have tried following in SQLPlus:

SQL> SET FEEDBACK OFF HEADING OFF ECHO OFF
SQL> SPOOL C:\External_Tables\emp.csv
SQL> SELECT *FROM EMP ;
SQL> SPOOL OFF

it is creating following output
SQL> SELECT *FROM EMP;

INCOL Inbound collector
SQL> SPOOL OFF


Required output:
INCOL,Inbound collector

BOLD IS NOT REQUIRED JUST GIVE FOR IDENTIFICATION

what are the changes required to the above statements to achieve this?

Thanks,
Eshwar.

[Updated on: Fri, 28 June 2013 06:46]

Report message to a moderator

Re: Create directory in UNIX [message #588785 is a reply to message #588784] Fri, 28 June 2013 07:35 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's how: create a SQL script, such as this one (called SP.SQL):
set termout off
set pagesize 1000
set echo off
set heading off
set feedback off
set verify off

spool a.txt

select * from dept;

spool off

Run the script from SQL*Plus (i.e. don't execute all these statements (which are stored in SP.SQL) manually at the SQL*Plus prompt), such as
SQL> @sp
SQL>
See? Nothing is displayed on the screen.

Now, contents of my output file (A.TXT):
SQL> $type a.txt

        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>
As you can see, there's only DEPT table's contents, nothing else.
Re: Create directory in UNIX [message #588786 is a reply to message #588785] Fri, 28 June 2013 07:47 Go to previous messageGo to next message
eshwar401
Messages: 17
Registered: June 2013
Location: India
Junior Member
This is working but if i want to give , as a delimitor? and there is an empty row in the starting of the output file how to avoid this?

Thanks a lot for the time.

Regards,Eshwar.

[Updated on: Fri, 28 June 2013 07:48]

Report message to a moderator

Re: Create directory in UNIX [message #588787 is a reply to message #588786] Fri, 28 June 2013 07:51 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Set column separator:
SQL> set colsep ,
SQL> select * from dept;

    DEPTNO,DNAME         ,LOC
----------,--------------,-------------
        10,ACCOUNTING    ,NEW YORK
        20,RESEARCH      ,DALLAS
        30,SALES         ,CHICAGO
        40,OPERATIONS    ,BOSTON

SQL>

As of an empty line - no idea.

What will you use these CSV files for?
Re: Create directory in UNIX [message #588790 is a reply to message #588787] Fri, 28 June 2013 07:58 Go to previous messageGo to next message
eshwar401
Messages: 17
Registered: June 2013
Location: India
Junior Member
It will be used as a input filees to the SQL Server tables. I have there is a limitation on size of each row and also no of rows in Oracle for spool?

Thanks,
Eshwar.
Re: Create directory in UNIX [message #588795 is a reply to message #588787] Fri, 28 June 2013 08:06 Go to previous message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
Littlefoot wrote on Fri, 28 June 2013 08:51
Set column separator:
[code]SQL> set colsep ,


However, be mindful of data such as addresses that have commas in them. It will throw off your fields for that row.
Previous Topic: how can i sync my oracle 10g database with sql server 2005
Next Topic: Regarding Toad Error
Goto Forum:
  


Current Time: Sat Aug 30 09:16:50 CDT 2014

Total time taken to generate the page: 0.10013 seconds