Jarodtweiss
asked on
How to write to a file ?
Hello,
I have a stupid problem...
I know that I have to use the UTL_FILE built in and I know how to use it.
I know that I have to give access to the folder I want to use with specifying UTL_FILE_DIR in the init.ora file.
My problem is that I do not have any init.ora file anywhere....
I have tried to create a simple one with the new UTL_FILE_DIR sentence but it doesn't seem to work.
To be more precise, my DB is installed on Computer1 and I run my queries from Computer2 from TOAD.
And I want to have some output in a file.
Where do I have to set my Init.ora file ? On Computer1 or Computer2 ? and in which folder ? (I have told... stupid question...)
My generated file can be on Computer1 ?
Can you give me the example of Init.ora file ? I can set only the UTL_FILE_DIR in it ?
btw, I have also read that I could give access with ALTER SYSTEM command while logged as SYSDBA. But it doesn't seem to work, giving me the error 'Cannot change initialization options' or something like that...
I have a stupid problem...
I know that I have to use the UTL_FILE built in and I know how to use it.
I know that I have to give access to the folder I want to use with specifying UTL_FILE_DIR in the init.ora file.
My problem is that I do not have any init.ora file anywhere....
I have tried to create a simple one with the new UTL_FILE_DIR sentence but it doesn't seem to work.
To be more precise, my DB is installed on Computer1 and I run my queries from Computer2 from TOAD.
And I want to have some output in a file.
Where do I have to set my Init.ora file ? On Computer1 or Computer2 ? and in which folder ? (I have told... stupid question...)
My generated file can be on Computer1 ?
Can you give me the example of Init.ora file ? I can set only the UTL_FILE_DIR in it ?
btw, I have also read that I could give access with ALTER SYSTEM command while logged as SYSDBA. But it doesn't seem to work, giving me the error 'Cannot change initialization options' or something like that...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if you use NT platform, open regedit, read HKEY_LOCAL_MACHINE\SOFTWAR
if you don`t find him, run
select NAME, VALUE
from v$parameter
where isdefault = 'FALSE'
it`s be your init.ora (NAME=VALUE), put them on normal place.
create file dir, make access to him,
then change parameter UTL_FILE_DIR in new init.ora
Now you can use UTL_FILE
Best regards
Dmitry.
if you don`t find him, run
select NAME, VALUE
from v$parameter
where isdefault = 'FALSE'
it`s be your init.ora (NAME=VALUE), put them on normal place.
create file dir, make access to him,
then change parameter UTL_FILE_DIR in new init.ora
Now you can use UTL_FILE
Best regards
Dmitry.
ASKER
@seazodiac
"select count(*) from v$spparameter where isspecified ='TRUE'" gives me 29
So I run the alter which is accepted.
Then I run teh select again and it gives me 30. If I check in the v$parameter, the utl_file_dir has not been changed
andI still have the same problem then
@SDutta
I'm using Oracle 9.2 but your solution doesn't seem to work
I connect as a SYSDBA (either on my machine or directly on the DB server)
the CREATE DIRECTORY and GRANT were ok (no error) but when I run the utl_file commands, I still have the error (not a vlalid...)
I checked and cheked again the path and case and it was ok.
@GDE
I'm using W2K
and I have nothing in the registry ang the select show me the path of dumps and control files. That's all
Any other idea ? or precision if your solution should have work. I may have done something wrong...
"select count(*) from v$spparameter where isspecified ='TRUE'" gives me 29
So I run the alter which is accepted.
Then I run teh select again and it gives me 30. If I check in the v$parameter, the utl_file_dir has not been changed
andI still have the same problem then
@SDutta
I'm using Oracle 9.2 but your solution doesn't seem to work
I connect as a SYSDBA (either on my machine or directly on the DB server)
the CREATE DIRECTORY and GRANT were ok (no error) but when I run the utl_file commands, I still have the error (not a vlalid...)
I checked and cheked again the path and case and it was ok.
@GDE
I'm using W2K
and I have nothing in the registry ang the select show me the path of dumps and control files. That's all
Any other idea ? or precision if your solution should have work. I may have done something wrong...
Exporting the Server Parameter File
You can export a server parameter file to create a traditional text initialization parameter file. Reasons for doing this include:
Creating backups of the server parameter file
For diagnostic purposes, listing all of the parameter values currently used by an instance. This is analogous to the SQL*Plus SHOW PARAMETERS command or selecting from the V$PARAMETER or V$PARAMETER2 views.
Modifying of the server parameter file by first exporting it, editing the output file, and then recreating it.
The exported file can also be used to start up an instance using the PFILE option.
The CREATE PFILE statement is used to export a server parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this statement. The exported file is created on the database server machine. It contains any comments associated with the parameter in the same line as the parameter setting.
The following example creates a text initialization parameter file from the server parameter file:
CREATE PFILE FROM SPFILE;
Because no names were specified for the files, a platform-specific name is used for the initialization parameter file, and it is created from the platform-specific default server parameter file.
The following example creates a text initialization parameter file from a server parameter file where the names of the files are specified:
CREATE PFILE='/u01/oracle/dbs/tes
FROM SPFILE='/u01/oracle/dbs/te
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90117/create.htm#1012672
Best regards
Dmitry.
You can export a server parameter file to create a traditional text initialization parameter file. Reasons for doing this include:
Creating backups of the server parameter file
For diagnostic purposes, listing all of the parameter values currently used by an instance. This is analogous to the SQL*Plus SHOW PARAMETERS command or selecting from the V$PARAMETER or V$PARAMETER2 views.
Modifying of the server parameter file by first exporting it, editing the output file, and then recreating it.
The exported file can also be used to start up an instance using the PFILE option.
The CREATE PFILE statement is used to export a server parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this statement. The exported file is created on the database server machine. It contains any comments associated with the parameter in the same line as the parameter setting.
The following example creates a text initialization parameter file from the server parameter file:
CREATE PFILE FROM SPFILE;
Because no names were specified for the files, a platform-specific name is used for the initialization parameter file, and it is created from the platform-specific default server parameter file.
The following example creates a text initialization parameter file from a server parameter file where the names of the files are specified:
CREATE PFILE='/u01/oracle/dbs/tes
FROM SPFILE='/u01/oracle/dbs/te
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90117/create.htm#1012672
Best regards
Dmitry.
ASKER
Finally it's ok.
I was able to create manually a init.ora file and it is loaded correctly in Oracle.
I can now write to the file.
I'm checking if I don't have extra problems before closing the question.
I was able to create manually a init.ora file and it is loaded correctly in Oracle.
I can now write to the file.
I'm checking if I don't have extra problems before closing the question.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now I have an error when I write to the file.
In all the case, it is ok except for that line :
UTL_FILE.PUT_LINE(file_id,
Notice that myQuery is a VARCHAR2 and that has a length of 20 000 or so...
And I receive a UTL_FILE.WRITE_ERROR exception.
Is it because of the length ? Do I have to substring it ? There is no other way ?
In all the case, it is ok except for that line :
UTL_FILE.PUT_LINE(file_id,
Notice that myQuery is a VARCHAR2 and that has a length of 20 000 or so...
And I receive a UTL_FILE.WRITE_ERROR exception.
Is it because of the length ? Do I have to substring it ? There is no other way ?
Log in as SYSDBA
SQL> CREATE DIRECTORY my_directory AS 'C:\NEW'; -- this is on the DB server
SQL> GRANT READ ON DIRECTORY my_directory TO scott; -- read is enough to read/write to the location
Now you can connect as SCOTT and use 'my_directory' as the first parameter in UTL_FILE.FOPEN like this
file_handle := utl_file.fopen('my_directo
Note : Case of the directory is important here, use it in the same case that it was created.
If you need to write to a remote location (not on the DB server) then the database and listener services need to run under a domain usercode which has access to the remote location.