select a random row in oracle database

when you run a select query everytime it will return the same rows. If you wanna make it different every time that is randomly, then you can use it,
SELECT col_name  FROM
(SELECT col_name
FROM table_name
ORDER BY dbms_random.value)
WHERE rownum = 1;

Nijhum Dip, How to go

Nijhum Dwip is an offshore island in the Bay of Bengal situated in the extreme south of Hatia island which is separated by Hatia channel. There are several approaches to the island. Passenger launches everyday plies between Sadarghat in Dhaka to Tomoruddy Ghat of Hatia upazilla. Adventure tourists can hire a trawler from Tomoruddy to Nijhum Dwip. A trawler may cost between Tk 1,200 and Tk 1,500 per day. Another approach is from Chittagong. A coastal passenger vessel plies between Chittagong and Nalchira Ghat twice a week. Our Star Holiday team took the sea-truck ride from Char Jabber Ghat to Nalchira Ghat at the the northern tip of Hatia which took around two and a half hours. For overnight stay in the island you have to contact the UNO of Hatia in advance for booking the bungalow on the island, or you can contact the DFO of Noakhali to stay at Nijhum Dwip forest office. You must carry your food and other necessities.

Nijhum Dip , The Real Beauty.

Nijhum Deep is a very precious island, as it was concealed by water for many years. The island slowly started to surface, and since 1972 the forestry department has been involved in the planting of trees and brush such as Bain, Keowra and Geowra. The forest, which is irrigated by the bay, has also attracted a great number of bird species. By 1978, wildlife was introduced to the island, and the eight spotted deer that were originally released on the island have grown to a healthy population of approximately six thousand individuals.

The spotted deer share Nijhum Deep with various other animals such as snakes, buffalo and monkeys, and it is estimated that almost thirty-five bird species are permanent residents of the island. Migratory birds also find the island the perfect resting destination as it is almost untouched and not threatened by human interaction. The beaches and sightings of crabs, sea snails and oysters create great excitement amongst tourists, as they are able to appreciate nature against the backdrop of the lush, green landscapes.

The authorities have made the island more attractive to tourists by erecting visitor facilities along the beaches. Viewing a sunset or sunrise from the shores of this tranquil and inspiring natural paradise is unforgettable. Bangladesh has realized how significant and important their wildlife and nature can be for the economy and tourism industry and is looking at creating more protected areas within the country. Nijhum Deep is the perfect example of how conservation and forestry can, not only assist and ensure the survival of animals, but enhance an industry and a country.

Family Name and Given Name

We have to face question about family name and given name Lot of time while filling the forms. Basically,
Family Name: Last name
Given Name: Rest of the part of full name.

Scope of the Loop Counter Variable

<< main>>
DECLARE
i NUMBER := 5;
BEGIN
FOR i IN 1..3 LOOP -- assign the values 1,2,3 to i
DBMS_OUTPUT.PUT_LINE( 'local: ' || TO_CHAR(i)
|| ' global: ' || TO_CHAR(main.i));
END LOOP;
END main;
/


The variable 'i' is used inside the loop as well as outside of the loop. Now if we wanna use the outer variable inside the loop having the same name as the loop counter then we have to use lebel.Here , 'main' is the name of the lebel.

Reverse FOR..LOOP in PL/SQL

Normal For Loop:
FOR k IN 1..500 LOOP
p := p + 1;
END LOOP;

Reverse For Loop:
BEGIN
FOR i IN REVERSE 1..3 LOOP -- assign the values 1,2,3 to i
DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
END LOOP;
END;
/

Exit the Loop in PL/SQL

IF and EXIT:
DECLARE
credit_rating NUMBER := 0;
BEGIN
LOOP
credit_rating := credit_rating + 1;
IF credit_rating > 3 THEN
EXIT; -- exit loop immediately
END IF;
END LOOP;
END;
/

EXIT and WHEN:
DECLARE
credit_rating NUMBER := 0;
BEGIN
LOOP
credit_rating := credit_rating + 1;
EXIT WHEN credit_rating > 3; -- exit loop immediately
END LOOP;
END;
/

Optimize a query having multiple CONTAINS clause

Consider the following multiple CONTAINS query:

SELECT title, isbn FROM booklist WHERE CONTAINS (title, 'horse') > 0 AND CONTAINS (abstract, 'racing') > 0

We can obtain the same result with section searching and the WITHIN operator as follows:

SELECT title, isbn FROM booklist WHERE CONTAINS (alltext, 'horse WITHIN title AND racing WITHIN abstract')>0;


This will be a much faster query. In order to use a query like this, we must copy all the data into a single text column for indexing, with section tags around each column's data.

Case Sensetivity in Oracle Text Search

Word queries are case-insensitive by default. This means that a query on the term dog returns the rows in your text table that contain the word dog, Dog, or DOG.

You can enable case-sensitive searching by enabling the mixed_case attribute in your BASIC_LEXER index preference. With a case-sensitive index, your queries must be issued in exact case.

Querying with MATCHES [CTXRULE INDEX]

When you create an index of type CTXRULE, you must use the MATCHES operator to classify your documents.

SELECT classification FROM querytable WHERE MATCHES(query_string,:doc_text) > 0;

Putting it all-together,

create table queries (query_id number,query_string varchar2(80));
insert into queries values (1, 'oracle');
insert into queries values (2, 'larry or ellison');
insert into queries values (3, 'oracle and text');
insert into queries values (4, 'market share');
create index queryx on queries(query_string)
indextype is ctxsys.ctxrule;

select query_id from queries where matches(query_string, 'Oracle announced that its market share in databases increased over the last year.')>0

This query will return queries 1 (the word oracle appears in the document) and 4 (the phrase market share appears in the document).

Querying with Oracle Text [CTXCAT INDEX]

When you create an index of type CTXCAT, you must use the CATSEARCH operator to issue your query. The operators available for CATSEARCH queries are limited to logical operations such as AND or OR. The operators you can use to define your structured criteria are greater than, less than, equality, BETWEEN, and IN

SELECT FROM auction WHERE CATSEARCH(title, 'camera', 'order by bid_close desc')>0;

For example, assuming that category_id and bid_close have a sub-index in the ctxcat index for the AUCTION table, you can issue the following structured query:

SELECT FROM auction WHERE CATSEARCH(title, 'camera', 'category_id=99 order by bid_close desc')> 0;

Querying with Oracle Text [CONTEXT INDEX]

Querying with CONTAINS:
When you create an index of type CONTEXT, you must use the CONTAINS operator to issue your query. With CONTAINS, you can also use the ABOUT operator to search on document themes.

SELECT SCORE(1), title from news WHERE CONTAINS(text, 'oracle', 1) > 0;

The CONTAINS operator must always be followed by the > 0 syntax, which specifies that the score value returned by the CONTAINS operator must be greater than zero for the row to be returned. When the SCORE operator is called in the SELECT statement, the CONTAINS operator must reference the score label value in the third parameter as in the previous example.

Structured Query with CONTAINS:
SELECT SCORE(1), title, issue_date from news WHERE CONTAINS(text, 'oracle', 1) > 0 AND issue_date >= ('01-OCT-97')
ORDER BY SCORE(1) DESC;

Managing DML Operations for a CONTEXT Index

Viewing Pending DML:
SELECT pnd_index_name, pnd_rowid, to_char(pnd_timestamp, 'dd-mon-yyyy hh24:mi:ss') timestamp FROM ctx_user_pending;

Synchronizing the Index:
The following example synchronizes the index with 2 megabytes of memory:
begin
ctx_ddl.sync_index('myindex', '2M');
end;

You can set CTX_DDL.SYNC_INDEX to run automatically at regular intervals using the DBMS_JOB.SUBMIT procedure.The location of this script is:

$ORACLE_HOME/ctx/sample/script/drjobdml.sql

you must be the index owner and you must have execute privileges on the CTX_DDL package. For example,to set the index synchronization to run every 360 minutes on myindex,

SQL> @drjobdml myindex 360

Viewing Index Errors in Oracle Index

Sometimes an indexing operation might fail or not complete successfully. When the system encounters an error indexing a row, it logs the error in an Oracle Text view.

You can view errors on your indexes with CTX_USER_INDEX_ERRORS. View errors on all indexes as CTXSYS with CTX_INDEX_ERRORS.

SELECT err_timestamp, err_text FROM ctx_user_index_errors ORDER BY err_timestamp DESC;

To clear the view of errors, you can issue:
DELETE FROM ctx_user_index_errors;

Create Oracle Text Index [CTXRULE Index]

The first step is to create a table of queries that define your classifications. We create a table myqueries to hold the category name and query text:

CREATE TABLE myqueries ( queryid NUMBER PRIMARY KEY, category VARCHAR2(30), query VARCHAR2(2000) );

INSERT INTO myqueries VALUES(1, 'US Politics', 'democrat or republican');
INSERT INTO myqueries VALUES(2, 'Music', 'ABOUT(music)');
INSERT INTO myqueries VALUES(3, 'Soccer', 'ABOUT(soccer)');

Use CREATE INDEX to create the CTXRULE index. You can specify lexer, storage, section group, and wordlist parameters if needed:

CREATE INDEX ON myqueries(query) INDEXTYPE IS CTXRULE PARAMETERS ('lexer lexer_pref storage storage_pref section group section_pref wordlist wordlist_pref');

Assume that incoming documents are stored in the table news:
CREATE TABLE news ( newsid NUMBER, author VARCHAR2(30), source VARCHAR2(30),article CLOB);

You can create a before insert trigger with MATCHES to route each document to another table news_route based on its classification:

BEGIN
-- find matching queries
FOR c1 IN (select category
from myqueries
where MATCHES(query, :new.article)>0)
LOOP
INSERT INTO news_route(newsid, category)
VALUES (:new.newsid, c1.category);
END LOOP;
END;

Create Oracle Text Index [CTXCAT Index]

Creating a CTXCAT Index
The CTXCAT indextype is well-suited for indexing small text fragments and related information. A CTXCAT index is comprised of sub-indexes that you define as part of your index set. However, adding sub-indexes to the index set has its cost.

Creating CTXCAT Sub-indexes,
create table auction(item_id number,title varchar2(100),category_id number,price number,bid_close date);
To create your sub-indexes, create an index set to contain them:
begin
ctx_ddl.create_index_set('auction_iset');
end;


Now, The CATSEARCH query operator takes a mandatory text clause and optional structured clause.
SELECT FROM auction WHERE CATSEARCH(title, 'camera', 'price < 200')> 0;
begin
ctx_ddl.add_index('auction_iset','price'); /* sub-index A */
end;

SELECT FROM auction WHERE CATSEARCH(title, 'camera','price = 100 order by bid_close')> 0;
begin
ctx_ddl.add_index('auction_iset','price, bid_close'); /* sub-index B */
end;

So, the full procedure is,
begin
ctx_ddl.create_index_set('auction_iset');
ctx_ddl.add_index('auction_iset','price'); /* sub-index A */
ctx_ddl.add_index('auction_iset','price, bid_close'); /* sub-index B */
end;

CREATE INDEX auction_titlex ON AUCTION(title) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS ('index set auction_iset');

Create Oracle Text Index [CONTEXT Index]

Creating a CONTEXT Index:
Default CONTEXT Index Example,
CREATE INDEX myindex ON docs(text) INDEXTYPE IS CTXSYS.CONTEXT;
Deafult Values:
1. The text is to be stored in text column.column can be of type CLOB, BLOB, BFILE, VARCHAR2, or CHAR.
2. Language of the DB will be asumed to be same.
3.Default Spotlist will be used.
4.Enables fuzzy and stemming queries for your language.


Custom CONTEXT Index Example:
Indexing HTML Documents,

begin
ctx_ddl.create_preference('my_url','URL_DATASTORE');
ctx_ddl.set_attribute('my_url','HTTP_PROXY','www-proxy.us.oracle.com');
ctx_ddl.set_attribute('my_url','NO_PROXY','us.oracle.com');
ctx_ddl.set_attribute('my_url','Timeout','300');
end;

begin
ctx_ddl.create_section_group('htmgroup', 'HTML_SECTION_GROUP');
ctx_ddl.add_zone_section('htmgroup', 'heading', 'H1');
end;

create index myindex on docs(htmlfile) indextype is ctxsys.context parameters('datastore my_url filter ctxsys.null_filter section group htmgroup');