How to connect to a Db via sqlplus without editing tnsnames.ora?

I've not been given admin privilege to edit the tnsnames.ora file in my pc.I had been facing difficulties to connect to other dbs which are not in my tnsnames.ora file. So two methods given to overcome this >10g .(Toad has implemented this feature)
(1). Use,
sqlplus user_name/password@//host/service_name
Eg: > sqlplus scott/tiger@//192.168.161.66/orcl
you may get "ORA-12154: TNS:could not resolve the connect identifier specified"
Check u have added/uncommented following line in sqlnet.ora which is at \11.1.0\client_1\network\admin
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
(2). Use, Here it's not necessary to have above entry in sqlnet.ora file.
sqlplus scott/tigger@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.161.66)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(service_name=orcl)))'
Note: No line breaks, No spaces and No semicolon(;) at the end
Reference : Oracle Magazine July/August 2010

How to Call a C program in PL/SQL?

For the DB installed in Linux platform
Compiled the C code and generated .so file
gcc -o calc_tax.so -shared calc_tax.c

Step1: Conn scott/tiger@orcl

Step2: CREATE OR REPLACE LIBRARY c_lib
AS
'/tmp/calc_tax.so';

Note : You should have CREATE LIBRARY privelege

Step3: CREATE OR REPLACE FUNCTION tax_amt(x BINARY_INTEGER)
RETURN BINARY_INTEGER
AS LANGUAGE C
LIBRARY c_lib
NAME "calc_tax";

calc_tax.c is given below
-----------------------------------------------------
calc_tax(n)

int n;

{

int tax;

tax = (n*8)/100;

return(tax);

}



main()

{

int tot_ord;

printf("Enter the Order Total ");

scanf("%d",&tot_ord);

printf("\n%d", calc_tax(tot_ord));

printf("\n\n");

}



Step4: SELECT tax_amt(10000)
FROM DUAL;

How to use UNICODE characters in PL/SQL?

We had a requirement to write a pl/sql function to return strings in sinhala/tamil characters. So just copying and pasting the sinhala/tamil characters in the function body don't work. Hence we need to use unicode characters for this. just using unicode characters not sufficient. Should use the
UNISTR function.
Eg:
UNISTR('\0DB1\0DDC:') returns අංක: (Sinhala)
UNISTR('\0B87\0BB2:') returns இல: (Tamil)

Although the stored unicode characters values be taken as
ASCIISTR(column_name)

Virtual Columns in Oracle 11g

Very exiting feature of Virtual columns are introduced in Oracle 11g. Have a look. Click Here

Recyclebin in Oracle 10g

The recyclebin concept is introduced in oracle 10g onwards . So far we have been knowing that the DDL commands are auto commited and cannot be recovered as a normal user. But the oracle 10g onwards the recyclebin is introduced like we have in windows.
The user_recyclebin and dba_recyclebin data dictionaries can be used to view the deleted contents. Let's see the following example to understand this concept.

SQL> create table test
     ( id number,
      name varchar2(30));

SQL> insert into test values ( 1,'name1');

SQL> select *
     from test;

 ID       NAME
------------------
 1        name1

Let's drop the table

SQL> drop table test;

Now this table goes into the recyclebin. (Here recyclebin is a synonym for user_recyclebin )

SQL> select *
      from recyclebin;

OBJECT_NAME                       ORIGINAL_NAME   DROP_TIME
----------------------------------------------------------
BIN$b5sG6wgurG3gQAnAoNwPOA==$0    TEST            2009-07-26:16:50:38

If you want to get back(restore) then

SQL> flashback table test to before drop;          

Now you can view the test table.

If you want to drop the table permenantly without going to the recyclebin then issue

SQL> drop table test purge;        

If u know "system" users' password then U know every users password in oracle 11g...

We all know that the fact all the users' passwords are encripted in the oracle database. And that could be decripted if you know the system users' password only .This is due to sys.user$ dectionary has to be queried to get the encripted password.Here we go..

Step 1: Login as a system user.

SQL> conn system/manager@orcl

Step 2: Execute the following query.

SQL> SELECT name,password,spare4
FROM sys.user$
WHERE name ='CRS' ;

Step3 : Download the password cracker

Step4 : Fill the username,OLDSK3WLHASH(ie password) and SPARE4HASH(ie spare4) and press the start button.

Now you will get the exact password. Hope Oracle will come up with new technology where no one can decrypt at all. So guys be carefull with your DBAs.

How To Call a JAVA Program from PL/SQL?

Step1: Create the JAVA Class ( Here Factorial.java )

public class Factorial
{

public static int calcFactorial(int n)
{
if(n==1)
return 1;
else
return n * calcFactorial(n-1);
}

}


Step2: Load the JAVA Class into the DB ( Using the command prompt)
> loadjava -user scott/tiger@c920 E:\Factorial.java
Or
> loadjava -user scott/tiger@c920 E:\Factorial.class ( If it’s a class file)

Step3: Verify the Java source has been loaded or not by
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_TYPE LIKE ‘JAVA%’;
You Could see the source code by
SELECT text
FROM USER_SOURCE
WHERE NAME=’ Factorial’;

Step4 : Create a Procedure or Function that could call the JAVA method

CREATE OR REPLACE FUNCTION calc_fact( p_no NUMBER)
RETURN NUMBER
AS
LANGUAGE JAVA
NAME ‘Factorial. calcFactorial (int) return int’ ;