Ask Anantha

Here is what Oracle documentation says about this error:     ORA-06502: PL/SQL: numeric or value error string   Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER (2).   Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.     More Information: These could be the errors: This error mainly occurs due to one of the following: 1. If you assign NULL value to a NOT NULL field. 2. If you assign number which is larger than the precision of the field. 3. If you assign character values greater than the field width. Click here to read more on solving this error  

How to solve this error? 1. Find out which application is causing this error. Zero down on which package/procedure is loaded and try to keep it in shared pool by pinning it. 2. Sometimes, the application may not give the errors. In which case, set an event in init.ora, as follows and generate a trace file. event = "4031 trace name errorstack level 2" or with 9i and higher and spfiles you can issue alter system set events='4031 trace name errorstack level 2'; What to look for in the trace? The trace contains a dump of state objects, when the error occurs. Look for 'load=X' a few lines below that 'name='[name of the object]. So, this error occurs at the time loading this object. Pin that object in the shared pool, thereby keeping it. How to Pin objects to shared Pool? Pinning objects to the shared pool is a key to tuning your shared pool. Having objects pinned will reduce fragmentation and changes of encountering the ORA-0403...

Covering Developer version 9i for Windows NT/2000/XP By Richard Holowczak This tutorial introduces the Oracle9i Developer Suite Release 2 (August, 2002) that includes Oracle Forms 9.0 and Oracle Reports 9.0. The main objectives are to demonstrate and provide hands-on instructions on creating and modifying data entry and query forms in various configurations, reports and graphics. Caveats: Please note that Oracle tends to change things like menu items, prompts and other small things between each major (certainly) and often minor release. Depending on the exact release of Oracle9i Developer Suite you have, you may find some small discrepencies between what is shown in this tutorial and what you see on your screen. Prerequisites Before following this tutorial, a student must have a valid user account in an Oracle server or a local installation of an Oracle database. Contact your DBA or systems administrator to learn the details of how Oracle server is set up in your organization. If yo...

*** SOLVED *** We have a linux server in our office, and yesterday the machine got restarted (power failure). We were able to bring up our Oracle database server afterwards but there was some problems with temporary tablespace. We were running a procedure when we encountered this error: ORA-25153: Temporary Tablespace is Empty Once querying dba_temp_files view, there were no temp files being listed. Though the temporary file was residing in the oradata folder and the dba_tablespaces where the tablespace was present.  The Solution: As it was a temporary tablespace, I could add a tempfile to the same and made the procedure working: ALTER TABLESPACE temp ADD tempfile '/fullpath.dbf' SIZE 50M; The error ORA 25153 got solved. NOTE: Do not give the same name as the existing file in oradata folder. Let the file REST IN PEACE, give a new name. For example if you have temp01.dbf existing create with temp02.dbf.

Oracle Universal Installer by default installs any oracle products in its GUI mode. In situations where the GUI could not be started, it is not possible to install Oracle, such as in Linux environments if X Server could not be started it is difficult to install any Oracle products. Moreover for a DBA, it is very cumbersome to sit and click buttons for installation. For automating purposes, Oracle Universal Installer (OUI) provides a silent mode of installation. The runInstaller script which is used for calling the OUI has some switches which can be used to achieve this functionality. ./runInstaller -record -destinationFile   ./runInstaller -silent -responseFile Here is how: First Step ./runInstaller -record -destinationFile ResponseFile.txt The record parameter tells the installer to write to the response file and the destinationFile parameter defines the name of the response file. Once the response file is created you can run the installer in silent mode using the following command:...

The Oracle Internet Application Developer Release 6i Certified Professional (OCP) certification and its related exams will be retired effective April 30, 2009 . Exams for this certification may taken up to this date, after which time they will no longer be available through Prometric or any other avenue. Those working with Oracle PL/SQL and Forms should consider the newer Oracle PL/SQL Developer Certified Associate (OCA), Oracle Advanced PL/SQL Developer Certified Professional (OCP), and Oracle Forms Developer Certified Professional (OCP) certifications. EXAM RETIREMENTS: Oracle will retire the following four related exams: 1Z0-001 – Introduction to Oracle: SQL and PL/SQL 1Z0-101 – Develop PL/SQL Program Units 1Z0-131 – Build Internet Applications I 1Z0-132 – Build Internet Applications II Exams 1Z0-001 and 1Z0-101 are recognized toward several Oracle certifications that are not being retired. Exams passed prior to April 30, 2009 will be accepted and will count towards any ...

I got a following query from Suvankar Das. "I need to unload few Oracle tables with average data volume of 15M records to flat files. Is the SQL* Plus spool a good option for this volume of data from performance point of view OR is it better to do through PL/SQL UTL_FILE option." And this is how I responded: "SQL* Plus will have the fastest result to the flat file. Only use UTL_FILE if you need some more programmatic control in between the spooling. To make sure first take a sample set of data (some thousands of records) for spooling using SQL *Plus and do the same using UTL_FILE. Compare both the timings. Also based on the sample estimate the time for the entire 15M records." The Verdict: There is no hard rule that says either SQL *Plus or UTL_FILE will perform faster. It varies from environment to environment. Where SQL *Plus/UTL_FILE could perform faster SQL *Plus is a client program which is accessing Oracle database. Therefore it highly depends on the network. ...

This is a step-by-step guide on the simplest of the simplest ways by which data can be uploaded from CSV file to Oracle table. I have done uploading using this way for as many as 10,000 records without any performance issues. It went very swiftly. Login to OracleXE first and follow the instructions below: Step 1 - Click Utilities Step 2 - Click Data Load/Unload Step 3 - Click Load Step 4 Step 5 - Click Load Spreadsheet Data Step 6 - Follow the screen Step 7 - Follow the screen Step 8 - Follow the screen Step 9 - Follow the screen Step 10 - Follow the screen

Today I was answering one of the queries, this question seriously made my interest. The question was "I have 15 million records in my table. What do you suggest a best method for unloading them from Oracle table to a flat file". Seriously till that time I had never done this. The need had not come. I clearly know two ways of doing this: Using SQL Plus and SPOOL command Using UTL_FILE built-in package UTL_FILE vs SPOOL I know that SPOOL command will be faster in execution than UTL_FILE package , but also I reminded myself that this may not be the case always. SPOOL commands operates in the client machine and depending upon the network traffic it might take time for the SPOOL operation than UTL_FILE.  SO I ADVICE TO TEST BOTH OPTIONS IN YOUR ENVIRONMENT BEFORE FINALISING ON ONE OF THE ABOVE. I will soon publish routines for this purpose. Your patience is appreciated. More resources for your inquisitive mind A little surfing did a wonderful job in putting this article up. In ask...

This article is a continuation on Making Oracle Case Insensitive . Read that first to understand the context discussed here. Oracle is always Case Sensitive while querying data. Now we have learned how to make Oracle Case Insensitive for queries. Let us ask some questions to get going. What is the purpose of making Oracle Case Insensitive? By making Oracle Case Insensitive, you need not any more use Case mixed words. For example Test, TEST, tESt are all same once you have made Oracle Case Insensitive. Its use could be in Data warehousing applications. How to make Oracle Case Sensitive back again after this? You have to alter two session paramaters NLS_COMP and NLS_SORT. First set NLS_COMP to binary. ALTER SESSION SET nls_comp=binary; Secondly set NLS_SORT to generic. ALTER SESSION SET nls_sort=GENERIC_M_CI; Once these variables are altered Oracle is back in business as case sensitive.

Download Now 68.4 MB Oracle's description of SQL Developer Oracle SQL Developer is a free and fully supported graphical tool for database development. With SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own. SQL Developer enhances productivity and simplifies your database development tasks. Screen shot Click on the image to view Documentation Click here to access SQL Developer Documentation

To compile an entire schema in Oracle, there are two utilities provided by Oracle. DBMS_UTILITY.COMPILE_SCHEMA Two procedures in UTL_RECOMP DBMS_UTILITY.COMPILE_SCHEMA This package prior to 10g would recompile all objects (prior to Oracle 10g) and optionally recompile all Invalid Objects (starting from Oracle 10g). This procedure is available with all schemas (there is no need for giving additional privileges). I am using Oracle 10g (10.2.0.3.0) for the following examples. Syntax: DBMS_UTILITY.COMPILE_SCHEMA( schema VARCHAR2, compile_all BOOLEAN, reuse_settings BOOLEAN); Example (from SQL *Plus): EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'SCOTT', compile_all => FALSE); For recompiling all objects irrespective or VALID or INVALID use the following example (simply omit the compile_all parameter): EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'SCOTT'); UTL_RECOMP Package This package has been introduced from Oracle 10g Release 2 , which is ...

There are two ways of renaming a table in Oracle: Method 1: Simple rename {old_table_name} to {new_table_name} Example: rename CUSTOMER to CUSTOMER_BACKUP Method 2: Not so Complex alter table {old_table_name} rename to {new_table_name}; Example: alter table CUSTOMER rename to CUSTOMER_BACKUP; The minimum version that supports table renaming is Oracle 8i . All the dependencies of the table will automatically updated. No need of updating them after wards.