ORA-06502 PL/SQL: numeric or value error string – Tips and Example
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).
Example 1:
declare
l_name varchar2(3);
begin
l_name := ‘abcd’ ;
end;
/
Oracle export commands
Oracle Export utility provides a number of options to help you export your data for different scenarios.
Here are some examples of the most common options that Developers/DBAs use.
Export based on a query:
You can use thess options to export selected rows from a table. For example below I am exporting all records from orders table where id < 300 .
exp scott/tiger file=test1.dmp query=\”where id \< 300\” tables=orders statistics=none
Note above that I am using escape character “\”. This will be needed when you run your export in Unix/Linux environment.
Continue reading
How to Install Oracle 11g Client
These instructions apply to Microsoft Windows only. When you install Oracle database software (try installing Enterprise edition), by default it also installs Oracle client. So if you have installed Oracle database software in your machine and want to connect to your local database you simply invoke the Oracle Client and given the necessary login credentials and you are in. You do not have to worry much about setting up your sql*net configuration as the installation process should usually take care of that if you chose to do so.
But for people who want to access an Oracle database that is not local and in a remote machine then you must need to install Oracle client in your machine. Even if You do not want to use SQL*Plus but want to use TOAD or SQL Developer or something like that, you still need Oracle client. Because internally these database access/development tools use Oracle clients software to connect to the target database.
So the most important thing to remember is, you must need Oracle client software in your machine if you want to connect to a remote Oracle database.
Installing Oracle client is not difficult. You just need to be a bit careful. The step by step instructions below should help you to install an Oracle Client in your machine. These instructions assume that you have downloaded the Oracle client software that you desire to install and unzipped the zip file into some folder. Continue reading
Re-Compiling ALL INVALID objects in your Oracle database
This script will re-compile all invalid objects of all schemas in yuor database. This is quite powerfull script as it will try to re-compile everything in the database that is invalid. So you must need to run this script as a highly priviledged user (such as SYS or SYSTEM or a user with DBA role)
The script first builds up the ALTER commands for all invalid objects and put (spool) the list into an operating system file called templist.sql. The ALTER commands will be of the format
ALTER PROCEDURE HR.CALLPROC COMPILE;
ALTER PACKAGE SCOTT.PKG_TEST1 COMPILE BODY;
ALTER PACKAGE SCOTT.PKG_TEST1 COMPILE BODY;
Note above that I am using the schema name before the object name e.g. HR.CALLPROC). This is to make sure that we are re-compile the invalid object in its own schema. The list file templist.sql will be run against the database to run all these commands in one go.
Here is the script:
Re-Compiling INVALID objects in own (users) schema
Here is a very handy script which you can use to re-compile all the invalid objects in your schema.
The script first builds up the ALTER commands for all invalid objects and put (spool) the list into an operating system file called templist.sql. The ALTER commands will be of the format
ALTER PROCEDURE CALL COMPILE;
ALTER PACKAGE PKG_TEST1 COMPILE BODY;
ALTER PACKAGE PKG_TEST1 COMPILE BODY;
Then the list file templist.sql will be run against the database to run all this commands in one go. Which means you just need to run this one script to re-compile all your invalid objects in your schema. Note that I am using here USER_OBJECTS – which is a VIEW owned by SYS user. Continue reading
Recent Comments