When I create a directory using sytem user (vin.txt is my file)
create directory emp_dir1
AS "'C:\Documents and Settings\Administrator\Desktop\vin.txt'";
it creates it.
When I do the same using user Scott it gives an error for path of file that
"Identifier is too long"
but when I put this file path in single quotes instead of double quotes for scott, it creates it.
What is the reason behind?
Why are you using double-qoutes?
Strings in Oracle have single quotes, while double quotes can be used around column- and table-names.
I tried it using Oracle 10.2 (don't have any 9g around), and I can't reproduce this. It's never working, no matter what user I use.
When the string is as long as yours, I always get
ORA-00972: identifier is too long
When I try a shorter path (CREATE DIRECTORY emp_dir1 As "C:\vin.txt";) I get
ORA-01780: string literal required
A couple of things concern me about your question.
Firstly, regular users such as SCOTT should not be creating directories. The CREATE ANY DIRECTORY is extremely powerful, because it confers read/write privileges on any OS directory which is accessible to the oracle account; this is a massive security hole.
Secondly, the directory path must be just the path, without a file. We create files using UTL_FILE, Data Pump or whatever. A call to UTL_FILE.FOPEN() will fail if the passed DIRECTORY is actually a path to a file not an OS directory.
I cannot explain why the same statement executed successfully by SYSTEM fails when executed by SCOTT. I don't have 9i to hand, so I cannot test it. Please cut'n'paste the whole SQL*Plus output so that we can see what happens. As Peter has noted, your question appeared to contain a typo, so at the moment we cannot be certain that what you think is happening actually is what is happening.