ORA-06052: PL/SQL: numeric or value error on PL/SQL Collections First and Last

Cause:
The error ORA-06052: PL/SQL: numeric or value error would be found when using for loop on the type collection object, as shown below, and the collection is empty:

FOR i IN type_variable.FIRST..t_variable.LAST LOOP
     ----your code
End Loop ;

When the collection is not empty, error ORA-06052: PL/SQL: numeric or value error would not be found.
This is because FIRST and LAST return the first and last (smallest and largest) index numbers in a collection that uses integer subscripts.


Solution:

Use  FOR i IN 1..t_variable.count instead of FOR i IN type_variable.FIRST..t_variable.LAST , and also apply exception block.

BEGIN
      FOR i IN 1..t_variable.count
           ----your code
      End Loop;
EXCEPTION WHEN OTHERS
     -- error handling code
END; 

Read More

Migrate SUSE Linux to Oracle Linux

If you are thinking of migrating your SUSE to Oracle Linux, there are things to think of and things to be prepared before you do it.

The preinstallation process and pro installation process need to be prepared. Below is the link to get information on the migration. Please read it before you do it.

http://www.oracle.com/technetwork/articles/servers-storage-admin/dell-linux-1498654.html?ssSourceSiteId=ocomen

Read More

The Reason to Upgrade to Oracle 11g Release 2

If you are using Oracle 9i, 10g, and decide to move forward to use 11g, below are what will you get form the 11g R2 upgrade:-

Oracle Database 11g Release 2, the latest release of the award-winning Oracle
Database 11g, enables IT professionals to deliver more information with higher quality of
service, make more-efficient use of their budgets, and reduce the risk of change in
datacenters. By deploying Oracle Database 11g Release 2 as their data management
foundation, organizations can utilize the full power of the world’s leading database to

• Reduce server costs by a factor of 5.

• Reduce storage requirements by a factor of 10. 

• Improve mission-critical system performance by a factor of 10.

• Increase DBA and developer productivity by a factor of 2.

• Maximize availability and eliminate idle redundancy.

• Maximize security and enable compliance.

• Simplify their overall IT software portfolio.

For more information, please visit http://www.oracle.com/us/products/database/039448.pdf.

Read More

How many database Indexes is too many?

Having a lot of indexes on your table will affect the performance of your table either bad side or good side. It depends on what operation you use most.
       
Index will help fasten your query, and at the same time might slow your query down. When you do a lot of insertion, update, deletion based on the index columns, this will slow the query down. On the other hand, it will also help with select statement.

So how many indexes you should have just depends on the characteristic of statement you will use. If you have a lot of insert, update, delete statement, then too many indexes is not so good. While having most of query as select statement, then it is worth having many of them.

Read More

ORA-06512: at line

ORA-06512: at stringline string

Cause: Backtrace message as the stack is unwound by unhandled exceptions.

Action: Fix the problem causing the exception or write an exception handler for this condition. Or you may need to contact your application administrator or DBA.
PostingPosting

---

Read More

For Loop in PL/SQL

One of the statement  in oracle I use most is for loop in pl/sql. This for loop is very handy to use and the basic statement comes like this:


for variable in 1..x 
loop
   statement;
end loop;


The reverse loop statement is:

for variable in reverse 1..x 

loop

   statement;

end loop;

The example code comes like this

for v_cnt in 1 .. 100

       

loop

insert into table_x (pk, text) values (v_cnt, 'count_' || v_cnt);

end loop;

    for v_cnt in reverse 1 .. 100

loop

insert into table_x (pk, text) values (v_cnt, 'count_' || v_cnt);

end loop;

For in pl/sql could also work with cursor, this make handy as I said before. I like to use for loop in pl/sql with cursor so I don't have open and close the cursor, the for loop will do it automatically. Here is how for loop in pl/sql works:


for c_cur in (select statement)
loop
  statement;
end loop;

for c_cur in c_name 
loop
  statement;
end loop;


Where c_name is the declared cursor and c_cur is the variable (you don't need to declare it).

For example:


for c_cur in (select a, b from tab_x) 
loop
  insert into tab_y values(c_cur.a, c_cur.b);
end loop;



for c_cur in declared_cursor
loop
  insert into tab_y values(c_cur.a, c_cur.b);
end loop;

Read More

PL/SQL Best Practices

PL/SQL Best Practices with Steven Feuerstein

Renowned Oracle PL/SQL expert, Steven Feuerstein, presents "PL/SQL Best Practices" -- offering high-level principles to guide our work.

Here are some example of the PL/SQL best practices derived from PL_SQL_Best_Practices2.pdf:


DEV-01: Set Standards and Guidelines for Your Application Before Anyone Starts Writing
DEV-02: Ask for Help if you find Yourself Spending More Than 30 Minutes to Solve a problem
DEV-03: Make Code Review a Regular Part of Your Development Process
DEV-04: Validate Standards by using SQL to Analyze Source Code Stored in the Database
DEV-05: Generate Code Whenever Possible and Appropriate
DEV-06: Set Up —and Use! —A Formal Unit Testing Procedure
DEV-07: Get Someone Else to Perform Functional Tests on your Code

Read More

Database Change Tracking for Oracle

It has often been said that the only constant is change. IT's ability to manage change is an important business success criteria in today's competitive markets. Change can either be planned or unplanned. This presentation will help you understand the effects of change, and how to manage change properly. It will help database administrators better prepare to implement changes and measure the positive (or negative) affects those changes produce on the system.

Read More

Automating SQL Optimization

Quest® SQL Optimizer for Oracle proactively identifies potential performance issues and automates SQL optimization by scanning and analyzing running SQL statements, PL/SQL, or other source code. * The value of SQL tuning to you and your organization * What the ideal solution looks like * How to use the key functions and new features in Quest SQL Optimizer for Oracle v7.2

Read More

Common Mistakes in Oracle PL/SQL Programming

I just came a cross this video about oracle pl/sql programming, which provide good technique to write pl/sql code.
Very good video to take a look for pl/sql programmer.



You wrote an explicit cursor? You declared a variable using the VARCHAR2 datatype? You raised an exception to skip over unnecessary lines of code?

If slip-ups like these sound familiar (or if you don't know why these are mistakes), join Quest for a one-hour Webcast featuring guest speaker, Steven Feuerstein, as he examines common mistakes in Oracle PL/SQL programming. Learn how to correct mistakes to improve the maintainability and performance of your application.

This Webcast will also demonstrate CodeXpert — a powerful feature available within Toad® that will help you avoid common mistakes and ensure you implement PL/SQL best practices in your daily activities.

About the Guest Speaker:
Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language, having written nine published books on PL/SQL (all from O'Reilly & Associates). Steven has been developing software since 1980, spent five years with Oracle (1987-1992) and serves as a Senior Technology Advisor to Quest Software.

Read More

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Cause:
The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.


Solution:
1. Check your tnsnames.ora as shown in my post on ora-12154.
2. Check if your listener service are on, if not start your lisnter using lsnrctl. More information on this take a look at the same post at ora-12154. 
3. Check if the service is on using command line lsnrctl services
4. Check your listener.log 

Read More

ORA-12154: TNS:could not resolve the connect identifier specified

Cause:
A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.

Solution:
1. Check your tnsnames.ora.
Check your tnsnames.ora if it is configured property the key to check is SID, dba host name or ip address. The syntax are something similar this this:

ORA_TNS =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA-10)
 )
)

2. Check your listener.ora if sid_list_listenner setup is there.
By Default when you install new oracle database the listener.ora would contain only listener configuration section but no sid_list_listenner setup. In many occasion when I have this ora-12154 problem the solution that help me is modifying listener.ora, adding SID_LIST_LISTENER section the syntax should be something similar to the below syntax:

SID_LIST_LISTENER =

   (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1)
      (SID_NAME = ORA-10)
    )
  )

The complete file are as shown below

# listener.ora Network Configuration File: /u01/app/oracle/product/10.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1)
       (SID_NAME = ORA-10)
     )
   )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

After adding SID_LIST_LISTENER in your listener.ora, restart or reload your listener service. I prefer using command line lnsrctl follow by parameter reload or restart, or you can just type lnsrctl and then enter and type reload or restart. With this command line (lsnrctl) you can also check oracle listener status by enter parameter status. 

3. Check the environmental variables
Check if the environmental variables like ORACLE_HOME, ORACLE_SID, ORACLE_BASE, ORACLE_PATH do exist. The most important environmental variables are the first two variable ORACLE_HOME, ORACLE_SID.

Tools
The very handy tool you can use to check the connection is the command tnsping follow by your service name configured in your tnsnames.ora from the example above is ORA_TNS.

Read More

SQLLoader with text file format

When loading text file into db via sqlloader, there might be the case that varchar2 column with Thai character data turn out weird (i.e. instead of showing "รถยนต์นั่งไม่เกิน 7 คน" it turn out to be "เธžเธฃเธš เธฃเธ–เธขเธ™เธ•เนŒเธ™เธฑเนˆเธ‡เน„เธกเนˆเน€เธเธดเธ™ 7 เธ„เธ™").

The cause of this problem, from what I experienced, is that the format/encoding of text file is invalid.
To solve this you need to open the file with your fave text editor, then use "save as" menu and choose file format/encoding as ANSI/ASCII (default one is the current format/encoding of the file).

Read More

Save your foreign key constraint before dropping table

There might be the case that you find it difficult to recreate the table with another table having foreign keys dependent on it. When you need to recreate the table that mean you need to drop that table first. Apart form losing data and other objects related to that table you will also lost foreign key constraint on every tables which referring to it. There is a little tip top help you get all those foreign key constraints back with an ease, below are a few step to do it.

1. Before dropping the table run the below script which will prompt you to input the table you are about to drop and keep the output.

select 'alter table ' || cc.table_name || ' add constraint ' || c.constraint_name || ' foreign key(' ||
cc.column_name || ') references ' || cc_r.table_name || '( ' || cc_r.column_name || ');'
from user_constraints c, user_cons_columns cc, user_cons_columns cc_r
where c.constraint_type = 'R' and
cc.constraint_name = c.constraint_name and
cc.table_name = c.table_name and
cc_r.constraint_name = c.r_constraint_name and cc_r.TABLE_NAME = '&ref_tab';

2. drop the desired table
3. recreate that table
4. run the output scripts you get from number 1

Read More

solaris UX: useradd: ERROR: Inconsistent password files. See pwconv(1M).

When trying to add oracle user on unix or linux there might be the case that you get the following error:
solaris UX: useradd: ERROR: Inconsistent password files. See pwconv(1M).

Here are steps to fix this problem, and what causes this error.
The reason behind this error is that the /etc/passwd and /etc/shadow files are out of synchronization on your machine.
To fix this, run the following command

• pwconv to check if there is any error, if there is/are fix that/those error(s).
• wc -l /etc/passwd /etc/shadow to check if both password and shadow file are synchronized, the out put will be
  
  x /etc/passwd
  
  y /etc/shadow
  
  x+y total
  
  where x, y is the number of lines in passwd and shadow file, and the total line would be the sum of lines in passwd and shadow file.

Read More