OraFAQ Forum: Client Tools » Create directory in UNIX

Home » Other » Client Tools » Create directory in UNIX (oracle 10.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Create directory in UNIX [message #588766]

Fri, 28 June 2013 04:07

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.

Report message to a moderator

Re: Create directory in UNIX [message #588767 is a reply to message #588766]

Fri, 28 June 2013 04:11

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: Create directory in UNIX [message #588768 is a reply to message #588767]

Fri, 28 June 2013 04:13

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?

Report message to a moderator

Re: Create directory in UNIX [message #588770 is a reply to message #588768]

Fri, 28 June 2013 04:24

John Watson
Messages: 8964
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.

Report message to a moderator

Re: Create directory in UNIX [message #588771 is a reply to message #588768]

Fri, 28 June 2013 04:25

Littlefoot
Messages: 21823
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

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

Littlefoot
Messages: 21823
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

Report message to a moderator

Re: Create directory in UNIX [message #588781 is a reply to message #588776]

Fri, 28 June 2013 06:15

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

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Create directory in UNIX [message #588786 is a reply to message #588785]

Fri, 28 June 2013 07:47

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

Littlefoot
Messages: 21823
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?

Report message to a moderator

Re: Create directory in UNIX [message #588790 is a reply to message #588787]

Fri, 28 June 2013 07:58

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.

Report message to a moderator

Re: Create directory in UNIX [message #588795 is a reply to message #588787]

Fri, 28 June 2013 08:06

joy_division
Messages: 4963
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.

Report message to a moderator

Previous Topic:	how can i sync my oracle 10g database with sql server 2005
Next Topic:	Regarding Toad Error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 13:18:50 CST 2025