, , , ,

Create directory in Oracle

Create directory in Oracle

create or replace directory foo_dir as ‘/tmp’;

Directories must be created if external tables are used.
Created directories are shown in either dba_directories or all_directories. There is no user_directories.

Privileges

When a «directory» has been created, the read and write object privileges can be granted on it:

create directory some_dir;
grant read, write on directory some_dir to micky_mouse;

An example

The following example shows how create directory and utl_file can be used to write text into a file:

create or replace directory dir_temp as ‘c:temp’;

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen(‘DIR_TEMP’, ‘something.txt’, ‘w’);
  utl_file.put_line(f, ‘line one: some text’);
  utl_file.put_line(f, ‘line two: more text’);
  utl_file.fclose(f);
end;
/

/by
, ,

QUERY FOR FINDING REQUEST GROUP

SELECT fa.application_short_name,
       frg.request_group_name,
       fe.execution_file_name,
       fe.executable_name
  FROM fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_request_groups frg,
       fnd_executables fe,
       fnd_application fa
 WHERE     frgu.request_unit_id = fcp.concurrent_program_id
       AND frgu.request_group_id = frg.request_group_id
       AND fe.executable_id = fcp.executable_id
       AND FRG.application_id = fa.application_id
       AND fe.executable_name = ‘XX_PC_PURCHASE’;
/by
, , , ,

BULK COLLECT

Bulk Collect concept help us reducing the Iterations between SQL Engine and PL/SQL Block.

Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine. And if we have some SELECT statement in the PL/SQL Block, then it will move from SQL Enginer to get the data from SQL Enginer to the PL/SQL Block. For the SELECT statement, we use the BULK COLLECT to record the Iteration from the SQL Enginer to the PL/SQL Block.

If I have to put my self in other word then, I should say, BULK COLLECT will process group of SELECT statements at one short.

Similarly for the DML Operations, we use FORALL Concept, please refer the FORALL Concept for more details.

Note:- This Example is based on the EMP table in the SCOTT schema. Here say, EMP table have 90 Records and if we do it in the Normal way (Without BULK COLLECT concept, then It will iterate SQL enginer to the PL/SQL Block for 90 Times). By using BULK COLLECT, we can completly reduce the Iterations.

In the following Example, I have limited to 10, so for 90 records, this will iterate for 9 times.

Example:-
———–

declare
type array is table of number index by binary_integer;
l_data array;
cursor c is select empno from emp;
begin
open c;
loop
fetch c bulk collect into l_data limit 10;
if ( c%notfound )
then
dbms_output.put_line
( ‘Cursor returned NOT FOUND but array has ‘ || l_data.count
|| ‘ left to process’ );
else
dbms_output.put_line
( ‘We have ‘ || l_data.count
|| ‘ to process’ );
end if;
exit when c%notfound;
end loop;
close c;
end;

Returning clause with the Bulk collect with the DML opeartions:-
—————————————————————————

DELETE FROM emp WHERE num = 30
RETURNING empno, ename BULK COLLECT INTO p_num, p_ename;
/by
, , ,

How to find concurrent program attached to which responsibility

You know the concurrent program name, but you do not know to what all responsibilities it is attached to. In that case, you can use the following select statement to know the responsibilities names to which your concurrent program is attached.

select responsibility_name
from fnd_responsibility_tl rsp_tl, fnd_responsibility fr, –fnd_request_groups frg,
fnd_request_group_units frgu, fnd_concurrent_programs_tl fcpt
where rsp_tl.responsibility_id = fr.responsibility_id
–and frg.request_group_id = fr.request_group_id
and fr.request_group_id = frgu.request_group_id
and fcpt.concurrent_program_id = frgu.request_unit_id
and upper(fcpt.USER_CONCURRENT_PROGRAM_NAME) = upper(‘concurrent program name’);

Example:-

select responsibility_name
from fnd_responsibility_tl rsp_tl, fnd_responsibility fr, –fnd_request_groups frg,
fnd_request_group_units frgu, fnd_concurrent_programs_tl fcpt
where rsp_tl.responsibility_id = fr.responsibility_id
–and frg.request_group_id = fr.request_group_id
and fr.request_group_id = frgu.request_group_id
and fcpt.concurrent_program_id = frgu.request_unit_id
and upper(fcpt.USER_CONCURRENT_PROGRAM_NAME) = upper(‘OracleERPApps Test Program’);

/by
, ,

How to Select the Nth highest / lowest value from a table

I am posting this script as lot of my friends have asked about this. They are lot of ways to do this. I am showing you in two best ways.

Note:- In ‘N’ place, provide your Nth number you want to findout.

How to Select the Nth highest value from a table:-
————————————————-

select level, max(sal) from scott.emp
where level = ‘&n’
connect by prior (sal) > sal
group by level;

How to select the Nth lowest value from a table:-
——————————————————-
select level, min(sal) from scott.emp
where level = ‘&n’
connect by prior (sal) < style=”font-weight: bold;”>N th Top Salary
——————-

select a.ename,a.sal
from emp a
where n = (select count(distinct(b.sal))
from emp b where a.sal <= b.sal) N th Least Salary
———————
select a.ename,a.sal
from emp a
where n = (select count(distinct(b.sal))
from emp b
where a.sal >= b.sal)
/by