, , , , ,

Query to List all the responsibilities attached to a User

Based on a request from one of the reader here is the query which he was looking for.

He needed query that can list all the responsibilities attached to a user.

select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv(‘LANG’)

/by
, , , , , ,

Create Trace File for Concurrent Program

If your program is taking time to complete, then the best way to know what is causing the problem is by creating a trace file.

Navigation:
System Administrator(R) –> Concurrent –> Program –> Define

Query for the concurrent program and check enable trace button.

Now when the concurrent program is executed the trace file is created in the udump directory. The path of udump directory can be found by executing following query.
select * from v$parameter
where name like ‘%user_dump_dest%’


The trace file can be converted to a readable format by running a tkprof command over the trace file.
Syntax:

tkprof [trace_file_name] [new_file_name]

Understanding TKPROF
The TKPROF program can be used to format the contents of the trace file and convert it into a readable output file.
TKPROF can also be used to generate Explain Plan for the queries.
I will create a seperate post to discuss various options available with TKPROF.
/by
, , , , , , ,

Set Profile Option Value using PL/SQL

On request here is how to set the profile option value using PL/SQL

Function FND_PROFILE.SAVE can be used to set the value of any profile option at any level i.e. Site, Application, Responsibility, User etc.

Below is a sample code of how to use this function

DECLARE
   a   BOOLEAN;
BEGIN
   a := fnd_profile.SAVE (‘CONC_REPORT_ACCESS_LEVEL’
                        , ‘R’
                        , ‘USER’
                        , ‘22746’
                        , NULL
                        , NULL
                         );
   IF a
   THEN
      DBMS_OUTPUT.put_line (‘Success’);
      COMMIT;
   ELSE
      DBMS_OUTPUT.put_line (‘Error’);
   END IF;
END;

/by
, , ,

Insert BLOB image file in oracle database table

Here we will discuss how to insert BLOB file in the database. For this we will create a table and then a procedure that will be used to insert records in the table.

Use following script to create an employee table

CREATE TABLE SV_EMP_PHOTO
(
  ID          NUMBER(3) NOT NULL,
  PHOTO_NAME  VARCHAR2(40),
  PHOTO_RAW   BLOB,
  EMP_NAME    VARCHAR2(80)
)

Create a directory where the photos will be stored. I am creating a directory in UNIX as our database is created in UNIX.

Create directory SV_PHOTO_DIR as ‘/u002/app/applmgr/empphoto’

Script to create a procedure SV_LOAD_IMAGE that will insert records in the table.

CREATE OR REPLACE PROCEDURE sv_load_image (
   p_id                NUMBER
 , p_emp_name     IN   VARCHAR2
 , p_photo_name   IN   VARCHAR2
)
IS
   l_source   BFILE;
   l_dest     BLOB;
   l_length   BINARY_INTEGER;
BEGIN
   l_source := BFILENAME (‘SV_PHOTO_DIR’, p_photo_name);

   INSERT INTO sv_emp_photo
               (ID
              , photo_name
              , emp_name
              , photo_raw
               )
   VALUES      (p_id
              , p_photo_name
              , p_emp_name
              , EMPTY_BLOB ()
               )
   RETURNING   photo_raw
   INTO        l_dest;

   — lock record
   SELECT     photo_raw
   INTO       l_dest
   FROM       sv_emp_photo
   WHERE      ID = p_id AND photo_name = p_photo_name
   FOR UPDATE;

   — open the file
   DBMS_LOB.fileopen (l_source, DBMS_LOB.file_readonly);
   — get length
   l_length := DBMS_LOB.getlength (l_source);
   — read the file and store in the destination
   DBMS_LOB.loadfromfile (l_dest, l_source, l_length);

   — update the blob field with destination
   UPDATE sv_emp_photo
   SET photo_raw = l_dest
   WHERE  ID = p_id AND photo_name = p_photo_name;

   — close file
   DBMS_LOB.fileclose (l_source);
END –sv_load_image;
/
I have copied few .jpg images in /u002/app/applmgr/empphoto in UNIX.
Execute the procedure as follows to create record in database

exec sv_load_image(1,’Pavki’,’one.jpg’)
exec sv_load_image(2,’Suresh’,’two.jpg’)
exec sv_load_image(3,’Rachna’,’three.jpg’)

/by
, , , , , ,

Resolving ORACLE ERROR:ORA-28000: the account is locked

From your command prompt, type
sqlplus “/ as sysdba”

Once logged in as SYSDBA, you need to unlock the scott account
SQL> alter user scott account unlock;
SQL> grant connect, resource to scott;

/by