SQL Doesn’t Like Logical Files

Today’s tip: Don’t put DDS-defined logical file names in your SQL statements.
To understand why, let me give you a little background. When you execute an SQL command, the system determines the best way to carry out your request. That is, you concentrate on the task that needs to be done, and the system figures out [...]

Read Full Post »

More about SQL and Logical Files

This week’s tip: In your QAQQINI file, set IGNORE_DERIVED_INDEX to *YES.
So what does that mean? First, QAQQINI is a file that you can use to control query processing. If you’re not familiar with it, follow the links at the end of this article to learn about it.
At this point, I assume you know what QAQQINI [...]

Read Full Post »

Indian Currency Converter

create or replace function spell_number( p_number in number ) return varchar2
          as type myArray is table of varchar2(255);
          l_str myArray := myArray( ”, ‘ thousand ‘, ‘ million ‘, ‘ billion ‘, ‘ trillion ‘, ‘ quadrillion ‘, ‘ quintillion ‘, ‘ 
          sextillion ‘, ‘ septillion ‘, ‘ octillion ‘, ‘ nonillion ‘, ‘ decillion ‘, [...]

Read Full Post »

Tips about Stored Procedures

To pass the parameter to stored procedure to get it executed at the run time,
param2 varchar2(50);
BEGIN keyword as param2 varchar2(50);
param2 := chr(39)||replace(param1,’,’,”’,”’)||chr(39);
select * from hr.employees where job in (param2);
as
select * from hr.employees where job in (param2)

Read Full Post »

Database server consolidation

A Nice article about database server consolidation
The best bet is to start by completing a Server Consolidation Worksheet like the sheet provided by Microsoft to get a better idea of the consolidation opportunities. Microsoft has provided a SQL Server consolidation Worksheet to assist with the process of consolidating SQL Servers and it can be found [...]

Read Full Post »

Way to find the SID of the database

In $ORACLE_HOME\NET80 or NETWORK\ADMIN,  
   Open the file tnsnames.ora and it can show some entries as mentioned
    below:
    SIDSAMPLE (DESCRIPTION (ADDRESS = (PROTOCOL = TCP)(HOST =  
                             193.156.145.121)(PORT = 1521)) (CONNECT_DATA = (SID =          
                              XYZ)) )
Here    SIDSAMPLE is the SID you have to provide while connecting forms to db.

Read Full Post »

MS-SQL’s built-in replication capabilities

MS-SQL’s built-in replication feature is designed to publish periodically updated, read-only copies of selected data. It is not designed to provide data protection or application availability to the entire database.
The built-in disaster recovery capability provided by MS-SQL is called ‘log shipping’. This technique assumes that a recent copy of the database exists, possibly on tape, [...]

Read Full Post »