Ask Anantha

The following is a query from a friend, Reproducing it here: I have a menu structure using a hierarchical query. i have search field to enter some string and search. I should get the result in the same menu format with with the node and its parents. Can you please help me with the query for search ? eg. A1 A11 A12 A123 A13 B1 B123 C1 C11 C12 If I search for  %123%  the result - A1 A12 A123 B1 B123 My idea initially was, yeah possible BUT HOW. I was sure it needed two result sets and joining to form a query to get the output. But wait, what is internet saying about it.  I stumbled upon this brilliant article in orafaq and I was sure my initial thought was wrong. I was in a different mindset. Thanks to the article and Oracle SQL the query was much simpler. But with a catch, the hierarchy was in reverse. SELECT lpad(' ', LEVEL, '-') || h.title descr FROM   test_hier h CONNECT BY id = PRIOR parent_id START  WITH parent_id...

One point which is usually overseen with the usage of TO_CHAR() is the leading space that it returns when converting a number to character . Example: select length(to_char('109','000.00')) from dual; TO_CHAR('109','000.00') " 109.00" Do you notice the leading space that TO_CHAR() has returned? If you have not noticed and surprised, execute this and find it for yourself. This is not a bug. The Reason is Oracle reserves the first character for Sign. If you won't beleive me, execute the following statement: select to_char('-109','000.00') from dual; TO_CHAR('-109','000.00') "-109.00" Notice that the length of the string has not increased with the negative value being converted by TO_CHAR(). Hold on your tendency of calling TRIM() function to trim out the space. TO_CHAR() has a format model that suppresses this trailing space. To suppress this leading space simply use the FM (stands for...

I have a data in CSV format (comma separated values) like follows: first,second,third,fourth,fifth  I want the output as follows: column_value first second third fourth fifth The easiest way to achieve is to use a CONNECT BY clause in DUAL table and to populate the results. Here is the query: SELECT substr(str, instr(str, ',', 1, LEVEL) + 1, instr(str, ',', 1, LEVEL + 1) - instr(str, ',', 1, LEVEL) - 1) column_value FROM   ( SELECT ',' || '&mystring' || ',' str  FROM  dual) CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) - 1; Pass value   first,second,third,fourth,fifth to mystring.

Virtual Columns has been introduced in Oracle Database 11g Release 1. Here is a good tutorial I could find from Oracle-Base website. The link for the tutorial is at the bottom of this article. - Anantha When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below. column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL] If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only. The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form. CREATE TABLE employees (  id          NUMBER,  first_name  VARCHAR...

Question:  How to get the last password changed time for a oracle user? Answer: The SYS view user$ consists of a column PTIME which tells you what was the last time the password was changed for the user. Try this query: SELECT name,        ctime,        ptime FROM   sys.user$ WHERE   name = ' USER-NAME '; Note: Replace USER-NAME with the user name which you want to know the information. CTIME Indicates - Creation Time PTIME Indicates - Password Change Time Here's the table DESC ription: Name         Type                Nullable Default Comments  ------------ ------------------- -------- ------- --------  USER#        NUMBER                                         NAME         VARCHAR2(30 BYTE)   ...

Creating or Rebuilding Indexes Online: Online Index Rebuilds allows you to perform DML operations on the base table during index creation. You can use the CREATE INDEX ONLINE and DROP INDEX ONLINE statements to create and drop an index in an online environment. During index build you can use the CREATE INDEX ONLINE to create an index without placing an exclusive lock over the table. CREATE INDEX ONLINE statement can speed up the creation as it works even when reads or updates are happening on the table. The ALTER INDEX REBUILD ONLINE can be used to rebuild the index, resuming failed operations, performing batch DML, adding stop words to index or for optimizing the index. The CREATE INDEX ONLINE and ALTER INDEX REBUILD ONLINE options have been there for a long time to easy the task of online index rebuilding. However in highly active they still can introduce locking issues. Table Locks: A table lock is required on the index base table at the start of the CREATE or REBUILD process to...

Targeted Audience: Beginners Till today I was of the illusion that USER and SCHEMA are both equal. But Oracle Guru Andrew Clarke describes the difference between both USER and SCHEMA in his blog . Thanks Clarke for your blog. So understanding this difference we will define what an Oracle Schema is: A Schema represents set of objects owned by the user of the same name. For example in an organization there are 100 employees, each of these employees should have a separate space where they manage their objects. It is synonymous with the concept of an employee been allocated a new cabin where he can keep or organize his belongings. The same way in Oracle a user must be created for each database user. An organization can keep its own rules in naming the users but it is better to use a naming notation always in such cases. If a database user logs in to his space (using connect) he can create objects which becomes the schema. What a schema can contain? Just like a cabin where the employee sits...

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.

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.

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

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

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