GREATEST

Hi all , hope you aware of MIN and MAX function in Oracle SQL. These functions are used as grouping function which can calculate the minimum or maximum value in a particular column. Since we normally group against a column. 

Requirement : Let us assume we have a table shown in the picture . Students and their marks against each subject as separate column. Now we have to find the maximum mark scored by a student across the subjects ( that is maximum value between English, Maths and Science for a student) as shown in the picture.  

Solution:  

SELECT student_name, GREATEST (english, maths, science)   FROM student;

GREATEST is a SQL function used to get max value between columns.

PL/SQL Collection : Storing TABLE values into a TABLE TYPE

After a long time had a time to blog ...

Below are some of the PL/SQL Collections
OBJECT Type
TABLE Type
RECORD Type

Hope you can google about these to get more information.
In this article I like to explain how to retrieve records from a table and store it in a TABLE type.

Create a table emp_tb as given below and insert some values

CREATE TABLE emp_tb ( empid NUMBER , NAME VARCHAR2(20) );
INSERT INTO emp_tb VALUES (10, 'Bala');
INSERT INTO emp_tb VALUES (20, 'Kaarhtik');






Now create a OBJECT type same like the table structure.

CREATE OR REPLACE TYPE emp_type IS OBJECT (
   empid   NUMBER,
   NAME    VARCHAR2 (20)
);

Create TABLE type with the object object

CREATE OR REPLACE TYPE emp_tb_type IS TABLE OF emp_type;

Below is a PLSQL block to retrieve the data from emp_tb and store it in the emp_tb_type object.

SET serveroutput on
DECLARE
   v_tab   emp_tb_type;
   v_rec   emp_type;
BEGIN
   v_tab := emp_tb_type ();

   FOR i IN (SELECT * FROM emp_tb)
   LOOP
      v_tab.EXTEND ();
      v_rec := emp_type (i.empid, i.name);
      v_tab (v_tab.LAST) := v_rec;
   END LOOP;

   FOR i IN v_tab.FIRST .. v_tab.LAST
   LOOP
      DBMS_OUTPUT.put_line (v_tab (i).empid || '-' || v_tab (i).name);
   END LOOP;
END;
/

v_tab - is a variable for the TABLE type emp_tb_type
v_rec - is a variable for the OBJECT type emp_type. This variable is create to assign the value in the TABLE type variable v_tab. This is how a record is inserted into the table type object

emp_tb_type() - This is a constructor function which will be created along with the OBJECT type definition. We need to call this to initialize the variable v_tab.

EXTEND() - This is used to create one record index (Last record in the TABLE type variable)

emp_type(i.empid,i.name) - This is how a record is created from a OBJECT type so that we can assign it to its variable v_rec

v_tab(v_tabl.LAST) - This is used to navigate to the end of the TABLE type record  so that we can store the new value at the end.

Later of the code is use to retrieve the records from the TABLE type to display.

Below is the sample output.
10-Bala
20-Kaarhtik

TIMESTAMP - Datatype

We all know the basic datatypes in oracle like CHAR, VARCHAR, NUMBER, DATE . But most of them will not have the exposure to TIMESTAMP datatype. I like to share my view on this datatype today.

First let us know the difference in DATE and TIMESTAMP datatypes. We generally say both used to store date information. But there is a huge difference in it.

DATE

Its a dataype in which we can store the information like DATE,MONTH,YEAR with HOUR,MINUTE,SECONDS information. We normally call DD-MON-YYYY as a date part and HH24:MI:SS as a timestamp .

TIMESTAMP

Its also a datatype in which we can store date part as well as timestamp. Additionally it also stores the fractional part of seconds and the timezone information.

we can get the timezone information from the below query

select * from v$timezone_names;

As like sysdate we also have systimestamp which gets you the following information.

select systimestamp from dual;

The following example queries may explain you about the timestamp data type.

select to_char(timestamp_col,'YYYY-MM-DD"T"HH:MM:SS.FF3"Z"') time_stamp from tab_name;

Output : 2012-03-20T02:03:28.000Z


In the above example we already know about the format type YYYY-MM-DD HH:MM:SS .
.FF3 - It limits the fraction part as 3 . we can also use .FXX to get all the fraction part in the timestamp

select to_timestamp('2012-03-20T02:03:28.000Z','YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') from dual;

The above query is the reverse query.

we have more functions regarding timezone conversion. For more information you can always google it.

Item Catalog Group

Item - Its the base element in Oracle Inventory. During Implementation the items will be loaded through "Item Import" . Its a standard program to load items from Interface table. Its always a best practice to use "Item Template" to load items. In this way we can create similar type of items.

One other thing you can think about at that point is Item Catalog Groups. With Item Catalog Groups in Oracle you can catalog your items in groups and add extra information to your items based upon the category the items is related to.

Creating catalog groups and placing items in catalog groups will have several benefits. One of them obviously is that you will be able to group items and search within groups. Other benefits are that you will be able, for example, add more information which is relevant only for the items within this group and you will be able to automatically create aƒ item description based upon this. So this will also help you bring uniformity in the way you create your item descriptions.

To be able to do so you first will have to create a Catalog Item group within Oracle Inventory. To do so navigate to "Setup -> Items -> Catalog Groups". Here you will be able to view, edit and create new catalog groups.





As shown in the above screenshot I created a new Catalog Group in Oracle Inventory named "Gear Pumps" which will hold all the items which are gear pumps. Or in other words, in alter stage I will assign the Catalog Group "Gear Pumps" to all the existing items who are gear pumps. If you create a new group, save it and click on the Details button you will be able to set more options.



As you can see in the above screenshot I have set some details for this Item Catalog Group. I have been adding Descriptive Elements. Descriptive Elements are used per Item. In this case I have chosen 4 Descriptive Elements which can be set for all the items which will be placed this Item Catalog Group. The sequence is deliberately defined as 10, 20, 30, 40 because in this case I can easily add a record in the middle without having to change all the sequences of the existing records. This is in general a good idea to do. I also have set for all 4 of the Descriptive Elements that they are required so when a item is added to the group you have to set it. Only for 3 of the 4 I have set that it is a Description Default. When you set a Descriptive Element to Description Default it will be by default used to generate you description.

When you like to add a item to a Item Catalog group you go to the Item Master and query the item, then go to the Tools menu and select Catalog. you will be presented with a screen similar as in the screenshot below.



In this case I have selected the Item Catalog Group I created " gear Pumps" in the top "Catalog Group" field. When you select it you will be presented with a list of all the available and active Item Catalog Groups. After filling all the requierd fields for the Descriptive Elements, in this case all, you can use the Update Description button which will fill the item description with the generated "Item Catalog Description" based upon the catalog name and the descriptive elements in combination with their sequence. This is how you can generate standardized item descriptions. You do however have the possibility to change and edit the description so you will ne bound to the generated description. If you set this up smartly however it can be very beneficial.

AND - Binary Function - Left to Right execution

Hi , We all know about the AND function in Oracle PL/SQL . But only least know about the importance of the execution method. Let us start with the functionality of a AND function.
AND is a binary function which has two input and one output. The input and output will be either a Boolean TRUE / FALSE.

AND				F				T
F				F				F
T				F				T

Above is the Truth table which explains the AND function. So if one of the input is FALSE the AND function will return FALSE no matter about the second input . This is the thing we normally remember about the AND function.

But we also have to remember the execution sequence of it. For example we can take the below cases. We have a AND between two functions which returns a Boolean value.

fun1 - will always insert a data into a table and returns TRUE
fun2 - does nothing and returns FALSE

Case 1 : fun1 AND fun2
Case 2: fun2 AND fun1

In both cases the result will be FALSE. But we have some difference in the execution.

Case 1 : fun1 AND fun2
In this case fun1 will be executed first , a data will be inserted into the table and TRUE will be returned. since the first input is TRUE, the AND will check the next input . In this case it checks the fun2 which returns FALSE. So the result will be FALSE.

Case2: fun2 AND fun1
In this case fun2 will be executed first , it returns FALSE. Since the first input is FALSE, the AND will not check (execute) the next input. Because as per AND logic if one input is FALSE it will always return FALSE , no matter about the second input.

Even thought both cases return FALSE , a data got inserted in Case 1 where as it doesn't happen in Case 2.

The main motive of the above writeup is to explain the execution sequence of a binary function in PL/SQL.

Oracle PL/SQL functions always follows a LEFT to RIGHT execution.

User Responsibility Issue

Sometimes we might face some issue in User Responsibilities. I want to share the issue which I faced.

A responsibility was assigned to my user and it got end dated in production. So if a clone was made from production the responsibility which was end dated comes as it is with the end date.

I need to use the responsibility in the cloned instance. So I removed the end date for the responsibility and saved it. But it was still unavailable for me to use. Then I started to trace the tables which causing the issue.

FND_USER – The first table to get my user_id for my user.

FND_USER_RESP_GROUPS_DIRECT – Is the table which has the list of responsibility shown in the form (which includes my responsibility which is not available to use)

FND_USER_RESP_GROUPS – which contains the responsibilities for the user available to use it ( this will not have my responsibility which has the issue)

WF_USER_ROLES – This is a part of the above view FND_USER_RESP_GROUPS which tell the active user roles.

WF_LOCAL_USER_ROLES – This is a part of the above view WF_USER_ROLES which has all the roles including the inactive one.

This is where the issue came. WF_LOCAL_USER_ROLES has my responsibility role but it has been end dated (effective_end_date). By updating this to 1/1/9999 (unlimited end date) I was able to rectify my issue.

Hope this is useful.

WF DOCUMENT Manager Error

ORA - 04061 : existing state of has been invalidated

ORA - 04065: not xecute,altred or dropped package body

The above errors may occur in WF when we modify or execute any pLSQL WF Package.

Technically when you apply some PLSQL changes to the database that could impact the mailer/notification code, you should shutdown the agent listener service, apply the changes and re-start them. Compiling a PLSQL package in the Database invalidates the state of that package in other active sessions and the next time the other session accesses that package, it appears invalid though in real-time the package is valid.

To release the issue clear the cache and bounce the WF Listener, WF mailer and Agent Listener.

PO Re-Approval

A PO is AUTO Created from REQ and its in Approved status.

If we cancel any REQ line then it will propogate the changes to the PO and the PO will changed to 'Inprocess' States.

To Accept the changes , just login as the PO Buyer and query the PO Summary.

Then go to Tools -> Repsond To Changes.

A separate page will open and you can accept the changes which will cancel the corresponding PO line and it will move the status to 'Approved'.

PO Header Total Amount

The PO Header Total Amount is sum of line amount. It will not include the amount for cancelled lines. If its still picks from the cancelled lines check whether the lines are partially cancelled.

You can check this in the shipments.

For cancelled lines the CLOSED_CODE will be 'CLOSED' and the CANCEL_FLAG will be 'Y'.

If the line got fully cancelled then the QUANTITY will become zero.

,pl.cancel_flag,pl.unit_price,pl.quantity , ( pl.unit_price*pl.quantity) line_amount , sum(( pl.unit_price*pl.quantity)) over(partition by ph.po_header_id) total_amount

,pd.quantity_cancelled,pd.quantity_billed,sum(( pl.unit_price*pd.quantity_billed)) over(partition by ph.po_header_id) matched_amount

Getting More then 9 parameters in a batch file

While calling a batch file using parameters we can use the parameters as %1,%2,etc.. But the 10th parameters can not be called as %10. Instead we should shift the parameters using SHIFT command and move the parameter one left. By this we can use the parameter 10 as %9

You can test it by using the below batch file by passing 10 parameters

c:\>kaarthik.bat a b c d e f g h i j

REM kaarthik.bat - file to test parameters
echo off
echo Program to test parameters
echo Parameter 1 : %1
echo Parameter 2 : %2
echo Parameter 3 : %3
echo Parameter 4 : %4
echo Parameter 5 : %5
echo Parameter 6 : %6
echo Parameter 7 : %7
echo Parameter 8 : %8
echo Parameter 9 : %9
echo Parameter 10 : %10
SHIFT
echo Parameter 10 : %9

IS_NUMBER - Equivalent SQL Query

The below query is used to check whether a varchar column contains only NUMBER or NON NUMBER data.

SELECT DECODE (REPLACE (TRANSLATE ('1123', '1234567890', '##########'), '#'),
               NULL, 'NUMBER','NON NUMER') RESULT
  FROM DUAL;

RESULT
------
NUMBER
1 row selected.

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

SELECT DECODE (REPLACE (TRANSLATE ('11a23z', '1234567890', '##########'), '#'),
               NULL, 'NUMBER','NON NUMER') RESULT
  FROM DUAL;

RESULT 
---------
NON NUMER
1 row selected.

Finding Database Version

select * from v$version;

BANNER

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

PL/SQL Release 11.1.0.7.0 - Production

CORE 11.1.0.7.0 Production

TNS for Linux: Version 11.1.0.7.0 - Production

NLSRTL Version 11.1.0.7.0 - Production

Oracle 10g FORMS Issue : FRM - 10142 : The HTTP Listener is not running.

Many of them may find the following error while running the Oracle Forms 10g after a fresh installation

FRM - 10142 : The HTTP Listener is not running.

This error occurs because Oracle 10g FORM output will be displayed in web browser.

So the browser settings to be given in the forms before running it.

1. Go to Edit->Preferences.

2. Select "Runtime" tab.

3. Fill the web browser field.

ie.. mention the iexplorer.exe path in that if u r using Miscrosoft Internet Explorer as a web browser.

4. Save the setting and run the forms.

5. The result will be displayed in a separate webbrowser.

Before running the FORM check OC4J is running.

Sorting in Heirarchical Query

Sorting in Heirarchical Query

Hierarchical queries are used to created hierarchical tree structured outputs.
For example


select ename,sal
from emp;


The above query displays the employees and its salary.

If we need the output as who's boss is who and their salary then the query should be modified like below.



select level,ename,sal
from emp
start with mgr is null
connect by prior empno=mgr;

In the above query "start with" is used to start the output from the boss,who's doesnt have a manager (ie mgr is null)
"connect by piror" is used to defined the relation between the boss and his siblings.

If the output is to be sorted by salary in each level then we need to modify the query as



select level,ename,sal
from emp
start with mgr is null
connect by prior empno=mgr
order siblings by sal desc;

In the above query "order siblings by" is used to sort the output by salary without distrubing the heirarchical structure.

''IN' is not a valid integer value'" Bug In TOAD With Oracle 10.2

TOAD has a bug with its versions earlier than 8.6. You can check details of bug in TOAD's support. The resolution is to upgrade TOAD with new releases.
Follow the steps given below to fix it.

1) log in as SYS.
2) Get the script for ALL_ARGUMENTS view and create a new view called ALL_ARGUMENTS_OLD for backup.
3) Run the below script:

CREATE OR REPLACE VIEW all_arguments (owner,
                                    object_name,
                                    package_name,
                                    object_id,
                                    overload,
                                    argument_name,
                                    POSITION,
                                    SEQUENCE,
                                    data_level,
                                    data_type,
                                    DEFAULT_VALUE,
                                    default_length,
                                    in_out,
                                    data_length,
                                    data_precision,
                                    data_scale,
                                    radix,
                                    character_set_name,
                                    type_owner,
                                    type_name,
                                    type_subname,
                                    type_link,
                                    pls_type,
                                    char_length,
                                    char_used,
                                    subprogram_id
                                   )
AS
 SELECT u.NAME,                                                  /* OWNER */
               NVL (a.procedure$, o.NAME),                 /* OBJECT_NAME */
        DECODE (a.procedure$, NULL, NULL, o.NAME),        /* PACKAGE_NAME */
                                                  o.obj#,    /* OBJECT_ID */
        DECODE (a.overload#, 0, NULL, a.overload#),           /* OVERLOAD */
                                                   a.argument,
                                                         /* ARGUMENT_NAME */
                                                              a.position#,
                                                              /* POSITION */
        a.sequence#,                                          /* SEQUENCE */
                    a.level#,                               /* DATA_LEVEL */
        DECODE (a.type#,                                     /* DATA_TYPE */
                0, NULL,
                1, DECODE (a.CHARSETFORM, 2, 'NVARCHAR2', 'VARCHAR2'),
                2, DECODE (a.scale, -127, 'FLOAT', 'NUMBER'),
                3, 'NATIVE INTEGER',
                8, 'LONG',
                9, DECODE (a.CHARSETFORM, 2, 'NCHAR VARYING', 'VARCHAR'),
                11, 'ROWID',
                12, 'DATE',
                23, 'RAW',
                24, 'LONG RAW',
                29, 'BINARY_INTEGER',
                69, 'ROWID',
                96, DECODE (a.CHARSETFORM, 2, 'NCHAR', 'CHAR'),
                100, 'BINARY_FLOAT',
                101, 'BINARY_DOUBLE',
                102, 'REF CURSOR',
                104, 'UROWID',
                105, 'MLSLABEL',
                106, 'MLSLABEL',
                110, 'REF',
                111, 'REF',
                112, DECODE (a.CHARSETFORM, 2, 'NCLOB', 'CLOB'),
                113, 'BLOB',
                114, 'BFILE',
                115, 'CFILE',
                121, 'OBJECT',
                122, 'TABLE',
                123, 'VARRAY',
                178, 'TIME',
                179, 'TIME WITH TIME ZONE',
                180, 'TIMESTAMP',
                181, 'TIMESTAMP WITH TIME ZONE',
                231, 'TIMESTAMP WITH LOCAL TIME ZONE',
                182, 'INTERVAL YEAR TO MONTH',
                183, 'INTERVAL DAY TO SECOND',
                250, 'PL/SQL RECORD',
                251, 'PL/SQL TABLE',
                252, 'PL/SQL BOOLEAN',
                'UNDEFINED'
               ),
        default$,                                        /* DEFAULT_VALUE */
                 deflength,                             /* DEFAULT_LENGTH */
        DECODE (in_out, NULL, 'IN', 1, 'OUT', 2, 'IN/OUT', 'Undefined'),
                                                                /* IN_OUT */
        LENGTH,                                            /* DATA_LENGTH */
               precision#,                              /* DATA_PRECISION */
        DECODE (a.type#, 2, scale, 1, NULL, 96, NULL, scale),
                                                            /* DATA_SCALE */
                                                             radix,
                                                                 /* RADIX */
        DECODE (a.CHARSETFORM,
                1, 'CHAR_CS',                       /* CHARACTER_SET_NAME */
                2, 'NCHAR_CS',
                3, NLS_CHARSET_NAME (a.CHARSETID),
                4, 'ARG:' || a.CHARSETID
               ),
        a.type_owner,                                       /* TYPE_OWNER */
                     a.type_name,                            /* TYPE_NAME */
                                 a.type_subname,          /* TYPE_SUBNAME */
                                                a.type_linkname,
                                                             /* TYPE_LINK */
                                                                a.pls_type,
                                                              /* PLS_TYPE */
        DECODE (a.type#, 1, a.scale, 96, a.scale, 0),      /* CHAR_LENGTH */
        DECODE (a.type#,
                1, DECODE (BITAND (a.properties, 128), 128, 'C', 'B'),
                96, DECODE (BITAND (a.properties, 128), 128, 'C', 'B'),
                0
               ),                                            /* CHAR_USED */
        a.procedure#                                     /* SUBPROGRAM ID */
   FROM obj$ o, argument$ a, user$ u
  WHERE o.obj# = a.obj#
    AND o.owner# = u.user#
    AND (   owner# = USERENV ('SCHEMAID')
         OR EXISTS (SELECT NULL
                      FROM v$enabledprivs
                     WHERE priv_number IN (-144, -141))
         OR o.obj# IN (
                      SELECT obj#
                        FROM SYS.objauth$
                       WHERE grantee# IN (SELECT kzsrorol
                                            FROM x$kzsro)
                         AND privilege# = 12)
        )
/

4) Now the error will be rectified. If you face the error again please restore the previous view which we made a backup in step 2.

Script to read a file

echo "This script helps to display a file word by word"
for i in `cat FILES.txt`
do
echo $i
done
echo "--------------------------------------------------"
echo "This script helps to display a file line by line"
n=`wc -l ‹ FILES.txt`
i=1
while [ "$i" -le "$n" ]
do
line= cat FILES.txt | head -$i | tail -1
echo $line
i=`expr $i + 1`
#echo $i
#echo "$i" -le "$n"
done

Dynamic Record Group

LOV (List of Values)
LOV is nothing but a form item which is used to provide list of values to a text field.
Each LOV contains a Record Group which provides the data to the LOV

Record Group
A record groups contains the query which is used to populate the values to the LOV.

To alter a list of values(LOV) of a text field, we should change the query of the record group which is attached to the corresponding LOV or we should create a new record group and attach it to the existing LOV.

Note : We cant create a new LOV. But we can attach a exsiting LOV to a form field.

Method 1: Changing the query of the existing Record group

declare
rg_id RECORDGROUP ;
err number;
lv_id lov;
begin
rg_id:=find_group('LOV4'); -- LOV4 is the name of the existing LOV
if not id_null(rg_id) then
 err:=populate_group_with_query(rg_id,
         'select deptno from dept where dname=''SALES''');
end if;
lv_id:=find_lov('LOV4');
set_lov_property(lv_id,group_name,rg_id);
end;

Method 2: Creating a new Record Group if doesn't exists

declare
rg_id recordgroup;
pg_num number;
lv_id lov;
begin
rg_id:=find_group('MYGROUP');  -- MYGROUP is the Group Name
if id_null(rg_id) then
 rg_id:=create_group_from_query('MYGROUP','SELECT 100 deptno FROM dual');
end if;
pg_num:=populate_group(rg_id);
lv_id:=find_lov('LOV4');
set_lov_property(lv_id,group_name,rg_id);
end;

Move Order Transfer vs Sub-Inventory transfer

The difference between move order transfer and subinventory transfer is
In move orders, you can have an approval so you are not simply "taking" material from a subinventory that is under someone else's control.

Also, move orders create allocations. So you can place hold on the material with the intention of picking it up a little later. In subinventory transfer, there is no reservation / allocation. So until you actually pick up the material and perform the transaction, someone else can claim the material.

Script to transfer a file from one UNIX box to another

HOST = '192.168.100.2'
USER = 'username'
PASSWD = 'password'
FILE = 'myfile.log'

ftp -n $HOST   <<  END_SCRIPT
quote USER $USER
quote PASS $PASSWD
get $FILE
quit
END_SCRIPT
exit 0

Script to check a directory and file

if [ -d /home/r12oravis/test ]
then
echo "Directory /home/r12oravis/test is available"
cd //home/r12oravis/test
if [ -f aaa.prg ]
then
  echo "File exists"
  if [ -r aaa.prg ]
  then
     echo "File readable"
     if [ -s aaa.prg ]
     then
        echo "File is not empty"
     else
        echo "File is empty"
     fi
  else
     echo "File is not readable"
  fi
else
  echo "File doesn't exists"
fi
else
echo "Directory doesn't exists"
fi