Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> error in pl/sql procedure in timesten in UNIX, i have error when executing this pl/sql procedure in timesten in UNIX
khawajah
post Feb 22 2012, 02:38 PM
Post #1


Newbie
*

Group: Members
Posts: 4
Joined: 22-February 12
Member No.: 46,812



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?????
Go to the top of the page
 
+Quote Post
aussie_dba
post Feb 22 2012, 05:22 PM
Post #2


Advanced Member
***

Group: Members
Posts: 618
Joined: 28-August 04
Member No.: 495



"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');


Go to the top of the page
 
+Quote Post
khawajah
post Feb 23 2012, 03:20 AM
Post #3


Newbie
*

Group: Members
Posts: 4
Joined: 22-February 12
Member No.: 46,812



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??
Go to the top of the page
 
+Quote Post
burleson
post Feb 23 2012, 05:40 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,610
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
khawajah
post Feb 23 2012, 01:26 PM
Post #5


Newbie
*

Group: Members
Posts: 4
Joined: 22-February 12
Member No.: 46,812



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.
Go to the top of the page
 
+Quote Post
HAL9000
post Feb 23 2012, 02:44 PM
Post #6


Advanced Member
***

Group: Members
Posts: 880
Joined: 25-September 07
Member No.: 12,336



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.
Go to the top of the page
 
+Quote Post
Pieter van Wijng...
post Aug 13 2012, 09:54 AM
Post #7


Newbie
*

Group: Members
Posts: 1
Joined: 13-August 12
Member No.: 47,517



I had the same problem, with actually the very same database.. the topic starter is a colleague of mine smile.gif. 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
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 21st October 2014 - 04:29 PM