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;

Display Negative Number in angle brackets

We often wonder and write all the codes to display negative numbers in brackets. But this can be easily achieved using a TO_CHAR function with the correct format.

TO_CHAR(number, '9999PR') -
Returns negative value in angle brackets.
Returns positive value with a leading and trailing blank.
Restriction: The PR format element can appear only in the last position of a number format model.

Below is an example to achieve this
select to_char(-133133,'99999999999PR') from dual

In above example the number is displayed in brackets without comma seperator.

Following query can be used to display negative numbers in angular bracket with comma seperator.

select to_char(-133133,'999G999G990D99PR') from dual

PL/SQL Wrap Utility

The PL/SQL Wrap Utility - Hide source code in Oracle

A company has a code(Package, Procedure, Function etc) with all the proprietary information and logic in it. If this information is leaked out in the market then the competitors can take advantage of it and this can affect the business. One of the way to deal with this is to hide the code from others.
This can be achieved using oracle's WRAP utility. The advantage of WRAP utility is that this converts the source code into some language that is not understood but the code can still be compiled like any other source code.
Using Wrap is very simple. In the bin directory of Oracle Home, the wrap utility is installed. The file name could be WRAP.exe or WRAP80.exe depending on the oracle version installed.
Syntax

 C:\orant\BIN>wrap.exe iname=[inputfilename] oname=[outputfilename]


e.g.
 C:\orant\BIN>wrap.exe iname=wrap_test.sql oname=wrap_test.plb

An example of using WRAP

Create a sample procedure wrap_test using following code

CREATE OR REPLACE PROCEDURE wrap_test
IS
BEGIN
 dbms_output.put_line('Wrap test complete');
END;
/
then call the wrap utility using following
wrap.exe iname=wrap_test.sql oname=wrap_test.plb

Content of new file wrap_test.plb
CREATE OR REPLACE PROCEDURE wrap_test wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
4f 8d
LPjE3qKQyH/yQRCK4+efvSyST50wg5nnm7+fMr2ywFznKMB04yhSssvum3SLwMAy/tKGBvVS
m7JK/iiyveeysx0GMCyuJOqygaVR2+EC8XcG0wJd5GisbnfxwzIu9tHqJB/2OabWTW+0
/

It is very clear from this that the new code is not readable and so is completely hidden from others.
Drop your procedure(if already created) and recreate using the the new file wrap_test.plb which can be compiled as any other package. Important point here is that the source code will be hidden and cannot be read.
Another important point to remember is that once wrapped, a code cannot be unwrapped.

Thanks please let me know your feedback.

Thanks & Regards,
Anto Joe Natesh

Password Policy and profile options

On apps there are a lot of profile options that are useful in making apps passwords difficult to guess, the profiles are

1-Signon password failure limit
2-Signon Password Length
3-Signon Password No Reuse
4-Signon Password Hard to Guess

For the first one it means how many time can I try to access the system using wrong password. It is recommended to change this value to 3. The default value is null.

The second one to allow minimum password length. The default value is 5, it is recommended to make it 6 or 7.

The 3rd profile is for not allowing using same password again for specified number of days.

The default value for 4th profile option is No. Following are the password rules if the value is set to Yes
1) The password contains at least one letter and at least one number.
2) The password does not contain the username.
3) The password does not contain consecutively repeating characters.

Reference: Metalink Note 362663.1

Thanks & Regards,
Anto Joe Natesh I

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.

Thanks & Regards,

Anto Joe Natesh I

Secure FTP the files from unix to windows

Securing FTP in shell scripts using .netrc

Often we use FTP in the shell scripts and for security reason it is advisable not to store username and password in the shell scripts.

Here I discuss how can we secure the FTP process and restrict sharing of username and passwords.

File .netrc in the $HOME directory allows file transfers in batch mode. This file stores the machine name, login and passwords. The FTP commands gets information from the file and connects to the FTP server.

Each record has the format:

machine machine_name login login_name password passwd

where machine_name, login_name, passwd refer to a system name with the login and password for that account on the machine

e.g machine xyz.server.com login anonymous password xyz123

There are following 2 ways to execute the FTP commands

1) Create a command file and store all the FTP commands in the file.

e.g. File command_ftp is created and saved. The file has following content

bin

cd /inbound

get abcd.txt

bye 

Write following code in the shell script

ftp xyz.server.com < command_ftp 

2) The FTP commands can also be stored in .netrc file as a macro and the commands will be executed with the FTP command.

The .netrc file content would be something like this

machine xyz.server.com login anonymous password xyz123

macdef bin

cd /inbound

get abcd.txt

bye 

For this case the shell script will have following ftp command

e.g.

ftp xyz.server.com

The other way of securing FTP is by using the sftp(secure FTP) commands which is a network protocol that provides file transfer and transfers file in a secure way.

Thanks & Regards,

Anto Joe Natesh I

FNDLOAD & WHO Columns

Usually if we download the DFF using fndload from UAT instance and upload into the PROD instance. The WHO Columns will be defaulted to the creation date in the UAT instance. If we want to change the creation date as sysdate. We need to alter the particular afffload.lct file.


Open the lct file by doing vi $FND_TOP/patch/115/import/afffload.lct
Search for string fnd_flex_loader_apis.up_desc_flex 
In the parameter last_update_date you can pass sysdate, replacing the value read from ldt file. 


Thanks & Regards,
Anto Joe Natesh I