Thanks to tom - A Challenge

The source for this article can be found in this link:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:516338600346572071

 

Although this challenge was posted in past, I thought to revive its magic. It was posted by Tom Kyte on Oct 31, 2007 4:52 AM. Without Tom or even AskTom I don’t think Oracle geeks would have some good repository of technical advantage. Here in this article I will rephrase and revive the magic of a small challenge for Oracle geeks. Find out from the following list of versions and features, when they where introduced in Oracle.

 

Of course you could read the link above and find the same. But try this as an exercise, and you will be surprised (for most of geeks they will find new features, for others they will battle with their memories).

 

Okay enough for the occasion, here is the features list:

 

1 Real Application Testing
2 Read only Replication
3 Distributed Query
4 Drop column
5 Client-Server (where the client could be elsewhere in the network)
6 Object Relational Features
7 Ability to return result sets from stored procedures (ref cursors)
8 Commit and Rollback (transactions)
9 Triggers
10 Function based indexes
11 Materialized Views
12 Rman
13 Audit SYSDBA/SYSOPER activity
14 Automatic Undo Management
15 Resumable Operations
16 Automatic Storage Management (ASM)
17 Streams
18 Bitmap Indexes
19 csscan - Character Set Scanner utility
20 Flashback Query
21 Case statement (IN SQL, instead of decode)
22 Parallel Query
23 Transparent column level encryption
24 Tablespace encryption
25 PL/SQL
26 Partitioning
27 Row Level Locking
28 Read Consistency (my favorite feature!)
29 2 Phase Commit
30 Sorted Hash Clusters
31 Conditional compilation for PL/SQL
32 Connect By Queries (select ename, level from emp connect by prior....)
33 Update anywhere Replication
 

And here is the version list:

 
10.1
10.2
11.1
2
3
4
5
6
7.0
7.1
7.2
7.3
8.0
8.1.5
8.1.6
8.1.7
9.0
9.2

 

Have fun in interpreting numbers and your memory.

Q-quote operator introduced in Oracle 10g

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

-- Document: Q-quoting mechanism

-- Created On: 17-Dec-07

-- Author: Amar Kumar Padhi

-- Email: amar.padhi@gmail.com

-- Purpose: Q-quote operator introduced in Oracle 10g.

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

 

We make use of single quotation mark in SQL and PL/SQL to identify sting literals. If the literal itself contains a single quote, we need to add one more quote next to it. This additional quote acts as an escape character and removes conflict with the outside quotes that are enclosing the string.

 

Oracle realises that long complex strings having lot of single quotes can turn out to become cumbersome and prone to errors that may not be caught during testing.

 

Release 10g onwards, a new quoting mechanism is provided in the form of "q". This new quote operator allows us to choose our own quotation mark delimiter.

 

Here are some examples -

SQL> select 'amar's web blog. It's personal..' str from dual;

select 'amar's web blog. It's personal..' str from dual

*

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

 

What we normally do:-

SQL> select 'amar''s web blog. It''s personal..' str from dual;

STR

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

amar's web blog. It's personal..

1 row selected.

 

Here is use of Q - quote operator. The above statement can also be represented as any one of the below.

 

select q'(amar's web blog. It's personal.)' str from dual;

select q'[amar's web blog. It's personal.]' str from dual;

select q'Aamar's web blog. It's personal.A' str from dual;

select q'/amar's web blog. It's personal./' str from dual;

select q'Zamar's web blog. It's personal.Z' str from dual;

select q'|amar's web blog. It's personal.|' str from dual;

select q'+amar's web blog. It's personal.+' str from dual;

 

(New added)

select q'zamar's web blog. It's personal.z' str from dual;

 

And so on. After the Q-quote is specified, provide the single quote along with a unique character at the beginning of the string. To close the string, provide the same character followed by the single quote. The single and the provided character form the two character string enclosure.

 

If you decide to use braces, Oracle expects matching enclosures, i.e., Open brackets should be represented by closed brackets at the end of the string. Other characters can be represented as they are at both ends.

 

All we need to take care of is that the last two character delimiter does not show up in the string itself. For instance the below will throw error as the closing characters get repeated within the string.

 

SQL> select q'aamar's web blog. Ita's personal.a' str from dual;

ERROR:

ORA-01756: quoted string not properly terminated

 

The same can be used in PL/SQL also.

SQL> declare

2 l_str varchar2(100) := q'[amar's web blog. Ita's personal.]';

3 begin

4 dbms_output.put_line(l_str);

5 end;

6 /

amar's web blog. Ita's personal.

PL/SQL procedure successfully completed.

 

I recently came across a program that framed dynamic INSERT statement to copy data from one database to another. Unfortunately, one of the record columns had a single quote embedded in the string and this resulted in the program unit to fail in production. This happened in release 9i and the only alternative available was to remove/double the single quotes from the string literal. From release 10g, Q-quote could also be used to prevent this problem. I will simulate the same scenario and try this out.

 

SQL> create table am100(col1 number, col2 varchar2(100));

Table created.

SQL> create table am102(col1 number, col2 varchar2(100));

Table created.

SQL> insert into am100 values(1, q'[amar's web blog. It's personal]');

1 row created.

SQL> insert into am100 values(2, q'[this is a simple string]');

1 row created.

SQL> insert into am100 values(3, q'[this is just another string]');

1 row created.

SQL> select * from am100;

COL1 COL2

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

1 amar's web blog. It's personal

2 this is a simple string

3 this is just another string

3 rows selected.

SQL> commit;

Commit complete.

 

Now I will call a simple routine that will read the data and generate dynamic SQL for inserting into another table.

 

declare

l_str varchar2(4000);

begin

for rec in (select col1, col2 from am100) loop

l_str := 'insert into am102(col1, col2) values (' || rec.col1 ||',''' ||

rec.col2 || ''')';

dbms_output.put_line(l_str);

execute immediate l_str;

end loop;

end;

/

 

The unit errors out as below, because of the single quote mismatch.

insert into am102(col1, col2) values (1,'amar's web blog. It's personal');

begin

*

ERROR at line 1:

ORA-00917: missing comma

ORA-06512: at line 4

 

I will now modify the program unit to add Q-quote. We can also use the REPLACE function to remove or double-up single quote in strings, both options are given below.

 

[] With REPLACE function (doubling the single quote);

declare

l_str varchar2(4000);

begin

for rec in (select col1, replace(col2, '''', '''''') col2 from am100) loop

l_str := 'insert into am102(col1, col2) values ('

|| rec.col1 ||',''' || rec.col2 || ''')';

dbms_output.put_line(l_str);

execute immediate l_str;

end loop;

end;

/

 

Output generated:-

insert into am102(col1, col2) values (1,'amar''s web blog. It''s personal')

insert into am102(col1, col2) values (2,'this is a simple string')

insert into am102(col1, col2) values (3,'this is just another string')

 

[] With Q-quote

declare

l_str varchar2(4000);

Begin

for rec in (select col1, 'q''[' || col2 || ']''' col2 from am100) loop

l_str := 'insert into am102(col1, col2) values ('

|| rec.col1 ||',' || rec.col2 || ')';

dbms_output.put_line( l_str );

execute immediate l_str;

end loop;

end;

/

 

Output generated:-

insert into am102(col1, col2) values (1,q'[amar's web blog. It's personal]')

insert into am102(col1, col2) values (2,q'[this is a simple string]')

insert into am102(col1, col2) values (3,q'[this is just another string]')

 

Q-quote will be a handy option when we deal with huge text literals.

Getting started with Oracle Advanced Queuing - Hands on example

In this article we will see how to create queues and to enqueue and dequeue messages. In this article I will not discuss what Advanced Queuing is about and why it is used. This is just a step-by-step guide to generate a message and to read it. The agenda of this article is:

 

  • Create a queue table
  • Create a queue
  • Start the queue
  • Enqueue the message
  • Dequeue the message

 

Create a queue table

--Type to hold the item object

create or replace type aqt_item as object(item_id number, item_description number) ;

--Type to hold order object
create or replace type aqt_order as object(order_id number, ord_name varchar2(60)) ;

exec DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => 'aqt_queue_table', queue_payload_type => 'aqt_order');

 

Now we will see what are the objects created.

 

SELECT queue_table,type,object_type,recipients FROM USER_QUEUE_TABLES;

 

QUEUE_TABLE       TYPE     OBJECT_TYPE        RECIPIENTS

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

AQT_QUEUE_TABLE           OBJECT             SYMSLSP.AQT_ORDER  SINGLE

 

Select object_name from user_objects where object_name like ‘AQ$%’;

 

OBJECT_NAME                OBJECT_TYPE   INDEX ON

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

AQ$AQT_QUEUE_TABLE         TABLE

AQ$_AQT_QUEUE_TABLE_E             QUEUE

AQ$_AQT_QUEUE_TABLE_F *    VIEW

AQ$_AQT_QUEUE_TABLE_I             INDEX         AQT_QUEUE_TABLE. TIME_MANAGER_INFO

AQ$_AQT_QUEUE_TABLE_T             INDEX         AQT_QUEUE_TABLE.( Q_NAME

   STATE

   ENQ_TIME

   STEP_NO

   CHAIN_NO

   LOCAL_ORDER_NO)

 

* The view will be created as a JOIN between AQT_QUEUE_TABLE and ALL_DEQUEUE_QUEUES view.

 

Creating Queues

 

exec DBMS_AQADM.CREATE_QUEUE (queue_name => 'aqt_queue', queue_table => 'aqt_queue_table');

 

select name, queue_table, queue_type from user_queues;

 

NAME                 QUEUE_TABLE          QUEUE_TYPE

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

AQ$_AQT_QUEUE_TABLE_E      AQT_QUEUE_TABLE      EXCEPTION_QUEUE

AQT_QUEUE            AQT_QUEUE_TABLE      NORMAL_QUEUE

 

 

Start the queue

Sql> exec DBMS_AQADM.START_QUEUE('aqt_queue');

Enqueue of message

DECLARE
  enqueue_options    dbms_aq.enqueue_options_t;
  message_properties       dbms_aq.message_properties_t;
  message_handle     RAW(16);
  message            aqt_order;
  message_id         NUMBER;
BEGIN

  Message := aqt_order(10, ‘Pencil’);

 

  enqueue_options.VISIBILITY := DBMS_AQ.ON_COMMIT;
  enqueue_options.SEQUENCE_DEVIATION := null;

 

  message_properties.EXPIRATION := DBMS_AQ.NEVER;

 

  DBMS_AQ.ENQUEUE (
    queue_name => ' aqt_queue ',

    enqueue_options => enqueue_options,

    message_properties => message_properties,

    payload => message,

    msgid => message_handle);

 

  COMMIT;

END;

/


Dequeue of message

 

DECLARE

  dequeue_options        dbms_aq.dequeue_options_t;

  message_properties     dbms_aq.message_properties_t;

  message_handle         RAW(16);

  message                aqt_order;

BEGIN

  dequeue_options.DEQUEUE_MODE := DBMS_AQ.BROWSE;

  DBMS_AQ.DEQUEUE ('aqt_queue', dequeue_options, message_properties, message, message_handle);

 

  Dbms_output.put_line(message. ord_name);

END;

/

 

Output

-------

Pencil

 

 

This concludes our hands-on session on how to create queue tables and queues and getting started with Oracle Advanced Queuing. Await for more articles on this topic.

 

 

Download 11g - how to create oracle account - Reply

For this comment in : http://askanantha.blogspot.com/2007/11/download-oracle-11g.html post, I could not reply due to some technical difficulties. Find my answer in this post:

The question was:
Im a student and while creating the account in the oracle site its asks for office name...what should I write?

While creating the user you specify your role as Student. You can enter any name for the company. Oracle might have to think again for this question.

Cheers.
Anantha

Download Oracle 11g

Oracle’s new database offering 11g is released and is available for download:

Click here to download 11g Release 1 (Size 1.7 GB)

Oracle Database 11g Release 1 (11.1.0.6.0)
Standard Edition, Standard Edition One, and Enterprise Edition

DownloadMicrosoft Windows (32-bit) (1.7 GB) | See All (Including Client, Examples, Gateways, Clusterware)
DownloadMicrosoft Windows (x64) (1.7 GB) | See All (Including Client, Examples, Clusterware)
DownloadLinux x86 (1.7 GB) | See All (Including Client, Examples, Gateways, Clusterware)
DownloadLinux x86-64 (1.8 GB) | See All (Including Client, Examples, Gateways, Clusterware)
DownloadSolaris (SPARC) (64-bit) (1.9 GB) | See All (Including Client, Examples, Gateways, Clusterware)
DownloadAIX (PPC64) Disk 1, Disk 2 (2.3 GB) | See All (Including Client, Examples, Gateways, Clusterware)
DownloadHP-UX Itanium Disk 1, Disk 2 (2.3 GB) | See All (Including Client, Examples, Gateways, Clusterware)

You will be asked with your otn login. It’s free to sign-up. Follow the below link on creating a FREE OTN account.

Click here for a tutorial on creating OTN account

Running 10gAS Form in full window

Mini Tip: Running 10gAS forms in MDI Window

Author: Anantha Narayanan

Date: November 6th 2007

 

Those who have been migrating from Oracle Client Server forms would have always wondered why the 10g forms are running always inside a browser. They probably know the reason of such behaviour.

 

In today’s mini article we will see how to change the default behaviour of 10gAS forms back to client server mode. If you have installed 10gAS in your local machine then there is no problem of access rights. If this is installed in some server and you do not have access to that machine, then forget this setting, because we need to modify the default server settings.

 

Locate the file formsweb.cfg (for default installations) (its default location will be in <AS_INSTALLATION_PATH>\forms\server and find out a parameter named separateFrame. This is a Forms applet parameter. These parameters are used to initialize the forms applet.

 

Modify the value for separateFrame from false to true. That’s all you need to do to change the default behaviour.

 

Note: If 10gAS is installed in D:\oracle\as, then the formsweb.cfg can be found out in D:\oracle\as\forms\server folder in case of Windows installations. In case of Linux/Unix installations if 10gAS is installed in /oracle/as, then the formsweb.cfg can be found out in /oracle/as/forms/server directory.

Tuning a LIKE-clause to use Index

The LIKE-clause can ignore indexes, causing queries to run forever while doing full table scans. This document describes how to tune such SQL statements by making use of Oracle Text or reverse key indexes.

Tuning the ‘LIKE’ Clause:


Generally, search arguments in the WHERE clause such as "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'" prevents Oracle from using an index to perform the search (however, not always).
If you use LIKE in your WHERE clause, try to specify one or more leading characters if at all possible. For example, use LIKE 'm%' and not LIKE '%m'. If you specify a leading character, Oracle has a better chance of being able to use an index to perform the query - this will increase performance and reduce the load on the database server.
To avoid such full table scans, consider the following scenarios:

Case 1: Tuning the LIKE-clause by using Oracle Text indexes


Requirements:
A. Install and configure Oracle's TEXT (done as part of the installation process).
B. Check whether Oracle TEXT is installed by looking for the 'CTXSYS' schema.
The problem
I. Create a test table:
create table t as select * from tab;
CREATE INDEX normal_index ON t
(TABLE_NAME) NOPARALLEL;
 
SQL> select TABLE_NAME from t where TABLE_NAME LIKE '%SEG%';
TABLE_NAME
------------------------------
D_MKTSEG
SEG$
II. Run a query to demonstrate that it will do a full table scan:
SQL> set autotrace traceonly explain
SQL> select TABLE_NAME from t where TABLE_NAME LIKE '%SEG%';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T'
III. Drop the index:
SQL> drop index normal_index;
 
Index dropped.
Solution:
I. Create an Oracle Text index on the columns that you would like to search:
SQL> create index xyz_oracle_txt_idx on t(TABLE_NAME) INDEXTYPE IS CTXSYS.CONTEXT;
Index created.
II. Change the LIKE-clause to an CONTAINS-clause - WHERE CONTAINS(TABLE_NAME, '%SEG%') > 0;
SQL> select TABLE_NAME from t WHERE CONTAINS(TABLE_NAME, '%SEG%')>0;
 
TABLE_NAME
------------------------------
D_MKTSEG
SEG$
III. Look at the SQL plan:
SQL> set autotrace traceonly explain
SQL> select TABLE_NAME from t WHERE CONTAINS(TABLE_NAME, '%SEG%')>0;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=24)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
          24)
 
   2    1     DOMAIN INDEX OF 'XYZ_ORACLE_TXT_IDX' (Cost=0)
Much better!

Case 2: Tuning the LIKE-clause by using reverse key indexes


Another trick for indexing queries with a leading wildcard character (like '%SON') is to create a REVERSE index - and then programmatically reverse the LIKE-clause to read "LIKE 'NOS%'", effectively indexing on the other side of the text, clumsy, yet effective.
Steps:
1. Create reverse key index on columns that will be searched. For example, create a reverse key index on Cust_Name of the customer table:
CREATE INDEX Cust_Name_reverese_idx
ON customer(Cust_Name)
REVERSE;
2. Programmatically reverse the SQL LIKE-clause to read '%saliV%':
Existing Query:
SELECT * 
FROM customer
WHERE Cust_Name LIKE '%Vilas%'
New Query:
SELECT * 
FROM customer
WHERE Cust_Name LIKE '%saliV%';