Ask Anantha

What is XML? XML stands for eXtensible Mark up Language. It is classified as an extensible language because it allows its users to define their own tags. XML was developed to provide a universal format for describing structured documents and data. There are no fixed tags for XML. Any user can add his own set of tags. The tags though are similar to HTML, they do differ by the way it is presented. Unlike HTML, which tags elements in Web pages for presentation by a browser, e.g. Oracle , XML tags elements as data, e.g. Oracle . In this example HTML identifies as a command to display the data within as Bold. But in case of XML, the company for instance can be a column name in a database and Oracle is the column value. Why do we use XML? As XML is W3C(World Wide Web Consortium) standard, various software companies have openly accepted and implemented it in their operations. It is a fee-free open standard. It is platform-independent, language-independent, textual data. XML can be used with...

Starting from Oracle 9i Release 1, there are datatypes available in Oracle which can store period intervals. The differences or intervals can be specified in years, months, days, hours, minutes and seconds. The new datatypes where introduced to comply with SQL 99 standards. Till Oracle 9i programmers had to convert the interval to some milliseconds/seconds format and store it in NUMBER datatype. Why do we use the INTERVAL datatypes introduced? We already have DATE datatype which can store DATE and TIME information. The answer is that the INTERVAL datatype stores an interval of time and not exactly one time at one point of time. Isn’t this a good feature? In Oracle 9i, the time interval datatypes INTERVAL YEAR TO MONTH , and INTERVAL DAY TO SECOND were added along with several other datatypes to deal more precisely with points in time. The TIMESTAMP , TIMESTAMP WITH TIME ZONE , and TIMESTAMP WITH LOCAL TIME ZONE datatypes all express time to fractions...

Let’s say we have an instance where in there are 100 tables in a schema and that Schema Owner is not supposed to use delete on any of those tables. Then instead of getting into the mess of Grants / revokes, we can use one of the facilities that are provided by Oracle. There is a table called as product_profile and it can be created (if not present) by executing the PUBBLD.SQL The PRODUCT_PROFILE table is owned by SYSTEM and has the following structure: Name           Null?         Type ------------------------------------ PRODUCT       NOT NULL     VARCHAR2(30) USERID                    VARCHAR2(30) ATTRIBUTE                 VARCHAR2(240) SCOPE         ...

Have you ever wondered after connecting to the database as SYS you wanted to check whether you are connected as regular user SYS or as SYS AS SYSDBA? Well the information is available in view V$SESSION_CONNECT_INFO. The following are the fields available in this view:   Name                        Type ------------------------    ------------------ SID                          NUMBER AUTHENTICATION_TYPE          VARCHAR2( 26) OSUSER                      VARCHAR2( 30) NETWORK_SERVICE_BANNER      VARCHAR2( 4000)     You can execute the following query to know the connection type...

The index skip scan is a new execution plan in Oracle9i whereby an Oracle query can bypass the leading-edge of a concatenated index and access the inside keys of a multi-values index. For example, consider the following concatenated index: create index sex_emp_id on emp (sex, emp_id ); Prior to Oracle9i, this index could only be used with both sex and emp_id were present in the SQL query, or when the sex column was specified.  The following query would not be able to use the concatenated index: select emp_id from emp where emp_id = 123; The Oracle9i skip scan execution plan allows for the concatenated index to be used , even though sex is not specified in the SQL query.  This feature promises that there is no need to provide a second index on the emp_id column.  Oracle acknowledges that the index skip scan is not as fast as a direct index lookup , but states that the index skip scan is faster than a full-table scan (otherwise why would oracle introd...

/*+ ALL_ROWS */ Explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption) /*+ CHOOSE */ Causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement /*+ FIRST_ROWS */ Explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row). It will also force the optimizer to make use of index, if available. There are other versions of FIRST_ROWS hints. This hint is useful in an OLTP environment when the user cannot wait till the last row is fetched. This is mainly used in Java lookup screens. If there are some calculations then this hint should not be used. Test your PL/SQL knowledge, Which code runs faster? /*+ RULE */ Explicitly chooses rule-based optimization for a...

If you've been developing applications on Oracle for a while, you've no doubt come across the concept of « Bind Variables » . Bind variables are one of those Oracle concepts that is key to application performance. To understand bind variables, consider an application that generates thousands of SELECT statements against a table; for example: SELECT fname, lname, pcode FROM cust WHERE id = 674; SELECT fname, lname, pcode FROM cust WHERE id = 234; SELECT fname, lname, pcode FROM cust WHERE id = 332; Each time the query is submitted, Oracle first checks in the shared pool to see whether this statement has been submitted before. If it has, the execution plan that this statement previously used is retrieved, and the SQL is executed. If the statement cannot be found in the shared pool, Oracle has to go through th...

Oracle 11g PL/SQL New Features o PL/SQL "continue" keyword - This will allow a "C-Like or PB" continue in a loop, skipping an iteration to bypass any "else" Boolean conditions. A nasty PL/SQL GOTO statement is no longer required to exit a Boolean within a loop. begin for i in 1..3 loop dbms_output.put_ line ( ' i ='|| to_char ( i )); if ( i = 2 ) then continue ; end if; dbms_output.put_ line ( 'Only if i is not equal to 2′); end loop; end ; o Disabled state for PL/SQL - Another 11g new feature is a "disabled" state for PL/SQL (as opposed to "enabled" and "invalid" in dba_objects ). o Easy PL/SQL compiling - Native Compilation no longer requires a C compiler to compile your PL/SQL. Your code goes directly to a shared library. o Improved PL/SQL stored procedure invalidation mechanism - A new 11g features wi...