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

Running a simple Shell Script as a HOST program

There are basically 12 types of executable available in Oracle applications. They are
(Concurrent -> Program -> Executable)

1. Oracle Reports
2. Host
3. Immediate
4. PL/SQL Stored Procedure
5. SQL * Loader
6. SQL * Plus
7. Spawned
8. Java Concurrent Program
9. Java Stored Procedure
10. Perl Concurrent Program
11. Request Set Stage Function
12. Multi Language Function

We are going to see about HOST type of executable. Its generally used to run a Shell Script.

What is meant by Shell Script?
Shell Script is a collection of shell commands(UNIX commands) which will be executed sequentially. Using a shell script you can create a directory, move files, check the file availability, run a SQL command, etc....

Example for a shell script:
echo "This is a sample script run by bala kaarthik"
echo $1
echo $2
echo $3
echo $4
exit 0

In the above script the first line used to print a string. '
Note: I have used the word 'shell' many times. What is a shell actually? Its nothing but a compiler which is used to run a command. UNIX have different type of compilers(shells) like bash shell, C shell, etc... For more details on shell refer http://www.wikipedia.org/


How to register a HOST program?

1. Create a Shell program (VBBKSH.prog)
Use the above script and save it as VBBKSH.prog (case sensitive)

2.Move the shell program to the application top bin directory.
Find your application top XX_TOP. In that you can find bin directory ( if not create one and give 777 permission (read write executable for all) ). Move the file to that directory and give 777 permission to the file

3. Create a soft link
Its essential to create a soft link for your shell program. To create a soft link use the following shell command
ln -s $FND_TOP/bin/fndcpesr $XX_TOP/bin/VBBKSH

Where XX_TOP is your application top. Don't include the file extension (.prog) while creating the soft link. After executing the shell command a soft link will be created in the name of the shell program without any file extension.

4. Create Executable
Change your responsibility to "System Administrator" or "Application Developer"
Create a executable with "Execution method" as HOST and "Execution file name" as VBBKSH.
Don't forget to give the correct custom application name.

5.Create a Concurrent Program
Create the concurrent program and give the executable name which was created in the previous step

Parameters:
Regarding the parameters basically 4 parameters will be sent to the shell program by default. They are
$1 - database username/password
$2 - loginid
$3 - application username
$4 - concurrent request ID

If you want to send some parameters to the shell program that will be used as $5,$6,etc.... inside the shell program.


6.Register the concurrent program to the custom application's responsibility

7. Change to your application responsibility and run the concurrent program

Running Concurrent Program from back end

Here i am going to explain how to execute a concurrent program from the backend.

Let us take a concurrent program "Subinventory quantities report" and run it in the front end. Check whether its executing successfully from the front end.

Login into the vision instance. (Username : OPERATIONS , Password : welcome )

Choose the "Inventory, Vision Operations (USA)" responsibility

Go to View -> Requests

Press "Submit a New Request"

Select "Single Request" and then press "OK"

Give the Request name as "Subinventory quantities report" and press TAB key

Give the parameters

Subinventory From : DropShip
Subinventory To : DropShip
Items From :
To :



Press OK and view the output for the report

Till now you successfully exectued a concurrent program from the front end.

Now we are going to see how this concurrent program can be executed in the back end

"fnd_request.submit_request" is used to execute the concurrent program from back end
before calling this function we need to initialize the Application from the back end.

"fnd_global.apps_initialize" is used to initialize the application from the back end.


procedure APPS_INITIALIZE(user_id in number,
resp_id in number,
resp_appl_id in number,
security_group_id in number default 0,
server_id in number default -1)

Argument : User_id

Its nothing but ID of the User in which the apps was logged in. Here we logged in as "OPERATIONS" as the user. To know

the user id of "OPERATIONS" we use the following query.

SELECT user_id FROM fnd_user WHERE user_name = 'OPERATIONS';

Arguemnt : resp_id

Its nothing but ID of the responsiblity which we selected. Here we are in "Inventory, Vision Operations (USA)" reponsibility.

select responsibility_id from fnd_responsibility_tl
where responsibility_name ='Inventory, Vision Operations (USA)'

If we know the resposbility short name, we can use the following query to get the responsiblity id.

select responsibility_id from fnd_responsibility
where responsibility_key='INVENTORY_VISION_OPERATIONS'

Argument : resp_appl_id

Its the ID for the application for which the responsibility was registered.

select application_id from fnd_responsibility_tl
where responsibility_name ='Inventory, Vision Operations (USA)'


select application_id from fnd_responsibility
where responsibility_key='INVENTORY_VISION_OPERATIONS'


function submit_request ( application IN varchar2 default NULL,
program IN varchar2 default NULL,
description IN varchar2 default NULL,
start_time IN varchar2 default NULL,
sub_request IN boolean default FALSE,
argument1 IN varchar2 default CHR(0),
argument2 IN varchar2 default CHR(0),
argument3 IN varchar2 default CHR(0),
.
.

argument100 IN varchar2 default CHR(0)) return number ;

Argument : application

Its the application to which the concurrent program was registered.

select fa.application_short_name
from fnd_application fa,fnd_concurrent_programs_tl fcp
where fa.application_id=fcp.application_id
and fcp.user_concurrent_program_name='Subinventory quantities report'

Argument : program

Its the short name of the concurrent program.

select fcp.concurrent_program_name
from fnd_concurrent_programs fcp,fnd_concurrent_programs_tl fcpt
where fcp.concurrent_program_id=fcpt.concurrent_program_id
and fcpt.user_concurrent_program_name='Subinventory quantities report'

Argument : description

Its the discription of the concurrent program. Its a optional parameter

select description from fnd_concurrent_programs_tl
where user_concurrent_program_name='Subinventory quantities report'

Argument : start_time
Its used for scheduled concurrent program. Its also a optional parameter

Argument : sub_request
Mostly FALSE

Argument : Argument1,Argument2,Argument3,.....Argument100
These are the parameters passed to the concurrent program. Some of the parameters were hiddend from the user as it

takes the value from profile.

To know the exact parameter values, check the previous concurrent program submission and check the parameter passed.

Otherwise open the Concurrent program from the application developer and see the parameter passed. Otherwise see the log file of the previously executed concurrent program

---log----
Arguments
------------
P_ORGANIZATION_ID='207'
P_QTY_PRECISION='2'
P_SUBINV_LO='DropShip'
P_SUBINV_HI='DropShip'
------------
---log----

In this case the arguments are
(tokens in the parameter window)
P_ORGANIZATION_ID (hidden)
P_QTY_PRECISION (hidden)
P_SUBINV_LO (optional)
P_SUBINV_HI (optional)
P_ITEM_LO (optional)
P_ITEM_HI (optional)

-------------------------------------------------------------------------------------------
Procedure to run the concurrent program from the back end

SET serveroutput on;

DECLARE
  v_user_id     NUMBER;
  v_app_id      NUMBER;
  v_resp_id     NUMBER;
  v_req_id      NUMBER;
  v_exec_mode   VARCHAR2 (1) := 'S';
BEGIN
  SELECT user_id
    INTO v_user_id
    FROM fnd_user
   WHERE user_name = 'OPERATIONS';

  SELECT application_id
    INTO v_app_id
    FROM fnd_application
   WHERE application_short_name = 'INV';

  SELECT responsibility_id
    INTO v_resp_id
    FROM fnd_responsibility
   WHERE application_id = v_app_id
     AND responsibility_key = 'INVENTORY_VISION_OPERATIONS';

  fnd_global.apps_initialize (v_user_id, v_resp_id, v_app_id);
  v_req_id :=
     fnd_request.submit_request (application      => 'INV',
                                 program          => 'INVIRSIQ',
                                 sub_request      => FALSE,
                                 argument1        => 207,
                                 argument2        => 2,
                                 argument3        => 'DropShip',
                                 argument4        => 'DropShip',
                                 argument5        => NULL,
                                 argument6        => NULL
                                );
  DBMS_OUTPUT.put_line ('Request_ID = ' || v_req_id);
  COMMIT;
END;

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

Usefull SQLs

To convert the number in words
select to_char(to_Date(5373484,'J'),'Jsp') Result from dual

The result is : Five Million Three Hundred Seventy-Three Thousand Four Hundred Eighty-Four
Note : This is the maximum number u can convert in words ( 5373484 , because Julian year ranges between 1 and 5373484 )


Procedure to copy values from one column to another column

DROP TABLE xxtest;

CREATE TABLE xxtest(col1 NUMBER,col2 NUMBER);

INSERT INTO xxtest VALUES (1, NULL);
INSERT INTO xxtest VALUES (2, NULL);
INSERT INTO xxtest VALUES (3, NULL);
INSERT INTO xxtest VALUES (4, NULL);

BEGIN
  FOR i IN (SELECT ROWID, col1 FROM xxtest)
  LOOP
     UPDATE xxtest SET col2 = i.col1
      WHERE ROWID = i.ROWID;
  END LOOP;
END;

Procedure to change first 3 character in a column

DROP TABLE xxtest2;

CREATE TABLE xxtest2(NAME VARCHAR2(20));

INSERT INTO xxtest2 VALUES ('abc123');
INSERT INTO xxtest2 VALUES ('abc124');
INSERT INTO xxtest2 VALUES ('abc125');

BEGIN
  FOR i IN (SELECT ROWID, NAME FROM xxtest2
             WHERE SUBSTR (NAME, 1, 3) = 'abc')
  LOOP
     UPDATE xxtest2 SET NAME = 'xyz' || SUBSTR (i.NAME, 4, LENGTH (i.NAME))
      WHERE ROWID = i.ROWID;
  END LOOP;
END;

DENSE_RANK

Here we are going to use the analytical function DENSE_RANK to find the employees who hold the 7th highest salary

select * from (select ename, sal , dense_rank() over(order by sal desc) myrank from emp) where myrank=7

Plan Table

Plan Table is a table which will be created to see the execution method of a SQL script. Using the execution method we can optimize the query. This is mostly used during performance tunning.

EXPLAIN PLAN is the utility used by oracle to know the execution path of the SQL query before actually executing it.

There are many third party to tool to know the performance of the query and the execution plan.
TOAD is one of such tool

Let us see how to view the explain plan in TOAD which is used while performance tunning

1. We should create a plan table to view the explain plan of the query
The script for creating a plan table will be in the following path
$ORACLE_HOME/rdbms/admin/ and the file name is utlxplan.sql
This script is used to create the plan table ( PLAN_TABLE)

2. Change the setting in your TOAD
Open the TOAD and go to View -> Options


Then go to Oracle -> General



In that give the schema and plan table name.
Schema : APPS
Table Name : PLAN_TABLE

3 .To view the explain plan
Create a query and click the explain plan tab in TOAD. Now you can see the execution method of the table which is used to optimize and tune the query


Errors while viewing Explain Plan
We may get two types of errors while viewing explain plan
1. ORA-02404: specified plan table not found
To resolve this error create the PLAN_TABLE in APPS schema

2.ORA-00904: "PARTITION_STOP": invalid identifier.
This indicates that the plan table was not properly created properly. To create the plan table execute the following query from the specified path
$ORACLE_HOME/rdbms/admin/utlxplan.sql

Use this code to create PLAN_TABLE if nothing works

CREATE TABLE PLAN_TABLE
(
  STATEMENT_ID       VARCHAR2(30 BYTE),
  PLAN_ID            NUMBER,
  TIMESTAMP          DATE,
  REMARKS            VARCHAR2(4000 BYTE),
  OPERATION          VARCHAR2(30 BYTE),
  OPTIONS            VARCHAR2(255 BYTE),
  OBJECT_NODE        VARCHAR2(128 BYTE),
  OBJECT_OWNER       VARCHAR2(30 BYTE),
  OBJECT_NAME        VARCHAR2(30 BYTE),
  OBJECT_ALIAS       VARCHAR2(65 BYTE),
  OBJECT_INSTANCE    INTEGER,
  OBJECT_TYPE        VARCHAR2(30 BYTE),
  OPTIMIZER          VARCHAR2(255 BYTE),
  SEARCH_COLUMNS     NUMBER,
  ID                 INTEGER,
  PARENT_ID          INTEGER,
  DEPTH              INTEGER,
  POSITION           INTEGER,
  COST               INTEGER,
  CARDINALITY        INTEGER,
  BYTES              INTEGER,
  OTHER_TAG          VARCHAR2(255 BYTE),
  PARTITION_START    VARCHAR2(255 BYTE),
  PARTITION_STOP     VARCHAR2(255 BYTE),
  PARTITION_ID       INTEGER,
  OTHER              LONG,
  DISTRIBUTION       VARCHAR2(30 BYTE),
  CPU_COST           INTEGER,
  IO_COST            INTEGER,
  TEMP_SPACE         INTEGER,
  ACCESS_PREDICATES  VARCHAR2(4000 BYTE),
  FILTER_PREDICATES  VARCHAR2(4000 BYTE),
  PROJECTION         VARCHAR2(4000 BYTE),
  TIME               INTEGER,
  QBLOCK_NAME        VARCHAR2(30 BYTE),
  OTHER_XML          CLOB
)

Basic UNIX commands

# ls - This command is used to see list of files in the directory and its subdirectories
# ls -all - This is used to see the hidden files and file sizes in a directory

diff - This command is ued to know the difference between two files

Important Tables

Table Name : ALL_TAB_COLUMNS
This table is used to get the column details of the given table

select column_name,data_type,data_length,nullable from ALL_TAB_COLUMNS where TABLE_NAME='OE_ORDER_HEADERS_ALL'


Table Name : ALL_IND_COLUMNS
This table is used to fetch the indexes and the columns to which the index was applied.

select index_name,column_name from all_ind_columns where TABLE_NAME='OE_ORDER_HEADERS_ALL'



.

Distinct Values in Value Set

To get distinct values in a valueset (without creating a view), the following steps can be followed

In the place of table name use the following
(select distinct column_name from table_name) a

where 'a' is the alias nameIn the value field give
a.column_name

Some Functions in Report Builder

To Print in log file

SRW.MESSAGE(1,'String to be printed in log file');

Here first parameter indicates the log file(1).
Second parameter is the string that to be printed in log file.

To Abort from the report

RAISE SRW.program_abort;

Use of Analytical Function

Requirement : I need list of items purchased in a month. If a item purchased more than once in a month then i need the recent purchase order number.

Solution :

SELECT *
  FROM (SELECT segment1, description, ponumber,
               ROW_NUMBER () OVER (PARTITION BY segment1 ORDER BY pocreationdate DESC) mycol
          FROM base_table
         WHERE TO_CHAR (pocreationdate, 'MM YYYY') = '04 2008')
         WHERE mycol = 1

DAY Validation

Hope you all know about SQL functions 'to_char' , 'trim' and 'length'

'to_char' used to convert a date or number type to a character type
'trim' mostly used to delete white spaces in a given string.(we can also trim character from a string . check the SQL oracle user guide)
'length' is used to get the length(no. of characters) of the given string

Please check the following queries to understand well about this

Assume today is MONDAY
select to_char(sysdate,'DAY') from dual;

will return MONDAY. we all know that

select length(to_char(sysdate,'DAY')) from dual;

should return 6. but it will return 9. the reason behind this is to_char(sysdate,'DAY') always return a string with 9 characters. so the MONDAY will have three white spaces at the end.

why its returning always the length of 9?
the answer is the maximum character a DAY can hold is 9(WEDNESDAY). so it always pad white spaces for the remaining length

to get the correct length use the following query

select length(trim(to_char(sysdate,'DAY'))) from dual;

Hope you can use these query in day validation.

Assume today is MONDAY
The following query will return 'Today is not Monday'.

set serveroutput on;
begin
if to_char(sysdate,'DAY')='MONDAY' then
dbms_output.put_line('Today is Monday');
else
dbms_output.put_line('Today is not Monday');
end if;
end;
/

To get the correct result ('Today is Monday') use the following query

set serveroutput on;
begin
if trim(to_char(sysdate,'DAY'))='MONDAY' then
dbms_output.put_line('Today is Monday');
else
dbms_output.put_line('Today is not Monday');
end if;
end;
/