Creating directory?

When I create a directory using sytem user (vin.txt is my file)

create directory emp_dir1 
     AS "'C:\Documents and Setting开发者_Go百科s\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?

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.

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