OraFAQ Forum: Server Utilities » ORACLE LOG writting in unix from pl/sql

Home » RDBMS Server » Server Utilities » ORACLE LOG writting in unix from pl/sql

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORACLE LOG writting in unix from pl/sql [message #151258]

Wed, 14 December 2005 00:26

cgk_js
Messages: 48
Registered: December 2005

Member

Did any one give me the source code of writing the log in unix from oracle PL/SQL

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #151354 is a reply to message #151258]

Wed, 14 December 2005 07:27

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

use UTL_FILE package.
Read Documentation
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#sthref14093
Search the forum for more examples

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #151674 is a reply to message #151354]

Fri, 16 December 2005 02:35

cgk_js
Messages: 48
Registered: December 2005

Member

but the link ask to sig in .

can u able to paste some sample prgm.

many thanks.

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #151696 is a reply to message #151674]

Fri, 16 December 2005 04:01

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

Login to www.oracle.com, register yourself for free and then use the same username/password to access that page.

It would be worth doing for the future also.

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #151707 is a reply to message #151696]

Sun, 18 December 2005 08:50

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Read sticky?
Please search the board for UTL_FILE.
One such example here.
http://www.orafaq.com/forum/m/73347/42800/?srch=write_log#msg_73347

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #151827 is a reply to message #151707]

Mon, 19 December 2005 06:36

cgk_js
Messages: 48
Registered: December 2005

Member

but i have gone thro, the error invalid path is reached
i have tried for the valid directory too......

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #151848 is a reply to message #151827]

Mon, 19 December 2005 07:34

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Please HELP US to solve your problem.
POST what you did.
EXPLAINING what you did will not help us to help you.
Post your oracle versions, OS, Your complete sessions (in code tags), errors etc.

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #152177 is a reply to message #151848]

Wed, 21 December 2005 09:09

cgk_js
Messages: 48
Registered: December 2005

Member

hi rajendran,
pls find my findings..............

SQL> CREATE OR REPLACE PROCEDURE write_log
2 (
3 fname IN VARCHAR2 ,
4 message IN VARCHAR2 ,
5 mo IN VARCHAR2
6 )
7 IS
8 log_file UTL_FILE.FILE_TYPE; -- file handle for the log file
9 BEGIN
10 log_file := UTL_FILE.FOPEN('c:report',fname,mo);
11 UTL_FILE.PUTF(log_file,'%sn',message);
12 UTL_FILE.FCLOSE(log_file);
13 EXCEPTION
14 WHEN NO_DATA_FOUND THEN
15 DBMS_OUTPUT.PUT_LINE('no_data_found');
16 WHEN UTL_FILE.INVALID_PATH THEN
17 DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
18 WHEN UTL_FILE.WRITE_ERROR THEN
19 DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
20 WHEN OTHERS THEN
21 DBMS_OUTPUT.PUT_LINE('other stuff');
22 END;
23 /

Procedure created.

SQL> begin
2 write_log('sampelfile.txt','hello world!','W');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> set serveroutput on;
SQL> /
UTL_FILE.INVALID_PATH

PL/SQL procedure successfully completed.

i have tried for the valid directory too.............

SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE write_log
2 (
3 fname IN VARCHAR2 ,
4 message IN VARCHAR2 ,
5 mo IN VARCHAR2
6 )
7 IS
8 log_file UTL_FILE.FILE_TYPE; -- file handle for the log file
9 BEGIN
10 log_file := UTL_FILE.FOPEN('c:\gopal',fname,mo);
11 UTL_FILE.PUTF(log_file,'%sn',message);
12 UTL_FILE.FCLOSE(log_file);
13 EXCEPTION
14 WHEN NO_DATA_FOUND THEN
15 DBMS_OUTPUT.PUT_LINE('no_data_found');
16 WHEN UTL_FILE.INVALID_PATH THEN
17 DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
18 WHEN UTL_FILE.WRITE_ERROR THEN
19 DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
20 WHEN OTHERS THEN
21 DBMS_OUTPUT.PUT_LINE('other stuff');
22* END;
SQL>
SQL> /

Procedure created.

SQL> begin
2 write_log('sampelfile.txt','hello world!','W');
3 end;
4 /
UTL_FILE.INVALID_PATH

PL/SQL procedure successfully completed.

Pls help on this .....................
the concept behind is that i need to record the log entries in the unix log
from oracle and send the mail to the users regarding this.

Pls find the oracle version

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #152178 is a reply to message #152177]

Wed, 21 December 2005 09:17

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Apprently the procedure says that it cannot locate the filepath.
1. Did you create the directories as sys and also give the right persmissions to the calling user? ( As said in docs).
2.
>> 10 log_file := UTL_FILE.FOPEN('c:report',fname,mo);
Are you sure you have path c:report in an unix machine?

May be we are over complicating things here.
Why not just spool your output and send it as email?

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #152458 is a reply to message #152178]

Fri, 23 December 2005 05:40

cgk_js
Messages: 48
Registered: December 2005

Member

Hi Mahendran,
I have created
the directory called /extract/gopal
for which i have given full permission.

chmod 777 /extract/gopal

still i find the invalid path error message is displayed.

also i the oracle version is the client version.

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #152474 is a reply to message #152458]

Fri, 23 December 2005 07:25

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

did you create the directory from oracle using "create directory " logged in as SYS?
>>chmod 777 /extract/gopal
These are unnecessary.
Read the link.
REad documentation.

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #152486 is a reply to message #151258]

Fri, 23 December 2005 08:01

cgk_js
Messages: 48
Registered: December 2005

Member

sorry i did not create with the
oracle
create directory <directory> as <path>

since i am not sys user ,

is there any possible solution to log down the without this.

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #152489 is a reply to message #152486]

Fri, 23 December 2005 08:16

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Said before, read documentation.

and you can just spool the sql output.

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #152665 is a reply to message #152489]

Mon, 26 December 2005 03:28

cgk_js
Messages: 48
Registered: December 2005

Member

Hi mahesh Rajendran,
The scenario for me is that,
our projects has many trigerrs inbuilt for it, and the error message catched it self returns about 2000 character and also
exceeds more than that,
but standard buffer limits gets spooled up and
that why i want to write it in the file instead of displaying the error which is being catched in the exception.

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #152686 is a reply to message #152665]

Mon, 26 December 2005 07:21

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Whatever it is ( your scenario),
You have only two solutions.
spool the output (clientside only)
or
Use UTL_FILE (serverside only).
Use either one of them.

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #152827 is a reply to message #151258]

Tue, 27 December 2005 03:11

cgk_js
Messages: 48
Registered: December 2005

Member

exception
when others then
v_error := sqlerrm;
dbms_output.put_line('Error while updating Product code[' || v_prod_code || '] ' ||v_error);

when i execute the following error message is displayed.

i have also added the following line in the procedure,but still it is getting displayed.

dbms_output.enable(1000000);

ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 133
ORA-06512: at line 46

/

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #152840 is a reply to message #151258]

Tue, 27 December 2005 03:40

cgk_js
Messages: 48
Registered: December 2005

Member

Hi mahesh,
I have also used
set serveroutput on size 1000000 wrapped;

but still the same error like sql buffer up to 255 chars is being displayed.

Then using the err length and using substr function
i have manually done to display the error, but it is not
wrapped.

is there any solutions exits for it.

Thanks
Gopal

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #152871 is a reply to message #152840]

Tue, 27 December 2005 06:37

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

create your own table to hold the errors( with a long column).
Insert the errors into the table (with long column).
query the table.

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #152936 is a reply to message #152871]

Wed, 28 December 2005 00:29

cgk_js
Messages: 48
Registered: December 2005

Member

Thanks Mahesh Rajendran, i have been using of Varchar2 datatype of length 2000 and storing the sqlerrm in the Table.

please tell me, can we use raw datatype if possible.

Please provide me the storing and retreiving syntax.

Thanks for the Valuable information. Cool

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #153018 is a reply to message #152936]

Wed, 28 December 2005 07:07

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Try something like this.
THe easiest method is to use UTL_FILE.

  1  declare
  2  code varchar2(30);
  3  err  varchar2(1000);
  4  begin
  5  execute immediate ('drop table NONEXSITENT_TABLE ');
  6  exception
  7   when others then
  8   code:=SQLCODE;
  9   err:=SQLERRM;
 10  insert into errtable values (code,err);
 11* end;
scott@9i > /

PL/SQL procedure successfully completed.

scott@9i > select * from errtable;

ERRCODE                        ERRMSG
------------------------------ ------------------------------------------------------------
-942                           ORA-00942: table or view does not exist

scott@9i > desc errtable
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ERRCODE                                                        VARCHAR2(30)
 ERRMSG                                                         CLOB

Report message to a moderator

Re: ORACLE LOG writting in unix from pl/sql [message #153098 is a reply to message #151258]

Thu, 29 December 2005 01:16

cgk_js
Messages: 48
Registered: December 2005

Member

Thanks for the reply.

I could not use UTL_FILE since i don;t have the privelage
on create directory

Thanks for the CLOB syntax, Cool

Regards
Gopal

Report message to a moderator

Previous Topic:	error while exporting
Next Topic:	query in exporting only structure of tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 09 00:58:04 CST 2024