Oracle procedure to generate records as per binary tree.

We are aware about binary tree structure which looks like

So each node in this binary tree has its respective Childs (2).
We can generate these relations among nodes as database records.
For example:
Node 7 has Node 14 and Node 15 has child nodes, so
The table should have

Step1)
I have created a table saying “tree”.

CREATE TABLE TREE (
PRNT NUMBER,
CHLD NUMBER);
Where PRNT column holds the parent node number and CHLD holds child node number.
Step 2)
I have created an oracle procedure which expects “argMaxLevel” as input number.
This is to generate the nodes of a binary tree to certain level, that is



Oracle procedure
CREATE OR REPLACE PROCEDURE Createchlds(argMaxLevel IN NUMBER)
AS
BEGIN
DELETE FROM TREE;
FOR i IN 2 .. POWER(2,(argMaxLevel))-1
LOOP
INSERT INTO TREE VALUES(FLOOR(i/2),i);
END LOOP;
COMMIT;
END;
/


Step 3)
I said
Exec Createchlds(20), which should then generate nodes to level 20
And number of nodes generated are 108575 (Power(2,20))-1.

Output:-

You can observe there are 1 record missing
1) That record is node 1 which is not inserted as there is no associated parent record to point.


The last child record should be (power(2,20)-1) which is 1048575 and it is perfect from above screen shot.
The record with maximum child node is 1048575

Sql query to get records associated to a node in tree

I have seen few guys querying regarding this kind of stuff in orkut communities.
I found this has a solution to resolve.
Query is like
Example :-


It is very similar to chain marketing.
The below sql functions help us to get the associated child nodes for the given node.
Step1:
I have created a table saying “tree”.

CREATE TABLE TREE (
PRNT NUMBER,
CHLD NUMBER);
Test data :


Step 2:
My sql function
CREATE OR REPLACE FUNCTION Getchilds(argC1 IN VARCHAR)
RETURN VARCHAR IS
CURSOR c1 IS SELECT chld FROM TREE WHERE prnt=argC1;
tmpChld VARCHAR(10);
tmpReturn VARCHAR(100);
tmpSubChilds VARCHAR2(100);
BEGIN
OPEN c1;
tmpReturn:=NULL;
LOOP
FETCH c1 INTO tmpChld;
EXIT WHEN c1%NOTFOUND;
IF(tmpReturn IS NULL) THEN
tmpReturn:=tmpChld;
ELSE
tmpReturn:=tmpReturn||','||tmpChld;
END IF;
tmpSubChilds:=Getchilds(tmpChld);
IF(tmpSubChilds IS NOT NULL) THEN
tmpReturn:=tmpReturn||','||tmpSubChilds;
END IF;
END LOOP;
RETURN tmpReturn;
END;
/


I am using recursion technique here to get the Childs associated to the current node.


Output:-
Sql Query

select Getchilds(1) as Childs from dual


select Getchilds(2) as Childs from dual

How to get Nth maximum value using sql query?

Please refer
To find 2nd maximum value using sql query in Prog. Finidngs .

I would like to take the same table considered in above link to find Nth maximum value.

Let us consider a database table named “Test” and which has a column named “C1”.
Let the data type of C1 be Number
Script:-

CREATE TABLE test
(
c1 NUMBER
)
I have inserted some random data using
INSERT INTO TEST
SELECT ROUND(dbms_random.value(1,1000),0) FROM dual
Multiple times to insert multiple records.
I have inserted 66 records into this test table

Data (first 30 records)

So we can observe that there are many number of records among which the record with maximum value of C1 with my test data is
select max(c1) from test
983


Now my intention is to find a record whose c1 value should be the highest nth value.
Query:-
I would like to get the 10th highest value of C1 from test table highlighted in below screen shot.
SELECT * FROM TEST ORDER BY c1 DESC


So,
SELECT c1 FROM
(SELECT ROWNUM AS MAXINDEX,c1 FROM
(SELECT * FROM TEST ORDER BY c1 DESC)) WHERE MAXINDEX=10 helps me to get the Nth maximum value.
Innermost query gives me set of records from Test table in descending order of C1 column, the next inner query helps to identify the ordered records based on “Rownum”.
Using this rownum, external query can identify specific record at Nth position.
My test data which has 10th maximum value is
853
So i am getting 10th highest value from the column of the table.

How to get second maximum value using sql query?

Let us consider a database table named “Test” and which has a column named “C1”.
Let the data type of C1 be Number
Script:-

CREATE TABLE test
(
c1 NUMBER
)
I have inserted some random data using
INSERT INTO TEST
SELECT ROUND(dbms_random.value(1,1000),0) FROM dual
Multiple times to insert multiple records.
I have inserted 66 records into this test table

Data (first 30 records)

So we can observe that there are many number of records among which the record with maximum value of C1 with my test data is
select max(c1) from test
983


Now my intension is to find a record whose c1 value is second highest value.
Query:-
Inner query gives the maximum value available which is 983, so now i am saying to find max value again but whose value should be less than 983 (value from inner query).
Obviously, it is next maximum value.
SELECT MAX(c1) FROM TEST where c1<(SELECT MAX(c1) FROM TEST)
946
So i am getting second highest value from the column of the table.

Oracle function that returns columns of a table in table format.

In this case it is not just to return the columns in a table but they should be returned in a table format.
So we should crate a user defined type.
To know about this in brief check at
Oracle Type in Prog. findings
I am creating a type called “tbl_columns”.
create type tbl_Columns as table of varchar2(255)

My oracle function that returns the columns in given table as table format is

--Function expecting table name as input parameter
CREATE OR REPLACE FUNCTION fun_columns(argTableName IN VARCHAR)
--setting return type, such that returning the pipe lined rows
RETURN tbl_columns pipelined
IS
--Initialising cursor to get column names of a given table
CURSOR cur_columns IS SELECT column_name FROM user_tab_cols WHERE table_name=UPPER(argTableName);
tmpRec user_tab_cols.column_name%TYPE;
BEGIN
--opening cursor to read values from the select query
OPEN cur_columns;
LOOP
FETCH cur_columns INTO tmpRec;
EXIT WHEN cur_columns%NOTFOUND;
--piping each value as row in the defined type table tbl_columns
pipe ROW(tmpRec);
END LOOP;
END;

I would like to create a table called “MyTable” to test with this oracle function as
CREATE TABLE MyTable
(
MyColumn1 NUMBER,
MyColumn2 FLOAT,
MyColumn3 DATE,
MyColumn4 VARCHAR2(100),
MyColumn5 CHAR
)

After compiling the oracle function, I am using the below select query to get the coulns available in this table (MyTable).
SELECT * FROM (TABLE(fun_columns('MyTABle')));
Output:

Oracle function that returns a table of random numbers or characters

This oracle function expects 3 Input Parameters.
argCount IN NUMBER : Total number of random numbers to be generated.
argMinLimit IN NUMBER : Random number sequence minimum value.
argMaxLimit IN NUMBER : Random number max value.
Sinec my oracle function has to return a TABLE of random numbers, I would like to create a type of table type.
Refer
Type in Prog. Findings for what type is?
I am creating a type of table as
CREATE TYPE tbl_Rand AS TABLE OF VARCHAR2(10)
I have created this type as varchar, though random numbers stored are number data type is because in future I can elaborate my oracle function to generate random varchar values.
Number can be stored in varchar data type but varchar cannot be stored in number data type.

My oracle function is:

--Expecting 3 Input parameters CREATE OR REPLACE FUNCTION fun_Rand(argCount IN NUMBER,argMinLimit IN NUMBER,argMaxLimit IN NUMBER) RETURN tbl_rand pipelined IS tmpVal VARCHAR2(10); BEGIN --Looping for given argCount number of times FOR i IN 1 .. argCount LOOP --Generating random number between given argMinLimit and argMaxLimit tmpVal:=ROUND(dbms_random.value(argMinLimit,argMaxLimit),0); --Piping the generated random number into the table type pipe ROW(tmpVal); END LOOP RETURN; END;

Output:-
Query: select * from table(fun_Rand(5,10,100))
This mean that to generate 5 random numbers between 10 and 100.

Now I can elaborate my oracle function to generate random characters argCount IN NUMBER : Total number of random charaters to be generated.
argMinLimit IN NUMBER : Ascii code value to be considered from or minimum value.
argMaxLimit IN NUMBER : Ascii code value to be considered up to or maximum value.
Oracle function:

--Expecting 3 Input parameters CREATE OR REPLACE FUNCTION fun_Rand(argCount IN NUMBER,argMinLimit IN NUMBER,argMaxLimit IN NUMBER) RETURN tbl_rand pipelined IS tmpVal VARCHAR2(10); BEGIN --Looping for given argCount number of times FOR i IN 1 .. argCount LOOP --Generating random number between given argMinLimit and argMaxLimit and --getting character associated with this ascii code tmpVal:=CHR(ROUND(dbms_random.value(argMinLimit,argMaxLimit),0)); --Piping the generated random number into the table type pipe ROW(tmpVal); END LOOP RETURN; END;

Query:
select * from table(fun_Rand(5,10,100))
This mean that to generate 5 characters whose ascii code is between 10 and 100.

Oracle function to return Comma separated values as Table

This oracle function expects varchar as input parameter and returns a table.

Input (String)	a,bc,de,fg,hi
Output (Table)

Since output is to expected to be a table, it does not mean to create a temporary table to store the comma separated values.
Oracle provides an option to define a type as user required, some thing like user type.
In similar to programming language as we have int, float, string which are predefined data types, oracle too has such predefined types like varchar, number, char, etc…
In programming language we can define user defined type like classes defined by user,
Also in similar fashion we can create user defined types in oracle using Type clause.
Syntax:-
CREATE OR REPLACE TYPE [TYPE NAME] AS [TYPE];
Ex :-
CREATE OR REPLACE TYPE SampleType AS OBJECT (
A VARCHAR2(100),
B VARCHAR2(100),
C NUMBER
)
In our case to return a table from oracle function, we should create a object of Type table.
Since table constitutes of columns to append rows, so first we should define an object which holds those columns and next an another object of type table for the above object which holds the columns.
That is,



1)
--An object type that holds the values that are comma separated or
--since a table has to hold the comma separated values, so we
--need a single column of varchar datatype
CREATE OR REPLACE TYPE ValueType AS OBJECT (
Val VARCHAR2(100)
)
2)
--We need to create a table that holds the values of the comma separated values,
--such that this table type is table of the above object type (ValueType).
CREATE OR REPLACE TYPE ValueType_Tbl AS TABLE OF ValueType


3) Oracle function.
Please copy and paste this function into toad or an editors to understand comments against each line.
--argStr is the input string that has to splitted based upon delimeter
--delimeter is input string which has to be considered to split,
--such for this comma seperated values delimeter is ","
CREATE OR REPLACE FUNCTION Csv(argStr IN VARCHAR,delimeter IN VARCHAR)
--Return type mean to say that the table type defined as expaind above,
--rows that are to piped in while executing this function should
--be inserted or pipelined into ValueType_Tbl.
--such that this function returns this pipelined table (ValueType_Tbl)
RETURN ValueType_Tbl PIPELINED IS
tmpStr VARCHAR2(2000);
tmpSubStr VARCHAR2(100);
--Declaring a varaible of the new row type in table (ValueType_Tbl) indirectly
--since ValueType_Tbl is table of ValueType
out_rec ValueType := ValueType(NULL);
--ValueType(null) mean that it is default value
BEGIN
--taking the input string into another variable
tmpStr:=argStr;
--chekcing the length of the given input string
WHILE(LENGTH(tmpStr)>0 AND tmpStr IS NOT NULL)
LOOP
--taking first par of substring from the string till delimeter occurs (,)
tmpSubStr:=SUBSTR(tmpStr,1,INSTR(tmpStr,delimeter)-1);
--chekcing if there is no other part of string then asking to pipe line this row
-- and exit the loop
IF(tmpSubStr IS NULL)THEN
out_rec.Val:=tmpStr;
PIPE ROW(out_rec);
EXIT;
END IF;
--Assigning the sub string value to the record and pipe linening into the considered table type
out_rec.Val:=tmpSubStr;
PIPE ROW(out_rec);
--Taking the other of string after removing the sub string
tmpStr:=SUBSTR(tmpStr,INSTR(tmpStr,delimeter)+1);
END LOOP;
RETURN ;
END;
/


Output:-
Query that has to be executed to run this oracle function
SELECT * FROM TABLE(Csv('1,bc,de,fg',','))
We can observe that this function which returns, we are trying to type cast the pipelined rows to a table, such that we can have the result set in table format

How to Import and Export Database dump file in Oracle.

After installing oracle, we can find imp.exe and exp.exe files in bin folder.
These exe files could help for importing and exporting database dump.

This database dump file consists of tables (with or without data), database objects (Stored procedures, Views, Triggers, Constraints, Indexes, Sequences, etc…).

While exporting or importing, we have an option whether to consider table data or not.
So situations, like where we are just in need of only schema it helps to export or import without data. We also have an option for importing or exporting selected tables, i.e; we can mention a list of tables which we require to export or import.

So these exp and imp exe files have input parameters which are set depending upon need.
To know the parameters of imp, say in command prompt as
Imp help=y


Similarly for exp say,
Exp help=y

We can find imp and exp files almost expect the same kind of parameters.

Example:-
If I have a database whose connection string is
Demo/abcd@MyDB
(User name: Demo
Password: abcd
Schema: MyDB)
If I need to export I can go to command prompt and say “exp”, then it asks me for username where I have to give Demo/abcd@MyDB.
After that it asks for the file path where dump has to be exported, Export table data or not.

or I can say
exp Demo/abcd@MyDB file=[dumpfile export path] rows=(Y or N) log=[log file path]
This directly connects to the database and exports the database objects to the specific dump file path mentioned. We can find a log file at given Log file path, to just verify if there are any errors while exporting.
If we want only selected tables to be exported we can say
exp Demo/abcd@MyDB file=[dumpfile export path] rows=(Y or N) log=[log file path] tables=(table1, table2, tables3, …tablen)

Also for importing a database dump file, I should say imp in command prompt.
Say, if I am willing to import a database dump file to Demo\abcd@MyDemo,
Then I should give username as Demo\abcd@MyDemo, wheimp.exe prompts for.
Then it asks for the database dump file where it is to export.
We can either give the path or drag and drop the dump file.

or
I can say
Imp Demo\abcd@MyDemo file=[import databse dump file path] log=[log file path] rows=Y tables=(table1, table2.. tablen).
So import and export process seems to be almost same.

Isolation Level in Oracle

We need to have an understanding of different Isolation Levels in Oracle;
Why and what are isolation levels:
With different isolation levels in oracle, the same work performed in the same fashion with the same inputs may result in different answers. The ANSI/ISO SQL standard defines 4 levels of transaction isolation, with different possible outcomes for the same transaction scenario. -
Read uncommitted:
Read committed
Serializable,
Read-only.
Application designers, application developers, and database administrators can choose appropriate isolation levels for different transactions, depending on the application and workload. I will be taking through each isolation level basing on an example how it works and how is it implemented.
Lets get into the details of Read Uncommitted and what exactly do I mean by Read UnCommitted.
This isolation level allows dirty reads. This means – you are permitted to read uncommitted, or dirty, data. Data integrity is compromised, foreign keys are violated, and unique constraints are ignored. Oracle Database doesn't use dirty reads, nor does it even allow them. The basic goal of a READ UNCOMMITTED isolation level is to provide a standards-based definition that allows for nonblocking reads. As you've seen, Oracle Database provides for nonblocking reads by default.
We shall look at an example of the transaction, whose isolation level has been set to Read Uncommitted:
Lets take a simple bank transaction scenario:

Row number	Account Number	Account Name	Account Balance
1	111	Paul	1000$
2	222	Jeff	2000$
3	333	Tom	3000$
4	444	Harry	4000$

Consider a transaction from account 111 to 444 an amount of 500$ from 111 to 444 which has not been committed yet and a select query which is accessing all the rows in the table.

Row number	Account Number	Account Name	Account Balance
1	111	Paul	(1000 ) changed to 500$
2	222	Jeff	2000$
3	333	Tom	3000$
4	444	Harry	(4000) changed

In this scenario, even if the transaction has not been committed yet, the select query sees the values: 500$ in 111 account and 4500$ in the 444 account – which means it is reading dirty data – uncommitted data. . Therefore this not only does it return the wrong answer, but also it returns a total that never existed in the table. In a multiuser database, a dirty read can be a dangerous feature.

Now, lets take a look at the Read Committed Isolation level:
This is the default transaction isolation level.
If we consider a scenario of a simple transaction and a query simultaneously, the query sees only the data that was committed before the query began (and not the transaction). It will completely avoid the dirty reads. But, even this level of isolation has a short coming when it is having multiple transactions at the same time – reason is that Oracle does not prevent other transactions from modifying the data while other transaction is still in a processing state (uncommitted). So, if we consider 2 things simultaneously on the same data – a query and a transaction. Assume transaction hit the DB first - being in the process (uncommitted). It will lock the corresponding rows until the transaction is committed. If a query hits the DB in the process, it would not be able to access the DB until the user commits the changes. But, the bad news here is that the query waiting to read the value of that particular row would actually read the wrong answer, meaning the uncommitted, previous values that were present before the transaction has began. Thus, a transaction that executes a given query twice can experience both nonrepeatable read and phantoms depending on the other transactions in process.
Here is the same example, where we consider another select query during the transaction. Once the transaction (transfer from 111 to 444 ) begins, the rows 111 and 444 are locked until the commit has been performed. So the query( SELECT Account Balance from TABLE1 ) is in wait state till the transaction commits. At some point the transaction commits and account balances in 111 and 444 are changed to 500 and 4500 respectively unlocking the rows. But, we do not get the appropriate result for the query ( SELECT Account Balance from TABLE1 ) - It will still see
Account Balance
1000
2000$
3000$
4000

Instead of actual correct values:
Account Balance
500
2000$
3000$
4500$



Lets take a look at the Serializable transaction.

This is the highest level of Isolation level that can be achieved and the most safest(but at the cost of performance). The transactions or queries that try to access the DB while the Serializable transaction is in process, would be able to get the correct values at any time. In this level of isolation, the rows involved in the transaction are blocked and any queries hitting the DB in the process would block also be blocked.
Serializable isolation is suitable for environments:
• With large databases and short transactions that update only a few rows
• Where the chance that two concurrent transactions will modify the same rows is relatively low.
• Where relatively long-running transactions are primarily read-only



I am considering the same example and scenario. A transaction in process and a query concurrently trying to read the same rows involved in the transaction. This isolation permits such concurrent transactions.
Oracle generates an error when a serializable transaction tries to update or delete data modified by a transaction that commits after the serializable transaction began:
Cannot serialize access for this transaction.
When a serializable transaction fails with the "Cannot serialize access" error, the application can take any of several actions:
• Commit the work executed to that point
• Execute additional (but different) statements (perhaps after rolling back to a savepoint established earlier in the transaction)
• Roll back the entire transaction.
We need to understand that the name suggests, they will be executed in a serial(sequential model) . These kind of transactions do not experience nonrepeatable reads or phantoms
Let's consider an example:
These levels are defined in terms of three phenomena that are either permitted or not at a given isolation level:
• Dirty read: The meaning of this term is as bad as it sounds. You're permitted to read uncommitted, or dirty, data. You can achieve this effect by just opening an OS file that someone else is writing and reading whatever data happens to be there. Data integrity is compromised, foreign keys are violated, and unique constraints are ignored.
• Nonrepeatable read: This simply means that if you read a row at time T1 and try to reread that row at time T2, the row may have changed. It may have disappeared, it may have been updated, and so on.
• Phantom read: This means that if you execute a query at time T1 and re-execute it at time T2, additional rows may have been added to the database, which may affect your results. This differs from a nonrepeatable read in that with a phantom read, data you already read hasn't been changed, but instead, more data satisfies your query criteria than before.
You can set the isolation level of a transaction by using one of these statements at the beginning of a transaction:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET TRANSACTION ISOLATION LEVEL READ ONLY;

Oracle Manages Data Concurrency and Consistency

The risk of handling the queries from different users in multi user environment on a single table with in a unique data base needs to be designed and organized.

In this diagram, we can find a sample table in a data base. There are many queries sent by multi users at same instant of time.
There are three kinds of queries here
1) Data Readers (select queries).
2) Data writer or updater (Insert, Delete, Update).
3) Data reader and writer (select with insert).

Here comes concurrency into picture, as we can see several requests on same table at same instant of time.
Data reader’s concurrency
When user hits the data base by opening a connection (beginning a transaction) and run
Insert/Update/Delete statements, they seem to do data manipulation.
But be sure that those records which are considered for these statements in this transaction are locked.
So when this transaction is not committed or still in process, and if another user who is data reader (select query) reads the entire table data (select * from..) shall get data from these records whose data is old or the manipulated data will not reflect.
So if this transaction is committed, then the manipulated data is saved and readers (select queries) who read data after the transaction is committed can find the updated data.

If the transaction is not committed, reading the un committed data is ridiculous because user may roll back the transaction.
So if readers read uncommitted data, it is called as dirty data.

Data writer’s concurrency.
Let us consider update query is executed by a user in a specific transaction, which is not yet committed.
If another user in another transaction tries to update the same record, will be blocked.
This is because that specific record which is already manipulated and is locked in another transaction.
So unless that transaction is either rollbacked or committed, it cannot be manipulated in this transaction.
To check it.


Open sql plus say this as window 1 for reference
1) Log in into your data base.
2) Create table “test” as
Create table test
(
id number,
name varchar2(10)
);
3) Insert a row into this table saying
Insert into test values(1,’Srinivas’);
4) Commit this transaction (commit) in window 1.

5) Open another sql plus window say as window 2 for reference and log into same data base.

6) Run an update query saying
Update test set name=’Srini’ where id=1;

7) Don’t commit this transaction in window 2. (So this record is locked in this transaction).
8) Now go back to the previous window (window 1). Run an update query as
Update test set name=’VSSS’ where id=1;

You can observe this window 1 is hanged or some thing like blocked.
It is because the record which we are trying to update is locked in another transaction (window 2).
So, now go back to window 2 and say either commit or roll back. Automatically the query which is blocked in window 1 transaction will be executed, since the record which it is trying to manipulate is unlocked.

This is called serializable transaction.

How to find child fields that are referencing to a parent field in oracle?

Oracle dictionary views help to trace the child tables and columns that are referencing (foreign key) to a column in parent table.
Oracle dictionary views that can be used are
1) user_constraints (This holds information about constraints available in current schema).
2) user_cons_columns (This holds information about columns available in current schema and also the constraint names that are associated to each individual column).
Refer for Oracle data dictionary views

Dictionary views in Prog. Findings part 1

Dictionary views in Prog. Findings part 2

Dictionary views in Prog. Findings part 3

Query:-
SELECT user_constraints.table_name AS chld_table_name,tab1.column_name AS chld_column_name FROM user_constraints,(SELECT column_name,constraint_name FROM
user_cons_columns) tab1
WHERE
tab1.constraint_name=user_constraints.constraint_name
AND
user_constraints.constraint_type='R'
AND
r_constraint_name IN (SELECT constraint_name FROM user_cons_columns WHERE table_name='[PARENT_TABLE_NAME INPUT]' AND column_name='[PARENT_TABLE COLUMN_NAME INPUT]')

Example:-

Consider 3 tables
1) User details table (Parent table).
2) Salary details table (Child table).
3) Leave details table (Child table).

User details table has ID as primary key field which is been referenced by ID columns in Salary details and leave details table.
Upon running the above given query as


SELECT user_constraints.table_name AS chld_table_name,tab1.column_name AS chld_column_name FROM user_constraints,(SELECT column_name,constraint_name FROM
user_cons_columns) tab1
WHERE
tab1.constraint_name=user_constraints.constraint_name
AND
user_constraints.constraint_type='R'
AND
r_constraint_name IN (SELECT constraint_name FROM user_cons_columns WHERE table_name='USER_DETAILS' AND column_name='ID')

Output:-

Varchar Sequence Index Finder Oracle

Please refer how to generate varchar sequene in oracle? Prog. Findindgs.

As discussed in earlier post, that varchar sequence generator helps to generate character or string sequence very similar to number sequence in oracle.
Varchar sequence: A, B, C … Z, AA, AB, AC….AZ, BA, BC …BZ…ZA, ZB… ZZ, AAA, AAB…ZZZ, AAAA…..
This post is to do vise versa.
That is to get the sequence index value when user gives the varchar value.

Input	Output
A	1
Z	26
AZ	52
GCPB	125478
ZA	677
ZZZ	18278
AAAA	18279

Oracle function:-
CREATE OR REPLACE FUNCTION GetIndexOfStr(argVal VARCHAR)
RETURN NUMBER
IS
tmpIndex NUMBER;
tmpLength NUMBER;
BEGIN
--Getting the length of given varchar value
tmpLength:=LENGTH(argVal);
tmpIndex:=0;
--Calculating the index of the given varchar value in sequence
--A, B, C … Z, AA, AB, AC….AZ, BA, BC …BZ…ZA, ZB… ZZ, AAA, AAB…ZZZ, AAAA…..
FOR i IN 1 .. tmpLength
LOOP
tmpIndex:=tmpIndex+(ASCII(SUBSTR(argVal,i,1))-64)*POWER(26,tmpLength-i);
END LOOP;
--returning the given varchar index in the sequence
RETURN tmpIndex;
END;
Output:-
Run below query
SELECT GetIndexOfStr('ZZZ') as Sequence_Index FROM dual
Which will return the Index of ‘ZZZ’ in the considered varchar sequence
A, B, C … Z, AA, AB, AC….AZ, BA, BC …BZ…ZA, ZB… ZZ, AAA, AAB…ZZZ, AAAA…..
Result is 18278, since Index of 'ZZZ' in the considered varchar sequence is
18278

Varchar sequence in Oracle

We know that oracle supports sequence, but it generates number type values.
So we can use oracle sequence for specifically only to numbers.

We use this sequence in triggers of a table to update the primary key value for the field of newly inserted record.
If the primary key field is number, it is ok to use the oracle sequence.
What to do if the primary key field is varchar? Also if requirement is to not store numbers in this primary key field.

In this case we can consider sequence as follows for varchar primary key fields.
A, B, C … Z, AA, AB, AC….AZ, BA, BC …BZ…ZA, ZB… ZZ, AAA, AAB…ZZZ, AAAA…..

To generate such kind of sequence, I would like to create an oracle function which internally uses an oracle sequence and retrieves number as usual.
But depending upon the number from sequence, it calculates the varchar sequence value based upon index.
Look at below table which is the input and output with respect to my varchar generate sequence oracle function

Input	Output
1	A
26	Z
52	AZ
125478	GCPB
677	ZA
18278	ZZZ
18279	AAAA

Step 1:
Create a sequence that starts from 1 increments by 1.
create sequence seq_varchar start with 1 increment by 1 nocache;
Step 2:

Run the below oracle function,
Input parameters:-
argSeqname – Name of the oracle sequence created.
argNextVal – Numeric value.
If 1 then to get next value from the oracle sequence.
Else to get current value from the oracle sequence
CREATE OR REPLACE FUNCTION Generatestr(argSeqName IN VARCHAR,argNextVal NUMBER)
RETURN VARCHAR
IS
tmpNum1 NUMBER;
tmpNum2 NUMBER;
tmpCode VARCHAR2(800);
argNumber NUMBER;
BEGIN
tmpCode:='';
tmpNum1:=argNumber;
IF(argNextVal=1) THEN
EXECUTE IMMEDIATE 'select '||argSeqName||'.nextval from dual ' INTO argNumber;
ELSE
EXECUTE IMMEDIATE 'select '||argSeqName||'.currval from dual ' INTO argNumber;
END IF;
//Calculting the varchar value from the varchar sequence based upon //index from oracle sequence
tmpNum1:=argNumber;
IF(argNumber>26) THEN
WHILE(tmpNum1>26)
LOOP
tmpNum2:=MOD(tmpNum1,26);
IF(tmpNum2=0) THEN
tmpNum2:=26;
END IF;
tmpNum1:=FLOOR(tmpNum1/26);
IF(tmpNum2=26) THEN
tmpNum1:=tmpNum1-1;
END IF;
tmpCode:=CHR(64+tmpNum2)||tmpCode;
END LOOP;
END IF;
tmpCode:=CHR(64+tmpNum1)||tmpCode;


RETURN tmpCode;

END;
/

Step 3:

Run the below sql query
SELECT Generatestr('seq_varchar',1) AS NextValue FROM dual
Which mean to get next value from varchar sequence

Output:-

Run again
SELECT Generatestr('seq_varchar',1) AS NextValue FROM dual
Which mean to get next value from varchar sequence
Output:-

Run
SELECT Generatestr('seq_varchar',0) AS NextValue FROM dual
Which mean to get current value from varchar sequence
Output:-


Conclusion:-

So the process of generating the next varchar values goes on as similar to number from oracle sequence.
This way of generating varchar sequence is using oracle sequence to get only index, but we need to calculate the varchar value based upon Index

Generate Random Password Oracle

When user tries to reset password, new password should be generated randomly which is always independent with respect to user details.

Below oracle function returns such randomly generated password of specific length.

CREATE OR REPLACE FUNCTION generatePassword(LENGTH IN NUMBER)
RETURN VARCHAR
IS
tmpChr VARCHAR2(1);
tmpPswd VARCHAR2(32767);
BEGIN
--Number of loops equal to the length specified
FOR i IN 1 .. LENGTH
LOOP
--1. Generate a random integer between 48 and 122 because to generate string that
--has alphanumeric charaters and few special characters availbale on key board
--2. Get the caharacter whose ascii is equal to the generated ascii code
SELECT CHR(ROUND(dbms_random.value(48,122),0)) INTO tmpChr FROM dual;
--Appending each randomly generated character
tmpPswd:=tmpPswd||tmpChr;
END LOOP;
--returning the final created random password
RETURN tmpPswd;
END;
After creating the above function call the function with 10 as input parameter which generates a text or password of length 10.
Run query:-

select generatePassword(10) as password from dual

Output:-

rZdjSZo7eg

How to create triggers – Oracle?

Any updates made to table, specific to data but not schema can be traced using triggers.
Data updates could be like inserting new row or updating an existing row in a data base table.
So this updates are categorized into Insert and Update operations.
When creating trigger on a table parameters that are to be considered are

1) Trigger name to identify uniquely among the existing triggers.
2) Trigger event that has to be raised before or after of a specific action.
3) Event action (Insert / Update).
4) Table name on which we are placing trigger.
5) Is Insert/Update for each row? (This is because there could be bulk inserts on a table, where multiple rows are inserted at once
Bulk insert in Prog. findings).

Syntax :-
Create or Replace trigger [TRIGGER_NAME]
[AFTER/BEFORE] [INSERT/UPDATE] on [TABLE NAME]
[optional : FOR EACH ROW]
declare
variable declaration
begin
trigger body
end;

Example :-
I will place a trigger on a table of insert type.
In this trigger i need to generate new id for each record that is been inserted into this table.

Actions :-

1) We need to create 3 triggers of Insert type to handle this.
a) After Insert for each row in this table to store the relevant row id in a package.
b) Before Insert to clear the package content.
c) After Insert to get row ids from the package and update the id of the row.

Table
create table test
(
id number,
name varchar2(10)
);

Package to store row id’s
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE ridArray IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
newRows ridArray;
empty ridArray;
END;
/

Trigger after insert for each row in Test table

CREATE OR REPLACE TRIGGER TRG_TEST_AIFR
AFTER INSERT ON TEST FOR EACH ROW --after insert, for each row
BEGIN
--fills the package with inserted row id(s)
Pkg_TEST.newRows( Pkg_TEST.newRows.COUNT+1 ) := :NEW.ROWID;
END;
/


Trigger after insert in Test table

CREATE OR REPLACE TRIGGER TRG_TEST_AI
AFTER INSERT ON TEST --after insert trigger
DECLARE
tempID NUMBER;
BEGIN

FOR i IN 1.. Pkg_TEST.NEWROWS.COUNT
LOOP
select decode(max(id),null,1,max(id)+1) into tempID from test;
UPDATE TEST SET ID =tempID WHERE ROWID = Pkg_TEST.newRows(i);
END LOOP;
END;
/



Trigger before insert in Test table


CREATE OR REPLACE TRIGGER TRG_TEST_BI
BEFORE INSERT ON TEST --before insert trigger
BEGIN
--clears the package
Pkg_Test.newRows := Pkg_Test.empty;
END;
/
After running the below insert query
INSERT INTO TEST (name) VALUES('Srinivas');
ID is automatically generated from trigger, which should be '1' as this is the first record.

Oracle - Bulk Insert

Bulk insert takes less time to perform operation than conventional insert.
We perform insert operation conventionally.
Example for conventional insert:-
Insert into table values (V1, V2…, Vn);

In the above query, considering there are n columns in table and the values are mentioned with out column order, so inserts values based on column_id order.

It could also be like,

Insert into table (C1, C2…, Cn) values (V1, V2…, Vn);
So the values V1 will be inserted into row and field name C1. Similarly for all columns and values respectively.

So, definitely the number of columns should match with number of values and the corresponding data type of columns should match with the values at that index.

Where as in case of bulk insert it is same as with conventional insert, but here we try to insert bulk amount of rows in one single query than writing multiple insert statements for multiple inserts.

This is possible only if we have data organised in rows and values at specific columns.

Ex:-

Say there is a table tbl1 having three columns.

Col1

Col2

Col3

Then bulk insert could be possible when data that is been to be inserted, is as below structure

Ab	cd	ef
Gh	ij	kl
Mn	xy	tu

So, if data is organised as table with rows in it, bulk insert is possible.

Obviously to organise data in table structure, it can be done using “select” query.
“Select” query can be performed on a data base table.

Finally, bulk insert can be used on a table by using result set from a select query.

This is helpful when we try to copy all rows of a table to another table, provided both having same table structure (same number of columns and data types).

Example:-
To copy rows from tbl1 to tbl2 (tbl1 and tbl2 have same columns and data types).

Insert into tbl2 select * from tbl1
Also, If a select query returns data which satisfies the table structure of the destination, bulk insert can be performed from result set of “Select” query to destination table.

Create sequence oracle

Creating sequence in oracle is a very easy task.
Sequences help more at instants like generating new id for a record that has been inserted into oracle table through triggers.

Syntax:-
create sequence [sequence_name] start with [sequence start from] increment by [increment by] [caching]
Nocache clause is used while creating sequence to generate sequences in an order
Example :-
create sequence seq_test start with 1 increment by 1 nocache

Once sequence is created , value can be retrieved using sequence name as
syntax :- select [sequence name].nextval from dual
Example :- select seq_test.nextval from dual.

next_val increments the current value of the sequence and returns the updated value.

Simmillalry to get current value of the sequence we should use currval.
Example :-
select seq_test.currval from dual

Right outer join in oracle

Right outer join, which when we try to apply to join 2 tables say tbl1 and tbl2.
(Tbl1 ------ Right Join ------>Tbl2)
It does mean to join all rows available in tbl2 (right side table) to rows in tbl1.
Rows from tbl1 are available in result set depending upon the joint condition where as for tbl2 all rows are considered and can be restricted in joint condition or where clause.
Ex:-
There are 2 tables in oracle, named as tbl1 and tbl2 with data as
Tbl1 Data


Tbl2 Data

If we say right outer join for the above 2 tables placing condition tbl1.id=tbl2.id, then all rows from tbl2 are considered but from tbl1 whose ids are in the set of
({1, 2, 6} (all id’s available in tbl2, as we said right outer join)

Query:-
SELECT TBL1.id AS id1, TBL1.name AS name1, TBL2.id AS id2, TBL2.name AS name2 FROM TBL1
right outer join TBL2 ON TBL1.id=TBL2.id
Result:-



We can observe there are 3 rows in result set with ID2 as all values from tbl2 , where as there is no row with ID1 as 3 or 4 , since there is no corresponding row in tbl2 with ID= 3 or 4.


Note: -
1) If there does not exit row with the condition specific in either of two tables the values are null.
2) I have inserted one more row in tbl2 with ID=1 which is already there in tbl2.

Data:-


Query:-
SELECT TBL1.id AS id1,TBL1.name AS name1,TBL2.id AS id2 , TBL2.name AS name2 FROM TBL1
right outer join TBL2 ON TBL1.id=TBL2.id

Result:-


We can observe that since there is only one row in tbl1 with ID=1, but 2 rows in tbl2 with ID=1.
we can see 2 rows in result set after running left outer join query with ID2=1 and 2 rows having same Name1 but different Name2.