Webutil

1. Purpose

The purpose of this article is to demonstrate how, with the Webutil library, we can upload local documents into the database, edit those stored documents on the client machine, save modified documents to the database

2. Upload local document to the database

Select a file on the client machine with the WEBUTIL_FILE.FILE_OPEN_DIALOG function

FUNCTION WEBUTIL_FILE.FILE_OPEN_DIALOG

(

directory_name,

file_name,

file_filter,

title

) return VARCHAR2;

• directory_name is the name of the starting directory (let empty to start at the root)
• file_name is the name of the searching file
• file_filter is the list of file types to display (e.g. '|All files|*.*|' or '|Word Files|*.doc|Excel Files|*.xls|')
• title is the title of the dialog box
• 
  Upload the selected document into the database with the Webutil_File_Transfer.Client_To_DB function

FUNCTION Webutil_File_Transfer.Client_To_DB

(

clientFile in VARCHAR2,

tableName in VARCHAR2,

columnName in VARCHAR2,

whereClause in VARCHAR2,

asynchronous in BOOLEAN default FALSE,

callbackTrigger in VARCHAR2 default NULL

) return BOOLEAN;

• clientFile is the full file name returned by the File_Open_Dialog() function
• tableName is the table that contains a BLOB column
• columnName is the BLOB column you want to store the file
• whereClause is the Where clause to identify a unique raw in the table
• asynchronous allows form to re-draw its screen before the end of the upload
• callbackTrigger is the name of a form-level trigger which will be called once the upload is completed.

Assume you want to store the c:\docs\doc.xls in the DOC column of the DOCUMENTS table where DOC_ID = 10, use the following instruction:

Declare

LB$Result BOOLEAN ;

Begin

LB$Result := Webutil_File_Transfer.Client_To_DB

(

‘c:\docs\doc.xls’,

‘DOCUMENTS’,

‘DOC’,

‘DOC_ID = 10’

) ;

End ;

GRANT Statement

Grant privileges to a user (or to a user role)

Syntax:

Grant System-wide Privs:

  GRANT system_priv(s) TO grantee
     [IDENTIFIED BY password] [WITH ADMIN OPTION]

  GRANT role TO grantee
     [IDENTIFIED BY password] [WITH ADMIN OPTION]

  GRANT ALL PRIVILEGES TO grantee
     [IDENTIFIED BY password] [WITH ADMIN OPTION]

Grant privs on specific objects:

  GRANT object_priv [(column, column,...)]
     ON [schema.]object
        TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]

  GRANT ALL PRIVILEGES [(column, column,...)]
     ON [schema.]object
        TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]

  GRANT object_priv [(column, column,...)]
     ON DIRECTORY directory_name
        TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]

  GRANT object_priv [(column, column,...)]
     ON JAVA [RE]SOURCE [schema.]object
        TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]

grantee:
  user
  role
  PUBLIC

system_privs:
  CREATE SESSION - Allows user to connect to the database
  UNLIMITED TABLESPACE  - Use an unlimited amount of any tablespace.
  SELECT ANY TABLE - Query tables, views, or mviews in any schema
  UPDATE ANY TABLE - Update rows in tables and views in any schema
  INSERT ANY TABLE - Insert rows into tables and views in any schema
  Also System Admin rights to CREATE, ALTER or DROP:
    cluster, context, database, link, dimension, directory, index,
    materialized view, operator, outline, procedure, profile, role,
    rollback segment, sequence, session, synonym, table, tablespace,
    trigger, type, user, view. (full list of system privs)

object_privs:
  SELECT, UPDATE, INSERT, DELETE, ALTER, DEBUG, EXECUTE, INDEX, REFERENCES
 
roles:
  SYSDBA, SYSOPER, OSDBA, OSOPER, EXP_FULL_DATABASE, IMP_FULL_DATABASE
  SELECT_CATALOG_ROLE,  EXECUTE_CATALOG_ROLE, DELETE_CATALOG_ROLE
  AQ_USER_ROLE, AQ_ADMINISTRATOR_ROLE - advanced queuing
  SNMPAGENT - Enterprise Manager/Intelligent Agent.
  RECOVERY_CATALOG_OWNER - rman
  HS_ADMIN_ROLE - heterogeneous services

  plus any user defined roles you have available

Notes:

Several Object_Privs can be assigned in a single GRANT statement
e.g.
GRANT SELECT (empno), UPDATE (sal) ON scott.emp TO emma

WITH HIERARCHY OPTION will grant the object privilege on all subobjects, including any created after the GRANT statement is issued.

WITH GRANT OPTION will enable the grantee to grant those object privileges to other users and roles.

"GRANT ALL PRIVILEGES..." may also be written as "GRANT ALL..."

SYSDBA role permissions:

CREATE DATABASE
CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up, or change character set
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege

SYSOPER role permissions:

CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege

The roles CONNECT, RESOURCE and DBA are now deprecated (supported only for backwards compatibility) unless you are still running Oracle 6.0 use CREATE ROLE instead. Using deprecated roles is likely to result in a database installation that is not secure. Also beware of 3rd party packages that issue GRANT CONNECT instead of GRANT CREATE SESSION.

• Create a Role
• Grant object and system priviliges to the role
• Grant the role to individual Users.

When using roles, parse performance is faster (one role vs multiple permissions)

Changes in a grant applied to a role will be immediately picked up by all logged-in users.
Changes in a grant applied directly to a user require a logoff and re-login.

Grants of UNLIMITED TABLESPACE must be direct.
Grants to users running background jobs (dbms_job.submit) have to be direct; when the job starts running no roles are active.

GRANT CREATE session, CREATE table, CREATE view,
     CREATE procedure,CREATE synonym,
     ALTER table, ALTER view, ALTER procedure,ALTER synonym,
     DROP table, DROP view, DROP procedure,DROP synonym,
     TO MyRole;

SQL Statements

The following is an alphabetical list of SQL statements that can be issued against an Oracle database. These commands are available to any user of the Oracle database. Emphasized items are most commonly used.

• ALTER - Change an existing table, view or index definition
• AUDIT - Track the changes made to a table
• COMMENT - Add a comment to a table or column in a table
• COMMIT - Make all recent changes permanent
• CREATE - Create new database objects such as tables or views
• DELETE - Delete rows from a database table
• DROP - Drop a database object such as a table, view or index
• GRANT - Allow another user to access database objects such as tables or views
• INSERT - Insert new data into a database table
• No AUDIT - Turn off the auditing function
• REVOKE - Disallow a user access to database objects such as tables and views
• ROLLBACK - Undo any recent changes to the database
• SELECT - Retrieve data from a database table
• UPDATE - Change the values of some data items in a database table

Some examples of SQL statements follow. For all examples in this tutorial, key words used by SQL and Oracle are given in all uppercase while user-specific information, such as table and column names, is given in lower case.

To create a new table to hold employee data, we use the CREATE TABLE statement:

     
  CREATE TABLE employee
  (fname           VARCHAR2(8),
   minit           VARCHAR2(2),
   lname           VARCHAR2(8),
   ssn             VARCHAR2(9) NOT NULL,
   bdate           DATE,
   address         VARCHAR2(27),
   sex             VARCHAR2(1),
   salary          NUMBER(7) NOT NULL,
   superssn        VARCHAR2(9),
   dno             NUMBER(1) NOT NULL) ;

To insert new data into the employee table, we use the INSERT statement:

INSERT INTO employee
VALUES ('BUD', 'T', 'WILLIAMS', '132451122',
      '24-JAN-54', '987 Western Way, Plano, TX',
      'M', 42000, NULL, 5);

To retrieve a list of all employees with salary greater than 30000 from the employees table, the following SQL statement might be issued (Note that all SQL statements end with a semicolon):

SELECT fname, lname, salary FROM employee WHERE salary > 30000;

To give each employee in department 5 a 4 percent raise, the following SQL statement might be issued:

         
         UPDATE employee
         SET    salary = salary * 1.04
         WHERE  dno = 5;

To delete an employee record from the database, the following SQL statement might be issued:

         
         DELETE FROM employee
         WHERE  empid = 101 ;

The above statements are just an example of some of the many SQL statements and variations that are used with relational database management systems.

The SQL Language

Structured Query Language (SQL) is the language used to manipulate relational databases. SQL is tied very closely with the relational model.

In the relational model, data is stored in structures called relations or tables. Each table has one or more attributes or columns that describe the table. In relational databases, the table is the fundamental building block of a database application. Tables are used to store data on Employees, Equipment, Materials, Warehouses, Purchase Orders, Customer Orders, etc. Columns in the Employee table, for example, might be Last Name, First Name, Salary, Hire Date, Social Security Number, etc.

SQL statements are issued for the purpose of:

• Data definition - Defining tables and structures in the database (DB).
• Data manipulation - Inserting new data, Updating existing data, Deleting existing data, and Querying the Database ( Retrieving existing data from the database).

Another way to say this is the SQL language is actually made up of 1) the Data Definition Language (DDL) used to create, alter and drop scema objects such as tables and indexes, and 2) The Data Manipulation Language (DML) used to manipulate the data within those schema objects.

Oracle 10g New Features

The main thrust of the new features and enhancements for Oracle 10g are geared towards grid computing which is an extension of the clustering features (Real Application Clusters) introduced with Oracle 9i.

The idea of Oracle 10g is that the database will be able to dynamically "requisition" resources from the grid to meet levels of peak demand.

Other enhancements for Oracle 10g are aimed at reducing costs and improving the quality of service by making the database easier to deploy and manage including enhancements to backup and recovery and data warehousing .

1. Ease of Management

As with Oracle 9i, one of the major focuses of Oracle 10g is on reducing costs and increasing up-time by making the database easier to manage. One way of doing this is to automate tasks that previously had to be done manually by the dba. To achieve this for Oracle 10g, a new Automated Storage Management (ASM) sub-system has been introduced. This feature removes the need to have a file system or a volume manager for managing the database files and eliminates the complexity of balancing data across disks and controllers manually. Instead, the ASM stripes data across the disks automatically, thereby maximizing throughput and minimizing the risk of data loss. The ASM also maintains the data striping as disks are added/removed and re-balances the i/o load dynamically.

Oracle 10g has also been given a self-management infrastructure which captures information about the running of the database and stores it in the Automatic Workload Repository. This information is used by tools such as the new Automatic Database Diagnostic Monitor (ADDM) which analyses data such as the top SQL statements and passes this onto the SQL Tuning Advisor which provides recommendation for improving performance. These recommendations can be implemented transparently to the application making it a useful feature for 3rd-party applications.

Other major enhancements in this area include:- enhancing Enterprise Manager to be able to manage the whole grid and the complete stack of resources and simplifying installation and configuration by reducing the number of initialisation parameters - for example there are now just two memory size parameters, one each for the SGA and the PGA.

2. Availability

Oracle 10g Real Application Clusters have been enhanced to provide Automatic Service Provisioning - servers are automatically allocated to work loads and clients are automatically assigned to the server with the least load. Also on failure of a server, the surviving servers are automatically reallocated to work loads.

Other enhancements in this area include:

• flashback available at the row, transaction, table or database level;
• recovery area on disk that is maintained automatically by the database and contains only those blocks changed since the last backup - thereby enabling faster recovery from media failure;
• data guard (standby database) has been enhanced to enable compression and encryption of log traffic from the master database to the standby system;
• tables can now be redefined without invalidating stored procedures;
• support for rolling upgrades of the hardware, operating system and the database to reduce planned down time.

3. Data Warehousing

Oracle 10g Data Warehousing enhancements include:

• an increase in the size limits of the database to support ultra-large databases of millions of terabytes in size and ultra-large files of terabytes in size. The 4GB restriction on LOBs has been raised to 128 terabytes.
• Improvements to Real Application Clusters (RAC) enable resources to be allocated automatically and means that operational data can be used immediately without the need to copy it to another database.
• Enhancements to OLAP analytic, a data-mining GUI and a new SQL model allow query results to be treated as sets of multi-dimensional arrays on which complex inter-dependent operations - such as forecasting - can be run without the need to extract data to spreadsheets or perform complex joins and unions on the data.
• A new changed data capture facility based on Oracle Streams provides low or zero latency trickle feeds that combined with integrated extraction, transformation and loading (etl) enable real-time warehousing.

Getting Started With Oracle

Overview
Oracle is one the most popular Relational Database Management System (RDBMS). Some other famous RDBMS includes Microsoft SQL Server, Sybase, MySQL, PostgreSQL, etc. Essentially, all the aforementioned RDBMS employs Structural Query Language (SQL) as their query interface. Users usually issue their queries by SQL through a "client". Different RDBMS offer different forms of clients. For example, MS SQL Server offers a GUI interface for user to type in their SQL language, and their queries would be executed after pressing the "Execute" button on the client. Oracle provides both GUI client and command-line client. In this lesson, we will study the command-line client, SQL*Plus. In addition, Oracle extends the standard SQL (e.g. select * from table) with its application-specific commands (e.g. checking how many table you have been created in your Oracle account) into a Oracle specific language called PL/SQL.