Oracle - Substr function

Substr function in Oracle

This function helps in retrieving a part of string. It is very flexible such that we can retrieve any part of the string from any index and length.

Syntax :-
Substr(String,From_Index,[Length])

String --- Indicates the main string from which user is willing to retrieve a part or substring.
From_Index --- Indicates the character position or index from where user tries to take substring. If this value is negative, then sub string from index will be calculated from back else front or front to back.

Length --- This is optional parameter. This indicates the number of characters from the From_Index to be considered for building sub string.
By default , Substr(String,From_Index) build sub string from From_Index to end of the string.
If length is specified as ‘0’, then substr will always return null.

Example :-
Input	Output
Substr(‘srinivas’,2,0)	null
Substr(‘srinivas’,2)	rinivas
Substr(‘srinivas’,-2)	as
Substr(‘srinivas’,2,3)	rin
Substr(‘srinivas’,-2,1)	a

Oracle - Instr function

Instr function in Oracle

Instr retruns the position or Index of a character or a sub string in a string.

It is very use full and eases to use for many kind of searching a string in a string at specific positions and from specific location of a string.
Syntax: - Instr(String1,Sub String 1,[Position or Index to start search],[Position (nth) occurance].

[Position or Index to start search] --- Indicates the position from where to start search in given string for sub string. If the value is negative, it means to perform search in string from back to front for the sub string. Where as if positive it performs search in string from front to back. By default the value is 1. This is optional.

[Position (nth) occurrence] --- Indicates to consider the character occurrence or appearance count while performing search. By default the value is 1. This is optional.

Example:-
Input Query	Output Result
Instr(‘abcdefghabcdefgh’,’abcdefgh’) (Starts search from abcdefghabcdefgh and first occurrence of ‘a’ from a is at 1 st position)	1
Instr(‘srinivas’,’s’) (Starts search from srinivas and first occurrence of‘s’ from s is at 1 st position)	1
Instr(‘srinivas’,’s’,2,1) (Starts search from srinivas and first occurrence of‘s’ from r is at 8 th position)	8
Instr(‘srinivas’,’s’,-2,1) (Searches back to front) (Starts search from srinivas and first occurrence of‘s’ from a is at 1 st position)	1

How to display hyperlink using label in html page

Cascading style sheet helps in this kind of stuff. Indirectly displaying a label as hyperlink is anchor tag. But it differs much more in actual attributes or properties with anchor tag.
Ex:-

.hyperlnk
{
color:Blue;
text-decoration:underline;
}


When the above style class (hyperlnk) is applied as cssclass attribute to a label, it looks like simillar to an anchor tag.
Further more styles can be applied on mouse over/on mouse out of label to have more user friendly response.

In this case say if we would like show hand cursor on mouse over , we just say as onmouseover=”this.style.cursor=’hand’;”.
Also when user clicks on the hyper link to open window with url of label.
<label class="hyperlnk" id="lblLnk" onmouseover="this.style.cursor='hand';" onclick="window.open(this.innerText);">http://www.google.com</label>

Out put:

Now when user clicks on the above link, window opens with google.com as page.

How to generate Log files in web application.

When web applications are published onto web or application server, whom ever the user accesses the application through web server, since it has its own directories structured gives perfect response.

Since web server has directories defined for each application separately, we can create separate directory named “Log” in application folder path.
This Log folder helps in holding the audit logged files.
Audit log files can be created/updated at many instants, few of them are like
1) User logged in time with user id.
2) User logged out time.
3) User transactions done in application between logged in and logged out time.
4) Any application errors or exceptions.


These audit log files are ordinary text files which we usually create.

.NET frame work provides a class for which we have to create an object and perform file write/update operations.
System.IO package consists of required classes.

TextWriter tw = new StreamWriter(File path);
tw.WriteLine(“User abcd has logged in into system at “+Date.now.ToString());
tw.Close();


We can define a generic function, that writes information to log file when and there required by calling that function.


Simillarly inorder to trace errors, we just call that generic function inside catch blocks. Logging class name, function name along with error in log file helps support team to find the error easily by looking into log file.

This process of logging information to a text file is very important , could be used in many projects may be with different business conditions.

Reading Xml file using javascript

Xml is very important in things like especially communication between various applications which are built in different plat forms or technologies.

We can say simply in a word xml is mediator or a channel between applications to talk to each other.

So to read or write xml we have parsers in frame work library classes built by each technology of its own. Where as the xml format or way of handling data in it is always optimized and same.

For example to read xml file through java script Microsoft has provided an activex object "Microsoft.XMLDOM". So creating an instance to this can be used to load an xml file and read the file.
Ex:-
var xmlDoc=new ActiveXObject("Microsoft.XMLDOM")
xmlDoc.async=false; //says to load the file and read asynchornously
xmlDoc.load(xml filepath);


In this way we can load an xml file through browser.
A small example below shows how to read data from xml file and display in browser.


Demo Example:-
I create a xml file named as Demo.xml file which has information about strength of each class in different schools.

<?xml version="1.0" encoding="utf-8" ?>
<Schools>
<School SchoolName="Sikile school">
<Classes>
<Class Class="1" Strength="39"></Class>
<Class Class="2" Strength="40"></Class>
<Class Class="3" Strength="50"></Class>
</Classes>
</School>
<School SchoolName="Bharatiya vidhya bhavan">
<Classes>
<Class Class="1" Strength="59"></Class>
<Class Class="2" Strength="70"></Class>
<Class Class="3" Strength="80"></Class>
</Classes>
</School>
</Schools>










I am creating an html page say Demo.htm which should read this xml file and display information accordingly.



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Demo</title>
<script type="text/javascript" language="javascript">
var xmlDoc=new ActiveXObject("Microsoft.XMLDOM");
xmlDoc.async=false;
var Types=new Array();
function affiliate(){
div_xml.innerHTML='Loading...';
xmlDoc.load("Demo.xml");
Load("Demo.xml")
}
var htm;
function Load(SV){ Root=xmlDoc.documentElement;

htm="<blockquote>The file: "
htm+="<a href='"+SV+"'>"+SV+"</a><br></blockquote>";
htm+="<table rules='rows'>";
WriteChildNodes(Root);
htm+="</table>";
div_xml.innerHTML=htm;
}

function WriteChildNodes(rt)
{
htm+="<tr>";
if(rt.attributes!=null)
{
for(var j=0;j<rt.attributes.length;j++)
{
htm+="<td width=100>"+rt.attributes[j].nodeName+"</td>";
htm+="<td width=100>"+rt.attributes[j].nodeValue+"</td>";
}
}
if(rt.childNodes!=null)
{
for(var i=0;i<rt.childNodes.length;i++)
{
WriteChildNodes(rt.childNodes[i]);
}
}
htm+="</tr>";
}
</script>
</head>
<body>
<table>
<tr>
<td>
<input type="button" value="Read Xml" id="btnReadXml" onclick="affiliate();"/>
<div id="div_xml"></div>
</td>
</tr>
</table>
</body>
</html>

Input :

Output:

Oracle - Data Dictionary views Part - 3

USER_SYS_PRIVS, ALL_SYS_PRIVS, DBA_ SYS_PRIVS:
Displays information about object privileges on objects owned by the user or available to the current user, respectively, or all system privileges granted to all users in Oracle.

USER_SOURCE, ALL_SOURCE, DBA_ SOURCE:
Displays the source code for PL/SQL programs owned by the user or available to the current user, respectively, or all PL/SQL source code in entire Oracle database.


USER_TRIGGERS, ALL_ TRIGGERS, DBA_ TRIGGERS:
Displays information about triggers owned by the user or available to the current user, respectively, or all triggers in the Oracle database.

ROLE_TAB_PRIVS, ROLE_SYS_PRIVS ROLE_ROLE_PRIVS:
Displays information about object privileges, system privileges or roles granted to roles in the database respectively.

DBA_TABLESPACES, DBA_TS_QUOTAS:
Displays information about all table spaces in Oracle, as well as space quotas assigned to users in each table space.

DBA_DATAFILES, DBA_SEGMENTS, DBA_EXTENTS, DBA_FREE_SPACE:
Displays information about data files in your oracle database, as well as segments, extents and free space in each data file respectively.

DBA_PROFILES:
Displays information about user profiled in Oracle. Profiles are a way for you as the DBA to restrict the physical resources of the host system (such as process memory allocation, CPU cycles, and so on) that users may utilize in conjunction with oracle processing.

Oracle - Data Dictionary views Part - 2

USER_CONS_COLUMNS, ALL_CONS_ COLUMNS, DBA_ CONS_ COLUMNS:
Displays information about table columns that have constraints owned by or available to the current user, respectively, or all table columns in Oracle that have constraints on them.

USER_IND_COLUMNS, ALL_IND_ COLUMNS, DBA_ IND_ COLUMNS:
Displays information about table columns that have indexes owned by or available to the current user, respectively, or all table columns in Oracle that have indexes on them.

USER_TAB_COLUMNS, ALL_TAB_ COLUMNS, DBA_ TAB_ COLUMNS:
Displays information about columns in table owned by or available to the current user, respectively, or all columns in all tables in Oracle.


USER_ROLES, ALL_ ROLES, DBA_ ROLES:
Displays information about rows owned by or available to the current user, respectively, or all roles in Oracle database.

USER_TAB_PRIVS, ALL_TAB_PRIVS, DBA_ TAB_PRIVS:
Displays information about object privileges on objects owned by or available to the current user, respectively, or all object privileges available to all users in Oracle.

Oracle - Data Dictionary views Part - 1

There are dictionary views available after installing oracle data base that helps user to identify objects available on current data base.

USER_OBJECTS, ALL_OBJECTS, DBA_OBJECTS:
Gives information about various database objects owned by the current user, available to the current user, or all objects in the database, respectively.

USER_TABLES, ALL_TABLES, DBA_TABLES:
Displays information about tables owned by or available to the current user, respectively, or all tables in the Oracle database.

USER_INDEXES, ALL_INDEXES, DBA_INDEXES:
Displays information about indexes owned by or available to the current user, respectively, or all indexes in the Oracle database.

USER_VIEWS, ALL_VIEWS, DBA_VIEWS:
Displays information about views owned by or available to the current user, respectively, or all views in the Oracle database (including dictionary views).

USER_SEQUENCES, ALL_ SEQUENCES, DBA_ SEQUENCES:
Displays information about sequences owned by or available to the current user, respectively, or all sequences in the Oracle database.

USER_USERS, ALL_ USERS, DBA_ USERS:
Displays information about the current user, respectively, or all users in Oracle respectively.

USER_CONSTRAINTS, ALL_ CONSTRAINTS, DBA_ CONSTRAINTS:
Displays information about constraints owned by or available to the current user, respectively, or all constraints in the Oracle database.

How to drop all triggers existing in a Oracle schema?

Triggers that are created or local with in a schema are listed in “USER_TRIGGERS” tables.
So by executing a sql statement that retrieves all triggers names with in a schema is help full to drop them name by name.

Following sql statement helps out to give a result set that has sql queries to drop each trigger name by name.

Query:-

select 'drop trigger “'|| trigger_name||'”;' from user_triggers;

So I can spool the result set of this query to an sql file that can be again runned to drop the triggers.

I write a batch that does these 2 tasks
Batch File:-

@echo off
setlocal
set /p schema=Please enter your TNSname:
set /p username=Please enter your Schema Username:
set /p password=Please enter your Schema Password:
sqlplus %username%/%password%@%schema% @Script1.sql
sqlplus %username%/%password%@%schema% @Script2.sql


Scrip1.sql consists the above mentioned sql query which spools the result set to script2.sql;

So immediately after script1.sql is done then script2.sql is said to be runned.

Scrip1.sql:


set heading off
set linesize 1500
set pagesize 1000;
set feedback off
spool Script2.sql
select 'drop trigger “'|| trigger_name||'”;' from user_triggers;
select ‘commit;’ from dual;
select ‘exit;’ from dual;
spool off
exit;

How to compile the entire oracle schema?

Objects which are seem to be not compiled are said to be as ‘Invalid’.
So to compile such objects we need to alter there status as ‘Valid’ , indirectly oracle server compiles those objects to set there status as ‘Valid’.

Below sql statement lists out all the objects that are invalid and relevant sql query to make them valid or compile those objects.

Query :-

select 'alter ' || decode(object_Type,'PACKAGE BODY','PACKAGE',object_Type) || ' ' || object_name || ' compile;' from user_objects where status='INVALID';


So we can spool this result set to a sql file using a batch file, then running the spooled file compiles all the invalid objects or un compiled objects.

Batch File :


@echo off
setlocal
set /p schema=Please enter your TNSname:
set /p username=Please enter your Schema Username:
set /p password=Please enter your Schema Password:
sqlplus %username%/%password%@%schema% @Script1.sql
sqlplus %username%/%password%@%schema% @Script2.sql



Where here script1.sql consists of the above mentioned query and spools the result set to scrit2.sql.

Script1.sql :


set heading off
set linesize 1500
set pagesize 1000;
set feedback off
spool Script2.sql
select 'alter ' || decode(object_Type,'PACKAGE BODY','PACKAGE',object_Type) || ' ' || object_name || ' compile;' from user_objects where status='INVALID';
select ‘commit;’ from dual;
select ‘exit;’ from dual;
spool off
exit;


So script2.sql consists the relevant queries that compile those UN complied oracle objects.
Also batch files execute those statements from script2.sql.



After running batch file it asks for the database schema name, user name and password to log in through sql plus.
Output after running the batch file :

How to handle list box double click event in ASP.NET?

“ondblclick” client event available for list box control should be used to handle and post back or submit the page.

Where on page load event of the page , there should be sort of logic build to handle that this post back is because of double clicking the list box item.


In aspx Page :-


ondblclick="lstTableName _DoubleClick()">
1
2
3
4





In aspx.cs page

In page load event function

if (Request.Params["lstTableName_Hidden "] != null && (string)Request.Params["lstTableName_Hidden "] == "dblclk")
{ //logic whether to connect data base using the table name //selected in list box etc…
lstTableName.SelectedItem.Text
}

The hidden field “lstTableName_Hidden” value should be updated when client double event is fired such that after assigning flag to the hidden field through lstTableName _DoubleClick() function , it postbacks the page.
Where in page load event of the page we check the value of the lstTableName_Hidden value.
If it says the flag value it seems that the page postback has occurred just because of double clicking on the list box.

Oracle - SCNs (system change numbers)

When a transaction is committed as said redo information generated by user process during that transaction is committed to online redo files. But here the issue is when there are several transaction that could occur concurrently or parallel, use same redo log buffer to log information. How does redo log information associated to a specific transaction is identitied?
So when a specific single transaction is committed among those, SCNs which are ID’s that are generated by Oracle for each and every transaction helps to find the associated redo entry for that committed transaction to update online redo log files.

To get SCN number of current transaction we have v$database view:

Select CURRENT_SCN FROM v$option

Oracle - Stages in processing commits

Issuing a commit statement says that the current transaction is closed and committed. Even roll back says the same that transaction is completed but the data changes are not be committed or discarded.

For roll backing a transaction undo segments helps in achieving it. As undo segment holds the new and old value of a data change transaction.
Note:
Commit does not mean that, server process to immediately perform post or data change to disk from memory (buffered cache). This is the functionality or part of back ground process (DBW0). So what commit statement do?


1) Release table/row locks acquired by transaction

As discussed earlier that when a DML statement is executed by user , there are several steps one among which is to acquire lock on the row/table to avoid another user doing the same.
So those locks are released, as another user can go ahead to perform data changes on the same row/table.

2) Release UNDO segment locks acquired by transaction

As updated, a lock is said to be acquired on the UNDO segment when user executes a DML statement. Such lock is set be released when transaction is completed. So the space acquired by this old values are new values for data change are said to be released which can be used by another transaction.
Oracle does not clean the information from UNDO segment when a transaction is completed instead it is over written by another transaction that performs DML.

3) Generate redo information for committed transaction
Once a transaction is committed, redo log entry is generated by the user process stating that all the data changes made by this transaction are committed or finalized.
This also results in flushing the content of redo log buffer into redo log files for both committed and roll back as well.

Oracle - Moving data changes from memory to disk

Memory is entirely different to disk. Memory is what server process uses like fetching data from disk to buffer cache (memory) or memory is also used to perform actions made by DML statements in oracle server instead of directly posting on to the main disk.

Once DML statements are executed there is no further need to fetch data from disk to buffer cache or cursor as select statement.
When these DML statements are executed which does data manipulation upon the rows or blocks in buffer which are no longer identical with the one in disk are said to be “dirty” blocks. This does not mean that these blocks are erased as they contain the actual data change values which are to be posted or saved to the disk. Also the redo log buffer is updated with the data changes made by the DML statement. By making the data changes in memory, Oracle is able to achieve excellence as it takes less time to do actions on memory than on disk directly.
Mainly, it is not required to hold or make user in wait state till the data change is updated on to disk.

This process of saving data changes from buffer cache (memory) to disk is done asynchronously and said to as one among the back ground processes of Oracle.
There are 2 back ground processes that are part of this synchronous action.

1) DBW0.
2) LGWR.

These write data changes from buffer cache and redo log buffer to disk. Since these processes are asynchronous, they occur some time after the user actually made the changes.

DBW0:
This is called as database writer process, this process identifies the dirty blocks in buffer cache and updates them to disk. There are recently many versions of this released by Oracle. There are several instants that fire this process.

1) When server process need to make room in buffer cache to fetch data from disk for user process.
2) When DBW0 is told by LGWR (log writer process) to write data to disk.
3) Every 3 seconds due to time out.
4) The number of dirty buffers reaches maximum extent or threshold value.
The event when LGWR asks DBW0 to write data to disk is called check point.
DBW0 indicates 0th process, there can be from 0 to 9 digits which means there can be 10 data base writer processes for a single Oracle instance.


LGWR:

This back ground process is called log writer process, this writes redo log entries from redo log buffer in memory to online redo log files on disk. This also has specialized functions related to management of redo information.
As said LGWR asks DBW0 to write dirty blocks to disk.
There are instants when this LGWR is fired or a asked to perform actions.

1) When a transaction is committed.
2) When redo log buffer is 1/3 rd full.
3) When there is more than a mega byte of changes recorded in the redo log buffer.
4) Before DBW0 writes dirty blocks from buffered cache to disk files.

Oracle - Stages in DML Statements

DML statements include SQL statements like delete, insert, and update which does data manipulation. Since these statements differ to what select statement mean for obviously these have different steps of execution.
For these DML statements which perform data manipulations there comes a new concept of UNDO segments that only store old values, not the new values.
The new values that are given or calculated through DML statements are stored in object itself.
Following are the steps that are said to be occurred when DML statements are mean to be executed.
1) Parse statement

This is a common statement for select query as well which is to prepare an execution plan or parse tree and store in shared pool (library cache). If such execution already exist in library cache RDBMS skips this step.

2) Execute statement

RDBMS performs all processing to execute the DML statement. Again insert statement from DML differs from update or delete. Insert statement does not need to fetch data from disk to buffer cache where as for update or delete server process has to retrieve from disk to perform update or delete actions.
While running delete or update, server process will fetch data from disk to buffer cache, implicitly acquire a lock on the data to be changed and then make specific data change in buffer cache. As performing manipulations on data in memory is faster than in disk.
A lock has to be acquired on the data because to avoid other users doing the same thing.
For insert statements, the server process need not retrieve data from disk to buffer cache but should retrieve a block from a disk that has enough space available to house the new row of data and places that new row into block.
Also a part of this process DML statement is writing old and new versions of data to the Undo log segment acquired for that transaction. A lock must be acquired on the undo segment to write changes to an undo segment as well.

3) Generate redo

There can be several action been performed by a user in his/her session which has to be stored into redo log buffer after these DML statements are executed. So that DBA can recover the data change if damage is later done to the disk files containing oracle data.

Oracle - Stages in Processing Queries, changes and commits

Oracle has its own process of retrieving or performing actions on data from system. User cannot define or say such process for the Oracle server to do action but instead use SQL functional programming language as opposed to procedural programming language like C, COBOL.
RDBMS (Relational Data Base Management System) translates the outcome defined in SQL statement into a process by which Oracle can obtain.

These are the steps that go on in Oracle server when a “select” statement is issued by the user to the Oracle server.

1) Search Shared pool :

RDBMS will first attempt to determine if a copy of this parsed SQL statements exists in library cache (shared pool).

2) Validate Statement :
This step is by RDBMS is to check if the syntax of the statement is valid?

3) Validate Data sources :

RDBMS validates the data sources that are being used by the statement exist?
Like tables, columns referred by the statement.


4) Acquire Locks :

RDBMS locks the objects that are been referred by the statement to avoid the changes made on their definitions while the statement is parsed.

5) Check privileges :

RDBMS ensures that the user attempting to execute this SQL statement has enough privileges in data base to do so.

6) Parse statement :

This step is to prepare an execution plan or parse tree for the statement and place in library cache. Oracle considers this execution plan as optimized for executing the SQL statement. This is a list of operations the RDBMS uses to obtain data.
If execution plan or parse tree already exists in library cache then RDBS will omit this step.

7) Execute statement :

RDBMS performs all processing to execute the select statement. At this point, server process fetches data from disk to buffer cache.

8) Fetch values from cursor :

After the select statement is executed all data returned from Oracle is stored in cursor. That data is then placed into bind variables, row by row, and returned to the user process.


After the above 8 steps the execution plan is made and stored in library cache, data in buffer cache. Just in case if the same user or another user wants to execute the same select statement (multiuser environments), performance is said to be achieved as RDBMS skips the steps of preparing execution plan or parse tree and server process to fetch data from disk to buffer cache.

Oracle - Background processes

There are many back ground running process all the time running.
There are of 3 types.
1) Background.
2) Server.
3) Network.

The most imperative back ground process is Server Process.
This issues data from disk to the buffer cache where user can manipulate.
DBA can configure this server process in 2 ways.

1) Dedicated server (One genie, one master).
2) Shared server (One genie, Many Masters).

Dedicated server:

In this set up every single user connecting to Oracle will have a personal genie handling data retrieval from disk.
So where there are 10 users accessing to a dedicated serve there will be 10 genie’s to perform data retrieval from disk and put in buffer cache.

Shared server:

In this set up there exists a small pool of server processes running in Oracle that support data retrieval requests for a large number of users.
Several users are processed by one server process. Oracle manages this utilization by means of network process called dispatcher.

Dispatcher puts user request for data in queue and server process shall fulfill the requests one at a time. There can be multiple dispatchers and multiple server processes which are configurable after installing oracle.
These parameters help in reducing memory, CPU burden, and limits server process idle time.

In dedicated when user request is made for data retrieval a dedicated process had to be creates for each connection. In shared server this is not the case; the request is placed by the existing dispatcher and executed by exiting server process.


This shared server accessibility is also called as Multithreaded Server (MTS) which is encouraged when there are large number of users connect and disconnect database.

Earlier in under Memory Structure of Oracle it is said that Session layer is optional.

Memory Structure -- System Global Area (SGA) -- Shared Pool – Session layer

This session layer which holds the user process connected to Oracle is considered or configured by DBA in this Shared server environment to handle user data retrieval.
But it is not required in dedicated server as there will be separate genie for each connection, where session information is housed in PGA (Program Global Area).


Structure that connect users to Oracle Servers.

Oracle Listener:

Oracle listener listens (ear) when user tries to connect to Oracle Database via network. If it detects such instant it handles the request based upon type of the server process.

This process is called as Listener process.

a) Dedicated Server:
If the server process is dedicated it updates oracle database to generate new dedicated process and assigns the request to it.

b) Shared Server:
If the server process is shared, then listener assigns the request to dispatcher process.

After forwarding user request to specific (a) or (b) listener is no way in the picture.

The queue where this dispatcher places the request for server process to execute is called request queue. This request queue is allocated in SGA.
This request queue is shared by all dispatcher processes of an instance.

Also once the request is executed the relevant response is placed in response queue which too available in SGA.
Each dispatcher has its own response queue inside SGA. Then dispatcher sends the response from response queue to specific user who has sent the request.


This is the back ground process when user requests or hits oracle database.

Oracle consists of ?

Oracle constitutes of components for its existence.

1) Memory Structures.
2) Back ground running processes.
3) Disk resources to store the data.
4) Special resources designed to handle problems ranging from incorrect entry to disk failure.

Memory structures with back ground running processes said to be an Oracle Instance.

Oracle Instance with remaining resources said to be as Oracle Database



MEMORY STRUCTURE

Oracle memory structure is organized in the manner shown above.

1) System global area seems to be the primary memory component (SGA).

This is further classified into
a) Buffer Cache.
b) Shared Pool
1. Library Cache.
2. Dictionary or Row Cache.
3. Sessions (User processes connect to oracle) (Optional).
c) Redo Log buffer.


2) Program Global Area (PGA).


1 - A) SGA – Buffer Cache.


This memory structure consists of buffers, each buffer size of a database block.
This stores data needed by sql statements issued by user processes.

This has multiple units of and each unit considered as a block. So the size of buffer cache is always mentioned as blocks but not bytes.


In each block oracle can store several rows of data. So more the size of buffer more the information it can sore in it during the execution cycle of the select statement.
Its main purpose is to
i) Improve performance of subsequent repeated select statements on the same data.
ii) Oracle users can perform data changes quickly in memory and writes those changes to disk later.

1 – B) Shared Pool.

This is further classified into 2 mandatory structures and 1 optional structure.

1 – B – 1) Library Cache.

i) This is very vital to parse and store sql statement text.
ii) Also stores statements execution plan for reuse.

1 – B – 2) Dictionary Cache or Row Cache.

i) This stores recently accessed information.
Ex: - Username, password, table, columns, privileges etc…

These 2 components help there most to improve performance of Oracle.
1 – B - 3) Sessions (Optional)
This optional structure is to save the sessions like user process connected to oracle.


1 – C) Redo Log Buffer.

Actions performed by user on data through sql statements like delete, update, insert, create, alter, drop are not posted and saved to disk immediately. Instead they are maintained in this redo log entry till those changes are saved on to the disk.
Advantage of this functionality is DBA can use Redo information to recover the Oracle database to the point of database failure.


2) Program Global Area (PGA).


i) This memory structure helps in aspects like cursor handling, sort areas, sorting information like bind variable values. This differs from library cache in holding or saving the real values in place of bind variables for executing sql statements.

How to Send Parameter to sql file?

To run a set of queries (DDL or DML or DQL) in sql plus , we should write them in a sequence required to a sql file and execute that sql file to have the relevant output.

As we do open sql plus editor where we write our query language and execute them,
Similarly we save a sql file in a specific location with all set of queries in it can be executed with one line of statement.

Ex:-

Say set if queries I want to execute

Select * from tab;
Select sysdate fom dual;
Select 1 as Number from dual;

I can save all these sql queries in a sql file at c:\queries.sql;
In sql plus at the prompt I say

@c:\queries.sql and press enter.

The queries which are written in this file are executed.

Now there may be situation where we need to send an input parameter to this sql file which reads that in one of its query.

Ex:-


Select ‘My Name is &1 ‘ as MyName from dual;
Save at c:\myname.sql;

Here &1 denotes to consider first parameter among the input parameter list.
Now from sql plus I say
@c:\myname.sql Srinivas

Output :

How to send parameter to a batch file?

When we run batch files (.BAT), there might be instants where the batch file should perform operation based upon some input parameters.
In such case we should write those batch files reading input parameters while calling them.
Say suppose I want to run a batch file to which I will give my name and it should say “Hai “ (My Name).
Let the batch file name be “MyName.BAT” saved at C: drive.
I go to run, and type c:\MyName.BAT srinivas. Then the batch file existing at the location specified is executed and gives required output.

Logic that has to be built or to be written inside “MyName.BAT” file that displays my name.
It is very simple and 1 line command :).

Echo Hai %1
Pause

Save this content inside a batch file whose name is MyName.BAT in C drive.

Go to Command Prompt Type
C:\MyName.BAT Srinivas and press enter

Output:














Specific parameter can be considered or read based upon the parameter index in input list.
If I say % 2 then the batch file considers the second parameter from the input list

2)


What sowmya said is very true, that batch file cannot accept more than 10 parameters.
To resolve this , i mean to send parameters for a batch file more than 10 s possible by using shift key word.
Example :-
I want to send 11 characters to a batch files which has to display all those 11 input parameters.
Output :















Batch file content



@echo off
echo 1) %1
echo 2) %2
echo 3) %3
echo 4) %4
echo 5)%5
echo 6) %6
echo 7) %7
echo 8) %8
echo 9) %9
shift /1
echo 10) %9
shift /1
echo 11) %9
pause



Shift key word helps in shifting the parameters towards left.
Syntax :-
shift /5
this shits the input parameters towards left starting from 5th position.so parameter at 5 th position becomes now as 4th and 6th as 5 th etc...
so in the above file content when i say shift /1 then the 10th parameter will be the 9 th parameter and once again shift /1 makes 11th parameter as the 9 th parameter.
So i can make use of those 2 parameters as well.

Cheers,
Srinivas

how to handle caps lock using java script

Output :











Let us consider Login form or Login page in a web application.
When user tries to enter password in the provided text box, we can check whether the Caps lock is on, on the client machine.

This is done by using a java script function that can be called on key press event of the text box.

Password Text Box:

<asp:TextBox ID="txtPwd" runat="server" TextMode="Password" Width=150 onkeypress="CheckPasswordFont('Images/Warning.GIF',this);" onkeyup="kyup(this);" onfocusout="hideTip();"></asp:TextBox><br/>

'Images/Warning.GIF' is an image file that says friendly image saying that caps lock is on.
Java script function:

function CheckPasswordFont(imgpth,obj)
{
var e=event;
var kc = e.keyCode?e.keyCode:e.which;
var sk = e.shiftKey?e.shiftKey:((kc == 16)?true:false);
//Checking even when user hits a button holding shift key
if(((kc >= 65 && kc <= 90) && !sk)||((kc >= 97 && kc <= 122) && sk))
showPswdTip("<IMG SRC='"+imgpth+"' />",obj);
else
hideTip();
return true;
}
“spnTip” is html label whose inner html is filled with the Caps lock on image indicator.
function showPswdTip(txt,obj)
{

var theTip = document.getElementById("spnTip");
var crd=findPosition(document.getElementById(obj.id));
theTip.style.top=crd[1]+30;
theTip.style.left=crd[0];
theTip.innerHTML = txt;
theTip.style.visibility = "visible";
}

function hideTip()
{
document.getElementById("spnTip").style.visibility = "hidden";
}

<label id="spnTip" style="position:absolute; visibility:hidden;background:lightyellow;
border:1px solid gray;padding:2px;font-size:8pt;font-family:Verdana;" onmouseout="hideTip()"></label>

How to show tool tip using Java script?

When we consider drop down list or list box, user selects a specific option it might be required to display tool tip of the selected option.
Where as components like link button, button, check box, etc... Which always display static text or mean for static role it is easy to assign tool tip either using title or tool tip attribute.
So now comes into picture drop down list or list box whose tool tip should be dynamic based upon selected option. Obviously, we should write a java script function that has to called on change and on mouse over event of drop down list and list box. To display tool tip, label or div can be used whose position is absolute (position can be changed but not fixed). To assign this position as absolute, use style attribute of the label or div.
Ex:-

I have a list box which populates data dynamically



In aspx.cs or code behind page I can write code to connect to data base and load some business related data into this list box.

Observe I have included 3 events (onmouseover,onchange,onmouseout).
onmouseover,onchange – to display tool tip
onmouseout – to hidetooltip

Java script function that displays tool tip of the component on a div whose position is absolute.
Since div position is absolute , I am changing its top and left attributes based upon client x and y coordinates.

function showTip(oSel)
{

var theTip = document.getElementById("div_Tooltip");
theTip.style.top = window.event.clientY + 20;
theTip.style.left = window.event.clientX;
if(oSel.selectedIndex!=-1 && oSel.options[oSel.selectedIndex]!=null)
{
theTip.innerText = oSel.options[oSel.selectedIndex].text;
theTip.style.visibility = "visible";
}
else
{
theTip.style.visibility = "hidden";
}
}

Java script function to hide the tool tip.
function hideTip()
{
document.getElementById("div_Tooltip ").innerText=’’;
document.getElementById("div_Tooltip ").style.visibility = "hidden";
}

How to send mail using Smtp Object?

SMTP :- Simple Mail Transfer Protocol
In ASP.NET applications we use Smtp Client object to send mails.
Before sending mail , we should prepare the mail message and for which there is a class called Mail Message.
So object created to Mail Message class is used to define subject, body, Mail From , Mail To , Content type is HTML or not, etc…
Also for this Smpt Client Object we should assign attributes like Mail server IP , Port number etc.. to send mails from application.
These mail server details can either be configured in web.config file or shall be hard coded in code or can be saved in a data base table.
In Web.config

<system.net>
<mailSettings>
<smtp from="Admin@app.com" deliveryMethod="Network">
<network host="Your Mail Server IP" port="Mail server port (Default 25)"/>
</smtp>
</mailSettings>
</system.net>


Through code


string strFromEmailID="Hello@app.com";
string argEmailId ="Hello1@app.com";

SmtpClient smtpMail = new SmtpClient();
smtpMail.Host = strMailServerIP;
smtpMail.Port = 25;

MailMessage mailMsg = new MailMessage();
mailMsg.Subject ="Hello";
mailMsg.Body = ="Hello ";
mailMsg.To.Add(argEmailId);
mailMsg.From = new MailAddress(strFromEmailID);
mailMsg.IsBodyHtml = true;
smtpMail.Send(mailMsg);

Timer in javascript

There can be certain requirements where we need to call a function using java script after specific time instants like (Thread.Sleep(Time in Milli seconds)).

since java script does not support Threads at back ground , but indirectly can replicate the functionality of Thread.Sleep with javascript function (setTimout) which shall call a function after every specific time mentioned as parameter to this function.
Example :-
var cnt=0;
function test()
{
cnt++;
alert(cnt);
}
var tmr=setTimout("test();",2000);


In the above case we can see test is a javascript function which is when called increments the cnt variable and displays an alert box that displays the value of the variable cnt.
Also i have declared a timer variable that refers to timer function that calls this test function for every 2000 milli seconds or 2 seconds.
We need not require to bother for calling the function for every 2 seconds once this timer function is assigned or called.

To reset or of the timer function we say clearTimeout(tmr);

How to create user in Oracle

To create a user which is administration task , cuurent logged in user must have DBA (Data base Administration) Priviliges.
Open sql plus and log in with a user which is having DBA Priviliges.
After logging in into sql plus there are sql commands which are used to create a user.

Syntax:- Create user (User name) identified by (Password);
Example :- Create user test identified by test;

After creating a user we should assign priviliges or roles to the created user.
Syntax:- Grant (rolename) to (username);
Example :- Grant resource,connect,dba to test;
Once the user is created successfully and roles are granted we use that user.
While logging in with the user created above,
Syntax :- Username/Password@cSchemaName
Example :- test/test@(current schema name).

To drop an existing user,
Drop user (username) cascade;
Example :- drop user test cascade;

Priviliges of current logged in oracle user

When we create a schema/user in a data base. These schemas can be accessed using user name and relevant password linked to it in sql plus.
Syntax : -UserName/Password@DataBaseName

This user may not have all the available rights to perform tasks in database.
There are many instants, say suppose if we would like to create another user from this user in data base, definitely the current logged in user must have DBA prviliges to perform such administration task.

In such case to know all the available roles/privilges with the current login.
There is a table called "DBA_ROLES" or "SESSION_ROLES" which holds data.

1) Log in into sql plus with username,password and data base name provided.
2) Run Query "select * from SESSION_ROLES";

Ex:-

ROLE
---------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN

ROLE
---------------------------
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA

You can find listed role names available to current logged in user.

Good learning today about Project Integration

It is a very fantastic learning today,while installing patch release of my project on client machine connecting remotely.
My project is one among 2 projects with client that share common data base. Which i did not realise when installing or configuring my project.
I started running DB scripts which consists of 350 triggers that i need to re create on client data base. So before that i self decided to drop all exiting triggers which might create mess up. So i have written a batch file that drops all excising triggers in schema. Unfortunately i forget to skip the triggers existing and being used by another project , so the main pipe line between the 2 projects is broken once i dropped triggers.
Client has reported an issue saying there is no communication between 2 projects which i realised when client stated.
Tomorrow is most interesting day that i need to sort out this issue :)

I hope you understand the theme of this article, we should be very care full when working on projects that have dependencies or have projects that share i/p or o/p each other.
Cheers,

Cocatenating list using cursor in Oracle

This function expects cursor as an Input parameter , where the function reads the content from the cursor and concatenates using delimeter.
CREATE OR REPLACE FUNCTION Concatenate_List (p_cursor IN SYS_REFCURSOR)
RETURN VARCHAR2
IS
l_return VARCHAR2(32767);
l_temp VARCHAR2(32767);
BEGIN
LOOP
FETCH p_cursor
INTO l_temp;
EXIT WHEN p_cursor%NOTFOUND;
l_return := l_return || ',' || l_temp;
END LOOP;
CLOSE p_cursor;
RETURN LTRIM(l_return, ',');
END;
/

How to send mail in Oracle

This function accepts destination mail ID,Mail subject,Mail body.
This uses UTL_SMTP package (In built) to handle process of sending mails.
CREATE OR REPLACE FUNCTION Sendmail
(
MAILID IN VARCHAR,
MAILSUB IN VARCHAR,
MAILBODY IN VARCHAR
) RETURN NUMBER
AS
--TO GET REQUIRED DETAILS FROM CONFIGURATION DETAILS TABLE FOR REQUIRED PARAMETERS
L_MAILHOST VARCHAR2(64) := Your Mail Server IP;
L_FROM VARCHAR2(64) := From Mail ID;
L_PORT NUMBER := Your Mail server Port Number;
TEMPVALUE VARCHAR(2000);
L_MAIL_CONN UTL_SMTP.CONNECTION;
TEMPCOLNAME USER_TAB_COLS.COLUMN_NAME%TYPE;
V_REPLY UTL_SMTP.REPLY;
TEMPCURSOR SYS_REFCURSOR;
BEGIN
--ESTABLISHES A CONNECTION TO MAIL SERVER WITH MENTIONED IP AND SENDS MAIL WITH MENTIONED SUBJECT AND BODY
L_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(L_MAILHOST,L_PORT);
V_REPLY:=UTL_SMTP.HELO(L_MAIL_CONN, L_MAILHOST);
UTL_SMTP.MAIL(L_MAIL_CONN, L_FROM);
UTL_SMTP.RCPT(L_MAIL_CONN, MAILID);
UTL_SMTP.OPEN_DATA(L_MAIL_CONN);
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, 'DATE: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || CHR(13));
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, 'FROM: ' || L_FROM || CHR(13));
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, 'SUBJECT: '||MAILSUB||' '||CHR(13));
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, 'TO: ' || MAILID || CHR(13));
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, '' || CHR(13));
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, ''
||CHR(13));
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, ' '||MAILBODY||'. '||CHR(13));
UTL_SMTP.CLOSE_DATA(L_MAIL_CONN);
UTL_SMTP.QUIT(L_MAIL_CONN);
RETURN 1;
EXCEPTION WHEN OTHERS THEN
RETURN -1;
--RETURNING VALUE BASED ON EXECUTION
END;
/

Oracle function that retuns all columns for a table.

This function accepts table name as input parameter and returns columns available in that table.

This function uses user_tab_cols table to read column names.
CREATE OR REPLACE FUNCTION DPGLOC.Getcolumns(P_table IN VARCHAR)
RETURN VARCHAR
IS
ColumnNames VARCHAR(20000);
ColumnName user_tab_cols.COLUMN_NAME%TYPE;
indexvalue NUMBER;
CURSOR tmpCursor(TAB_LE VARCHAR) IS
--CURSOR tmpCursor IS
SELECT COLUMN_name FROM user_tab_cols WHERE UPPER(table_name)=UPPER(TAB_LE);
BEGIN
indexvalue:=0;
OPEN tmpCursor(P_table);
--OPEN tmpCursor;
LOOP
FETCH tmpCursor INTO ColumnName;
EXIT WHEN tmpCursor%NOTFOUND;
IF(indexvalue=0)THEN
ColumnNames:=ColumnName;
indexvalue:=1;
ELSE
ColumnNames :=ColumnNames||','||ColumnName;
END IF;
END LOOP;
RETURN ColumnNames;
END;
/

Sql query to list out tables available in current data base - Oracle

Once the data base is created successfully we can create user defined objects like tables,procedures,views,triggers,views,indexes,jobs,etc...

What ever we try to create in data base they are listed out or stored in there respective object type tabels with specific object name and object specific attributes.
This is very help full for many kind of applications.
For example ,Application should show all the tables in a data base.

In such case there is a sql query which returns the table (data base object) names with fields available in it.

Oracle supports a table called "user_tab_cols" to store user defined tables/views.
As views also come under table but which are virtual.

So "select * from user_tab_cols" query helps to list out all tables and there attributes available in oracle data base.

To just retrieve table names but not there fields.
select distinct table_name from user_tab_cols.

Note :- The reason for using distinct clause is, since in a table there can be more than one field so this query with out distinct clause returns same table name multiple times as the number of columns it has.

Also note distinct clause plays a vital role in query performance.
If it is not required to use distinct clause please try to avoid because sql optimiser takes more time to list out distinct values always.
So using distinct clause for a primary key or unique field is absurd.