Tuesday, August 30, 2005

How many elements does the IN clause support

I had taken this quetions and reply from Best Practice PL/SQL
with Steven Feuerstein available at OTN.

How many elements does the IN clause support?
Asked by Alejandra Vergara from Somewhere in South America on Tuesday, July 27, 2004

Question:
I have a query of type select * from table1 where cod_table in ( 1,2,...,300). Does the IN clause in Oracle9i Database support more than 256 elements?

Answer:
Alejandra, in Oracle9i, you can have up to 1,000 elements in your IN list.

I take it you are worried about errors like this:

ORA-01795: maximum number of expressions in a list is 1000

And that is a valid concern....we might ask the same question about Oracle Database 10g and then the future releases as well. One way to get the answer is to check Oracle documentation, but an even better way is to check by running code.

I answered your question to my satisfaction by creating the following program:


CREATE OR REPLACE PROCEDURE test_in_limit (
table_in IN VARCHAR2
,int_column_in IN VARCHAR2
,limit_in IN PLS_INTEGER
)
IS
v_sql VARCHAR (32767);
i integer;
BEGIN
v_sql :=
'SELECT ' || int_column_in || ' FROM ' || table_in ||
' WHERE ' || int_column_in || ' IN (1';

FOR indx IN 2 .. limit_in
LOOP
v_sql := v_sql || ',' || indx;
END LOOP;

v_sql := v_sql || ')';

EXECUTE IMMEDIATE v_sql into i;
END test_in_limit;
/

(Source code available here.) I then ran it as follows without any error on Oracle9i:

BEGIN
test_in_limit ('sg_script', 'id', 1000);
END;
/

Yet here is what happened with 1,001:

SQL> BEGIN
2 test_in_limit ('sg_script', 'id', 1001);
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-01795: maximum number of expressions in a list is 1000
ORA-06512: at "QNXO_DEV.TEST_IN_LIMIT", line 24
ORA-06512: at line 2

Running this program on Oracle Database 10g has identical results.

Saturday, August 20, 2005

External Tables

Benefit of using External Tables is you can alleviates the need for staging tables and redundant flat file loading as the file expands.

1.First you need to diagnose which character you can able to define as a delimited file.
2.CREATE DIRECTORY data_dir as 'c:\test';

3.CREATE DIRECTORY log_dir as 'c:\test\log';

4.
CREATE TABLE EXTERNAL_FILE_READ
(
EMPNO NUMBER(5),
EMP_NAME VARCHAR2(80),
DEPT_ID NUMBER(5)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
RECORDs DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE log_dir:'emp_%p.bad'
LOGFILE log_dir:'emp_%p.log'
FIELDS TERMINATED BY "|"LDRTRIM
)
location
('emp.csv')
)
REJECT LIMIT UNLIMITED NOPARALLEL
/
5.select * from external_file_read;
more information on external table you can read Database Utilities guide of oracle documentation.

Encrypting Data Travell withing or outside Network Through Oracle Advanced Security Option.

Data Transfer between network in plain text through Advance Security you can encrypt it.

You must need to configure oracle server and client.
You can do this through GUI tool.
But its also simple to do through without GUI. so below I had mention steps without GUI option.

You can use EtherDetect tool to check data traveling between Server and client to check this.

Steps at sever side
1.You need to edit sqlnet.ora which you will find in oracle home, network,admin folder (directory).
2.Add below three parameters.
SQLNET.ENCRYPTION_TYPES_SERVER = (3DES168)
SQLNET.ENCRYPTION_SERVER = accepted
SQLNET.CRYPTO_SEED= retquewruq

You can set any text in crypto_seed.

Only if we can set server side this will not work mean text will not be encrypted. You must need to specify at client side sqlnet.ora file.

3.Add below three parameters which you will find at same place.
SQLNET.ENCRYPTION_TYPES_CLIENT = (3DES168)
SQLNET.ENCRYPTION_CLIENT = requested
SQLNET.CRYPTO_SEED= sdfhasfasfha

We had set encryption_client parameter to requested means during the handshake between client and server. Client will request to sever for encryption and we had set at server to be accepted so server will accept encryption.

You can find more information on parameter at oracle documentation at oracle technology site in Oracle Advance Security Administrator’s guide.

Wednesday, August 17, 2005

Tuesday, August 09, 2005

Drop Constraints.

Once i had faced problem in one table which have more than 287 duplicate constraints.

So i had buid one solution as below.

I had create one script and saved in c:\ REMOVECONSTRAINT.sql.

Contains of the script REMOVECONSTRAINT.sql as below.
SET HEADING OFF
SPOOL C:\DROPCONSTRAINT.SQL
select 'alter table 'OWNER'.'table_name' drop constraint 'constraint_name' cascade;'
from dba_constraints where owner=UPPER('&owner') and table_name=UPPER('&table_name')
/
SPOOL OFF;
@C:\DROPCONSTRAINT.SQL

if you go through with script you come to know it will generate dropconstraint.sql and execute it.

This script will remove all constraint from table.
After this you need to Re-create constraints your constraints.

Creating temporary tables

Temporary tables are created using the global temporary clause

Create global temporary table Hr.employees_temp
As select * from hr.employees;

Use of Temporary Tables

  • Tables retain data only for the duration of a transacton or session.
  • DML locks are not acquired on the data
  • Can create indexes,views and triggers on temporary tables

You can import and export the definition but you can able to import and export the data.

Demonstration

Create global temporary table tt1
(lname varchar2(25), fname varchar2(25),empno number) on commit delete rows;

desc tt1;

select count(*) from tt1;

insert into tt1 values (‘thaker’,’nikunj’,1);

select * from tt1;

commit;

select * from tt1;

create global temporary table tt2
(lname varchar2(25), fname varchar2(25),empno number) on commit preserve rows;

data reside in the table will remain in the table after commit but logs are remove of this tables on structure of the table will resides.

Select table_name,temporary from user_tables Where table_name like ‘TT%’;

Drop table tt1;

Drop table tt2;

This will gives you error because it contains row because of the table we had created with on commit preserve rows;

Because of preserve rows row will reside in the table till session is open.

So we reconnet with that user and try to

select count(*) from tt2;

there is no data now we can remove this tables.

MOVE DATA FILE FROM X LOCATION TO Y LOCATION

The section offers some procedures for renaming and relocating datafiles in a single tablespace. You must have the ALTER TABLESPACE system privilege to rename datafiles of a single tablespace.
  1. The following query of the data dictionary view DBA_DATA_FILES lists the datafile names and respective sizes (in bytes) of the users tablespace:
  1. SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';
  1. Take the tablespace containing the datafiles offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace.

ALTER TABLESPACE USERS OFFLINE NORMAL;

/*ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF' OFFLINE NORMAL;*/

  1. Copy the datafiles to their new locations and rename them using the operating system.

COPY C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF TO C:\ORACLE\ORADATA\USERS01.DBF

  1. Rename the datafiles within Oracle. The datafile pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.

If the tablespace is offline but the database is open, use the ALTER TABLESPACE ... RENAME DATAFILE statement. If the database is mounted but closed, use the ALTER DATABASE ... RENAME FILE statement.

ALTER TABLESPACE users RENAME DATAFILE 'C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF' TO 'C:\ORACLE\ORADATA\USERS01.DBF';

  1. Bring the tablespace online, or open the database. If the users tablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database.

ALTER TABLESPACE USERS ONLINE;

/*ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\USERS01.DBF' ONLINE;*/

  1. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

How to move system tablespace datafile

You can use one of two techniques as below.

  1. Shutdown, move the file, re-create the control file, and startup.
  2. Shutdown, stat up in mount mode, move them, and then open the database.

Here I am demonstrating 2nd option because its easy.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>startup mount;

ORACLE instance started.

System Global Area 122754516 bytes

---

---

---

Database mounted.

SQL> !copy ‘c:\oracle\ora92\oradata\nikunj\system.dbf’ to ‘c:\oracle\ora92\oradata\system.dbf’

SQL>alter database rename file ‘c:\oracle\ora92\oradata\nikunj\system.dbf’ to ‘c:\oracle\ora92\oradata\system.dbf’;

Database altered.

SQL>alter database open;

Database altered.

Backup your control file again, and that’s it.

Saturday, August 06, 2005

How to run Oracle listener on non-default port

You can able to run Oracle listener on non default oracle port.
open listener.ora file you can find that in oracle_home/network folder

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1577))
)
)
)

change port=

then open tnsnames.ora in the same directory.
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1577))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

mention same port no.

Now oracle listen on non-default oracle port.

Also you can run multiple listener for one database.
change your listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1577))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
)
)
)


I had asked thomas kyte for proc and cros there input as below.
Hi Tom,

If two listener on two port i.e. 1521 and 1522 are listening for one (X
database) database.

What are the pros and cros if one database have 2 listener and have 1 listener ?

Regards,
Nikunj

Followup:
pros - extreme cases.

cons - extra work, maintainance, monitoring.

if and only if a single listener was getting so nailed, hit so hard, that it
built up a queue of requests and started failing the requests because the queue
was too long would I consider another listener.

And even then, it would likely be on another machine, for another instance,
using the same database - if you are nailing a listener that hard....

Tuesday, August 02, 2005

Worksheet: Windows Server 2003 default services

Click above link.
Microsoft has made several key changes to Windows Server 2003. One of the best ways for admins to evaluate these changes is to examine the core services of the OS. This Excel worksheet breaks down the default services installed in Windows Server 2003.

TO RUN A COMMAND FROM ORACLE FOR ORACLE 9I

/* RUN BELOW SCRIPT LOGIN WITH SYS ACCOUNT */
begin
dbms_java.grant_permission
('TIMETRACK',
'java.io.FilePermission',
'C:\WINNT\*',
'execute');
dbms_java.grant_permission
('TIMETRACK',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
end;
/
/* RUN BELOW SCRIPT LOGIN WITH APPLICATION USER */
create or replace and compile
java source named "Util"
as
import java.io.*;
import java.lang.*;
public class Util extends Object
{
public static int RunThis(String[] args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;
try
{
Process p = rt.exec(args[0]);
int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);
rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/

create or replace
function RUN_CMD( p_cmd in varchar2) return number
as
language java
name 'Util.RunThis(java.lang.String[]) return integer';
/
create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
x := run_cmd(p_cmd);
end;
/
variable x number;
set serveroutput on
exec dbms_java.set_output(100000);
exec :x := run_cmd('net send nikunj test');
10514.ksh
10565.sql
...
xxx.dat
xxx.dbf
zz

Database Cloning

How to create database base on existing database.
You can able to clone database in same machine using Oracle Database Configuration Assistant but what about to copy database from Production to Test Server.


So I had done this through below way.
I have 2 Oracle Database EE 9.2 on Windows 2000 Server.
I had done below task on my A server.

CREATE PFILE=’d:/oracle/ora92/inittest.ora’ FROM SPFILE;
shutdown the database instance.
make a copy of oracle database files from oracle/ora92/oradata//*.* to temporary location including your PFILE so your database will not be down for more time.

I had done below taks on my B server.
1) create directory in oracle/ora92/oradata/ ie. Test
2) copy all files from temporary location where u had copy in A server. To ur directory in above steps.
3) go to command prompt.
set ORACLE_SID=TEST
create windows service through below command
ORADIM -new -sid babul92 -intpwd oracle -startmode manual
or
ORADIM -new -sid TEST -intpwd password -startmode auto -pfile
%ORACLE_HOME%\DATABASE\INITTEST.ORA
4) Start the above service. (if you mention with –startmode auto no need to start it manually It will start automatically after that command)
5) lsnrctl stop
6) add below entry into listener.ora (which u find in D:\oracle\ora92\network\admin)
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = test)
)
7) lsnrctl start
8) Create test directory into D:\oracle\admin (where test is your instance name)
9) open D:\oracle\admin\test directory & create below directory in it.
bdump
cdump
create
pfile
udump
10) go to command prompt and enter sqlplus /nolog
CONNECT SYS/password AS SYSDBA
-- create the server parameter file
CREATE SPFILE='d:/oracle/ora92/database/spfilemynewdb.ora' FROM
PFILE='d:/oracle/ora92/database/initmynewdb.ora';
startup

Note : if you get error at time of startup you can create password file through below command
orapwd file=d:\oracle\ora92\database\pwdtest.ora password=oracle
Now if you wish to set oracle database should start automaticaly when ur service start (if u choose mannual in steps 3) then follow below steps.
1) Open Administration Assistant for Windows NT which u find in Oracle - OraHome92 -> Configuration and Migration Tools
2) Double click on Oracle Managed Objects then Computers then nikunj then Databases single click on your SID (TEST)
3) Right click on it and select Startup/Shutdown options.
4) check mark Start up instance when service is started.
orapwd file=d:\oracle\ora92\database\pwdtryguard.ora password=oracle
CONNECT SYS/password AS SYSDBA
-- create the server parameter file
CREATE SPFILE='d:/oracle/ora92/database/spfilemynewdb.ora' FROM
PFILE='d:/oracle/ora92/database/initmynewdb.ora';
SHUTDOWN

-- this time you will start up using the server parameter file
CONNECT SYS/password AS SYSDBA
STARTUP
EXIT
orapwd file=d:\oracle\ora92\database\pwdtryguard.ora password=oracle


IF database in different directory then
I have 2 Oracle Database EE 9.2 on Windows 2000 Server.
I had done below task on my A server.
CREATE PFILE=’d:/oracle/ora92/inittest.ora’ FROM SPFILE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
This will create control file statement in your server trace file (at last) i.e.
D:\oracle\admin\nikunj\udump
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "NIKUNJ" RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'D:\ORACLE\ORADATA\NIKUNJ\REDO01.LOG' SIZE 100M,
GROUP 2 'D:\ORACLE\ORADATA\NIKUNJ\REDO02.LOG' SIZE 100M,
GROUP 3 'D:\ORACLE\ORADATA\NIKUNJ\REDO03.LOG' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLE\ORADATA\NIKUNJ\SYSTEM01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\UNDOTBS01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\CWMLITE01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\DRSYS01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\EXAMPLE01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\INDX01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\ODM01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\TOOLS01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\XDB01.DBF'
CHARACTER SET WE8MSWIN1252
;
# Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
# Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''D:\oracle\orabackup\%F''');
# Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT ''d:\oracle\orabackup\backup_db_%d_S_%s_P_%p_T_%t'' MAXPIECESIZE 250 M');
# Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('MAXSETSIZE TO','UNLIMITED');
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\NIKUNJ\TEMP01.DBF'
SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
# End of tempfile additions.
#
shutdown the database instance.
make a copy of oracle database files from oracle/ora92/oradata//*.* to temporary location including your PFILE so your database will not be down for more time.
I had done below taks on my B server.
1) create directory in oracle/ora92/oradata/ ie. Test
2) copy all files from temporary location where u had copy in A server. To ur directory in above steps.
3) go to command prompt.
set ORACLE_SID=TEST
create windows service through below command
ORADIM -new -sid babul92 -intpwd oracle -startmode manual
or
ORADIM -new -sid TEST -intpwd password -startmode auto -pfile
%ORACLE_HOME%\DATABASE\INITTEST.ORA
4) Start the above service. (if you mention with –startmode auto no need to start it manually It will start automatically after that command)
5) lsnrctl stop
6) add below entry into listener.ora (which u find in D:\oracle\ora92\network\admin)
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = test)
)
7) lsnrctl start
8) Create test directory into D:\oracle\admin (where test is your instance name)
9) open D:\oracle\admin\test directory & create below directory in it.
bdump
cdump
create
pfile
udump
10) go to command prompt and enter sqlplus /nolog
1. CONNECT SYS/password AS SYSDBA
-- create the server parameter file
2. CREATE SPFILE='d:/oracle/ora92/database/spfilemynewdb.ora' FROM
PFILE='d:/oracle/ora92/database/initmynewdb.ora';
3. paste your edited control file create script.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "NIKUNJ" RESETLOGS
ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'C:\ORACLE\ORADATA\NIKUNJ\REDO01.LOG' SIZE 100M,
GROUP 2 'C:\ORACLE\ORADATA\NIKUNJ\REDO02.LOG' SIZE 100M,
GROUP 3 'C:\ORACLE\ORADATA\NIKUNJ\REDO03.LOG' SIZE 100M
DATAFILE
'C:\ORACLE\ORADATA\NIKUNJ\SYSTEM01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\UNDOTBS01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\CWMLITE01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\DRSYS01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\EXAMPLE01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\INDX01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\ODM01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\TOOLS01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\XDB01.DBF'
CHARACTER SET WE8MSWIN1252
;
ALTER DATABASE OPEN RESETLOGS;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\NIKUNJ\TEMP01.DBF'
SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Shutdown immediate;
Startup;

Covert Number to word for indian

create or replace function spell_indian_money
( p_number in number )
return varchar2
as
type myArray is table of varchar2(255);
l_str myArray := myArray( ' Thousand ',
' Lakh ',
' Crore ',
' Arab ',
' Kharab ',
' Shankh ' );
l_num varchar2(50) default trunc( p_number );
l_return varchar2(4000);
d_count number;
begin
if ( substr(l_num, length(l_num)-2, 3) <> 0 )
then
l_return := to_char(
to_date(
substr(l_num, length(l_num)-2, 3),
'J' ),
'Jsp' );
end if;
l_num := substr( l_num, 1, length(l_num)-3 );

for i in 1 .. l_str.count
loop
exit when l_num is null;
if ( substr(l_num, length(l_num)-1, 2) <> 0 )
then
l_return := to_char(
to_date(
substr(l_num, length(l_num)-1, 2),
'J' ),
'Jsp' ) || l_str(i) || l_return;
end if;
l_num := substr( l_num, 1, length(l_num)-2 );
end loop;

if to_char( p_number ) like '%.%'
then
l_num := substr( round( p_number, 2), instr( p_number, '.' )+1 );

/* here i had done my modifcation */
if (length(substr( round( p_number, 2), instr( p_number, '.' )+1)))=1 then
l_num:=to_number(to_char(l_num)||'0');
end if;
/* here i had completed my modifcation */
if l_num > 0
then
l_return := l_return || ' And '
|| to_char(
to_date(
l_num,
'J' ),
'Jsp' )
|| ' Paise';
end if;
end if;

return l_return||' Only.';
end spell_indian_money;
/

Oracle Database 10g Release 2 (10.2.0.1) Installation On RedHat Advanced Server 4.0

A brief guide to installing Oracle 10g Release 2 (10.2.0.1) on RedHat Advanced Server 4.0

Oracle Database 10g Release 2 (10.2.0.1) Installation On Fedora Core 4

A brief guide to installing Oracle 10g Release 2 (10.2.0.1) on Fedora Core 4.

Build Your Own Oracle RAC 10g Cluster on Linux and FireWire

by Jeffrey Hunter
Learn how to set up and configure an Oracle RAC 10g development cluster for less than US$1,800.

Oracle Database 10g Release 2 (10.2.0.1) Installation On RedHat Advanced Server 3.0 at Oracle-Base

A brief guide to installing Oracle 10g Release 2 (10.2.0.1) on RedHat Advanced Server 3.0.