What is Public Synonym?

The web gives me a definition of Synonym as "A word that means the same as another word." Well Oracle also defines its Synonyms in the same way. Then what is a Public Synonym? It is nothing more than a Synonym accessible to all users of a Oracle database.

How to create a Public Synonym?
The syntax for creating a public synonym is

CREATE [ OR REPLACE ] PUBLIC SYNONYM synonym_name FOR object;

Example:

CREATE PUBLIC SYNONYM clerk FOR employee;

In our context employee is a table (or can be a view). As Clerk is also an employee, it makes understanding more easy.

You can query from clerk as you would do from employee table/view. Now you must have got the idea of why a synonym is used. 

Note: The usage of a public synonym from private synonym will be where you need to expose your not-so critical data to all database users. If your data is considered private, you should not be creating public synonyms for this purpose.

How to destroy a Public Synonym?

To destroy or drop a public synonym follow the syntax:

DROP PUBLIC SYNONYM synonym_name;

Example:

DROP PUBLIC SYNONYM clerk;

How to kill a process within windows BATch file

This article is result of a friend's query related to "How to kill a windows/DOS process within a windows Batch file.

This is the solution in brief:

START "do something window" dir
FOR /F "tokens=2" %I in ('TASKLIST /NH /FI "WINDOWTITLE eq do something window"' ) DO SET PID=%I
ECHO %PID%
TASKKILL /PID %PID%

FOR Loop is only required if there are multiple processes.

A link is what I am going to provide as for more information:

Click here to access the solution

This is an existing Oracle Support note.  This is note #18089.1

 

1) Introduction

~~~~~~~~~~~~~~~

This article describes the database DBA 'CONNECT INTERNAL' functionality.

 

In Oracle7 there are 3 special classes of user with privilege to perform special operations (such as shutdown and startup) on the database:

        - A DBA user (SYSDBA)

        - An OPERATOR user (SYSOPER)

        - Connect INTERNAL privilege

This article discusses 'CONNECT INTERNAL' and the issues that determine if a user has permission to connect internal or not. The details here apply to releases 7.0.16 through to 8.1. If using Oracle 7.1.6 or higher Oracle strongly advises you use the SYSDBA or SYSOPER privileges instead as 'CONNECT INTERNAL' may be removed after Oracle 8.1 .

 

For details of SYSDBA and SYSOPER see <Note: 50507.1>

 

2) Local Connections

~~~~~~~~~~~~~~~~~~~~

To make local 'internal' connections to an Oracle instance a user must either:

 

a) Supply a password OR b) Be a user belonging to a special UNIX 'dba' group.

 

The first of these follows the same rules as defined in Section (3) below so is not discussed in this section. Here we concentrate on the rules for option (b).

 

2.1) DBA Group

~~~~~~~~~~~~~~

The 'DBA' group is chosen at installation time and is usually the group 'dba' by default.

 

The DBA group is compiled into the 'oracle' executable and so it the same for all databases running from a given ORACLE_HOME directory. The actual group being used as this

DBA group can be checked thus:

 

cd $ORACLE_HOME/rdbms/lib

cat config.[cs]

 

The line '#define SS_DBA_GRP "group"' should name the chosen DBA group. If you wish to change the DBA group change the group name shown in this file.

 

Eg: Change: #define SS_DBA_GRP "dba"

to:     #define SS_DBA_GRP "mygroup"

 

To effect any changes to the DBA group and to be sure you are using the group defined in this file relink the Oracle executable as below. Be sure to shutdown all databases

before relinking:

 

Oracle Version < 7.3         

~~~~~~~~~~~~~~~~~~~~         

rm config.o                              

make -f oracle.mk config.o  

make -f oracle.mk ioracle   

 

Oracle Version >= 7.3

~~~~~~~~~~~~~~~~~~~~~

rm config.o

make -f ins_rdbms.mk config.o

make -f ins_rdbms.mk ioracle

 

(Note the above instructions may vary depending on your platform / release)

 

For a DBA group to be accepted by Oracle it must:

 

- Be compiled into the Oracle executable

- The group name must exist in /etc/group (or in 'ypcat group' if NIS is being used)

- It CANNOT be the group called 'daemon'

 

2..2) Local Connect Internal

~~~~~~~~~~~~~~~~~~~~

On UNIX systems a user may be a member of more than one group and should be allowed to connect internal without a password provided:

 

- One of the groups of which they are a member is the DBA group as defined in config.c (config.s on some platforms) and as linked into the 'oracle' executable.

- The DBA group is a valid group as defined in /etc/group (Or as defined in NIS by 'ypcat group')

- The users PRIMARY group (I.e.: the one shown by the 'id' command) is not the

special group 'daemon'.

 

It is quite common for the 'root' user to be required to have 'connect internal' privilege. Unfortunately it is also common for the root users primary group to be the group 'daemon' which precludes it from being allowed to connect internal. There are two ways to tackle this problem:

 

a) Make the root users PRIMARY group the DBA group

or b) Where available use the 'newgrp' command to change the users primary group to the DBA group.

 

Eg:  $ newgrp dbagroup

$ sqldba mode=line

SQLDBA> connect internal

 

This can also be used in shellscripts thus:

:

newgrp dbagroup <<!

# Commands requiring connect internal privilege

# Eg: dbstart

!

 

or c) For systems where 'newgrp' is not available or does not work from scripts you can use 'su' instead. Eg:

:               

su - oracle <<!

# Commands requiring connect internal privilege

!

 

Note: The user you 'su' to should be able to 'connect internal' without a password, for example by having their primary group as DBA.

 

Some Oracle releases have problems with identifying the DBA group when it is not the user’s primary group. If you encounter problems with connect internal and the DBA group is set correctly try making the users primary group the DBA group, or use 'newgrp' as in (b) above.

 

3) Remote Connect Internal

~~~~~~~~~~~~~~~~~~~~~~

Remote connect internal requires the database to be configured to allow remote DBA operations. The remote user will HAVE to supply a password in order to connect internal. This is a tightening in security from Oracle 6 where remote users could connect internal without a password.

 

I.e.: In Oracle7 to perform a remote connect internal you must use the syntax 'CONNECT INTERNAL/PASSWORD'

 

To allow remote internal connections you must:

- Set up a password file for the database on the server

- Set up any relevant init.ora parameters

- Set up any SQL*Net options required

 

3.1) Setting up a Password File

~~~~~~~~~~~~~~~~~~~~~~~

The connect internal password protection is controlled by an Oracle 'Password' file. The exact commands used to set up this file are different for different Oracle releases but the basic concept is that a special file is created to hold the 'connect internal' password.

 

Oracle  Init.Ora Parameters        Command

~~~~~~  ~~~~~~~~~~~~~~~~~~~        ~~~~~~~

7.1.x   remote_login_passwordfile  orapwd file=<fname> password=<pwd>

7.0.x                              orapasswd

   

7.1.x

~~~~~

To create a password file log in as the Oracle software owner and issue the command:

 

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd

 

using the required password. The file name is important and should be specified as above. You should create this file when the database is shut down.

 

To change a password:

- shut down the database,

- Rename the $ORACLE_HOME/dbs/orapw$ORACLE_SID file

- Issue a new orapwd command with a new password

 

7.0.x

~~~~~

To create a password file log in as the Oracle software owner and issue the command:

 

orapasswd

 

This will prompt for the ORACLE_HOME, the ORACLE_SID and the 'connect internal' password. Enter the required values.

To change a password re-run the 'orapasswd' tool.

 

3.2) Setting up the Init.Ora file

~~~~~~~~~~~~~~~~~~~~~~

To enable remote internal connections set the init.ora parameters thus:

 

7.1.x

~~~~~

Set REMOTE_LOGIN_PASSWORDFILE to either EXCLUSIVE or SHARED. EXCLUSIVE forces the password file to be tied exclusively to a single instance.  To disable remote internal connections set REMOTE_LOGIN_PASSWORDFILE to NONE.

 

7.0.x

~~~~~

No init.ora changes are required for 7.0.x releases.

 

Note: The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect internal from a remote machine.              

 

3.3) SQL*Net Options

~~~~~~~~~~~~~~~~

From 7.0.16 onwards there are no SQL*Net settings that control the ability to connect internal from remote machines. In particular the settings shown below are no longer required:

 

SQL*Net V1:     The dbaon / dbaoff options are irrelevant

 

SQL*Net V2:     The REMOTE_DBA_OPS_ALLOWED / REMOTE_DBA_OPS_DENIED parameters are irrelevant

 

4) Bugs and Special Notes

~~~~~~~~~~~~~~~~~~~~~~~~~

Common Errors

~~~~~~~~~~~

ORA-09910: Unable to find ORACLE password file entry for user.

On 7.0.x the $ORACLE_HOME/dbs/orapasswd file cannot be accessed.

       

ORA-01031: insufficient privileges 

Connect Internal has been issued with no password.

For local connections the user is NOT in the DBA group as compiled into the 'oracle' executable.

For remote connections you must always supply a password.

 

This error can also occur after a successful connect internal/password if there REMOTE_LOGIN_PASSWORDFILE is either unset or set to NONE in the init.ora file.

     

ORA-01017: invalid username/password; logon denied

This is a fairly general error that indicates one of the following:

- REMOTE_LOGIN_PASSWORDFILE is set to NONE

- The password file does not exist

- The password supplied does not match the one in the password file

- The password file been changed since the instance was started

 

7.1.x - Deleting/Changing the 'orapw$SID' File                <Bug:262100>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If you delete the Oracle password file while the instance is running you will NOT be able to connect internal from remote machines, even if you re-create the file. You must:

- Shutdown the instance (using a local connection)

- Create the new password file

- You can now connect internal remotely and restart the instance

 

7.1.3 - REMOTE_LOGIN_PASSWORDFILE ignored                     <Bug:209449>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If a password file exists at all then you can perform remote CONNECT INTERNAL operations even if the value of REMOTE_LOGIN_PASSWORDFILE is set to NONE (the default).

 

The workaround is to delete the password file to prevent remote internal connections.

 

7.1,7.2 - Security Loophole                    <Bug:321727>

~~~~~~~~~~~~~~~~~~~~~~~~~~~

Be aware that there is a potential security loophole in the orapwd implementation in Oracle 7.1 and 7.2. Details of this are NOT disclosed so as not to compromise existing users of the option. The fix is in Oracle 7.3.2.

 

What is Oracle Application Express?

Oracle Application Express or APEX as it is famously known is a rapid application development for the web closely coupled with Oracle's flagship product, its database. It was earlier known as HTML DB.

It's key features are:
Reporting
Forms
Charting
PDF Printing
Web Services
Spreadsheet Upload
Session State Management
User Interface Themes
Flow Control & Navigation
Conditionality on all Components
External Interfaces & Extensibility
Declarative BLOB Support
Optional Runtime-Only Installation
Security
SQL Workshop Tools
Supporting Objects Utility
Performance
Packaged Applications
Hosted Development


For more information read through What is Apex

Converting Forms using APEX-By David Peake

Modernize Oracle Forms applications with Oracle Application Express.

Oracle Forms has been around since before the days of client/server computing, and numerous Oracle Forms applications have been faithfully meeting business requirements for many years.

Converting Oracle Forms applications to use another development tool is a labor-intensive, time-consuming process, and recent rumors that Oracle will soon drop support for Oracle Forms are unfounded. With these things in mind, why would anyone want to convert their Oracle Forms applications to use another development tool?

Typically, Oracle Forms modernization projects convert legacy applications to use the latest Oracle Database version and enable developers to satisfy user demands for greater user interactivity and Web 2.0 capabilities. A modernization option for Oracle Forms applications that will deliver on both of these objectives is Oracle Application Express.

Oracle Application Express renders applications using HTML, and it has built-in Web 2.0 functionality such as interactive reports, Flash charts, and the ability to extend user interactivity by use of JavaScript and Asynchronous JavaScript and XML (Ajax). In addition, the rapid application development environment offered by Oracle Application Express is similar to that found in Oracle Forms, utilizing a declarative framework together with SQL and PL/SQL to define processes and enter business logic.

Understanding the Conversion Process

The capabilities for converting from Oracle Forms to Oracle Application Express in Oracle Application Express 3.2 generate an initial application design that you then enhance and expand to fully replicate the original application. The conversion maps blocks to pages and various item types, but it does not convert business logic stored in Oracle Forms triggers, program units, or libraries. After you generate the initial design, you can take full advantage of the rapid application development capabilities in Oracle Application Express to enhance the generated pages. Figure 1 describes the process flow of an application migration from Oracle Forms to Oracle Application Express.

.

.

Read the full article here:Oracle Magazine

Conclusion

Oracle Forms conversion in Oracle Application Express is not designed to fully replicate an Oracle Forms application. The conversion process does, however, offer significant benefit by generating user interface components and providing project management tracking capabilities.

The effort and resources required for each Oracle Forms conversion project are largely dependent on the size, complexity, and business logic implementation within the Oracle Forms application. I strongly recommend that you undertake a prototype or a proof of concept to properly plan each Oracle Forms conversion project before you begin.

---

David Peake is a principal product manager in Oracle's Server Technologies division. He has been with Oracle since 1993.

BANGALORE: Wipro Technologies said on Monday that its Business Process Outsourcing division, Wipro BPO has partnered with Oracle for 'best-of-breed HR platform solutions.'

Wipro has also selected The Hackett Group, a global strategic advisory firm, to provide empirical data, best practices and world-class performance insights on the development of its innovative bundled solution platform, it said in a release here.

The solution simPlify, allows employers to reduce and control cost as it provides an opportunity to centralize and standardize processes while eliminating duplicative management structures.

The solution has the ability to leverage a many-to-one technology capability, while maximizing service quality and HR customer satisfaction.

 

From News: Economic Times

Oracle buys Sun

Oracle announced today they have entered into a definitive agreement under which Oracle will acquire Sun at $9.50 a share in cash. The deal has been unanimously approved by Sun's board, but still needs to be approved by Sun's shareholders and financial regulators, the companies said.

"Oracle will be the only company that can engineer an integrated system — applications to disk — where all the pieces fit and work together so customers do not have to do it themselves", said Larry Ellison.

The deal means Oracle will be taking over the Java language and software, which underpins Oracle Fusion Middleware. Also Oracle picks up the Solaris operating system, described by the companies as the leading platform for Oracle's database products. However Oracle is as committed as ever to Linux and other open platforms, the company said.

QUERY parameter in Export Utility

This parameter is used in conjunction with TABLE parameter of exp (export) utility of Oracle. This parameter will enable selection of rows from the list of tables mentioned in TABLE parameter. The value to this parameter is a WHERE clause for a SELECT statement which you would normally issue.

For example if you want to query all records of employees for a particular department you will use:

SELECT *
FROM employees
WHERE dept = 10;

To export these rows into a file using exp utility you will follow the below syntax:

exp scott/tiger TABLES=employees QUERY=\"WHERE dept=10\"

Use \ for providing character or special characters like less than or greater than symbol inside the string. Also for operating system keywords you need to place \ as escape character.

For example:

exp scott/tiger TABLES=employees QUERY=\"WHERE name=\ANANTHA\' and sal \<1600\" The default value for QUERY parameter is NULL. Note: If there are multiple tables in TABLES parameter, this QUERY will be applied to all those tables.

You can also use ROWID for exporting, for example:

exp scott/tiger@slspnc1 tables=emp query=\"where ROWID='AAAMgzAAEAAAAAgAAB'\" file=test.dmp log=test.log

In Windows it is not required to put escape character for single quote character wheras you need to put escape character if you are using Linux or its variants.

exp scott/tiger@slspnc1 tables=emp query=\"where ROWID=\'AAAMgzAAEAAAAAgAAB\'\" file=test.dmp log=test.log

Restrictions When Using the QUERY Parameter:
The QUERY parameter cannot be specified for full, user, or tablespace-mode exports.
The QUERY parameter must be applicable to all specified tables.
The QUERY parameter cannot be specified in a direct path Export (DIRECT=y)
The QUERY parameter cannot be specified for tables with inner nested tables.
You cannot determine from the contents of the export file whether the data is the result of a QUERY export.

ORA-01658: Unable to create INITIAL extent for segment in tablespace %s

** Solved **

You will encounter this error when a tablespace is full or cannot extend automatically.

These are the possible solutions (if there are any other ways add a comment):

Solution 1: Resize Tablespace

alter database datafile '' resize M;

Solution 2: Add Datafile

alter tablespace tablespace_name add datafile '' size M autoextend M maxsize M|off>;

How to Pin objects to shared Pool?

How to Pin objects to shared Pool?
Pinning objects to the shared pool is a key to tuning your shared pool. Having objects pinned will reduce fragmentation and changes of encountering the ORA-04031 error.

You must determine which objects to pin. These are particular to your own database, the application you are running, the size of your database, and the activity on your database.

You need to pin objects when any of these happen:
1. If you have encountered the ORA-04031 already and need to resolve it or
2. You can also pin large packages frequently used by the users. (It is better to pin necessary objects while startup of database.

Pinning a package to shared pool

1. Oracle automatically loads SYS.STANDARD, SYS.DBMS_STANDARD and SYS.DIUTIL.  Here is an example: 

 

pk1 is a package with a variable called dummy.  Assigning dummy to a value and then executing the package will load it into the shared pool: 

Example:-                                                                   

begin  

pk1.dummy := 0 ; /* THIS ASSIGNMENT TO THE DUMMY VARIABLE IS BY */  

end;               /*    EXECUTING THE PACKAGE. */   

                                                                            

2.Then you must pin the package. Here is an example: 

execute dbms_shared_pool.keep(owner.pk1);

Pinning stored procedure/function to shared pool

You can pin procedures and triggers with the dbms_shared_pool procedure. Either procedures or packages can be pinned with the 'P' flag, which is the default value (so you can leave it out). Triggers are pinned with 'R' and anonymous plsql blocks need any letter other than [p,P,r,R] as a flag.  Refer to dbmspool.sql for more documentation. 

Here is an example: 

execute dbms_shared_pool.keep(owner.trigger, 'R');

What objects should I pin?

You can check the x$ksmlru fixed table.  This table keeps track of the objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm. 

             

Because this is a fixed table, once you query the table, Oracle will automatically reset the table, thus, you can only query the table once. Suggestion for workaround: spool the output to a file so you can capture the output for analysis. 

       

describe x$ksmlru        

    

Table or View x$ksmlru          

Name                            Null?    Type             

------------------------------- -------- --------------            ADDR                                     RAW(4) 

INDX                                     NUMBER             

KSMLRCOM                                 VARCHAR2(20)             

KSMLRSIZ                                 NUMBER                     

KSMLRNUM                                 NUMBER            

 

KSMLRNUM stores the number of objects that were flushed to load the large object. KSMLRISZ stores the size of the object that was loaded (contiguous memory allocated) 

We do not need the other columns. 

 

Here is an example of a query you issue to find all the objects that are larger than size 5k which you may want to pin:    

select * from x$ksmlru where ksmlrsiz > 5000;    

                      

In general, pinning SYS.STANDARD ,SYS.DBMS_STANDARD & SYS.DIUTIL which are large packages used by Oracle, should help.   

 

SYS.DIUTIL is used only during generations of SQL*forms so it may not be necessary to pin this package in your production database. 

Here is what Oracle documentation says about this error:

 

 

ORA-06502: PL/SQL: numeric or value error string

 

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER (2).

 

Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

 

 

More Information:

These could be the errors:

This error mainly occurs due to one of the following:
1. If you assign NULL value to a NOT NULL field.
2. If you assign number which is larger than the precision of the field.
3. If you assign character values greater than the field width.

Click here to read more on solving this error

 

ORA-04031: shared memory error

How to solve this error?

1. Find out which application is causing this error. Zero down on which package/procedure is loaded and try to keep it in shared pool by pinning it.
2. Sometimes, the application may not give the errors. In which case, set an event in init.ora, as follows and generate a trace file.

event = "4031 trace name errorstack level 2"

or with 9i and higher and spfiles you can issue

alter system set events='4031 trace name errorstack level 2';

What to look for in the trace?
The trace contains a dump of state objects, when the error occurs.

Look for 'load=X' a few lines below that 'name='[name of the object]. So, this error occurs at the time loading this object. Pin that object in the shared pool, thereby keeping it.

How to Pin objects to shared Pool?
Pinning objects to the shared pool is a key to tuning your shared pool. Having objects pinned will reduce fragmentation and changes of encountering the ORA-04031 error.

You must determine which objects to pin. These are particular to your own database, the application you are running, the size of your database, and the activity on your database.

You need to pin objects when any of these happen:
1. If you have encountered the ORA-04031 already and need to resolve it or
2. You can also pin large packages frequently used by the users. (It is better to pin necessary objects while startup of database.

Pinning a package to shared pool

1. Oracle automatically loads SYS.STANDARD, SYS.DBMS_STANDARD and SYS.DIUTIL.  Here is an example: 

 

pk1 is a package with a variable called dummy.  Assigning dummy to a value and then executing the package will load it into the shared pool: 

Example:-                                                                   

begin  

pk1.dummy := 0 ; /* THIS ASSIGNMENT TO THE DUMMY VARIABLE IS BY */  

end;               /*    EXECUTING THE PACKAGE. */   

                                                                            

2.Then you must pin the package. Here is an example: 

execute dbms_shared_pool.keep(owner.pk1);

Pinning stored procedure/function to shared pool

You can pin procedures and triggers with the dbms_shared_pool procedure. Either procedures or packages can be pinned with the 'P' flag, which is the default value (so you can leave it out). Triggers are pinned with 'R' and anonymous plsql blocks need any letter other than [p,P,r,R] as a flag.  Refer to dbmspool.sql for more documentation. 

Here is an example: 

execute dbms_shared_pool.keep(owner.trigger, 'R');

What objects should I pin?

You can check the x$ksmlru fixed table.  This table keeps track of the objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm. 

             

Because this is a fixed table, once you query the table, Oracle will automatically reset the table, thus, you can only query the table once. Suggestion for workaround: spool the output to a file so you can capture the output for analysis. 

       

describe x$ksmlru        

    

Table or View x$ksmlru          

Name                            Null?    Type             

------------------------------- -------- --------------            ADDR                                     RAW(4) 

INDX                                     NUMBER             

KSMLRCOM                                 VARCHAR2(20)             

KSMLRSIZ                                 NUMBER                     

KSMLRNUM                                 NUMBER            

 

KSMLRNUM stores the number of objects that were flushed to load the large object. KSMLRISZ stores the size of the object that was loaded (contiguous memory allocated) 

We do not need the other columns. 

 

Here is an example of a query you issue to find all the objects that are larger than size 5k which you may want to pin:    

select * from x$ksmlru where ksmlrsiz > 5000;    

                      

In general, pinning SYS.STANDARD ,SYS.DBMS_STANDARD & SYS.DIUTIL which are large packages used by Oracle, should help.   

 

SYS.DIUTIL is used only during generations of SQL*forms so it may not be necessary to pin this package in your production database. 

A Basic Tutorial on Oracle9i Forms and Reports

Covering Developer version 9i for Windows NT/2000/XP By Richard Holowczak

This tutorial introduces the Oracle9i Developer Suite Release 2 (August, 2002) that includes Oracle Forms 9.0 and Oracle Reports 9.0. The main objectives are to demonstrate and provide hands-on instructions on creating and modifying data entry and query forms in various configurations, reports and graphics.

Caveats: Please note that Oracle tends to change things like menu items, prompts and other small things between each major (certainly) and often minor release. Depending on the exact release of Oracle9i Developer Suite you have, you may find some small discrepencies between what is shown in this tutorial and what you see on your screen.

Prerequisites
Before following this tutorial, a student must have a valid user account in an Oracle server or a local installation of an Oracle database. Contact your DBA or systems administrator to learn the details of how Oracle server is set up in your organization.

If you have a local instance of the Oracle database, please do not use the SYSTEM (DBA) account to practice development. There is a good chance you can corrupt the entire database by mistake. You are much better off making use of the SCOTT/TIGER schema or (even better) creating a separate user (schema) for this tutorial work and for practicing.

One more word of caution. Oracle has designed Oracle9i as a three tier architecture. This means that the database (Oracle9i server) should run on one server, the application Server (oracle9i Application Server or Oracle9iAS Containers for Java (OC4J) server as used here) runs on a second server and the client application (written in Oracle Forms and Reports for example) runs on a third machine.

Contents

• 1. Introduction
• 2. Prerequisites
• 3. Developer Tools
  • 3.1 Starting Developer tools under Windows
• 4. The Oracle Forms Builder Main Screen
  • 4.1 Oracle Forms Object Navigator
  • 4.2 Connecting to the Database
• 5. Forms Design
• 6. Creating a Form with a Single Block
  • 6.1 Creating a New Block
  • 6.2 The Layout Editor
  • 6.3 Displaying Properties
  • 6.4 Saving, Generating and Running Forms
    • 6.4.1 Saving a Form
    • 6.4.2 Compiling/Building a Form
    • 6.4.3 Running a Form
    • 6.4.4 Query By Example
  • 6.5 Exercise: Creating a Single Block Form
• 7. Creating a Master-Detail Form
  • 7.1 The Master-Detail Relationship
  • 7.2 Steps to Create a Master-Detail Form
    • 7.2.1 Create the Master Block
    • 7.2.2 Create the Detail Block
  • 7.3 Relation Properties of a Master-Detail Form
  • 7.4 Program Units in a Master-Detail Form
  • 7.5 Extending Master-Detail Forms
  • 7.6 Exercise: Creating a Master/Detail Form
• 8. List of Values (LOVs) and Non-Base Table Fields
  • 8.1 Creating a List of Values
    • 8.1.1 Create a new LOV Object
    • 8.1.2 Specify the LOV Query Text
    • 8.1.3 Specify the Column Mapping Properties
    • 8.1.4 Attach the LOV to the item
    • 8.1.5 Test the LOV
  • 8.2 Non-Base Table fields
    • 8.2.1 Adding a New Item to an Existing Data Block
    • 8.2.2 Creating a Trigger
    • 8.2.3 Create a new List of Values
  • 8.3 Exercise: Creating an LOV and Non-Base Table field
• 9. Oracle Forms Program Units and Stored Program Units
  • 9.1 Creating Procedures in Oracle Forms
    • 9.1.1 Add a new item to the EMPLOYEE Block
    • 9.1.2 Create a Procedure to Count Other Department Members
    • 9.1.3 Create a Trigger to Call the Procedure
    • 9.1.4 Save, Compile and Run the Form
  • 9.2 Creating Stored Program Units (Procedures) in SQL*Plus
  • 9.3 Additional Stored Procedures and Triggers
    • 9.3.1 Generating unique or consecutive identifiers using the table
    • 9.3.2 Generating unique or consecutive identifiers using an Oracle Sequence
    • 9.3.3 Checking constraints at the client
    • 9.3.4 Adding Alerts (Pop-up Messages)
    • 9.3.5 Automatic Forms Query
    • 9.3.6 Populate items with default values
    • 9.3.7 Attaching functionality to command buttons
    • 9.3.8 Passing parameters between forms
  • 9.4 Exercise: Procedures and Triggers
• 10. Oracle Reports Basics
  • 10.1 Starting Developer tools under Windows
  • 10.2 The Oracle Reports Object Navigator
  • 10.3 Creating Reports in Oracle Reports
  • 10.4 Creating a Single-Table Report
    • 10.4.1 Specify the Data Model and Layout
    • 10.4.2 Saving, Generating and Running the Report
  • 10.5 Exercise: Creating a Report
  • 10.6 Creating a Master-Detail Report
    • 10.6.1 Specify the Master-Detail Data Model and Layout
    • 10.6.2 Saving and Running the Master-Detail Report
  • 10.7 Exercise: Creating a Master-Detail Report
  • 10.8 Parameter forms in Oracle Reports
    • 10.8.1 Building a Custom Parameter Form
• 11 Creating Menus in Oracle Forms
  • 11.1 The Oracle Forms Menu Hierarchy
  • 11.2 Creating a Custom Menu

---

About the Author: Richard Holowczak is an Associate Professor of Computer Information Systems at the Zicklin School of Business in Baruch College, City University of New York.

---

ORA-25153-Temporary Tablespace is Empty-Error solved

*** SOLVED ***

We have a linux server in our office, and yesterday the machine got restarted (power failure). We were able to bring up our Oracle database server afterwards but there was some problems with temporary tablespace.

We were running a procedure when we encountered this error:

ORA-25153: Temporary Tablespace is Empty

Once querying dba_temp_files view, there were no temp files being listed. Though the temporary file was residing in the oradata folder and the dba_tablespaces where the tablespace was present. 

The Solution:

As it was a temporary tablespace, I could add a tempfile to the same and made the procedure working:

ALTER TABLESPACE temp ADD tempfile '/fullpath.dbf' SIZE 50M;

The error ORA 25153 got solved.

NOTE: Do not give the same name as the existing file in oradata folder. Let the file REST IN PEACE, give a new name. For example if you have temp01.dbf existing create with temp02.dbf.

Installing Oracle silently

Oracle Universal Installer by default installs any oracle products in its GUI mode. In situations where the GUI could not be started, it is not possible to install Oracle, such as in Linux environments if X Server could not be started it is difficult to install any Oracle products.

Moreover for a DBA, it is very cumbersome to sit and click buttons for installation. For automating purposes, Oracle Universal Installer (OUI) provides a silent mode of installation.

The runInstaller script which is used for calling the OUI has some switches which can be used to achieve this functionality.

./runInstaller -record -destinationFile  

./runInstaller -silent -responseFile

Here is how:

First Step
./runInstaller -record -destinationFile ResponseFile.txt

The record parameter tells the installer to write to the response file and the destinationFile parameter defines the name of the response file. Once the response file is created you can run the installer in silent mode using the following command:

Second Step

./runInstaller -silent -responseFile ResponseFile.txt

The silent parameter indicates the OUI to install the product using the parameters found in the file passed throught responseFile parameter.

Once the installer stops for executing root.sh as root user, the installation will be completed.