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.
Tuesday, November 10, 2009
IS_NUMBER - Equivalent SQL Query
The below query is used to check whether a varchar column contains only NUMBER or NON NUMBER data.
Monday, August 3, 2009
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
Sunday, June 14, 2009
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.
Friday, June 12, 2009
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.
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.
Tuesday, May 26, 2009
''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:
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.
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.
Tuesday, January 20, 2009
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
Tuesday, January 6, 2009
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
Method 2: Creating a new Record Group if doesn't exists
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;
Subscribe to:
Posts (Atom)