error in pl/sql procedure in timesten in UNIX, i have error when executing this pl/sql procedure in timesten in UNIX Options

[khawajah]

create or replace procedure log_file
(
odsm_profile IN VARCHAR2
)
AS
handle utl_file.file_type;
begin
handle := utl_file.fopen('/opt/TimesTen/TT_KPN/plsql/utl_file_dir','SpInterface.log','A');
utl_file.putf(handle, odsm_profile);
utl_file.fflush(handle);
utl_file.fclose(handle);
end;
/
i have error when executing this pl/sql procedure in timesten in UNIX



this is the excution error:



Command> exec log_file('hi');

8507: ORA-29280: invalid directory path

8507: ORA-06512: at "SYS.UTL_FILE", line 41

8507: ORA-06512: at "SYS.UTL_FILE", line 478

8507: ORA-06512: at "SIUA.LOG_FILE", line 8

8507: ORA-06512: at line 1

The command failed.

how i can solve this problem?????

[aussie_dba]

"how i can solve this problem????? "

Stert by examining the error:

http://www.dba-oracle.com/t_ora_29280_inva...ectory_path.htm

Action: Correct the directory object parameter, or create a corresponding directory object with the CREATE DIRECTORY command.

It's on this line:

handle := utl_file.fopen('/opt/TimesTen/TT_KPN/plsql/utl_file_dir','SpInterface.log','A');

[khawajah]

hi,
thanks for your replay..

i am using timesten not oracle which has some restriction;

In TimesTen 11.2.x releases, UTL_FILE is limited so can not use CREATE DIRECTORY command.
this link explain the limitation of timesten : http://docs.oracle.com/cd/E11882_01/timest...le.htm#CIABFCFB


in windows : only to the directory install_dir/plsql/utl_file_temp so i used as below and worked correctly without using create directory .
handle := utl_file.fopen('utl_file_temp','SpInterface.log','A');




so what to do in unix??

[burleson]

Hi,

>> i am using timesten not oracle

This is an Oracle DBA forum, you know . . .

******************************************
As you know, your issue is with the directory name:

Is this a valid file directory path?

Go to UNIX and cd to the path. As the UNIX user, more the file:

CODE

more  SpInterface.log

For testing, change the permissions on the directory, in case it is a permission issue:

CODE

chmod 777 /opt/TimesTen/TT_KPN/plsql/utl_file_dir

I may be that the Oracle user cannot write to the directory.

I would also open an SR on MOSC (Oracle support), and check for bugs:

htttp://support.oracle.com

[khawajah]

hi,
thanks for your replay; i do what you told me before but the same issue..and below is the execution :

Command> create or replace procedure log_file
> (
> odsm_profile IN VARCHAR2
> )
> AS
> handle utl_file.file_type;
> begin
> -- handle := utl_file.fopen('UTL_FILE_DIR',odsm_profile,'a');
> handle := utl_file.fopen('/tmp','hot_backup.cmd','W');
> utl_file.putf(handle, 'hi');
> utl_file.fflush(handle);
> utl_file.fclose(handle);
> end;
> /

Procedure created.

Command> exec log_file('SpInterface.log');
8507: ORA-29280: invalid directory path
8507: ORA-06512: at "SYS.UTL_FILE", line 41
8507: ORA-06512: at "SYS.UTL_FILE", line 478
8507: ORA-06512: at "SIUA.LOG_FILE", line 9
8507: ORA-06512: at line 1
The command failed.

[HAL9000]

I don't use timesTen, but Oracle tech support will know your answer!

I would open an SR on MOSC (Oracle support).

http://support.oracle.com

Good Luck and post the resolution.

[Pieter van Wijng...]

I had the same problem, with actually the very same database.. the topic starter is a colleague of mine . The trick is to use

handle := utl_file.fopen('UTL_FILE_TEMP',odsm_profile,'a');

(instead of UTL_FILE_DIR) as the directory to place the log files, since the <timesten install dir>/plsql/utl_file_temp/ directory is the only place where Oracle TimesTen allows file I/O.

and indeed, don't forget to execute

GRANT EXECUTE ON SYS.UTL_FILE TO <user>;

for the users that will use the file I/O procedure, otherwise they won't be able to.

for more info, view the Oracle TimesTen Packages Reference: docs.oracle.com/cd/E11882_01/timesten.112/e21645.pdf

Kind regards,
Pieter