Download Oracle Jinitiator - Version 1.3.1.28
Download the file and rename the executable to jinit.exe
Click here to download the file
When the download finishes you can install Oracle JInitiator by locating the file using the Windows Explorer and double-clicking on it to start the installation process.
When the installation process has finished, exit your browser and restart it. Return to the initial application starting page and the browser will then load Oracle JInitiator and start your Oracle Developer application.
Oracle JInitiator supports the following browser in Microsoft Windows environment:
Platform IE 5.5 IE 6.0 Netscape Navigator 4.7.x
Windows NT Yes Yes Yes
Windows 2000 Yes Yes Yes
Windows XP No Yes Yes
Windows Vista -- No Not Known
But do make sure with Oracle documentation before installing for a browser.
Other minimum system requirements for Oracle JInitiator are:
Pentium 166 MHz or better processor
70 MB free hard disk space before installing
32-48 MB system RAM minimum for running Oracle Forms applications
Read a brief introduction about JInitiator
Oracle Jinitiator a brief introduction
Hereafter referred to as Jinit, it is a JVM distributed by Oracle Corporation. Jinit enables the Oracle Forms client application (otherwise known as Oracle D2K in earlier versions) in a web browser.
Earlier the Oracle forms where only available as client installed stand-alone applications which accesses the database and file system to run the application. With the advent of Jinit, the client installation of Oracle Forms run time is not required. The browser plug in for Jinit only must be installed.
While accessing the application for the first time if the plug in is not installed the browser will prompt for the installation. At this point, you must be logged in with Admin rights. You can revert the rights once the installation is done.
Versions
The Jinit referred to in this article is 1.3.1.28 which was rolled on August 2007. The versions is based on some practises. The first two numbers that is 1.3 in our case refers to the Sun Microsystem's JDK version 1.3 or later.
The Jinit referred to in this article is 1.3.1.28 which was rolled on August 2007. The versions is based on some practises. The first two numbers that is 1.3 in our case refers to the Sun Microsystem's JDK version 1.3 or later.
Installing Jinit
Once the URL for the application is configured, use this URL from a supported browser. The browser should prompt for installation of the Jinit plug in. If the browser is not prompting for the installation, then most probably there is problems with configuration. Sometimes the browser may block the plug ins to be installed. Check for the information bar which appears in the browser. Also check the settings for installation of plug ins in your browser.
Once the URL for the application is configured, use this URL from a supported browser. The browser should prompt for installation of the Jinit plug in. If the browser is not prompting for the installation, then most probably there is problems with configuration. Sometimes the browser may block the plug ins to be installed. Check for the information bar which appears in the browser. Also check the settings for installation of plug ins in your browser.
Once the browser prompts for the installation, Install it. Just install with no changes as prompted by Oracle. Once the installation is complete, the application should open in the browser window. Though there are problems with Windows XP, with Internet Explorer the application may not open in the same browser window. Close the browser window and access the application URL once again. If the installation is properly done, the application should open.
Problems with Jinit
Jinit is known not to work with Windows Vista. There is no plans for Sun to support JDK 1.3 on Vista. So Oracle has not certified Jinit for Vista. Vista users should consider using Sun's plug in 1.5.0_06 or 1.6.0_04. This will work for Internet Explorer as well as FireFox browsers.
Jinit is known not to work with Windows Vista. There is no plans for Sun to support JDK 1.3 on Vista. So Oracle has not certified Jinit for Vista. Vista users should consider using Sun's plug in 1.5.0_06 or 1.6.0_04. This will work for Internet Explorer as well as FireFox browsers.
Can I run Jinit In Linux?
Jinit is not only certified for Microsoft Windows platform. Other operating systems must use the Sun's plug in for accessing Oracle forms application through browsers.
For accessing Oracle Forms application from inside Linux operating system, Oracle certifies the usage of Sun plug in for Mozilla 1.5 browser. Use plug in 1.4.2_06 for Mozilla 1.5
What is it's future of Jinit?
The future of Jinit is not bright (thanks to Oracle), it is being replaced by the Sun's JDK 1.5/1.6 in 11g. But it is just a rumour, not a confirmed news.
Appendix: Supported operating systems and web browsers for Jinit
Apple Max OS - Safari Browser - Sun Plug in
Microsoft Windows (XP, 2003, 2000) - Internet Explorer (6), FireFox, Mozilla, Netscape browsers - Jinit / Sun Plug in
Microsoft Vista - Internet Explorer, FireFox Browsers - Sun Plug in
Solaris (2.8/2.9) - Netscape Browser - Sun Plug in
Linux - Mozilla - Sun Plug in
References: Oracle Technology, Wikipedia
How to design a database - The untold way
Rule #1
The design of a database must only be thought after the through analysis of the user requirements.
Rule #2
Never implement any functionalities of Oracle if it is not required. Always adhere Rule #1.
Rule #3
Never implement as per theory, but base your design on practical thought. Always adhere Rule #2.
What does this mean?
I will elaborate each and every point mentioned above. Also note that all the rules are related to one another. This ensures that the design is always as per user requirements.
Rule #1
The first rule says that never start or think of database design before understanding what exactly the user requires. This is the major and very important rule which must be adhered, whatever comes.
This rule thus verifies the importance of analysis of user requirements. Unless the requirements of user is fully understood, it will be nearly impossible to design the database.
Rule #2
This rule again double verifies the rule #1. The design must be using only using such oracle functionalities which are exactly required. Otherwise if a new feature is used without additional thought on it, it will affect the application performance and also may throw the application as useless.
For example, for an order entry screen there is one master-detail table identified as per user requirements. The normal way of going for design for such a scenario is for designing two tables with parent key (primary key) and child keys (foreign keys). But if we design the same structure say with Nested tables, it will affect the way the data is stored. It will also be very difficult for MIS applications which require just the child tables.
Rule #3
This rule reminds of the theory we learn when studying the database concepts. For example Normalization. We have all learned that all tables must be normalized. And normally we all adhere to this rule. But this is just a guideline rather than a rule.
For example in a small application (it is left to the user about what a small application is and what is a large application) the performance might not be of a big effect. But in large applications, this will prove to be fatal. We will take the case of normalization itself. If all the tables are normalized then oracle will be storing data in more than one tables for a transaction. This will cause more overhead for the database to fetch the data, maintain index storage, maintain data storage etc.
It is not though meant that normalization is bad. But normalization of tables which can be avoided should not be normalized. Do not think of your database design in which normal form, wheras think how well it can help the user in maintaining their data.
Thus the database design cirulates around these:
1. Collect necessary user requirements
2. Only after collecting user requirements think of database design
3. Design the database in such a way that it is user oriented (data oriented) rather than theory oriented
4. You need not use all the features that oracle offers.
This ends part 1 of Tuning guide. Next parts will be updated soon. Watch this space.
The design of a database must only be thought after the through analysis of the user requirements.
Rule #2
Never implement any functionalities of Oracle if it is not required. Always adhere Rule #1.
Rule #3
Never implement as per theory, but base your design on practical thought. Always adhere Rule #2.
What does this mean?
I will elaborate each and every point mentioned above. Also note that all the rules are related to one another. This ensures that the design is always as per user requirements.
Rule #1
The first rule says that never start or think of database design before understanding what exactly the user requires. This is the major and very important rule which must be adhered, whatever comes.
This rule thus verifies the importance of analysis of user requirements. Unless the requirements of user is fully understood, it will be nearly impossible to design the database.
Rule #2
This rule again double verifies the rule #1. The design must be using only using such oracle functionalities which are exactly required. Otherwise if a new feature is used without additional thought on it, it will affect the application performance and also may throw the application as useless.
For example, for an order entry screen there is one master-detail table identified as per user requirements. The normal way of going for design for such a scenario is for designing two tables with parent key (primary key) and child keys (foreign keys). But if we design the same structure say with Nested tables, it will affect the way the data is stored. It will also be very difficult for MIS applications which require just the child tables.
Rule #3
This rule reminds of the theory we learn when studying the database concepts. For example Normalization. We have all learned that all tables must be normalized. And normally we all adhere to this rule. But this is just a guideline rather than a rule.
For example in a small application (it is left to the user about what a small application is and what is a large application) the performance might not be of a big effect. But in large applications, this will prove to be fatal. We will take the case of normalization itself. If all the tables are normalized then oracle will be storing data in more than one tables for a transaction. This will cause more overhead for the database to fetch the data, maintain index storage, maintain data storage etc.
It is not though meant that normalization is bad. But normalization of tables which can be avoided should not be normalized. Do not think of your database design in which normal form, wheras think how well it can help the user in maintaining their data.
Thus the database design cirulates around these:
1. Collect necessary user requirements
2. Only after collecting user requirements think of database design
3. Design the database in such a way that it is user oriented (data oriented) rather than theory oriented
4. You need not use all the features that oracle offers.
This ends part 1 of Tuning guide. Next parts will be updated soon. Watch this space.
Oracle Software Patching Using OPatch
The OPatch utility is a tool that allows the application and rollback of interim patches to Oracle products.
Patches are a small collection of files that are copied over an existing installation. They are associated to particular versions of Oracle Products. Patches, when applied to the correct version of an installed product, results in an upgraded version of the product.Interim patches are bug fixes that are made available to customers in response to specific bugs. They require a particular base release or patchset to be installed before they can be applied. They generally address specific bugs for a particular customer. These patches are not versioned and are generally made available in a future patchset as well as the next product release.
About OPatch
OPatch is an Oracle supplied utility to assist you with the process of applying interim patches to Oracle's software. OPatch is a Java-based utility which requires the Oracle Universal Installer to be installed. It is platform independent and runs on all supported operating systems.
OPatch supports the following:
-
Applying an interim patch.
-
Rolling back the application of an interim patch.
-
Detecting conflict when applying an interim patch after previous interim patches have been applied. It also suggests the best options to resolve a conflict.
-
Reporting on installed products and interim patch.
Requirements for OPatch
The OPatch utility requires the following:
-
The Oracle home environment variable (
ORACLE_HOME) must point to a valid Oracle home directory and match the value used during installation of the Oracle home directory. -
Java SDK 1.4 or higher, Java commands for Windows and ar, cp, fuser, and, make commands for UNIX must be made available.
-
The library path must be set correctly for Oracle Real Application Clusters environments. OPatch uses some APIs to detect if the system is Real Application Clusters. Ensure that the library path is set correctly as follows:
For Solaris LD_LIBRARY_PATH = $ORACLE_HOME/lib32:$ORACLE_HOME/lib For HP-UX - SHLIB_PATH=$ORACLE_HOME/lib32:/usr/lib
For the latest information about the OPatch utility, and to check for updates, and to get latest versions refer to OracleMetaLink at
Oracle Index and Like clause
Topic: Beginners Level
From time immemorial there has been debate over the usage of like clause and its association (or rather non-association with index). What is the fuzz all about? Let's check here.
Like clause allows you to use wildcard character searches over data stored in oracle database. By this means we can do pattern searching over the existing data.
For example:
You have a daily meeting calendar in which the attendees names are stored in comma seperated values in a VARCHAR2(2000) field. You want to search on what days a particular attendee say for instance Rose has attended the meeting.
table: meeting_schedule
fields:
meeting_date date
meeting_place varchar2(200)
meeting_attendees varchar2(2000)
In such a case of searching, without the usage of wildcard characters such as % will not yeild appropriate results.
The query for such a situation would be:
SELECT meeting_date, meeting_place
FROM meeting_schedule
WHERE meeting_attendees like '%Rose%';
Now the above query will list the meeting_date and meeting_place where Rose has attended. Of course this is a simple usage of LIKE clause.
The Like clause is valid to be used in any DML statement such as SELECT, INSERT, UPDATE and DELETE.
Now what is that links LIKE clause so much with the indexes in Oracle?
Indexes as you may know are like indexes in the book where it points to a page in the book. The same way Oracle indexes point to a data location. Both Indexes and Tables contain two different copies of data, but Indexes contain far less data compared to the tables. Think of Indexes as containing distinct values of a row in a table. So if there are more and more repetitive rows in a row, then accessing Index will prove to be lot faster than accessing the table.
So by this you must have understood where an Index would help. An Index should be used only in such cases, so that it can improve performance. If otherwise used in all places it will degrade the performance.
How can an Index degrade the performance?
By using an index side-by-side of a table, we are agreeing that it has some extra work to do. Like at every insert, update or delete to the table, the index also needs to be updated. This extra work can prove fatal if there are lot of rows in the index.
So as a simple way to know where to use index, if each row of a table is different from that of other row and there are no distinct values do not use indexes. Only use indexes if atleast there are 25% distinct rows available in the table.
Of course there is no such hard core rule as to when to use Index and when not to use. It just depends upon the situation.
From time immemorial there has been debate over the usage of like clause and its association (or rather non-association with index). What is the fuzz all about? Let's check here.
Like clause allows you to use wildcard character searches over data stored in oracle database. By this means we can do pattern searching over the existing data.
For example:
You have a daily meeting calendar in which the attendees names are stored in comma seperated values in a VARCHAR2(2000) field. You want to search on what days a particular attendee say for instance Rose has attended the meeting.
table: meeting_schedule
fields:
meeting_date date
meeting_place varchar2(200)
meeting_attendees varchar2(2000)
In such a case of searching, without the usage of wildcard characters such as % will not yeild appropriate results.
The query for such a situation would be:
SELECT meeting_date, meeting_place
FROM meeting_schedule
WHERE meeting_attendees like '%Rose%';
Now the above query will list the meeting_date and meeting_place where Rose has attended. Of course this is a simple usage of LIKE clause.
The Like clause is valid to be used in any DML statement such as SELECT, INSERT, UPDATE and DELETE.
Now what is that links LIKE clause so much with the indexes in Oracle?
Indexes as you may know are like indexes in the book where it points to a page in the book. The same way Oracle indexes point to a data location. Both Indexes and Tables contain two different copies of data, but Indexes contain far less data compared to the tables. Think of Indexes as containing distinct values of a row in a table. So if there are more and more repetitive rows in a row, then accessing Index will prove to be lot faster than accessing the table.
So by this you must have understood where an Index would help. An Index should be used only in such cases, so that it can improve performance. If otherwise used in all places it will degrade the performance.
How can an Index degrade the performance?
By using an index side-by-side of a table, we are agreeing that it has some extra work to do. Like at every insert, update or delete to the table, the index also needs to be updated. This extra work can prove fatal if there are lot of rows in the index.
So as a simple way to know where to use index, if each row of a table is different from that of other row and there are no distinct values do not use indexes. Only use indexes if atleast there are 25% distinct rows available in the table.
Of course there is no such hard core rule as to when to use Index and when not to use. It just depends upon the situation.
LONG to BLOB Migration
In release 8.1, a new SQL function, TO_LOB, copies data from a LONG column in a table to a LOB column. The datatype of the LONG and LOB must correspond for a successful copy. For example, LONG RAW data must be copied to BLOB data, and LONG data must be copied to CLOB data. In the next example we show how to migrate a table with one LONG to a CLOB datatype.
Create the LOB Tablespace
CREATE TABLESPACE lob1
DATAFILE '/lh4/lob1.dbf' SIZE 2048064K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
PERMANENT
ONLINE;
Disable temporarily all Foreign Keys
set feed off;
spool gen_dis_cons.sql;
SELECT 'ALTER TABLE ' table_name
' DISABLE CONSTRAINT ' constraint_name ';'
FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%'
/
spool off;
set feed on;
@gen_dis_cons.sql;
Convert LONG to LOB in temporary Table
Create a temporary table with converted BLOB field.
CREATE TABLE lob_tmp
TABLESPACE tab
AS SELECT id, TO_LOB(bdata) bdata FROM document;
Drop and Rename Tables
DROP TABLE document;
RENAME lob_tmp TO document;
Create the necessary Constraints and enable the Foreign Keys again
set feed off;
set heading off;
spool gen_ena_cons.sql;
SELECT 'ALTER TABLE ' table_name
' ENABLE CONSTRAINT ' constraint_name ';'
FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%'
/
spool off;
set feed on;
@gen_ena_cons.sql;
Courtesy: akadia
Create the LOB Tablespace
CREATE TABLESPACE lob1
DATAFILE '/lh4/lob1.dbf' SIZE 2048064K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
PERMANENT
ONLINE;
Disable temporarily all Foreign Keys
set feed off;
spool gen_dis_cons.sql;
SELECT 'ALTER TABLE ' table_name
' DISABLE CONSTRAINT ' constraint_name ';'
FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%'
/
spool off;
set feed on;
@gen_dis_cons.sql;
Convert LONG to LOB in temporary Table
Create a temporary table with converted BLOB field.
CREATE TABLE lob_tmp
TABLESPACE tab
AS SELECT id, TO_LOB(bdata) bdata FROM document;
Drop and Rename Tables
DROP TABLE document;
RENAME lob_tmp TO document;
Create the necessary Constraints and enable the Foreign Keys again
set feed off;
set heading off;
spool gen_ena_cons.sql;
SELECT 'ALTER TABLE ' table_name
' ENABLE CONSTRAINT ' constraint_name ';'
FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%'
/
spool off;
set feed on;
@gen_ena_cons.sql;
Courtesy: akadia
Switch to another Oracle User and back
Sometimes, the DBA needs to log into a user's account to make a change, such as to grant a privilege on a user's table to another user, but, may not know what the user's password is, or, may need to make changes to a set of users from a script, but, doesn't want to include their passwords in the script itself. Oracle provides an undocumented "identified by values" clause in the "alter user" command that can be used for this purpose.
Since the dba_users table contains the encoded password for each user, this value can be used to generate an "alter user" command to reset the user's password back to its original value. Then, from user system or another DBA user, you can alter the user's password to a known value (such as "whatever"), log into the user's account using "connect userid/whatever", make the changes that are needed, connect back to the system account, and run the generated "alter user" command to put the original password back.
The following SQL generates a password change script (setpw.sql) to set all users to a known password ("whatever"), and, another script (resetpw.sql) to set all users back to their original passwords. This would be used as part of another script to generate the password change scripts, run setpw.sql, log in and make the changes that are needed, and immediately run resetpw.sql to put the passwords back. Note that the users won't be able to log in during that time, since their passwords are not valid while you are running the script.
spool setpw.sql
select 'alter user ' || username || ' identified by whatever;' from dba_users;
spool off
spool resetpw.sql
select 'alter user ' || username || ' identified by values ''' || password || ''';' from dba_users;
spool off
Generated Script setpw.sql
alter user SYS identified by whatever;
alter user SYSTEM identified by whatever;
alter user DES identified by whatever;
alter user ELAN identified by whatever;
Generated Script resetpw.sql
alter user SYS identified by values '5638228DAF52805F';
alter user SYSTEM identified by values 'D4DF7931AB130E37';
alter user DES identified by values 'ABFEC5AC2274E54D';
alter user ELAN identified by values 'B53CE8493EC6FB92';
Source & Credits: www.akadia.com
How to switch Oracle in Single User-Multi Session Mode
During a migration it may be important, that nobody can connect to the database. You may have two possibilities to achieve this
On Table Level using LOCK TABLE
On Database Level using RESTRICTED SESSION
Exclusive Table Locks (X)
An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows:
$ sqlplus scott/tiger
SQL> GRANT ALL ON emp TO PUBLIC;
SQL> LOCK TABLE emo IN EXCLUSIVE MODE;
$ sqlplus test/test
SQL> SELECT * FROM scott.emp; (This works)
SQL> DELETE FROM scott.emp WHERE empno = 7369; (Waiting ...)
Permitted Operations
Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table.
Prohibited Operations
An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of lock on the table.
Lock Duration
Oracle releases all locks acquired by the statements within a transaction when you either commit or roll back the transaction. Oracle also releases locks acquired after a savepoint when rolling back to the savepoint. However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions will continue to wait until after the original transaction commits or rolls back completely.
Oracle in Single User Mode
You may use the RESTRICTED SESSION system privilege to switch the database in single user mode for migrations.
RESTRICTED SESSION - Specifies whether logon to Oracle is restricted
ENABLE - Allows only users with RESTRICTED SESSION system privilege to log on to Oracle. Existing sessions are not terminated.
DISABLE - Reverses the effect of the ENABLE RESTRICTED SESSION clause, allowing all users with CREATE SESSION system privilege to log on to Oracle. This is the default.
You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.
In a first step (before the migration), you shutdown the database an start it again to be sure that all users are disconnected. Now revoke the RESTRICTED SESSION system privilege from most users, this system privilege is normally not used.
$ svrmgrl
svrmgr> CONNECT SYS AS sysdba;
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
svrmgr> SPOOL revoke_restricted_session;
svrmgr> SELECT 'REVOKE restricted session FROM '
username ';' FROM dba_users
WHERE username NOT IN ('SYS','SYSTEM');
svrmgr> SPOOL OFF;
svrmgr> @revoke_restricted_session.log;
svrmgr> ALTER SYSTEM ENABLE RESTRICTED SESSION;
As user SYS you can now perform the migration. If an ordinary user tries to connect, he will get the following error messge:
sqlplus scott/tiger
ERROR: ORA-01035: ORACLE only available to users
with RESTRICTED SESSION privilege
After the migration to not to forget, to disable the RESTRICTED SESSION system privilege
svrmgr> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Source: www dot akadia dot com
On Table Level using LOCK TABLE
On Database Level using RESTRICTED SESSION
Exclusive Table Locks (X)
An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows:
$ sqlplus scott/tiger
SQL> GRANT ALL ON emp TO PUBLIC;
SQL> LOCK TABLE emo IN EXCLUSIVE MODE;
$ sqlplus test/test
SQL> SELECT * FROM scott.emp; (This works)
SQL> DELETE FROM scott.emp WHERE empno = 7369; (Waiting ...)
Permitted Operations
Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table.
Prohibited Operations
An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of lock on the table.
Lock Duration
Oracle releases all locks acquired by the statements within a transaction when you either commit or roll back the transaction. Oracle also releases locks acquired after a savepoint when rolling back to the savepoint. However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions will continue to wait until after the original transaction commits or rolls back completely.
Oracle in Single User Mode
You may use the RESTRICTED SESSION system privilege to switch the database in single user mode for migrations.
RESTRICTED SESSION - Specifies whether logon to Oracle is restricted
ENABLE - Allows only users with RESTRICTED SESSION system privilege to log on to Oracle. Existing sessions are not terminated.
DISABLE - Reverses the effect of the ENABLE RESTRICTED SESSION clause, allowing all users with CREATE SESSION system privilege to log on to Oracle. This is the default.
You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.
In a first step (before the migration), you shutdown the database an start it again to be sure that all users are disconnected. Now revoke the RESTRICTED SESSION system privilege from most users, this system privilege is normally not used.
$ svrmgrl
svrmgr> CONNECT SYS AS sysdba;
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
svrmgr> SPOOL revoke_restricted_session;
svrmgr> SELECT 'REVOKE restricted session FROM '
username ';' FROM dba_users
WHERE username NOT IN ('SYS','SYSTEM');
svrmgr> SPOOL OFF;
svrmgr> @revoke_restricted_session.log;
svrmgr> ALTER SYSTEM ENABLE RESTRICTED SESSION;
As user SYS you can now perform the migration. If an ordinary user tries to connect, he will get the following error messge:
sqlplus scott/tiger
ERROR: ORA-01035: ORACLE only available to users
with RESTRICTED SESSION privilege
After the migration to not to forget, to disable the RESTRICTED SESSION system privilege
svrmgr> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Source: www dot akadia dot com
Automatically Calculating Percentages in Queries
Starting with Release 7.1 of Oracle, users have had access to a feature called an inline view. An inline view is a view within a query. Using this feature, you can easily accomplish your task.
Example: Show percentage of salaries for each department
Every row in the report must have access to the total sum of sal. You can simply divide sum (sal) by that total, and you'll have a number that represents the percentage of the total.
column percentage format 99.9
select deptno, sum(sal),sum(sal)/tot_sal*100 "PERCENTAGE"
from emp, (select sum(sal) tot_sal from emp)
group by deptno, tot_sal;
With Oracle8i Release 2 (8.1.6 and higher), you can calculate percentages by using the new analytic functions as well. The query using an analytic function might look like this:
column percentage format 99.9
select deptno, sum(sal), (ratio_to_report(sum(sal)) over())*100 "PERCENTAGE"
from emp
group by deptno;
The query produces the same answer—but it does so more efficiently, because it does not have to make two passes over the data to arrive at the answer. Because the analytic functions are built-in, queries that use them will find the answer more rapidly than the "pure" SQL-based approach.
Source: www dot akadia dot com
Example: Show percentage of salaries for each department
Every row in the report must have access to the total sum of sal. You can simply divide sum (sal) by that total, and you'll have a number that represents the percentage of the total.
column percentage format 99.9
select deptno, sum(sal),sum(sal)/tot_sal*100 "PERCENTAGE"
from emp, (select sum(sal) tot_sal from emp)
group by deptno, tot_sal;
With Oracle8i Release 2 (8.1.6 and higher), you can calculate percentages by using the new analytic functions as well. The query using an analytic function might look like this:
column percentage format 99.9
select deptno, sum(sal), (ratio_to_report(sum(sal)) over())*100 "PERCENTAGE"
from emp
group by deptno;
The query produces the same answer—but it does so more efficiently, because it does not have to make two passes over the data to arrive at the answer. Because the analytic functions are built-in, queries that use them will find the answer more rapidly than the "pure" SQL-based approach.
Source: www dot akadia dot com
Read a file word by word using DBMS_LOB
Oracle offers several possibilities to process file from within PL/SQL. The most used package is UTL_FILE, but with the disadvantage that the read-buffer is limited to 1023 bytes. If you want to read huge chunks of files you can use the DBMS_LOB package, even for the processing of plain ASCII files.
There are two solutions to read a file with DBMS_LOB
-
The file is treaded as a large binary object LOB. The whole file is read and saved in a table column of the data type LOB and then processed.
-
The file is read and processed directly from the filesystem location. This Tip shows exactly this case.
Example:
Suppose we want to read a big file word by word directly from PL/SQL without saving the whole file in a table column. The words in the file are separated with a blank. For simplicity we assume, that there is exactly one blank between the words and the file is a stream with a newline at the end of the file.
First we have to create an ORACLE directory as the schema owner. Do not add a trailing "/" at the end of the directory path.
sqlplus scott/tiger
SQL> create directory READ_LOB_DIR as 'C:\Users\Zahn\Work';
Next we create the procedure READ_FILE_LOB, which reads the file word by word.
CREATE OR REPLACE
Procedure READ_FILE_LOB IS
----------------------------------------------------------------
-- Read an ASCII file word by word with DBMS_LOB package.
--
-- Before you can use this procedure create an ORACLE directory
-- object as the owner of this peocedure.
--
-- sqlplus scott/tiger
-- SQL> create directory READ_LOB_DIR as 'C:\Users\Zahn\Work';
--
-- Do not add a trailing "/" at the end of the directory path.
--
----------------------------------------------------------------
-- Input Directory as specified in create directory
l_dir CONSTANT VARCHAR2(30) := 'READ_LOB_DIR';
-- Input File which is read word by word
l_fil CONSTANT VARCHAR2(30) := 'testfile.txt';
-- Separator Character between words is a BLANK (ascii = 32)
l_seb CONSTANT RAW(100) := UTL_RAW.CAST_TO_RAW(CHR(32));
-- Character at the end of the file is NEWLINE (ascii = 10)
l_sen CONSTANT RAW(100) := UTL_RAW.CAST_TO_RAW(CHR(10));
-- Pointer to the BFILE
l_loc BFILE;
-- Current position in the file (file begins at position 1)
l_pos NUMBER := 1;
-- Amount of characters have been read
l_sum BINARY_INTEGER := 0;
-- Read Buffer
l_buf VARCHAR2(500);
-- End of the current word which will be read
l_end NUMBER;
-- Return value
l_ret BOOLEAN := FALSE;
BEGIN
-- Mapping the physical file with the pointer to the BFILE
l_loc := BFILENAME(l_dir,l_fil);
-- Check if the file exists
l_ret := DBMS_LOB.FILEEXISTS(l_loc) = 1;
IF (l_ret) THEN
dbms_output.put_line('File ' ||
l_fil || ' in Directory ' || l_dir || ' exists');
-- Open the file in READ_ONLY mode
DBMS_LOB.OPEN(l_loc,DBMS_LOB.LOB_READONLY);
LOOP
-- Calculate the end of the current word
l_end := DBMS_LOB.INSTR(l_loc,l_seb,l_pos,1);
-- Process end-of-file
IF (l_end = 0) THEN
l_end := DBMS_LOB.INSTR(l_loc,l_sen,l_pos,1);
l_sum := l_end - l_pos - 1;
DBMS_LOB.READ(l_loc,l_sum,l_pos,l_buf);
dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf));
EXIT;
END IF;
-- Read until end-of-file
l_sum := l_end - l_pos;
DBMS_LOB.READ(l_loc,l_sum,l_pos,l_buf);
dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf));
l_pos := l_pos + l_sum + 1;
END LOOP;
DBMS_LOB.CLOSE(l_loc);
ELSE
dbms_output.put_line('File ' ||
l_fil || ' in Directory ' || l_dir || ' does not exist');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error:' || SQLERRM);
DBMS_LOB.CLOSE(l_loc);
END;
/
The file testfile.txt has the following content
martin zahn seftigen
Output of the procedure
sqlplus scott/tiger
SQL> exec read_file_lob;
File testfile.txt in Directory READ_LOB_DIR exists
martin
zahn
seftigen
PL/SQL procedure successfully completed.
Article Source: akadia dot com