Insertion with condition

If there is a table with fields rt (number) which is a primary key and DF varchar2.If there r 10 records and first 5 records r deleted then when inserting records it want to start from 1 and then increment after each insert .

create table DF(DF VARCHAR2(3000),RT NUMBER(2) not null);

alter table DF add constraint E primary key (RT);

insert into df(rt,df)
with
all_numbers as (select level num from dual connect by level <=99),
available_number as (select num,rownum line from
(select num from all_numbers minus select rt from df order by 1) order by num )
select num,'B-'||rownum from available_number where rownum <=10;

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

Unknown Functions

A Word : Risk of using Oracle Undocumented functions or procedures here is that without any notice Oracle may remove them and there will not be support from ORACLE

To get similar characters among the two fields (from 9i onwards)

SELECT merge$actions('lNvl', 'pppl') FROM dual;
SELECT replace(merge$actions(1204, 1260),'BB','') FROM dual;

------------------------------------------------------------------------------
To view blob details (from 10g onwards)

SELECT sys_op_cl2c(ad_finaltext),s.product_id FROM pm.print_media s

-----------------------------------------------------------------------------
To get blocknumber of a particular tables (from 9i onwards)

select dbms_rowid.rowid_block_number(rowid) from dual;

SELECT COUNT(DISTINCT dbms_rowid.rowid_block_number(rowid)),
COUNT( dbms_rowid.rowid_block_number(rowid))FROM pm.print_media;

----------------------------------------------------------------------
To convert into HEX (from 9i onwards)

SELECT sys_op_descend('9')FROM dual;

-----------------------------------------------------------------------------
To show generate null coloumn (from 9i onwards)

SELECT sys_op_lvl(1,21,1,1,1,1 ,2) FROM dual;

-------------------------------------------------------------------------------
To generates and returns a globally unique identifier of 16 bytes The returned data type is RAW(16) (from 9i onwards)

SELECT sys_guid() FROM dual;

-----------------------------------------------------------------------
To get comma seperated values in rows wise (from 10g onwards)

select sys.odcivarchar2list('Football','Rugby') from dual;

select column_value from table(
select sys.odcivarchar2list('Football','Rugby') from dual);
-----------------------------------------------------------------------
To get data in ascii characters (from 10g onwards)


SELECT sys_op_c2c(9068986) FROM dual;
-------------------------------------------------------------------------
TO get number representation of rowid (from 9i onwards)

SELECT rowid, sys_op_rpb(rowid),dbms_rowid.rowid_block_number(rowid) FROM scott.emp
-------------------------------------------------------------------------
Similar to null (from 10g onwards)

SELECT sys_fbt_insdel, decode(sys_fbt_insdel,null,1,0) FROM dual;
-------------------------------------------------------------------------
To convert hex to num (from 9i onwards)


SELECT sys_op_rawtonum('000000FF'),UTL_RAW.CAST_FROM_BINARY_INTEGER(255) FROM dual;
-------------------------------------------------------------------------
To check whether a number is even or odd (from 9i onwards)

Return the value of the bit at position N
The return value is 0 or 1

SELECT sys_op_vecbit('255',0),sys_op_vecbit('255',1),sys_op_vecbit('22',1),sys_op_vecbit('22',0) FROM dual;
-------------------------------------------------------------------------
Return the binary AND of two hex values (from 9i onwards)

SELECT sys_op_vecand('FC','FD') from dual;
SELECT sys_op_vecand('FF','FD') from dual;
-----------------------------------------------------------------------
Return the binary OR of two hex values (from 9i onwards)


SELECT sys_op_vecor(('FC'),('FE')) from dual;
-----------------------------------------------------------------------
Return the binary XOR of two hex values (from 9i onwards)

SELECT sys_op_vecxor(('FF'),('FE')) from dual;
-----------------------------------------------------------------------
To get ref value visible (from 9i onwards)

SELECT sys_op_r2o(CUSTOMER_REF),CUSTOMER_REF FROM oe.oc_orders WHERE rownum = 1;

--------------------------------------------------------------------------------
Query to show which all values are present in a table and not present

SELECT nvl(to_char(e.EID), column_value||' Not found') empno from table(sys.odcinumberlist(7369,7370,7566,7555,1))l left outer join emp e on e.EID = l.column_value order by 1

sys.odcinumberlist is the default varray that oracle supplies . If you want to create your own type

CREATE TYPE typ_p is varray (32767) of number

SELECT * from table(typ_p(7369,7370,7566,7555,1 ))

-----------------------------------------------------------------------
To compare null (from 9i onwards)

SELECT 'hi there' FROM DUAL WHERE NULL = NULL;

SELECT 'hi there' FROM DUAL WHERE sys_op_map_nonnull (NULL) = sys_op_map_nonnull (NULL);
SELECT 'hi there' FROM DUAL WHERE nvl(null,'FF')='FF';
select sys_op_map_nonnull(null) from dual; --similat to nvl(null,'FF')
select * from dual where sys_op_map_nonnull(null) = 'FF';
SELECT 'hi there' FROM DUAL WHERE to_char(1||NULL) = to_char(1||NULL);
-----------------------------------------------------------------------

JSP Format Model

To convert date into numbers and words

select to_char(sysdate,'J') "WORDS" from dual;
select to_date(to_char(sysdate,'J'),'J') "WORDS" from dual;
select to_char(to_date( to_char(sysdate,'J') ,'J'), 'JSP') "WORDS" from dual;

----*--------------------------------*-----------------------
To convert numbers into words

select to_char(to_date(873,'J'), 'JSP') as converted_form from dual;

------*-------------------------------------*------------------------
To convert words into numbers


SELECT LEVEL wordasint FROM dual
WHERE TO_CHAR(TO_DATE(LEVEL,'J'), 'JSP') = 'ONE HUNDRED TWENTY-THREE'
CONNECT BY TO_CHAR(TO_DATE(LEVEL-1,'J'),'JSP') != 'ONE HUNDRED TWENTY-THREE'
AND LEVEL < 124; ---FOR 10 g and above
SELECT sp, n
FROM (SELECT 'EIGHTY-SIX THOUSAND THREE HUNDRED NINETY-NINE' sp FROM dual)
MODEL DIMENSION BY (1 dim)
MEASURES (0 n, sp) RULES ITERATE (86400) UNTIL (TO_CHAR(DATE '0001-01-01' +
(ITERATION_NUMBER/86400),'SSSSSSP')=sp[1])
(n[1]=ITERATION_NUMBER);

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

Query Analyzer

To check whether select or DML statements are correct ,here they will not execute (ie: if we check an insert statement it will not insert particular data into that table ) But DDL statement will get executed during parsing itself .

declare
c integer := dbms_sql.open_cursor;
begin
dbms_sql.parse(c, 'select * from scott.emp', dbms_sql.native);
dbms_sql.close_cursor (c);
end;

Oracle Text - Part 6

CTX_DOC

manipulate SQL queries by injecting arbitrary SQL code via the THEMES, GIST, TOKENS, FILTER, HIGHLIGHT, and MARKUP procedures

-----------
Sectioner

The sectioner object is responsible for identifying the containing section(s) for each text unit. Typically, these sections will be predefined HyperText Markup Language (HTML) or eXtensible Markup Language (XML) sections. Optionally,the sectioner can process all tags as sections delimiters.

For example:

<TITLE>XML Handbook</TITLE>. This allows search between tags using the WITHIN operator. Use of the WITHIN is illustrated in the section on XML searching.