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.

Oracle function that returns table row details.

Oracle function that accepts table name,primary key field name,primary key field value as input parameters
CREATE OR REPLACE FUNCTION Getrowdetails
(
TableName VARCHAR,
primarykey VARCHAR,
primarykeyvalue VARCHAR
)
RETURN VARCHAR
AS
--To return details of a row in table with primary key field name and value mentioned
tempColname user_tab_cols.COLUMN_NAME%TYPE;
usercursor sys_refcursor;
tempVar VARCHAR2(20000);
tempValueString VARCHAR2(25000);
BEGIN
tempValueString:=tempValueString||'
';
--Iterating for each column in the specific table and getting details for specific row for
-- that column
OPEN usercursor FOR SELECT column_name FROM user_tab_cols WHERE UPPER(table_name)=UPPER(TableName) ORDER BY column_id;
LOOP
FETCH usercursor INTO tempColname;
EXIT WHEN usercursor%NOTFOUND;
EXECUTE IMMEDIATE 'select '||tempColname||' from '||TableName||' where '||primarykey||' = '''||primarykeyvalue||''''INTO tempVar;
IF(tempVar IS NOT NULL) THEN
tempValueString:=tempValueString||'
'||tempColname||' -- > '||tempVar||'
';
ELSE
tempValueString:=tempValueString||'
'||tempColname||' -- > NULL
';
END IF;
END LOOP;
--Retuning the details of the row
RETURN tempValueString;
END;
/

This function returns details concatenating with delimeter (New Line character),

Oracle function to find whether a string has special characters?

This function expects a varchar input paramter for which it returns "0" if input string has special characters else returns 1.
CREATE OR REPLACE FUNCTION Containssplchrs(argValue IN VARCHAR)
RETURN NUMBER
AS
tempValue VARCHAR(1);
tempActValues VARCHAR(40);
tempRtrnValue NUMBER;
tempLength NUMBER;
BEGIN
tempActValues:=' ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
tempLength:=LENGTH(argValue);
IF(argValue IS NULL) THEN
RETURN 1;
END IF;
FOR I IN 1.. tempLength
LOOP
tempValue:=SUBSTR(UPPER(argValue),i,1);
tempRtrnValue:=INSTR(tempActValues,tempValue);
IF(tempRtrnValue=0) THEN
RETURN 0;
END IF;
END LOOP;
RETURN 1;
RETURN tempLength;
END;
/

Java script function to validate from and to dates?

fid indicates from date text box id and tid is to date text box id
function DateLsEq(fid,tid)
{
var msg="From date is greater than To date";
try
{
var fval=document.getElementById(fid).value;
var tval=document.getElementById(tid).value;
if(fval=='' && tval=='')
{
return true;
}
var fdts=null;
var tdts=null;
if(fval!='')
{
if(fval.indexOf(".")!=-1)
{
alert('--Not a valid Date(dd/mm/yyyy)');
document.getElementById(fid).focus();
return false;
}
fdts=fval.split("/");

}
if(tval!='')
{
if(tval.indexOf(".")!=-1)
{
alert('--Not a valid Date(dd/mm/yyyy)');
document.getElementById(tid).focus();
return false;
}
tdts=tval.split("/");
}
if(tdts!=null)
{
if(tdts[1].length==1)
{
alert('--Not a valid Date(dd/mm/yyyy)');
document.getElementById(tid).focus();
return false;
}
if(tdts[0].length==1)
{
alert('--Not a valid Date(dd/mm/yyyy)');
document.getElementById(tid).focus();
return false;
}
if(!isInteger(tdts[2]) || !isInteger(tdts[1])|| !isInteger(tdts[0]))
{
alert('--Not a valid Date(dd/mm/yyyy)');
document.getElementById(tid).focus();
return false;
}
if(!IsValidDate(tval))
{
document.getElementById(tid).focus();
return false;
}
}
if(fdts!=null)
{
if(fdts[1].length==1)
{
alert('--Not a valid Date(dd/mm/yyyy)');
document.getElementById(fid).focus();
return false;
}
if(fdts[0].length==1)
{
alert('--Not a valid Date(dd/mm/yyyy)');
document.getElementById(fid).focus();
return false;
}
if(!isInteger(fdts[2]) || !isInteger(fdts[1]) || !isInteger(fdts[0]))
{
alert('--Not a valid Date(dd/mm/yyyy)');
document.getElementById(fid).focus();
return false;
}
if(!IsValidDate(fval))
{
document.getElementById(fid).focus();
return false;
}
}
if(tdts!=null && fdts!=null)
{
if(tdts[2]<=fdts[2])
{
if(tdts[2]<fdts[2])
{
alert(msg);
document.getElementById(fid).focus();
return false;
}
if(tdts[1]<=fdts[1])
{
if(tdts[1]<fdts[1])
{
alert(msg);
document.getElementById(fid).focus();
return false;
}

if(tdts[0]<fdts[0])
{
alert(msg);
document.getElementById(fid).focus();
return false;
}

}
}
}
return true;
}
catch(e)
{
alert('--Not a valid Date(dd/mm/yyyy)');
return false;
}
}

How to import Data base Dump? - Oracle

When we set up oracle server , we should create a data base going to data base configuration tool provided by Oracle.
When a data base is created, go to command prompt and type "imp" or go to bin directory available inside oracle folder where we can find imp.exe and double click on it.
It prompts for user name and password. Where we have to provide user name of the schema that is created with the creation of data base.
Note: You can import a data base dump file into the data base shema through a user who has DBA privileges.

Once proper username/password@Databasename is provided, it prompts for the data base dump file path for which we can drag and drop the dump file available onto the prompt command window.
Once this is done, we should press enter , which continues importing data base dump file.
It also prompts for few options like to import entire dump file, to compress extents, etc...we can also observe it shows the charater set of the imported data base dump file ,destination data base character set, from user of the data base dump file etc...
Once the import is completed it automatically closes the window where user shall continue using the schema.

How to show hyperlink on mouse over?

There can be instants in web sites where when user places mouse cursor over a component like label should then display a hyper link and on click of which should open a window.

For this kind of requirement it is better to apply style sheet on mouse over of label and java script to open a window on click.

Style sheet :-

Mouse out style sheet

.lbl_mouseout
{
text-decoration:none;
color:Black;
}

Mouse over style sheet

.lbl_mouseover
{
text-decoration:underline;
cursor:hand;
color:Blue;
}


In aspx.cs page

//Javascript to handle mouse and mouse out events
lbl.Attributes.Add("onmouseover", "this.className='lbl_mouseover'");
lbl.Attributes.Add("onmouseout", "this.className='lbl_mouseout'");

//Javascript to handle on click of the label
lbl.Attributes.Add("onclick", "window.open('destinationurl.aspx'");

How to create procedure in Oracle.

Stored procedure is a data base object which has some syntax defined to make it undestand by the server to perform actions upon request.
Simillarly we have functions,triggers,views etc... called as data base objects that are once if compiled can be used to perform functionalities required.

Syntax for creating a stored procedure.

Create or Replace [Procedure Name]
(
Input/Output parameters list
)
as
[Variable declarations]
begin
[procedure body]
end;

Example:-
Procedure to find sum of 2 numbers
Create or Replace Procedure SumNos
(
a1 in number,//Input parameter
a2 in number,//Input parameter
a3 out sys_refcursor //Output parameter
)
as
tempSum number; //Variable declaration
begin
//Procedure body
tempSum:=a1+a2;
// To open a3 cursor and fill the result set from below select query
open a3 for
select tempSum as 'Sum' from dual;
end;


The above stored procedure can be copied and pasted directly in sql plus to create procedure in data base.
Note: It says create or replace which means if there exists a procedure with same name then it will be relaced with this new procedure content.