Just passed my 10g OCA exam(IZ0-042) yesterday. Hurray!!! I'd been delaying the exam for ages, at last i made up myself to go ahead and finish it once for all..hahah.. When you are working you become a bit lazy to go through all those revisions to make sure you are 100% prepared for the exam. Ok i know, i admit it just an lame excuse for being plain lazy.
Tips for passing the exam:
1. Revision on Oracle Administration document
2. Run some tests for prove of concept
3. Read the sample questions.
(This one really helps a lot. Test King, Brains Dump, Cert Killer and etc are providing useful test questions. Search on web, you may even find a bunch for free.)
The 10g OCA exam wasn't so hard overall. If you are familiar with working on Oracle databases, in fact you will find it very easy.
Custom Search
Wednesday, May 21, 2008
Friday, May 16, 2008
Create Calendar in Oracle Form
Before you start, make sure you have the "stndrd.olb" and"calendar.pll" file.
1.Open the file stndrd.old from Form Developer. You will notice the item "standards" is added to the object navigator/object libraries section.
2.Next open or create a new form.
3.Go to "Object Libraries" under "STANDARDS" library, expand the "library tabs", and double click on "COMPONENTS" object.
4.Drag the component "calendar" to the new form. A message box wills pop-up to ask whether you want to "sub-class" or "copy". Select "copy" or the new form with the calendar will not work on PC's that does not have the standards item added. Copying will add all required bits you need for the calendar.
5.Next open the "calendar.pll" file. This will add a "calendar" item to the "pl/sql libraries" section on the "object navigator". Select the "program unit" section and drag it up to the "programs unit" section of your form. This will add 2 items to the "programs unit" section (date_lov - package spec & date lov -package body).
6.After that you need to add a program unit procedure named DATE_CHOOSEN in the new form or you will get error “date_choosen not declared”. Copy this code into the procedure:
PROCEDURE date_choosen IS
BEGIN
copy(to_char(date_lov.current_lov_date,'dd-mon-yyyy'), date_lov.date_lov_return_item);
go_item(date_lov.date_lov_return_item);
if date_lov.lov_auto_skip = TRUE then
next_item;
end if;
END;
7.Next on the new form you can add a button to call the "calendar" with button trigger. Use the code below to call the "calendar".
DATE_LOV.GET_DATE
(NVL(:BLOCK.ITEM, SYSDATE),
'BLOCK.ITEM',
200, 100,
'SELECT DATE',
'OK','CANCEL',
TRUE, FALSE, FALSE);
The “block.item” is where you want the date to be inserted into via the calendar user interaction.
Parameters for the Date_LOV.Get_Date are as follows:
date_lov.get_date (initial date,
return block.item,
window x position,
window y position,
window title,
ok button label,
cancel button label,
highlight weekend days,
autoconfirm selection,
autoskip after selection);
You may take a look at the 'Calendar' code that is available as a download from the Oracle Forms website http://www.oracle.com/technology/sample_code/products/forms/6idemos.html
References: http://www.orafaq.com/forum/t/30334/0/
1.Open the file stndrd.old from Form Developer. You will notice the item "standards" is added to the object navigator/object libraries section.
2.Next open or create a new form.
3.Go to "Object Libraries" under "STANDARDS" library, expand the "library tabs", and double click on "COMPONENTS" object.
4.Drag the component "calendar" to the new form. A message box wills pop-up to ask whether you want to "sub-class" or "copy". Select "copy" or the new form with the calendar will not work on PC's that does not have the standards item added. Copying will add all required bits you need for the calendar.
5.Next open the "calendar.pll" file. This will add a "calendar" item to the "pl/sql libraries" section on the "object navigator". Select the "program unit" section and drag it up to the "programs unit" section of your form. This will add 2 items to the "programs unit" section (date_lov - package spec & date lov -package body).
6.After that you need to add a program unit procedure named DATE_CHOOSEN in the new form or you will get error “date_choosen not declared”. Copy this code into the procedure:
PROCEDURE date_choosen IS
BEGIN
copy(to_char(date_lov.current_lov_date,'dd-mon-yyyy'), date_lov.date_lov_return_item);
go_item(date_lov.date_lov_return_item);
if date_lov.lov_auto_skip = TRUE then
next_item;
end if;
END;
7.Next on the new form you can add a button to call the "calendar" with button trigger. Use the code below to call the "calendar".
DATE_LOV.GET_DATE
(NVL(:BLOCK.ITEM, SYSDATE),
'BLOCK.ITEM',
200, 100,
'SELECT DATE',
'OK','CANCEL',
TRUE, FALSE, FALSE);
The “block.item” is where you want the date to be inserted into via the calendar user interaction.
Parameters for the Date_LOV.Get_Date are as follows:
date_lov.get_date (initial date,
return block.item,
window x position,
window y position,
window title,
ok button label,
cancel button label,
highlight weekend days,
autoconfirm selection,
autoskip after selection);
You may take a look at the 'Calendar' code that is available as a download from the Oracle Forms website http://www.oracle.com/technology/sample_code/products/forms/6idemos.html
References: http://www.orafaq.com/forum/t/30334/0/
Monday, May 12, 2008
How to calculate table size
Sometime we need to estimate the size of the table for storage planing. But looking at the database, it doesn't shows us directly how many space is actually occupied by a particular table. A bit of peek on the USER_SEGMENTS and USER_TABLES views will do the trick.
To query the actual space allocated for the table:
select segment_name, bytes/1024/1024,blocks MB
from user_segments
where segment_name = 'T1'
or
select (BLOCKS*[replace your data block size here])
from user_tables
where table_name = 'T1';
To query the actual space occupied by the data:
select table_name,(num_rows*avg_row_len)/(1024*1024) MB
from user_tables
where table_name='T1';
or
select ((BLOCKS*[replace your data block size here])-(BLOCKS*AVG_SPACE))/1024/1024 MB
from user_tables
where table_name='T1'
Somehow the second method will show bigger amount of spaces being occupied.
Note: we need to analyzed the table before we can get all these statistic.
To query the actual space allocated for the table:
select segment_name, bytes/1024/1024,blocks MB
from user_segments
where segment_name = 'T1'
or
select (BLOCKS*[replace your data block size here])
from user_tables
where table_name = 'T1';
To query the actual space occupied by the data:
select table_name,(num_rows*avg_row_len)/(1024*1024) MB
from user_tables
where table_name='T1';
or
select ((BLOCKS*[replace your data block size here])-(BLOCKS*AVG_SPACE))/1024/1024 MB
from user_tables
where table_name='T1'
Somehow the second method will show bigger amount of spaces being occupied.
Note: we need to analyzed the table before we can get all these statistic.
Friday, May 9, 2008
Oracle Forms SQL Injection
As Oracle Form user, we always like to query record using the "query mode" button. A wonder button with a picture of database and question mark. But often, most of the people don't know that there is an ancient feature behind called "Query/Where" that allows users to modify existing SQL statements. This is an feature which is built with wonderful purpose but dangerous due to the fact that all forms user can use it to execute arbitrary SQL statements.This function available for Oracle Forms versions 3.0 up to 10g.
Short demonstration of Oracle Forms SQL Injection:
1. Start a Forms module and switch to the query mode
2. Enter a colon ( : ) or ampersand ( & )
3. An empty Query/Where windows will pops up
4. Inject your SQL statement
Example:
My data block is querying data from SUBJECT table.
The normal data block query will be "select subject_code, subject_description from subject".
if i put the data column into query mode then place ":" or "&" and execute the query. A blank box will comes out. Then i enter "subject_code = '%') union select table_name, null from user_tables--". Once i click on "OK", all table name is shown on the subject code column.
Suggestion of Solution:
1. Disable "query allow" for all data block columns which appear on the form
2. Set environment variable FORMSxx_RESTRICT_ENTER_QUERY=true (FORMS60_RESTRICT_ENTER_QUERY for Forms 6.x, FORMS90_RESTRICT_ENTER_QUERY for Forms 9.x/10g)
3. Use PRE_QUERY/ON-ERROR-trigger to detect sql injection
Short demonstration of Oracle Forms SQL Injection:
1. Start a Forms module and switch to the query mode
2. Enter a colon ( : ) or ampersand ( & )
3. An empty Query/Where windows will pops up
4. Inject your SQL statement
Example:
My data block is querying data from SUBJECT table.
The normal data block query will be "select subject_code, subject_description from subject".
if i put the data column into query mode then place ":" or "&" and execute the query. A blank box will comes out. Then i enter "subject_code = '%') union select table_name, null from user_tables--". Once i click on "OK", all table name is shown on the subject code column.
Suggestion of Solution:
1. Disable "query allow" for all data block columns which appear on the form
2. Set environment variable FORMSxx_RESTRICT_ENTER_QUERY=true (FORMS60_RESTRICT_ENTER_QUERY for Forms 6.x, FORMS90_RESTRICT_ENTER_QUERY for Forms 9.x/10g)
3. Use PRE_QUERY/ON-ERROR-trigger to detect sql injection
Thursday, May 8, 2008
Flashback Query
Anyone ever encounter some “oh my god” or “I’m in deep shit” situations when you did a batch update to the data?
Instead of recover the data from backup. We have something call flash back query from Oracle 9i onward. With this feature you are able to query out the version of data “as of” specific timestamp.
Command Example:
1. SQL> SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2006-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JON';
2. SQL> INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2006-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JON');
P/S: This feature is of available in Oracle 9i onward. You must make sure your UNDO_RETENTION value is big enough, else you will get "ora-01555 snapshot too old" error
Related topic: Oracle Flashback Technologies includes Flashback Database, Flashback Table, Flashback Drop, Flashback Versions Query, and Flashback Transaction Query.
Instead of recover the data from backup. We have something call flash back query from Oracle 9i onward. With this feature you are able to query out the version of data “as of” specific timestamp.
Command Example:
1. SQL> SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2006-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JON';
2. SQL> INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2006-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JON');
P/S: This feature is of available in Oracle 9i onward. You must make sure your UNDO_RETENTION value is big enough, else you will get "ora-01555 snapshot too old" error
Related topic: Oracle Flashback Technologies includes Flashback Database, Flashback Table, Flashback Drop, Flashback Versions Query, and Flashback Transaction Query.
Wednesday, May 7, 2008
Useful sql to clean duplicated records
Delete duplicated data from table can be a headache. In the example below we are using the unique ROWID to distinguish the duplicated rows. (Each record in database contains a uniques system generated number)
DELETE FROM TEST1 A
WHERE ROWID >
(SELECT MIN(ROWID)
FROM TEST1 B
WHERE A.COLUMN1 = B.COLUMN1
[AND A.COLUMN2 = B.COLUMN2
AND A.COLUMNn = B.COLUMNn])
Alternatively we can use a temporary table do the trick:
CREATE TABLE TEMP_TEST2 AS
SELECT DISTINCT *
FROM TEST1
WHERE [PUT IN UR CONDITIONS];
DELETE TABLE TEST1;
INSERT INTO TEST1 SELECT * FROM TEMP_TEST2;
DELETE FROM TEST1 A
WHERE ROWID >
(SELECT MIN(ROWID)
FROM TEST1 B
WHERE A.COLUMN1 = B.COLUMN1
[AND A.COLUMN2 = B.COLUMN2
AND A.COLUMNn = B.COLUMNn])
Alternatively we can use a temporary table do the trick:
CREATE TABLE TEMP_TEST2 AS
SELECT DISTINCT *
FROM TEST1
WHERE [PUT IN UR CONDITIONS];
DELETE TABLE TEST1;
INSERT INTO TEST1 SELECT * FROM TEMP_TEST2;
Hello World!!
Hi everyone, welcome to my blog. I will be basically sharing my 2 years plus experience as an Oracle Form/Report Developer and Oracle DBA in this blog. I will not rate myself as an Oracle expert but instead i to share some of the tips and tricks which i think it is quite useful for Oracle Developer and DBA beginner. Feel free to drop me some feedback if you have any opinion or suggestion on my articles. Thanks.
Subscribe to:
Comments (Atom)