Home » SQL & PL/SQL » SQL & PL/SQL » Problem
Problem [message #300640] Sun, 17 February 2008 04:59 Go to next message
mangalr
Messages: 8
Registered: February 2008
Junior Member
Hi,

I am facing following problem.
I am trying to create a directory from sql Plus using following command
create or replace directory DIR1 as 'C:/FDIR';


its giving directory created successfully message and its hown too in all_directories.
But i could not find the directory anywhere on my pc.
Ideally it should be present in C but i couldn't find it.I wanna do external tables so require creating directory.

Please respond

Thanks a lot in advance...
Re: Problem [message #300641 is a reply to message #300640] Sun, 17 February 2008 05:01 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
CREATE DIRECTORY creates an Oracle object. It does NOT create a directory on your operating system.
Re: Problem [message #300642 is a reply to message #300641] Sun, 17 February 2008 05:04 Go to previous messageGo to next message
mangalr
Messages: 8
Registered: February 2008
Junior Member
so why to specify path?
Actually for external table i had to pick a file which i wanna change to table.

that file needs to be present on my PC..
So how to pick that file ?

My friend tries this directory and it had created successfully but didn't work for me...
Re: Problem [message #300643 is a reply to message #300642] Sun, 17 February 2008 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ An Oracle dictory is a name for an OS directory, an alias, a synonym

2/ Oracle only reads SERVER file not client one.

By the way, "didn't work" is NOT an Oracle error.

Regards
Michel
Re: Problem [message #300645 is a reply to message #300643] Sun, 17 February 2008 05:21 Go to previous messageGo to next message
mangalr
Messages: 8
Registered: February 2008
Junior Member
Michel Cadot wrote on Sun, 17 February 2008 05:17
1/ An Oracle dictory is a name for an OS directory, an alias, a synonym

2/ Oracle only reads SERVER file not client one.

By the way, "didn't work" is NOT an Oracle error.

Regards
Michel



Thanks Michel

Ok I got that on my OS the directory needed to esist first...

.Can u explain the second point?
By "didn't work" i meant that I could not trace it...

regards

[Updated on: Sun, 17 February 2008 05:50]

Report message to a moderator

Re: Problem [message #300648 is a reply to message #300645] Sun, 17 February 2008 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
C:/FDIR can't exist on Windows. It must be C:\FDIR

And "Oracle only reads SERVER file not client one", is this that you don't understand?

Regards
Michel
Re: Problem [message #300650 is a reply to message #300648] Sun, 17 February 2008 06:38 Go to previous messageGo to next message
mangalr
Messages: 8
Registered: February 2008
Junior Member
Michel Cadot wrote on Sun, 17 February 2008 06:27
C:/FDIR can't exist on Windows. It must be C:\FDIR

And "Oracle only reads SERVER file not client one", is this that you don't understand?

Regards
Michel



Michel

It will be great if u can provide me the link to some material.
Ignore my query for point 2 ..Its self explanatory.

thanks

[Updated on: Sun, 17 February 2008 06:41]

Report message to a moderator

Re: Problem [message #300653 is a reply to message #300650] Sun, 17 February 2008 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Material for what?
Oracle documentation: http://tahiti.oracle.com/

Regards
Michel
Re: Problem [message #300710 is a reply to message #300653] Sun, 17 February 2008 22:36 Go to previous messageGo to next message
mangalr
Messages: 8
Registered: February 2008
Junior Member
Michel Cadot wrote on Sun, 17 February 2008 08:53
Material for what?
Oracle documentation: http://tahiti.oracle.com/

Regards
Michel



Thanks Michel,

I have successfully converted flat file to table.
Can yo suggest how to convert a table to a file?

thanks
Re: Problem [message #300750 is a reply to message #300710] Mon, 18 February 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SELECT for instance.

Regards
Michel
Re: Problem [message #300774 is a reply to message #300750] Mon, 18 February 2008 01:36 Go to previous messageGo to next message
mangalr
Messages: 8
Registered: February 2008
Junior Member
Michel Cadot wrote on Mon, 18 February 2008 00:47
Use SELECT for instance.

Regards
Michel



Hi Michel,

I used the following code


declare
    f utl_file.file_type;
begin
    f := utl_file.fopen('K1','1.txt','W');
    for s in (select * from t1)
   loop
        utl_file.put_line(f,s.c1);
utl_file.put_line(f,s.c2);
    end loop;
    utl_file.fclose(f);
end;


My concern of doubt is that I want my whole table to be converted in file
You can see that I used two put_line which are not providing me correct o/p

Can you suggest me how to alter this or any diff approach

Thanks
/
Re: Problem [message #300775 is a reply to message #300774] Mon, 18 February 2008 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You can see that I used two put_line which are not providing me correct o/p

Why don't you use 1 put_line concatening the fields?

Quote:
Can you suggest me how to alter this or any diff approach

spool myfile
select <whatever you want> from table
spool off

Regards
Michel
Re: Problem [message #300797 is a reply to message #300775] Mon, 18 February 2008 02:47 Go to previous messageGo to next message
mangalr
Messages: 8
Registered: February 2008
Junior Member
Michel Cadot wrote on Mon, 18 February 2008 01:41
Quote:
You can see that I used two put_line which are not providing me correct o/p

Why don't you use 1 put_line concatening the fields?

Quote:
Can you suggest me how to alter this or any diff approach

spool myfile
select <whatever you want> from table
spool off

Regards
Michel




I tried and got error

                 utl_file.put_line(f,s.n||' '||f,s.m);
                            *
ERROR at line 7:
ORA-06550: line 7, column 29:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 7, column 9:
PL/SQL: Statement ignored

[Updated on: Mon, 18 February 2008 02:49]

Report message to a moderator

Re: Problem [message #300814 is a reply to message #300797] Mon, 18 February 2008 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try again.

Regards
Michel
Re: Problem [message #300818 is a reply to message #300797] Mon, 18 February 2008 03:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
re-check your code.
See what you are trying to concatenate?
Re: Problem [message #300827 is a reply to message #300818] Mon, 18 February 2008 03:32 Go to previous messageGo to next message
mangalr
Messages: 8
Registered: February 2008
Junior Member
Frank wrote on Mon, 18 February 2008 03:18
re-check your code.
See what you are trying to concatenate?


Actually i wanna concat the two columns
n amd m so i have written the concat statment
but why its giving error
Re: Problem [message #300841 is a reply to message #300827] Mon, 18 February 2008 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have an error because you don't write what you are saying you want to do.
Recheck your code. Read what you wrote.

Regards
Michel

[Updated on: Mon, 18 February 2008 04:28]

Report message to a moderator

Re: Problem [message #300843 is a reply to message #300841] Mon, 18 February 2008 04:23 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Mon, 18 February 2008 11:19
You have an error because you don't write what you are sayuing you want to do.
Recheck your code. Read what you wrote.

Regards
Michel



Sometimes it's very hard to notice the error in your own code (happens even to me from time to time Wink )

So maybe someone else should read his code

With a little whitespace added, maybe the error is more noticeable

  utl_file.put_line( f , s.n ||' '|| f , s.m ) ;



Re: Problem [message #300852 is a reply to message #300843] Mon, 18 February 2008 04:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
another good way is to write from scratch what you want.
How would you concatenate s.n and s.m? (forget about utl_file, f, etc)
Compare that with what you got.
Previous Topic: Dynamic Fetching records when Table is created dynamically
Next Topic: Need Urgent help
Goto Forum:
  


Current Time: Thu Dec 08 18:25:43 CST 2016

Total time taken to generate the page: 0.14735 seconds