Tips and Tricks with SQL Loader

Following are some of the tips and tricks that can be used with SQL Loader

1) Load text for a column which is having more than 4000 bytes.
Use following syntax

LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
  long_text_columns char(40000)
)

2) How to use oracle functions with SQL Loader

LOAD DATA
APPEND INTO TABLE sv_test
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
  long_text_columns char(40000)   --- first 40000 characters
, column1  "trim(:column1)"       --- Trims and loads
, column2  "replace(:column2,'\n',chr(10))"  --- replace \n with new line
, column3  DATE "DD-MON-YYYY"   --- defining date style
, column4  "upper(:column4)"      --- changing to upper case
, column5  "lower(:column5)"      --- changing to lower case
, column6  constant "FIXED"      --- assigning a constant value
, column7  "sv_sequence.nextval"  --- defaulting a value from sequence
)

Thats it for today .. will update with more commands later.

Profile Value at any Level

Below is another handy query to get profile Values at all levels.
The query prompts for 2 parameters, the profile name and the level name that could be application/responsibility/username. Its a wild search so will retrieve data if any of parameter is passed a blank.

SELECT   distinct pot.user_profile_option_name Profile
     , DECODE (a.profile_option_value
             , '1', '1 (may be "Yes")'
             , '2', '2 (may be "No")'
             , a.profile_option_value
              ) Value
     , DECODE (a.level_id
             , 10001, 'Site'
             , 10002, 'Application'
             , 10003, 'Responsibility'
             , 10004, 'User'
             , '????'
              ) Level_identifier
     , DECODE (a.level_id
             , 10002, e.application_name
             , 10003, c.responsibility_name
             , 10004, d.user_name
             , '-'
              ) Level_Name
FROM     applsys.fnd_application_tl e
     , applsys.fnd_user d
     , applsys.fnd_responsibility_tl c
     , applsys.fnd_profile_option_values a
     , applsys.fnd_profile_options b
     , applsys.fnd_profile_options_tl pot
WHERE    1=1
AND      UPPER (pot.user_profile_option_name) LIKE UPPER ('%&v_profile%')
AND      pot.profile_option_name = b.profile_option_name
AND      pot.language = userenv('lang')
AND      b.application_id = a.application_id(+)
AND      b.profile_option_id = a.profile_option_id(+)
AND      a.level_value = c.responsibility_id(+)
AND      a.level_value = d.user_id(+)
AND      a.level_value = e.application_id(+)
AND      e.language(+) = userenv('lang')
AND      (   UPPER (e.application_name) LIKE UPPER ('%&appname_respname_username%')
        OR UPPER (c.responsibility_name) LIKE UPPER ('%&&appname_respname_username%')
        OR UPPER (d.user_name) LIKE UPPER ('%&&appname_respname_username%')
       )
ORDER BY Profile
     , Level_identifier
     , Level_name
     , Value

Do comment your feedback. Thanks.

Hidden Methods to Query Oracle Apps Forms -- Very Useful

Here I discuss how can we query a form by giving certain query condition like
- Display only records where a particular column is null
- Display only records where column is greater than or less than a certain value
- Display only records where data is in certain range of value etc etc....

Press Function Key F11 to enter into query mode and Ctrl+F11 to run the query.
Hash(#) is the magic key here
Below are few examples with screenshots. As an example I have taken the FND_MESSAGE form which is available in the Application Developer responsibility. Navigation Application Developer-->Application-->Message
Condition1: Display all records where Number is NULL
Query

Result


Condition2: Display all records where Number is not null
Query

Result
Condition3: Display all records where Number is greater than 1905
Query
Result
Condition4: Display all records where Number is between 1905 and 1911
Query
Result
Condition5: Display all records where Number in 1905,1911
Query
Result

Do not forget to put your comments for the post.
Thanks.

Calender in Concurrent Request Parameter

One of the problem with concurrent request date parameter is that we cannot attach a calendar the way we have for forms. But there is an alternate solution to have a List of values.

Create a view using following query

CREATE OR REPLACE VIEW xx_date
AS
   (SELECT     (TO_DATE (SYSDATE - 1 + LEVEL, 'DD-MON-RRRR')) date_range
             , (TO_CHAR (SYSDATE - 1 + LEVEL, 'Month, DD RRRR')) date_word
    FROM       DUAL x
    CONNECT BY LEVEL <= 1000)

In the above query sysdate can be replaced with the date range as per requirement. Also the above query will result in 1000 records which can be altered by changing the connect by level value.

Now that we have created a view, a table value set can be created based on it and this can be used in the request parameter.

Database and Application Information

Below are some of the queries that can be used to get the database and Application information.

1) Get Product Version

SELECT product
     , VERSION
     , status
FROM   product_component_version

The other way to get this information is by using following query

select * from v$version;

2) Get Applications Version and Patch Information

SELECT   SUBSTR (a.application_name, 1, 60) Application_Name
       , SUBSTR (i.product_version, 1, 4) Version
       , i.patch_level 
       , i.application_id 
       , i.last_update_date 
FROM     apps.fnd_product_installations i
       , apps.fnd_application_all_view a
WHERE    i.application_id = a.application_id
ORDER BY a.application_name

3) Patch Information AD_APPLIED_PATCHES table stores information about all the patches installed in the system.

SELECT applied_patch_id
     , patch_name
     , patch_type
     , source_code
     , creation_date
     , last_update_date
FROM   ad_applied_patches

4) Check if the application is setup for Multi-Org

SELECT multi_org_flag
FROM   fnd_product_groups

... More queries to come

Case Insensitive Query in Forms

One of the inconsistency Oracle Apps Forms is that some forms are case sensitive and some are not. For e.g. If we query the create concurrent request form these are case insensitive whereas the create DFF form is case sensitive.
Below I discuss how do we know whether the form is case insensitive and how to change its property using forms personalization.

As an example I have taken the DFF form.
Step to know if form is case insensitive.
Open the form and click on the field. Select Help-->Diagnostic -->Properties-->Item
In the property field, select case insensitive query. If the value is FALSE, then you will know that Case Insensitive Query is not possible on this field.

For changing the property select forms personalization. Select Help-->Diagnostics-->Custom Code -->Personalize

Enter any meaninful text for description. Click on Actions Tab.
Select the Target Object for which the property needs to be changed then select case_insensitive_query property name and change the value to TRUE. Save the changes.
Close the DFF form and check if the changes are saved.


Thats its ... now the title field is no longer a case sensitive field. Query by entering text in lower case.
Hurray... there we go .. the case insensitive query is now working fine.

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.

7 Secrets of Success

Got following quote in a mail ... Loved it and so thought of sharing it with all
Here is how it goes

7 Secrets of Success
No Wonder I found answers in my Room

Roof Said: Aim High
Fain Said: Be Cool
Clock Said: Every Minute is important
Mirror Said: Reflect before you act
Window Said: See the World
Calendar Said: Be Up-To-Date .. and finally
Door Said: Push hard to achieve the Goals

Send Email through PL/SQL

The short procedure can be used to send a mail through PL/SQL.
Although there is a limitation to this as the code below does not allow you to send attachments. This can only be used to send text messages.

CREATE OR REPLACE PROCEDURE email_message (
   from_name   VARCHAR2
 , to_name     VARCHAR2
 , subject     VARCHAR2
 , MESSAGE     VARCHAR2
)
IS
   l_mailhost    VARCHAR2 (64)       := 'server.com';
   l_mail_conn   UTL_SMTP.connection;
BEGIN
   l_mail_conn := UTL_SMTP.open_connection (l_mailhost, 25);
   UTL_SMTP.helo (l_mail_conn, l_mailhost);
   UTL_SMTP.mail (l_mail_conn, from_name);
   UTL_SMTP.rcpt (l_mail_conn, to_name);
   UTL_SMTP.open_data (l_mail_conn);
   UTL_SMTP.write_data (l_mail_conn
                      ,    'Date: '
                        || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
                        || CHR (13)
                       );
   UTL_SMTP.write_data (l_mail_conn, 'From: ' || from_name || CHR (13));
   UTL_SMTP.write_data (l_mail_conn, 'Subject: ' || subject || CHR (13));
   UTL_SMTP.write_data (l_mail_conn, 'To: ' || to_name || CHR (13));
   UTL_SMTP.write_data (l_mail_conn, Message );

   UTL_SMTP.close_data (l_mail_conn);
   UTL_SMTP.quit (l_mail_conn);
END;
/

Emails with attachment can be done using Java as a wrapper. The metalink Note:120994.1 has a very good explanation on how to create a program that facilates sending emails with attachment.

Which User is Locking the table

I am new to the blogging world and before I really get into it, I am trying to post some of the queries that I already have .. so may be few of my initial articles may have just queries.
Here is another query that can sometime be very useful. This will list the name of user that is locking a table. The object name is taken as an input parameter.

SELECT c.owner
      ,c.object_name
      ,c.object_type
      ,fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time
      ,vs.module
      ,vs.machine
      ,vs.osuser
      ,vlocked.oracle_username
      ,vs.sid
      ,vp.pid
      ,vp.spid AS os_process
      ,vs.serial#
      ,vs.status
      ,vs.saddr
      ,vs.audsid
      ,vs.process
FROM fnd_logins      fl
    ,fnd_user        fu
    ,v$locked_object vlocked
    ,v$process       vp
    ,v$session       vs
    ,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tabname_blank4all') || '%'
AND nvl(vs.status,'XX') != 'KILLED';