Oracle Escape Characters

Oracle allows the assignment of special escape characters to tell Oracle that the character is interpreted literally.  Certain characters such as the underscore “_” are not interpreted literally because they have special meaning within Oracle.

In the example below, we want to find all Oracle parameter that relate to I/O, so we are tempted to use the filter LIKE  “%_io_%’.  Below we will select from the x$ksppi fixed table, filtering with the LIKE clause:

select ksppinm
from x$ksppi
where ksppinm like '%_io_%';

 

KSPPINM                                                                        

--------------------------------           

sessions                                                                        

license_max_sessions                                                           

license_sessions_warning                                                       

_session_idle_bit_latches                                                       

_enable_NUMA_optimization                                                      

java_soft_sessionspace_limit                                                   

java_max_sessionspace_size                                                     

_trace_options                                                                 

_io_slaves_disabled                                                            

dbwr_io_slaves                                                                 

_lgwr_io_slaves

As you can see above, we did not get the answer we expected.  The SQL displayed all values that contained “io”, and not just those with an underscore.  To remedy this problem, Oracle SQL supports an ESCAPE clause to tell Oracle that the character is to be interpreted literally:

select ksppinm
from x$ksppi
where ksppinm like '%\_io\_%' ESCAPE '\';

 

KSPPINM                                                                        

--------------------------------------           

_io_slaves_disabled                                                            

dbwr_io_slaves                                                                 

_lgwr_io_slaves                                                                

_arch_io_slaves                                                                

_backup_disk_io_slaves                                                         

backup_tape_io_slaves                                                          

_backup_io_pool_size                                                            

_db_file_direct_io_count                                                       

_log_io_size                                                                   

fast_start_io_target                                                            

_hash_multiblock_io_count                                                      

_smm_auto_min_io_size                                                          

_smm_auto_max_io_size                                                           

_ldr_io_size 

rowconcate Function

Start by creating this function:

SQL> CREATE OR REPLACE FUNCTION rowconcat(q in VARCHAR2) RETURN VARCHAR2 IS
  2    ret  VARCHAR2(4000);
  3    hold VARCHAR2(4000);
  4    cur  sys_refcursor;
  5  BEGIN
  6    OPEN cur FOR q;
  7    LOOP
  8      FETCH cur INTO hold;
  9      EXIT WHEN cur%NOTFOUND;
 10      IF ret IS NULL THEN
 11        ret := hold;
 12      ELSE
 13        ret := ret || ',' || hold;
 14      END IF;
 15    END LOOP;
 16    RETURN ret;
 17  END;
 18  /
Function created.

This function returns a string result with the concatenated non-NULL values from a SQL statement. It returns NULL if there are no non-NULL values.
Here is an example of how to map several rows to a single concatenated column:

SQL> SELECT rowconcat('SELECT dname FROM dept') AS departments
  2    FROM dual;
DEPARTMENTS
--------------------------------------------------------------------------------
ACCOUNTING,RESEARCH,SALES,OPERATIONS

This example is more interresting, it concatenates a column across several rows based on an aggregation:

SQL> col employees format a50
SQL> SELECT deptno,
  2         rowconcat('SELECT ename FROM emp a WHERE deptno='||deptno) AS Employees
  3    FROM emp
  4   GROUP BY deptno
  5  /
    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        10 CLARK,KING,MILLER

DATA BASE LINKS

Purpose
Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.
Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

dblink
Specify the complete or partial name of the database link. If you specify only the database name, then Oracle Database implicitly appends the database domain of the local database.
If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects. If the value of GLOBAL_NAMES is FALSE, and if you have changed the global name of the database, then you can specify the global name.
The maximum number of database links that can be open in one session or one instance of a Real Application Clusters configuration depends on the value of the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE initialization parameters.

Restriction on Creating Database Links
You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema. Periods are permitted in names of database links, so Oracle Database interprets the entire name, such as ralph.linktosales, as the name of a database link in your schema rather than as a database link named linktosales in the schema ralph.)

CONNECT TO Clause
The CONNECT TO clause lets you enable a connection to the remote database.

CURRENT_USER Clause
Specify CURRENT_USER to create a current user database link. The current user must be a global user with a valid account on the remote database.
If the database link is used directly, that is, not from within a stored object, then the current user is the same as the connected user.
When executing a stored object (such as a procedure, view, or trigger) that initiates a database link, CURRENT_USER is the username that owns the stored object, and not the username that called the object. For example, if the database link appears inside procedure scott.p (created by scott), and user jane calls procedure scott.p, the current user is scott.
However, if the stored object is an invoker-rights function, procedure, or package, the invoker's authorization ID is used to connect as a remote user. For example, if the privileged database link appears inside procedure scott.p (an invoker-rights procedure created by scott), and user Jane calls procedure scott.p, then CURRENT_USER is jane and the procedure executes with Jane's privileges.

user IDENTIFIED BY password
Specify the username and password used to connect to the remote database using a fixed user database link. If you omit this clause, the database link uses the username and password of each user who is connected to the database. This is called a connected user database link.

dblink_authentication
Specify the username and password on the target instance. This clause authenticates the user to the remote server and is required for security. The specified username and password must be a valid username and password on the remote instance. The username and password are used only for authentication. No other operations are performed on behalf of this user.
You must specify this clause when using the SHARED clause.

USING 'connect string'
Specify the service name of a remote database. If you specify only the database name, then Oracle Database implicitly appends the database domain to the connect string to create a complete service name. Therefore, if the database domain of the remote database is different from that of the current database, then you must specify the complete service name.

Examples
The examples that follow assume two databases, one with the database name local and the other with the database name remote. The examples use the Oracle Database domain. Your database domain will be different.

Defining a Public Database Link: Example
The following statement defines a shared public database link named remote that refers to the database specified by the service name remote:

CREATE PUBLIC DATABASE LINK remote 
   USING 'remote'; 

This database link allows user hr on the local database to update a table on the remote database (assuming hr has appropriate privileges):

UPDATE employees@remote
   SET salary=salary*1.1
   WHERE last_name = 'Baer';

Defining a Fixed-User Database Link: Example
In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database:

CREATE DATABASE LINK local 
   CONNECT TO hr IDENTIFIED BY hr
   USING 'local';

Once this database link is created, hr can query tables in the schema hr on the local database in this manner:

SELECT * FROM employees@local;

User hr can also use DML statements to modify data on the local database:

INSERT INTO employees@local
   (employee_id, last_name, email, hire_date, job_id)
   VALUES (999, 'Claus', 'sclaus@oracle.com', SYSDATE, 'SH_CLERK');

UPDATE jobs@local SET min_salary = 3000
   WHERE job_id = 'SH_CLERK';

DELETE FROM employees@local 
   WHERE employee_id = 999;

Using this fixed database link, user hr on the remote database can also access tables owned by other users on the same database. This statement assumes that user hr has SELECT privileges on the oe.customers table. The statement connects to the user hr on the local database and then queries the oe.customers table:

SELECT * FROM oe.customers@local;

Defining a CURRENT_USER Database Link: Example
The following statement defines a current-user database link to the remote database, using the entire service name as the link name:

CREATE DATABASE LINK remote.us.oracle.com
   CONNECT TO CURRENT_USER
   USING 'remote';

The user who issues this statement must be a global user registered with the LDAP directory service.
You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database:

CREATE SYNONYM emp_table 
   FOR oe.employees@remote.us.oracle.com;

Can one generate HTML reports from SQL*Plus?

One can generate static HTML pages from SQL*Plus (8.1.6 and above) by setting the MARKUP option to HTML ON. This can be done by specifying -MARKUP "HTML ON" from command line, or with the "SET MARKUP HTML ON" command. Look at this example SQL Script:

specifying -MARKUP "HTML ON" from command line, or with the "SET MARKUP HTML ON" command. Look at this example SQL Script:

set markup HTML on
spool index.html
select * from tab;
spool off
set markup HTML off

Note: You can deploy this file on your web site or edit it in an HTML editor (like FrontPage or Dreamweaver). Another good idea is to develop a CSS to present the data more elegantly. One can also embed HTML tags in the select statement to create hyperlinks and add more HTML features.

How does one copy data from one database to another in SQL*Plus?

The SQL*Plus COPY command is one of the fastest ways of copying data between databases and schemas. This is also one of the few methods that will handle LONG columns correctly. Look at this example:

SQL> COPY FROM scott/tiger@db1 TO scott/tiger@db2 INSERT mytable USING select * from mytable;

COPY
----

Copies data from a query to a table in the same or another
database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.

COPY {FROM database | TO database | FROM database TO database}
           {APPEND|CREATE|INSERT|REPLACE} destination_table
           [(column, column, column, ...)] USING query

where database has the following syntax:
    username[/password]@connect_identifier

Note that this command is no more enhanced since Oracle 8.0 and may be declared as obsolete in a future version (but is still there in 11gR2) but there is no replacement to easily copy tables with LONG column (LONG datatype is obsolete).