Ask Anantha

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. Simply stating the value being assigned is not a valid value which can be assigned to the string/numeric field. For example: declare test varchar2(1); begin test := 'I am here'; end; The above block gives the error: ORA-06502: PL/SQL: numeric or value error: character buffer too small ORA-06512: at line 4 Of course in this you are getting more information as to the character variable is too small to hold the value you are assigning. Now consider the following example: declare test number(2); begin test := 100; end; The above block gives this error: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at line 4 So now you know more about this frequently occuring error. Some tips to avoid this error: 1. If...

Who can read this:  A basic understanding of indexes, updates and their effects on indexes would help better understand the topic of this document. This article is intended to be an introduction to the topic of Function based index. Who should not read this: This document is meant to be a basic manual for those who would like to learn what are function based indexes and what magic can they do. So the persons who are already aware of this concepts, please do not waste your time. Also this concept is confined to Oracle database. There are no general topics discussed in this article. Now let's start with the topic straightaway: What and why a function based index is used?   Function based indexes were introduced with Oracle 8i. This was the most coveted features that Oracle came up with its 8i version of database. Traditionally there was no such feature that will use an index if your select statement has a function in it. But with this featur...

I am trying to export a 10.2.0.3 database, and I am getting an error. I understand that one of the most common causes of these errors is that I am using a higher version of the export utility compared to my RDBMS version. But this is not the case. My database version is 10.2.0.3 and the exp version 10.2.0.3 EXP-00056: ORACLE error 6550 encountered ORA-06550: line 1, column 41:PLS-00302: component 'SET_NO_OUTLINES' must be declared ORA-06550: line 1, column 15: PL/SQL: Statement ignoredEXP-00000: Export terminated unsuccessfully >> Possbile Solutions : It could be that your Data Dictionary is not set to support this higher version of export. Sign on to your database as SYS and run the @?/rdbms/admin/catexp.sql script. If that doesn't fix the problem, try rerunning @?/rdbms/admin/catalog.sql and @?/rdbms/admin/catproc.sql scripts.

SQL Developer for Linux JInitiator 1.3.1.28 Oracle Database 11g Release 1 (11.1.0.6.0) Standard Edition, Standard Edition One, and Enterprise Edition Oracle SQL Developer Data Modeler

Oracle suports XML handling through the built in datatype XMLTYPE. Here is an example of handling XML data to convert xml to object types and viceversa. Consider the following XML <customer> <id>100</id> <name>XEROX</name> <country>FRANKFURT</country> <status>ACTIVE</status> </customer> Create a sample object type customer which contains the same elements as the XML data using the following command: CREATE OR REPLACE TYPE CUSTOMER AS OBJECT ( ID VARCHAR2(10) , NAME VARCHAR2(50) , COUNTRY VARCHAR2(50), STATUS VARCHAR2(15)) / The following pl/sql block will convert the xml data into one XMLTYPE variable and then populates the object variable v_in which is of type customer and displays the object contents. DECLARE v_in customer; v_xml xmltype; BEGIN v_xml :=XMLTYPE('<c...

Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format. --Unconditional Insert into all tables INSERT ALL INTO ap_cust VALUES (customer_id, program_id, delivered_date) INTO ap_orders VALUES (order_date, program_id) SELECT program_id, delivered_date, customer_id, order_dateFROM airplanes; -- Pivoting insert to split non-relational data INSERT ALL INTO Sales_info VALUES (employee_id,week_id,sales_MON) INTO Sales_info VALUES (employee_id,week_id,sales_TUE) INTO Sales_info VALUES (employee_id,week_id,sales_WED) INTO Sales_info VALUES (employee_id,week_id,sales_THUR) INTO Sales_info VALUES (employee_id,week_id, sales_FRI) SELEC...

I was hunting for an article when my friend came up and asked me this question. How to list/delete duplicate records without using ROWID? I never asked why he do not want to use ROWID, because I was caught in some thinking. I done some googling, and derived the method, but first the ROWID method First how to delete records with ROWID, and why? ROWID is the unique record identifier within Oracle, and it is easy to get the minimum of the ROWID for the duplicating criteria and delete the rest of them. We will take the example of EMP table for simplicity. Assume that there is no primary key in the Employee_Id column due to some data migration requirements. Once the data migration is over I need to check the existense of duplicate records. This is how traditionally we used to achieve this: Suppose the data after migration stands like this: Employee_Id Employee_Name 10          Scott 11          Tige...