To know the Application version before upgrade and current version

To know the Application version before upgrade and current version:-
---------------------------------------------------------------------------------

If there is some oracle Aplication Instance say (11.5.8) got upgrade to R12. After upgarding the Instance/Application, If I want to know the older version. Then use the following select statement.

SELECT SUBSTR(snapshot_name,12,8)
FROM ad_snapshots
WHERE comments = 'Preseeded'
AND REPLACE(SUBSTR(snapshot_name,12,8),'.') =
(
SELECT max(to_number(replace(SUBSTR(snapshot_name,12,8),'.')))
FROM ad_snapshots
WHERE comments = 'Preseeded'
);

Use the following to know the current Oracle Apps Version.
---------------------------------------------------------------------

SELECT release_name FROM fnd_product_groups;

How to know the columns of given table and status of the table or object given

How to know the columns of given table and status of the table or object given:-
-------------------------------------------------------------------------------------------

I know the Object Name (Object can be anything like Package, Procedure, Function, Table, View or synonym name etc), If I want to check whether that object exist in the database or not. If exist then, is it active or what is the owner of object etc. All the details can be found from the following select statement.

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = 'your object name';

Example:-

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = 'EMP';

I know the table name and from backend I want to know the column name, data_type, date_length etc of the given table. For this, you can use the following Query.

SELECT column_name,data_type,data_length FROM dba_tab_columns
WHERE table_name = 'Your Table Name';

Example:-

SELECT column_name,data_type,data_length FROM dba_tab_columns
WHERE table_name = 'EMP';

Query find Implementation

Query find LOV Implementation

EMPNO PRIMARY KEY

At Block levl(Pre-Query) trigger

If :PARAMETER.G_QUERY_FIND='TRUE' then

:EMP.EMPNO=:PARAMETER.P_EMPNO;

:PARAMETER.G_QUERY_FIND='FALSE';

END IF;

In block level query_find trigger

APP_FIND.QUERY_FIND('LOV106');

Query find WINDOW LEVEL Implementation

Open Template.fmb

Create query block

Open Appstand.fmb

Copy QUERY_FIND Object group to TEMPLATE.fmb at Object Group

Automatically 03 objects created in the template.fmb

WindowàQUERY_FIND

CanvasàQUERY_FIND

BlockàQUERY_FIND

Buttons New,Cancel,Find

Close the Appstand.fmb

Rename the default objects created by QUERY_FIND object group

WIN_QUERYFIND, CANVAS_QUERYFIND, BLOCK_QUERYFIND

Create one text_item for query criteria on BLOCK_QUERYFIND

Modification of code at button level(New/Find) at

(Trigger Name : KEY-NXTBLK)

New Button : app_find.new('EMP_BLK') --Query Block name

Find Button : :parameter.G_QUERY_FIND:='TRUE';

app_find.find('EMP_BLK');

:parameter.G_query_find:='FALSE'

BLOCK_QUERYFIND(Trigger Name : KEY-NXTBLK)

:parameter.G_query_find:='TRUE'

app_find.find('EMP_BLK');

:parameter.G_query_find:='FALSE'

PRE-QUERY TRIGGER-BLOCK LEVEL(Query Block )

If :PARAMETER.G_QUERY_FIND='TRUE' then

:EMP.EMPNO=:PARAMETER.P_EMPNO;

:PARAMETER.G_QUERY_FIND='FALSE';

END IF;

QUERY_FIND TRIGGER AT BLCOK LEVEL(Query Block )

APP_FIND.QUERY_FIND('EMP_BLK','WIN_QUERYFIND',BLCOKNAME_QUREYFIND);

--
Thanks,
Shashidhar

Find out the Patch Level

Find out the Patch Level:-
------------------------------

You may need to find-out the patch level In some cases. Specially, this may required when you have created the tar with Oracle corp. The following statement is mostly run by DBA

select patch_level, application_name
from fnd_product_installations fpi
, fnd_application_tl fat
where patch_level is not null
and fpi.application_id = fat.application_id
order by application_name;

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

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)

udump path

To know the udump path:-
------------------------------

When the trace is ON from the Front end. The trace file is stored in some location on the server.There is a way to findout from the front end. But, there is a easy way to know from the backend as well with SELECT statement.

Use the following statement to know from the udump path:-
-----------------------------------------------------------

SELECT name,value from v$parameter WHERE name like 'user_dump_dest'

Compile all the objects in given schema

To compile all the objects in given schema :-
--------------------------------------------------

You may have the requirement to compile all the invalid objects. in particular schema like 'APPS' or 'SCOTT' etc. This can be done in may ways. There is option to compile the Invalid objects in APPS schema in Admin utility I have documented about the Adadmin utility in the Unix section. Check out the Unix for the Adadmin utility.

begin
SYS.UTL_RECOMP.RECOMP_PARALLEL('N', 'Schema_name');
end;
/

To compile all of the invalid objects owned by APPS
-------------------------------------------------------------

Note: must use UPPER case on the schema name

begin
dbms_utility.compile_schema('APPS');
end;
/

concurrent program attached to which responsibility

findout which concurrent program attached to which all 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('ALLOracleTech Test Program');

Invoker or Definer of package

To know whether the package is INVOKER or DEFINER In APPS Schema:-
-------------------------------------------------------------------------------------

You know package name and you want to know whether it is definer or Invoker from the select statement then you can know from the following satement.

Know more about the AUTHID

SELECT dbo.object_name,
(DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid"
FROM dba_objects dbo,
sys.PROCEDURE$ p
WHERE p.obj# = dbo.object_id
AND dbo.object_name = 'package_name'
AND dbo.object_type = 'PACKAGE'
AND dbo.owner = 'APPS'

Example:-
-----------

SELECT dbo.object_name,
(DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid"
FROM dba_objects dbo,
sys.PROCEDURE$ p
WHERE p.obj# = dbo.object_id
AND dbo.object_name = 'ASO_APR_WF_INT'
AND dbo.object_type = 'PACKAGE'
AND dbo.owner = 'APPS'

Check the version of the file

1) To check the version of file:-
--------------------------------

There might be so some file exisits in many Oracle Apps versions. For different version, code in the file might be different. Oracle Corp track this changes in the file with the version number of the file. We may need this information something when we raising the tar with oracle corp.

The following script will help you finding the version of any file which is executed for your Oracle Application version.

CREATE OR REPLACE PROCEDURE alloracletech_ver(file_name VARCHAR2) AS
l_file_id NUMBER;
l_file_version_id NUMBER;
l_version NUMBER;
l_file_name varchar2(20);
l_app_short_name varchar2(10);
BEGIN
l_file_name := trim(upper(file_name));
select file_id,app_short_name into l_file_id,l_app_short_name from ad_files
where upper(filename) = l_file_name and rownum =1;
select file_version_id into l_file_version_id from ad_check_files
where file_id= l_file_id and rownum = 1;
select version INTO l_version from ad_file_versions
where file_version_id=l_file_version_id
and file_id=l_file_id and rownum = 1;
dbms_output.put_line('version for the file '||file_name||' : '||l_version);
--insert into v_file_table values (file_name,l_version,l_app_short_name );
commit;
END alloracletech_ver;
/

begin
alloracletech_ver('file_name');
end;

Example:-

>set serverout on

begin
file_ver('POXGCHGS.pls');
end;
/

Unix Commands

1) Editing the FILE:-

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

 *) To come to end of file:- G

 *) To delete the single word(can be free space):- dw

 *) to delete the current single char:- (Esc and x) 

 *) To delete the complete line:- dd 

 *) To right over in the file:- cw 

 *) To remove the case sensitive:- :set ic

 *) To Enable the case sensitive:- :set noic 

 *) Copy the complete line:- yy 

 *) Paste what ever copied:- p 

 2) GUN ZIP the file:-

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

gzip file_name 

Note:- While trying to download gunzip file from server to local machine it should be done in binary mode. Then it can exact using the wipzip software in WINDOWS Operating System. After gzip, if we want to unzip the file then use the following. gunzip file_name 

2.1) To unzip or gunzip the file :- 

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

gunzip file_name Example:- gunzip XYZ_1.0_5.tar.gz 

2.2) To untar the file:- 

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

tar -xvf file_name Example:- tar -xvf XYZ_1.0_5.tar 

 3) To run the strings for the spawand program:- 

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

strings -a (shell_script_file_name) > (logfile.log) 

Example:- strings -a GLPURGE > /home/partners-home/hex/preddy/phani1.log 

3.1) Basic grep command:- 

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

Example:- grep 'DROP index' adwork001.log > /home/partners-home/xyz/preddy/dropindex.txt 

Note:- grep command is just like the search for some specfic word in your windows file. In Windows Operating System, we use the (CTRL+F) and then we give the word we want to search. Similarly, grep is used for UNIX or LINUX Operating system. In the above Example, I am searching for the 'DROP index' Word in adwork001.log file. And I want all the finding of this search stored in some file. So, I have given the path and file name where it should save. 

4) Specfic to some project:-

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

To check the number lst files:-

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

Example:- ls -altr *.lst | wc -l

Note:- If some folder have some specfic extension files, then we can count the number of that specfic extension files then we can use the above command. In the above example, I am searching for the 'lst' extension files count.

To check the number of sql files with hit files :-

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

grep '\.sql' *.lst | wc -l Note:- Say, we have some .sql files names in all the lst files in some folder. Then we can use the grep command to count the sql file names.

To check the number of files with hits in the lst file:-

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

grep 'Number of files with hits : [1-9]' *.lst

Note:- You can also give the count of the files with the range. In the above example, I have givne the range from 1 to 9. If it find any number from this range then, it will get in the count.

To create the file of the complete module :-

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

grep '\.sql' *.lst > lst_sql_'module_name'.txt

Example:- grep '\.sql' *.lst > lst_sql_gl.txt

Note:- The above example to do the search and to the search results in some given file name.

5) To find the file in the current folder and subfolder:-

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

 find . -name ‘file_name*.sql’ -print

Example:- find -i . -name 'poXWFUNT*.sql' -print 

 Note:- POXWFUNT.sql is the file name which I want to search. 'i' in the above command indicates that, search should not be based on case sensitive. 

 6) To grep the file for a specific word:- 

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

 grep -i 'WORD' FILE_NAME > log_file_name 

Example:- grep -i 'ALTER table' adwork001.log > altertable.txt 

 7) To Know the PORT number of Env file:- 

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

 setenv | grep PORT 

Note:- Run the above command after login to Instance. And you have would have the read privileges for the file. This is mostly useful for people, who are working in the Oracle Application. 

7.1) For specfic session we can change the variable value:- 

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

 setenv variable_name variable_value

  Example :- The following was the current value. INSTANCE_ENVID=XYZ12 (Used the following command to check the value (setenv | grep OBT)). setenv OBTADMIN_ENVID OBT_12 

The above command has changed the value of the variable. 

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

 OBTADMIN_ENVID=OBT_12 

  Note:- If you exit from the session and then variable value will be revert back. 

 8) To know the path of the log and out files of the application:- 

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

 >>cd $APPLCSF 

 There you will have the log and out folders. You can also try the following command to know the value of the APPLCSF variable. env | grep log

 9) Turn-on the application server:- 

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

 ./oraWebAndForms.sh start Instance name 

  Example:- ./oraWebAndForms.sh start XYZ12 

  Note:- 

------

1) XYZ12 is the Instane name. 

2) After turnning the Database and concurrent manager up. still the application front end will not open. The you need to run the above script from the applmgr user 

  To login to different user:- 

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

 sudo su - applmgr 

Note:- applmgr is the superuser name. This username can depends on the DBA, what they have given at your Instance.

 9.1) If you are getting 500 Internal error message after bounce back then try this:-

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

 Try to restart your application and restart your manage and see if it resolve the issue. 

 For the 12 Release, the above solution may not work. After you cleared _pages in R12, you'll need to recompile because the default mode is 'justrun' instead of 'recompile' as was in 11i. I did the following while applmgr in my Instance XYZ12 env and the login now works: 

 1. cd $FND_TOP/patch/115/bin

 2. ./ojspCompile.pl --compile --flush -p 2 (You can check if you are able to open the application or not). If you are still facing the issue then try rebounce the application and see if it resolve the issue). 

 3. /home/applmgr/oraWebAndForms.sh bounce xyz12 (./oraWebAndForms.sh bounce zyx12)

  Note:- 

------ 

You need to run the above script in the applmgr user sudo su - applmgr 

 10) To start or stop the Internal concurrent manager:-

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

 cd $ADMIN_SCRIPTS_HOME/ ./adcmctl.sh start apps/apps ./adcmctl.sh stop apps/apps

  Note:- This need to be run from applmgr user ----- sudo su - applmgr 

 11) To stop the database or start the database:- 

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

 cd $ORACLE_HOME/appsutil/scripts/xyz12_server/ 

 Note:- The above path is specfic to my server Instance. 

 To stop the database:- adstopdb.sql* 

 To start the database:- adstrtdb.sql* 

  Note:- This need to be run from Oracle user. ------ sudo su - oracle 

 12) Converting all the tabs to space in the file:-

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

 By setting the following options in your ~/.vimrc file, when you use tabs, spaces are actually entered into the file, although it "feels" like there are tabs because backspaces work as they would with tabs. You'll never have to worry about differing tabstop standards again. Try it out, I promise you’ll be completely sold! set softtabstop=4 set expandtab set shiftwidth=4 Note also that if you want to convert a file with tabs to all spaces, set the above options and then use: :retab! 

 13) To know the Operating system on server (unix):-

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

 >uname

  Example:-

 -----------

 2007.08.06-3:18:03 preddy@oscar[6]/home/partners-home/xyz/phani/xyz_23Jul/abc/la/utils > uname HP-UX 

14) To know the Operating system on server from database:- 

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

 begin dbms_output.put_line('Port String: '||dbms_utility.port_string); end; 

 15) Admin utility to compile all the invalid objects:-

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

 i) This utility is available for the applmgr user. Use the following command to login with applmgr user. >>sudo su - applmgr

 ii) Choose the Instance and then use the following command to run the adadmin utility. >>adadmin { Note:- It will ask for the $APPL_TOP path. If the default path is not the correct one then provide the correct path. It will ask for the Log file name. (Default name will be (adadmin.log)). It will ask if you can be notified by email if a failure occurs. (Default will be set for NO). It will ask for the Batchsize (Default is 1000). It will ask for the correct database name. It will ask to enter the password for your 'SYSTEM' ORACLE schema.("manager" will be the password). (manager password will be the one you enter manually. Rest of things you take the default values.) It will ask to enter the ORACLE password of Application Object Library (default will be [APPS]). }

 iii) To will give the menu with different options. 

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

 We will choose the "Compile/Reload Applications Database Entities menu" option. It will be 3 option normally.  

iv) To will again give the other set of menu optins. 

----------------------------------------------- We will choose "Compile APPS schema" option to compile all the Invalid objects in APPS schema. (Note:- You can also use $ORACLE_HOME/rdbms/admin/utlrp.sql (which will compile all invalid objects thru SVRMGRL.)).

 17) To know apps password:- 

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

 cd $ORACLE_HOME/reports60/server more CGIcmd.dat You will find userid=APPS/apps_pass@Instance_name 

  Note:- If you have the FTP access for server files. Then we can know the Apps password. 

 18) QUOTA:- 

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

Say employees are allocated a certain amount of disk space on the file system for their personal files, say 1000Mb. If you go over your quota, you are given some 'n' days to remove excess files.

To check your current quota and how much of it you have used. For that,you can use the following command.

> quota -v

19) DF:- 

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

To find out how much space is left on the fileserver, use the following command.

> df .

20) DU:-

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

The du command outputs the number of kilobytes used by each subdirectory. Useful if you have gone over quota and you want to find out which directory has the most files. From your home-directory, use the following command.

> du -s *

The -s flag will display only a summary (total size) and the * means all files and directories.

21) GZIP:-

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

This reduces the size of a file, thus freeing valuable disk space. This compresses the file. it similar to the Winzip in the Windows Operating system.

Example:-

> ls -l phani.txt

note the size of the file using ls -l , then to compress phani.txt, use the following command.

> gzip phani.txt

This will compress the file and place it in a file called phani.txt.gz

To see the change in size, use ls -l again.

To expand the file, use the gunzip command.

> gunzip phani.txt.gz

This is similar to the Unzip the file in your Windows Operating system. 

 22) ZCAT:-

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

zcat will read gzipped files without needing to uncompress them first.

> zcat phani.txt.gz

If the text scrolls too fast for you, pipe the output though less .

> zcat phani.txt.gz | less

23) FILE:-

File classifies the named files according to the type of data they contain, for example ascii (text), pictures, compressed data, etc.. To report on all files in your home directory, use the following command.

> file *

24) DIFF:- 

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

This command compares the contents of two files and displays the differences. Say you have a file called file1 and you edit some part of it and save it as file2. To see the differences use the following command.

> diff file1 file2

25) ECHO:- 

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

  echo $variable_value The value of the environment variable can get from the echo command. 

  Example:- echo $APPL_TOP In the above command we get the APPL_TOP variable value set in the environment file. APPL_TOP is the environment variable in the environment file.

25) REPLACE Character in File:- 

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

Say, we want to replace the ~ or single Quote with Enter or some other symbol. 

Example:- sed -i 's/~/\n/g' OUT_*.TXT 2>/dev/null

In this command, "~" would be replaced with enter ("/n") 

Example:- sed -i 's/'\''/\n/g' edidata.* 2>/dev/null

In this command, \''  Note: for single quote "  '  " , we have to include the wild string too. 

26) General Commands:- 

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

 

Command	Meaning
ls	list files and directories
ls -a	list all files and directories
mkdir	make a directory
cd directory	change to named directory
cd	change to home-directory
cd ~	change to home-directory
cd ..	change to parent directory
pwd	display the path of the current directory
cp file1 file2	copy file1 and call it file2
mv file1 file2	move or rename file1 to file2
rm file	remove a file
rmdir directory	remove a directory
cat file	display a file
less file	display a file a page at a time
head file	display the first few lines of a file
tail file	display the last few lines of a file
grep 'keyword' file	search a file for keywords
wc file	count number of lines/words/characters in file
command > file	redirect standard output to a file
command >> file	append standard output to a file
command < file	redirect standard input from a file
command1 | command2	pipe the output of command1 to the input of command2
cat file1 file2 > file0	concatenate file1 and file2 to file0
sort	sort data
who	list users currently logged in
*	match any number of characters
?	match one character
man command	read the online manual page for a command
whatis command	brief description of a command
apropos keyword	match commands with keyword in their man pages
ls -lag	list access rights for all files
chmod [options] file	change access rights for named file
command &	run command in background
^C	kill the job running in the foreground
^Z	suspend the job running in the foreground
bg	background the suspended job
jobs	list current jobs
fg %1	foreground job number 1
kill %1	kill job number 1
ps	list current processes
kill 26152	kill process number 26152

User Guides

User Guide

11

11.5.1

11.5.2

11.5.3

11.5.4

11.5.5

11.5.6

11.5.7

11.5.8

11.5.9

11.5.10.2

Release 12

Download links

Download links

Oracle SQL Developer: Click Here

Reports n Forms 6i: Click here

XML Publisher: Click here

Putty: Click here

Winscp (FTP Tool): Click here

Developer suite 10g (Including reports, forms, Jdeveloper): Click here

BI Publisher: Click here

Workflow Builder: Click here

Toad Free Trail: Click here

JDeveloper n ADF Software: Click here

Warehouse Builder Software: Click here

Discoverer Desktop Software: Click here

BPEL Software: Click here

JInitiator: Click here

Oracle Database 9i: Click here

Oracle Database 10g:Click Here

Oracle Database 11g: Click Here

Oracle Linux: Click here

Oracle Virtual Machine (VM): Click here

Oracle Applications 11i and R12: Click here

AUTHID DEFINER V/S AUTHID CURRENT_USER

There are lot of times we get error in Oracle Apps while trying to execute the API's at development time, due to the AUTHID DEFINER and AUTHID CURRENT_USER. This article gives you good understanding about the AUTHID DEFINER and AUTHID CURRENT_USER.

A stored procedure runs either with the rights of the caller (AUTHID CURRENT_USER) or with the rights of the procedure's owner (AUTHID DEFINER). This behaviour is specified with the AUTHID clause. This authid clause immediatly follows the create procedure, create function, create package or create type statement. It can be ommited, in which case the default authid definer is taken.


AUTHID DEFINER and AUTHID CURRENT_USER
-----------------------------------------------------------
AUTHID DEFINER:-
--------------------

Example:-
---------
The following are done in APPS scheme.

create table a (a_a number);

CREATE OR REPLACE PACKAGE XYZ AUTHID DEFINER
AS
PROCEDURE XYZ_1;
END XYZ;

CREATE OR REPLACE PACKAGE body XYZ
AS
PROCEDURE XYZ_1
IS
BEGIN
INSERT INTO A VALUES (1);
END XYZ_1;
END XYZ;

begin
xyz.XYZ_1;
end;

select * from a;

Provide grants for this package to other schema (scott) and create the synonym for the xyz package in scott.

grant all on to

Example:-
---------

grant all on xyz to scott

Above command is run from the apps schema.

Now for the other schema SCOTT try to run the same query.

begin
xyz.XYZ_1;
end;

It have inserted new record in the 'A' table. Note there is no synonym for the table A in SCOTT schema.

Running this program from anywhere, it is as good as running from APPS schema in case of AUTHID DEFINER.

10.2) CURRENT_USER:-
----------------------

Example:-
-----------

The following are done in the APPS schema.

create table a (a_a number);

CREATE OR REPLACE PACKAGE XYZ AUTHID CURRENT_USER
AS
PROCEDURE XYZ_1;
END XYZ;

CREATE OR REPLACE PACKAGE body XYZ
AS
PROCEDURE XYZ_1
IS
BEGIN
INSERT INTO A VALUES (1);
END XYZ_1;
END XYZ;

begin
xyz.XYZ_1;
end;

select * from a;

Provide grants for this package to other schema (scott) and create the synonym for the xyz package in scott.

grant all on to

Example:-
-----------

grant all on xyz to scott

Above command is run from the apps schema.

Now for the other schema (scott) try to run the same query.

begin
xyz.XYZ_1;
end;

Got the error message table or view doesn't exist for the A table.

Create view for the a table and run the same program again.

create synonym 'A' for table 'A'

begin
xyz.XYZ_1;
end;

select * from a;

Now there is no error. It is inserting the record with no issue.

WITH NO AUTHID DEFINER and AUTHID CURRENT_USER :-
-----------------------------------------------------------------------


Example:-
---------

The following are done in the APPS schema.

create table a (a_a number);

CREATE OR REPLACE PACKAGE XYZ
AS
PROCEDURE XYZ_1;
END XYZ;

CREATE OR REPLACE PACKAGE body XYZ
AS
PROCEDURE XYZ_1
IS
BEGIN
INSERT INTO A VALUES (1);
END XYZ_1;
END XYZ;

begin
xyz.XYZ_1;
end;

select * from a;

Provide grants for this package to other schema (scott) and create the synonym for the xyz package in scott.

grant all on to

Example:-
---------

grant all on xyz to scott

Above command is run from the apps schema.

Now for the other schema SCOTT try to run the same query.


begin
xyz.XYZ_1;
end;

It is working in same way as it have done for the AUTHID DEFINER.

Q) Is it possible to know from the select statement if it is INVOKER(CURRENT_USER) or DEFINER

A) Yes, It is possible to get this information from the select statement. Use

SELECT dbo.object_name,
(DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid"
FROM dba_objects dbo,
sys.PROCEDURE$ p
WHERE p.obj# = dbo.object_id
AND dbo.object_name = "Your Package Name"
AND dbo.object_type = 'PACKAGE'
AND dbo.owner = 'APPS'

Example:-
-----------

SELECT dbo.object_name,
(DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid"
FROM dba_objects dbo,
sys.PROCEDURE$ p
WHERE p.obj# = dbo.object_id
AND dbo.object_name = 'ASO_APR_WF_INT'
AND dbo.object_type = 'PACKAGE'
AND dbo.owner = 'APPS'

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) <>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)

Adhoc Roles from backend.

Adhoc Roles in Oracle Applications

Adhoc roles can be created through PL/SQL from database or they can be created from applications using User Management Responsibility. If you use PL/SQL to create roles make sure you give all user names and role names in UPPER case to avoid some problems.

• Script to Create a Adhoc Role
• Script to Add user to existing Adhoc Role
• Script to Remove user from existing Adhoc Role
• Using Adhoc roles in workflow notifications
• Adhoc Roles Tables

Script to Create a Adhoc Role

DECLARE

lv_role varchar2(100) := 'PHANI_DEMO_ROLE';

lv_role_desc varchar2(100) := ' PHANI_DESC_DEMO_ROLE';

BEGIN

wf_directory.CreateAdHocRole(lv_role,

lv_role_desc,

NULL,

NULL,

'Role Demo for phani users',

'MAILHTML',

'USER1 USER2', --USER NAME SHOULD BE IN CAPS

NULL,

NULL,

'ACTIVE',

NULL);

dbms_output.put_line('Created Role' ||' '||lv_role);

End;

Script to Add user to already existing Adhoc Role

DECLARE

v_role_name varchar2(100);

v_user_name varchar2(100);

BEGIN

v_role_name := 'PHANI_DEMO_ROLE';

v_user_name := 'NAME3';

WF_DIRECTORY.AddUsersToAdHocRole(v_role_name, v_user_name);

--USER NAMES SHOULD BE in CAPS

END;

Script to Remove user from existing Adhoc Role

DECLARE

v_role_name varchar2(100);

v_user_name varchar2(100);

BEGIN

v_role_name := 'PHANI_DEMO_ROLE';

v_user_name := 'USER3';

WF_DIRECTORY.RemoveUsersFromAdHocRole(v_role_name, v_user_name);

--USER NAMES in CAPS

END;

Using Adhoc roles in workflow notifications:

Navigation: File à Load Roles from Database

Select roles you want to use and then click OK.

Open the notification properties and then navigate to node tab, select performer as the role you just created and loaded from database.

Tables:

• WF_ROLES
• WF_USER_ROLES
• WF_LOCAL_ROLES
• WF_USER_ROLE_ASSIGNMENTS