WRITE TO A FILE USING UTL_FILE

• Create a Table

 Create Table xx_parameters (

 param_name VARCHAR2(100) ,

 param_value NUMBER );

• Input some parameters
• Create a Directory 

CREATE OR REPLACE DIRECTORY DIR_MYTEMP AS '/home/oracle/ebsutl12/';

• Create the Procedure

CREATE OR REPLACE PROCEDURE xxtest_procedure_utl

AS

   CURSOR c_cur1

   IS

      SELECT param_name, param_value

        FROM xx_parameters;

   l_file   UTL_FILE.file_type;

   l_dir    VARCHAR2 (50)      := 'DIR_MYTEMP';

   l_name   VARCHAR2 (20)      := 'mydatafile.txt';

BEGIN

   l_file := UTL_FILE.fopen (l_dir, l_name, 'W');

   FOR cur_rec IN c_cur1

   LOOP

      UTL_FILE.put_line (l_file,

                         cur_rec.param_name || ',' || cur_rec.param_value

                        );

   END LOOP;

   UTL_FILE.fclose (l_file);

EXCEPTION

   WHEN UTL_FILE.invalid_path

   THEN

      DBMS_OUTPUT.put_line (‘invalid_path’);

      RAISE;

   WHEN UTL_FILE.invalid_mode

   THEN

      DBMS_OUTPUT.put_line (‘invalid_mode’);

      RAISE;

   WHEN UTL_FILE.invalid_filehandle

   THEN

      DBMS_OUTPUT.put_line (‘invalid_filehandle’);

      RAISE;

   WHEN UTL_FILE.invalid_operation

   THEN

      DBMS_OUTPUT.put_line (‘invalid_operation’);

      RAISE;

   WHEN UTL_FILE.read_error

   THEN

      DBMS_OUTPUT.put_line (‘read_error’);

      RAISE;

   WHEN UTL_FILE.write_error

   THEN

      DBMS_OUTPUT.put_line (‘write_error’);

      RAISE;

   WHEN UTL_FILE.internal_error

   THEN

      DBMS_OUTPUT.put_line (‘internal_error’);

      RAISE;

END xxtest_procedure_utl;

/

• Executing the procedure will create a file named mydatafile.txt in directory /home/oracle/ebsutl12/

Sample of DBMS_LOCK.SLEEP

• Create a Table

Create Table T_Sleep_Test(
S_No Number,
Dateandtime Date);

• Create a Procedure

CREATE OR REPLACE PROCEDURE XXTEST_PROCEDURE

IS

   l_cnt   NUMBER;

BEGIN

   l_cnt := 0;

   EXECUTE IMMEDIATE 'Truncate Table Xxsleep_table';

   FOR l_cnt IN 1 .. 5

   LOOP

      INSERT INTO xxsleep_table

           VALUES (l_cnt, SYSDATE);

      COMMIT;

      DBMS_LOCK.sleep (10);

   END LOOP;

END;

• Values get inserted into the table created above in an interval of 10 seconds

Exec XXTEST_PROCEDURE;

• Check once the execution of the procedure gets completed.

Select S_No, To_Char(Dateandtime,'DD-MON-RRRR HH24:MI:SS') Ace From   Xxsleep_table;

Locked Session in Oracle

Select L.Session_Id||','||V.Serial# Sid_Serial,
       L.Oracle_Username Ora_User,
       O.Object_Name,
       O.Object_Type,
       Decode(L.Locked_Mode,
          0, 'None',
          1, 'Null',
          2, 'Row-S (SS)',
          3, 'Row-X (SX)',
          4, 'Share',
          5, 'S/Row-X (SSX)',
          6, 'Exclusive',
          To_Char(L.Locked_Mode)
       ) Lock_Mode,
       O.Status,
       To_Char(O.Last_Ddl_Time,'dd.mm.yy') Last_DDL
From Dba_Objects O, Gv$Locked_Object L, V$Session V
Where O.Object_Id = L.Object_Id
      And L.Session_Id=V.Sid
Order By 2,3;

------------------------

ALTER SYSTEM KILL SESSION  'SID,SERIAL'; ( '633,857' );