Ask Anantha

The following script compiles all invalid objects in the schema. Login through SQL Plus and execute this file. In case all objects are not compiled even after the execution, execute the same file once or twice more. This is to ensure that all the dependent objects are compiled. compile.sql set echo off set feed off set sqlp '' set head off set pages 0 spool compile_objects.sql select 'ALTER '|| DECODE(object_type, 'VIEW', 'VIEW', 'PACKAGE') || ' '|| LOWER(object_name)|| ' COMPILE '|| DECODE(object_type, 'PACKAGE', 'SPECIFICATION', 'PACKAGE BODY', 'BODY', '')|| ' ; ' from user_objects where status = 'INVALID' order by DECODE(object_type, 'VIEW', 1, 'PACKAGE', 2, 'PACKAGE BODY', 3), object_name ; spool off set echo on set feed on set sqlp 'SQL>' set head on Once the compile.sql is executed, execute the compile_objects.sq...

I was very much frustrated with the simple but syntactical change between Oracle and MS-SQL Server. I was trying to just create a table with the normal Oracle syntax in MS-SQL Server: CREATE TABLE test AS SELECT * FROM existing_table; I got an error from MS-SQL and then I had to call up some experts on MS-SQL to understand how it is done in MS-SQL. The syntax for creating such a duplicate table of an existing one in MS-SQL is as follows: SELECT * INTO {new_table} FROM {existing_table} WHERE { if_clause if any}; Thus our example can be translated to as: SELECT * INTO test FROM existing_table; This short article is to just keep up the difference between MS-SQL and Oracle. Oracle professionals may sometimes need to work in MS-SQL, and this kind of tips on MS-SQL comes handy in times of trouble.

Tip Courtesy: Oracle Tips by Burleson Consulting (dba-oracle) Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques. This post shows some examples of using SQL to delete duplicate table rows using an SQL sub query to identify duplicate rows, manually specifying the join columns: DELETE FROM    table_name A WHERE   a.rowid >    ANY (      SELECT         B.rowid      FROM         table_name B      WHERE         A.col1 = B.col1      AND         A.col2 = B.col2         ); For a script that does not uses ROWID concept, read this post. Deleting /Listing duplicate records without ROWID

I chose to write on this topic in order to increase the readability of programs and to maintain a standard way of validating values from within PL/SQL blocks, whether be it Anonymous blocks or Stored Procedures or Stored Functions or Packages. There has been always need for writing efficient piece of code before delivering the final one. Validating NULL Values NVL Function This function is boon to programmers. I always chose to use this function because of its simplicity. In Oracle one must always remember that while validating with a NULL value the result you are going to get is also a NULL. So in order to avoid this it is better to use NVL() function. Syntax: NVL(variable1, variable2) Example: SELECT NVL(NULL, 'This is the output') null_test FROM dual; Result: null_test This is the output Both the parameters are required for NVL function to perform. You can use this function in WHERE clause so that you are not going to omit any NULL values from your query. NVL2 Function NVL2 ...

There is no oracle defined function for getting size of a table. After all if it is easy with one simple query who will require a function. Isn't it? Anyway you can choose to save this query as a function for easy retrieval. select segment_name table_name, sum(bytes)/(1024*1024) table_size_meg from user_extents where segment_type='TABLE' and segment_name = '&table_name' group by segment_name; Read more on what all to remember while getting the size of a table. Click here Create your own function for the purpose: CREATE OR REPLACE FUNCTION get_table_size (t_table_name VARCHAR2)RETURN NUMBER IS l_size NUMBER; BEGIN SELECT sum(bytes)/(1024*1024) INTO l_size FROM user_extents WHERE segment_type='TABLE' AND segment_name = t_table_name; RETURN l_size; EXCEPTION WHEN OTHERS THEN RETURN NULL; END; / Example: SELECT get_table_size('EMP') Table_Size from dual ; Result: Table_Size 0.0625

Q: How to enter a single quotation mark in Oracle? Ans: Although this may be a undervalued question, I got many a search for my blog with this question. This is where I wanted to address this question elaborately or rather in multiple ways. Method 1 The most simple and most used way is to use a single quotation mark with two single quotation marks in both sides. SELECT 'test single quote''' from dual; The output of the above statement would be: test single quote' Simply stating you require an additional single quote character to print a single quote character. That is if you put two single quote characters Oracle will print one. The first one acts like an escape character. This is the simplest way to print single quotation marks in Oracle. But it will get complex when you have to print a set of quotation marks instead of just one. In this situation the following method works fine. But it requires some more typing labour. Method 2 I like this method personally becaus...

OTN Login enables you to download Oracle softwares, post in Oracle Forums etc. This is a unique email id which you will use across all Oracle websites excluding Oracle Metalink. Click here to create your account You will be shown a screen like the following: Click the Create your Oracle account now. link. Upon getting the screen enter your email address and a password for accessing the Oracle sites. (Not your email account password) Fill in the mandatory fields in the following screen. Once you click Submit the following screen will be displayed: Click Submit button in the Screen. You will be asked some confirmations hereafter, click them and you will be shown the login screen again. THIS IS ALL you need to do to create your FREE login. You may use this login for downloading softwares from otn website, login to oracle forum for posting queries or answering them.

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows. To handle other Oracle errors, you can use the OTHERS handler. The functions SQLCODE and SQLERRM are especially useful in the OTHERS handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT to associate exception names with Oracle error codes. PL/SQL declares predefined exceptions globally in package STANDARD , which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names shown in the list below. Also shown are the corresponding Oracle error codes and S...

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...

Check the entry in tnsnames.ora file: EXTPROC_CONNECTION_DATA =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))     )     (CONNECT_DATA =       (SID = PLSExtProc)       (PRESENTATION = RO)     )   ) In this article we will be discussing about the PRESENTATION clause in the entry. Little history The whole tnsnames.ora file is meant to be information for client softwares which will be connecting to the Oracle server (database). The client must know where the database resides, which PROTOCOL to use for connection, unique identifier of the database etc. Back to EXTPROC_CONNECTION_DATA But in this particular tnsnames.ora entry, Oracle uses this for connecting to external procedures. The examples of external procedures are the procedures written in C/C++/VB which are compiled as available as shared librar...

If your DML statement encounters an exception, it immediately rolls back any changes already made by that statement, and propagates an exception out to the calling block. Sometimes that's just what you want. Sometimes, however, you'd like to continue past such errors, and apply your DML logic to as many rows as possible. DBMS_ERRLOG, a package introduced in Oracle10g Release 2 , allows you to do precisely that. Here is a quick review of the way this package works.  First you need to create an error log table where the errors will be inserted, and while issuing any DML statements use a clause newly introduced in 10g, LOG ERRORS. 1. Create an error log table for the table against which you will execute DML statements: BEGIN   DBMS_ERRLOG.create_error_log (dml_table_name => 'EMP'); END; Oracle then creates a table named ERR$_EMP that contains error-related columns as well as VARCHAR2 columns for each of your table's columns (where it...

Download Oracle Jinitiator - Version 1.3.1.28 Download the file and rename the executable to jinit.exe Click here to download the file When the download finishes you can install Oracle JInitiator by locating the file using the Windows Explorer and double-clicking on it to start the installation process. When the installation process has finished, exit your browser and restart it. Return to the initial application starting page and the browser will then load Oracle JInitiator and start your Oracle Developer application. Oracle JInitiator supports the following browser in Microsoft Windows environment: Platform IE 5.5 IE 6.0 Netscape Navigator 4.7.x Windows NT Yes Yes Yes Windows 2000 Yes Yes Yes Windows XP No Yes Yes Windows Vista -- No Not Known But do make sure with Oracle documentation before installing for a browser. Other minimum system requirements for Oracle JInitiator are: Pentium 166 MHz or better processor 70 ...

Hereafter referred to as Jinit, it is a JVM distributed by Oracle Corporation. Jinit enables the Oracle Forms client application (otherwise known as Oracle D2K in earlier versions) in a web browser.   Earlier the Oracle forms where only available as client installed stand-alone applications which accesses the database and file system to run the application. With the advent of Jinit, the client installation of Oracle Forms run time is not required. The browser plug in for Jinit only must be installed.   While accessing the application for the first time if the plug in is not installed the browser will prompt for the installation. At this point, you must be logged in with Admin rights. You can revert the rights once the installation is done.   Versions The Jinit referred to in this article is 1.3.1.28 which was rolled on August 2007. The versions is based on some practises. The first two numbers that is 1.3 in our case refers to the Sun Microsystem's JDK v...

Rule #1 The design of a database must only be thought after the through analysis of the user requirements. Rule #2 Never implement any functionalities of Oracle if it is not required. Always adhere Rule #1. Rule #3 Never implement as per theory, but base your design on practical thought. Always adhere Rule #2. What does this mean? I will elaborate each and every point mentioned above. Also note that all the rules are related to one another. This ensures that the design is always as per user requirements. Rule #1 The first rule says that never start or think of database design before understanding what exactly the user requires. This is the major and very important rule which must be adhered, whatever comes. This rule thus verifies the importance of analysis of user requirements. Unless the requirements of user is fully understood, it will be nearly impossible to design the database. Rule #2 This rule again double verifies the rule #1. The design must be using only using such oracle fun...

The OPatch utility is a tool that allows the application and rollback of interim patches to Oracle products. Patches are a small collection of files that are copied over an existing installation. They are associated to particular versions of Oracle Products. Patches, when applied to the correct version of an installed product, results in an upgraded version of the product.Interim patches are bug fixes that are made available to customers in response to specific bugs. They require a particular base release or patchset to be installed before they can be applied. They generally address specific bugs for a particular customer. These patches are not versioned and are generally made available in a future patchset as well as the next product release. About OPatch OPatch is an Oracle supplied utility to assist you with the process of applying interim patches to Oracle's software. OPatch is a Java-based utility which requires the Oracle Universal Installer to be installed. It is platfo...

Topic: Beginners Level From time immemorial there has been debate over the usage of like clause and its association (or rather non-association with index). What is the fuzz all about? Let's check here. Like clause allows you to use wildcard character searches over data stored in oracle database. By this means we can do pattern searching over the existing data. For example: You have a daily meeting calendar in which the attendees names are stored in comma seperated values in a VARCHAR2(2000) field. You want to search on what days a particular attendee say for instance Rose has attended the meeting. table: meeting_schedule fields: meeting_date date meeting_place varchar2(200) meeting_attendees varchar2(2000) In such a case of searching, without the usage of wildcard characters such as % will not yeild appropriate results. The query for such a situation would be: SELECT meeting_date, meeting_place FROM meeting_schedule WHERE meeting_attendees like '%Rose%'; Now the above query...

In release 8.1, a new SQL function, TO_LOB, copies data from a LONG column in a table to a LOB column. The datatype of the LONG and LOB must correspond for a successful copy. For example, LONG RAW data must be copied to BLOB data, and LONG data must be copied to CLOB data. In the next example we show how to migrate a table with one LONG to a CLOB datatype. Create the LOB Tablespace CREATE TABLESPACE lob1 DATAFILE '/lh4/lob1.dbf' SIZE 2048064K REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M PERMANENT ONLINE; Disable temporarily all Foreign Keys set feed off; spool gen_dis_cons.sql; SELECT 'ALTER TABLE ' table_name ' DISABLE CONSTRAINT ' constraint_name ';' FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%' / spool off; set feed on; @gen_dis_cons.sql; Convert LONG to LOB in temporary Table Create a temporary table with converted BLOB field. CREATE TABLE lob_tmp TABLESPACE tab AS SELECT id, TO_LOB(bdata) bdata FROM document; Drop and Rena...

Sometimes, the DBA needs to log into a user's account to make a change, such as to grant a privilege on a user's table to another user, but, may not know what the user's password is , or, may need to make changes to a set of users from a script, but, doesn't want to include their passwords in the script itself.  Oracle provides an undocumented "identified by values" clause in the "alter user" command that can be used for this purpose.  Since the dba_users table contains the encoded password for each user, this value can be used to generate an "alter user" command to reset the user's password back to its original value.  Then, from user system or another DBA user, you can alter the user's password to a known value (such as "whatever"), log into the user's account using "connect userid/whatever", make the changes that are needed, connect back to the system account, and run the generated "alter user...

During a migration it may be important, that nobody can connect to the database. You may have two possibilities to achieve this On Table Level using LOCK TABLE On Database Level using RESTRICTED SESSION Exclusive Table Locks (X) An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows: $ sqlplus scott/tiger SQL> GRANT ALL ON emp TO PUBLIC; SQL> LOCK TABLE emo IN EXCLUSIVE MODE; $ sqlplus test/test SQL> SELECT * FROM scott.emp; (This works) SQL> DELETE FROM scott.emp WHERE empno = 7369; (Waiting ...) Permitted Operations Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table. Prohibited Operations An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of loc...

Starting with Release 7.1 of Oracle, users have had access to a feature called an inline view. An inline view is a view within a query. Using this feature, you can easily accomplish your task. Example: Show percentage of salaries for each department Every row in the report must have access to the total sum of sal. You can simply divide sum (sal) by that total, and you'll have a number that represents the percentage of the total. column percentage format 99.9 select deptno, sum(sal),sum(sal)/tot_sal*100 "PERCENTAGE" from emp, (select sum(sal) tot_sal from emp) group by deptno, tot_sal; With Oracle8i Release 2 (8.1.6 and higher), you can calculate percentages by using the new analytic functions as well. The query using an analytic function might look like this: column percentage format 99.9 select deptno, sum(sal), (ratio_to_report(sum(sal)) over())*100 "PERCENTAGE" from emp group by deptno; The query produces the same answer—but it does so more efficiently, becau...

Oracle offers several possibilities to process file from within PL/SQL. The most used package is UTL_FILE, but with the disadvantage that the read-buffer is limited to 1023 bytes. If you want to read huge chunks of files you can use the DBMS_LOB package, even for the processing of plain ASCII files. There are two solutions to read a file with DBMS_LOB The file is treaded as a large binary object LOB. The whole file is read and saved in a table column of the data type LOB and then processed. The file is read and processed directly from the filesystem location. This Tip shows exactly this case. Example: Suppose we want to read a big file word by word directly from PL/SQL without saving the whole file in a table column. The words in the file are separated with a blank. For simplicity we assume, that there is exactly one blank between the words and the file is a stream with a newline at the end of the file. First we have to create an ORACLE directory as the schema owner. Do not ad...

The docs note this on the ORA-06502 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 by 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: ...

ORA-04031: Unable to allocate %s bytes of shared memory   Solutions:   alter system set shared_pool_size=100M; -- size you have to decide based alter system set large_pool_size=100M ;    -- on sga_max_size   Oracle limits the increase of size up to SGA_MAX_SIZE parameter defined in the initialization parameter file. SGA memory can not be increase beyond SGA_MAX_SIZE. If SGA_MAX_SIZE parameter is not enough for increasing the memory of dynamic parameters, you will get a ORA-00384 error. In this case either you have to increase the SGA_MAX_SIZE parameter or decrease the memory of dynamic parameter.   alter system set db_cache_size=135m;   alter system set db_cache_size=135m   *   ERROR at line 1:   ORA-02097: parameter cannot be modified because specified value is invalid   ORA-00384: Insufficient memory to grow cache     alter system set   SGA_MAX_SIZE=150M scope=spfile;   System altered. ...