Connect Grails with Oracle 11g Example Configuration
I developed a tiny web application in Grails that used HSQLDB by default and was wondering if it is possible to change the database to my Oracle 11g database. That would be neat and I can build more on Grails and at the same time use some of the powerful features offered by Oracle. It is very easy and straightforward to set up Grails to work with Oracle. However, you may encounter some exceptions ....
Oracle Concepts Course - Part 2
Courtesy: Dizwell The Structure of the Oracle Instance An Oracle instance is merely an allocation of memory in which to do work plus a bunch of background processes which automate a lot of that work for us. The memory areas are collectively known as the System Global Area (or SGA for short). The constituent 'pools' of memory, which in the aggregate make up an SGA, are called The ....
Oracle 12C - DEFAULT ON NULL - Examples
Here is a problem statement - you have a huge table with lot of columns, some of them are crucial to your application functionality. For some reason, these columns have started accepting NULL values. And you would like to default such columns with suitable values when the application sends a NULL during insert, not in other cases. In Oracle 11g and below, a possible way to achieve this would be ....
Oracle 12C - Session Sequences - Example / Usage
Let us explore session sequences in Oracle 12C in this tutorial with an example. Session sequences are new concept in Oracle 12C that return unique values only within a session. An interesting usage of session sequences is with global temporary tables - where you can generate a unique value at a session level using this sequence. Before Oracle 12C, all sequences are considered to be global - ....
Oracle ROLLUP Usage- Example
For some time till now, ROLLUP and CUBE were always complicated terms to comprehend and use in standard SQL usage for me. Today I decided to give it a go and understand them with some examples. So far so good and the simulation for ROLLUP is provided below. Let us create a basic table and do some updates so that we can get going. CREATE TABLE SAM_STR ASSELECT TRUNC(DBMS_RANDOM.value(1000,9999)) ....
Oracle SQLTXPLAIN SQLT Tutorial Example Part 5
We discussed XTRACT method of SQLTXPLAIN utility with an example earlier. We discussed the elements of the report produced by this tool (with the output we got). It was comprehensive enough with recommendations. In this post, we will see XPLAIN method of this tool, and see how to make use of that. I read the documentation for this tool and found that XPLAIN requires an SQL input from an SQL file. ....
Oracle SQLTXPLAIN SQLT Tutorial Example Part 1
There are many SQL Performance Tuning tools for Oracle Database spread out in the market. SQLTXPLAIN, shortly referred by SQLT is one that is becoming increasingly popular these days. This is a native performance tuning tool of Oracle, and can help to provide adequate diagnostic information for a SQL Query and help to increase the Query Performance by refining it. In this tutorial, we will try to ....
Oracle insertXMLbefore Function Example
After discussing how to use insertChildXML function, in this post let us see how to use insertXMLbefore function with a simple example. To illustrate the usage of insertXMLbefore function, consider the following XML below: HP G62X-400 LD644AAR 15.6 450 ....
Oracle Extract ExistsNode Usage with Examples Part 4
We created a simple XML in our previous post to explain the power of using XPath expressions in EXTRACT and EXISTSNODE functions in Oracle. In this post, we will present some more powerful uses of XPATH inside Extract and Existsnode functions in Oracle. As before the XML we will be using for the examples is as below:
<br><a href="http://thinktibits.blogspot.com/2011/03/oracle-xquery-on-xmltype-column-part-5.html" rel="bookmark" title="Oracle XQuery on XMLTYPE column Part 5">Oracle XQuery on XMLTYPE column Part 5</a><br><br>This is part 5 of our series on XMLQuery and in this post we will see how we can use XMLQuery function involving XQuery on XMLTYPE column. To run the example we will be demonstrating on this post, we will construct a simple table with two columns, one of them being of type XMLTYPE. We will add some data into this table to get ahead with the example. CREATE TABLE LEDTVS(TVID NUMBER,TVDESCR ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-xmlquery-examples-part-3.html" rel="bookmark" title="Oracle XMLQuery Examples Part 3">Oracle XMLQuery Examples Part 3</a><br><br>Continuing our XMLQuery examples, we now present an approach that shows how to use nested ‘for’ loops to get structured output. To make this example easier to understand, we truncate the POPULAR_BRAND and POPULAR_PRODUCTS table to have some data that is easier for us to understand. So, truncate these two tables and insert the rows as shown below TRUNCATE TABLE POPULAR_BRAND/INSERT INTO ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-xmltable-xquery-example-part-3.html" rel="bookmark" title="Oracle XMLTable XQuery Example Part 3">Oracle XMLTable XQuery Example Part 3</a><br><br>In this post, we will explain the extended features of XMLTable by introducing XQuery. For basic XQuery and XMLTABLE examples, you may wish to refer to the related post section at the bottom of this post. We have seen XMLTABLE and XMLSequence. Is XMLTABLE just a replacement for XMLSequence? I feel no, as XMLTABLE has got more powerful features over XMLSequence, predominant among them is to use ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-sql-xml-data-xmltype-final.html" rel="bookmark" title="Oracle SQL XML Data XMLTYPE Final Solution">Oracle SQL XML Data XMLTYPE Final Solution</a><br><br>This post explains the usage of XMLSequence with an example. We presented a complex XML and outlined the approach to extract data from XML using extractvalue and XMLSequence. We stopped the post at the last stage, presenting the reader an opportunity to fix the result as a comma separated value(CSV). In order to group specifications as a Comma separted value, we present two solutions in this ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/delayed-block-cleanoutoracle.html" rel="bookmark" title="Delayed Block Cleanout–Oracle">Delayed Block Cleanout–Oracle</a><br><br>To understand the ORA-01555 – Snapshot too old error, it is important to know what delayed block cleanout is, and this post aims to explain the concept behind this term with a simple example. To explain this term, consider a transaction in Oracle that is updating a table which contains a million rows. To make changes to the underlying data, this transaction will obviously have to visit a large ....
<br><br><a href="http://thinktibits.blogspot.com/2014/06/ora-65096-invalid-common-user-or-role.html" rel="bookmark" title="ORA 65096 Invalid Common User or Role Name - Oracle 12C">ORA 65096 Invalid Common User or Role Name - Oracle 12C</a><br><br>This post explains the reason for the weird error – ORA 65096 – Invalid Common User or Role Name that you may encounter in Oracle 12C while creating a new user / schema and offers a way to solve it. You would need a working version of Oracle 12C database and needless to say, encountered this error while you tried creating a schema with some of the commands you used before, as a reference ....
<br><br><a href="http://thinktibits.blogspot.com/2014/06/Bulk-Insert-Skip-Ignore-Duplicate-Rows-Oracle-Example.html" rel="bookmark" title="Bulk Insert Skip / Ignore Duplicate Rows Oracle Example">Bulk Insert Skip / Ignore Duplicate Rows Oracle Example</a><br><br>In this short tutorial, we will see how to ignore duplicate records from a table during a bulk insert operation in Oracle. The example works on 11g Oracle databases and above and utilizes the hint ignore_row_on_dupkey_index . First things first, why do we need this tutorial? The problems with Bulk Inserts Let us say you have a table with a unique key and millions of row. And you perform ....
<br><br><a href="http://thinktibits.blogspot.com/2013/01/oracle-listagg-function-example.html" rel="bookmark" title="Oracle LISTAGG Function Example">Oracle LISTAGG Function Example</a><br><br>Today I read about LISTAGG aggregate / analytic function in Oracle, and thought would write a short blog about this, explaining various methods of using LISTAGG with suitable examples. I’m going to approach this tutorial with really simple examples so that you can understand LISTAGG completely. The tutorial assumes you know some basics of SQL, using aggregate functions and analytic functions. ....
<br><br><a href="http://thinktibits.blogspot.com/2013/01/index-of-all-oracle-posts.html" rel="bookmark" title="Index of All Oracle Posts">Index of All Oracle Posts</a><br><br>ORA -65096 - Invalid Common User or Role in Oracle 12C Oracle ignore_row_on_dupkey_index - Bulk Insert Hint - Usage and Example Oracle LISTAGG Function Example Write Java Stored Procedure / Function - Oracle Example Bulk Load CSV File Data to Oracle Java Example Program Insert CSV Data to Oracle - Java JDBC Example Program Excel to Oracle - Batching - ....
<br><br><a href="http://thinktibits.blogspot.com/2014/08/oracle-change-execution-plan-for-sql.html" rel="bookmark" title="Oracle Change Execution Plan for SQL">Oracle Change Execution Plan for SQL</a><br><br>From Oracle 11g, there exists a new feature - SQL Plan Management, using which you can alter the execution plan of a query. This post aims to offer a simpler explanation of this works. To begin, let us create a simple table in a schema and pump millions of rows into it. This will provide us a test case that we can work upon. Use the SQL statement below for the purpose: Create a test table ....
<br><br><a href="http://thinktibits.blogspot.com/2014/08/oracle-12c-is-json-sql-example.html" rel="bookmark" title="Oracle 12C IS JSON SQL Example">Oracle 12C IS JSON SQL Example</a><br><br>In the last post, we saw a basic introduction to using JSON in Oracle 12C. In that post, we used a check constraint to enforce the incoming data being inserted to be of type JSON. However, you may have existing tables in your database, on which you cannot enforce such constraints due to business limitations and in this article we can see how to use the new SQL constructs in Oracle 12C IS JSON ....
<br><br><a href="http://thinktibits.blogspot.com/2014/08/Query-Insert-JSON-Oracle-Database-12C-Example.html" rel="bookmark" title="Query / Insert JSON Oracle Database 12C Example">Query / Insert JSON Oracle Database 12C Example</a><br><br>One of the new features introduced in Oracle 12C (12.1.0.2) is the support to query and insert JSON (Javascript Object Notation) data into database tables. In this article, let us explore how to query JSON data with suitable examples. You would need 12C database to try the examples presented in this post. This tutorial is tested on Oracle 12C in Linux 6.5. We will get started with a simple ....
<br><br><a href="http://thinktibits.blogspot.com/2014/07/oracle-12c-invisible-columns-example.html" rel="bookmark" title="Oracle 12C Invisible Columns Example">Oracle 12C Invisible Columns Example</a><br><br>This blog post discusses invisible columns - a concept introduced in Oracle 12C. We provide some Q&A on this concept with working examples. Feel free to add your own experience on this concept as comments to this post. Create an Invisible Column in a Table: To create an invisible column in Oracle DB at the time of table creation, append the keyword invisible against the column name as ....
<br><br><a href="http://thinktibits.blogspot.com/2014/07/Oracle-Change-dupkey-error-index-hint-example.html" rel="bookmark" title="Oracle CHANGE_DUPKEY_ERROR_INDEX Hint Example">Oracle CHANGE_DUPKEY_ERROR_INDEX Hint Example</a><br><br>In this tutorial, let us examine the hint CHANGE_DUPKEY_ERROR_INDEX introduced in 11g, and see how this can be used in DML operations in Oracle. We will provide different ways to use this hint with suitable examples as we move on. From the Oracle documentation, this hint helps to redirect one of the unique index exceptions that gets thrown during an INSERT or UPDATE operation to a different ORA ....
<br><br><a href="http://thinktibits.blogspot.com/2014/07/create-sha-256-hash-oracle-pl-sql-example.html" rel="bookmark" title="Create SHA-256 Hash - Oracle PL SQL Example">Create SHA-256 Hash - Oracle PL SQL Example</a><br><br>This post explains how you can create SHA-256 hash for a column / string in Oracle using PL SQL and Java Stored procedures with an example. You would need to have a basic understanding of creating Java stored procedures in Oracle to understand this tutorial. Also recommended is our tutorial to create SHA-256 for a file in Java (though not mandatory). Here is a step by step guide that explains ....
<br><br><a href="http://thinktibits.blogspot.com/2012/11/Oracle-XMLCast-SQL-Example-XMLType-XMLDB-Query.html" rel="bookmark" title="Oracle SQL XMLCast extractvalue Example Tutorial">Oracle SQL XMLCast extractvalue Example Tutorial</a><br><br>I stumbled across this new SQL function XMLCast when reading through the Oracle XML DB documentation today. This is a new function that is introduced in Oracle 11g and can be considered as a replacement to “extractValue†function. Oracle documentation has a confirmation that “extractValue†is deprecated with 11g Release 2. To understand how XMLCast performs better over extractvalue, I ....
<br><br><a href="http://thinktibits.blogspot.com/2010/12/oracle-concepts-course-part-1.html" rel="bookmark" title="Oracle Concepts Course - Part 1">Oracle Concepts Course - Part 1</a><br><br>I'm a great fan of Dizwell and have saved some of his valuable articles when I first saw them in the web. I'm posting it here so that the entire community can read through it and get the basics right. Courtesy: Dizwell the great.. What is an Oracle Server? When you edit a document in Word (or whatever word processor you happen to prefer!), you don't type documents directly into a disk file, ....
<br><br><a href="http://thinktibits.blogspot.com/2010/03/sql-cube-usage-examples.html" rel="bookmark" title="SQL CUBE Usage - Examples">SQL CUBE Usage - Examples</a><br><br>In the previous article, we discussed about the usage of ROLLUP statement in SQL with examples. We saw how ROLLUP modifies the output of a statement by running it against a test table. In this article, we will dwell on the usage of CUBE with examples.If you are jumping right into this article from the internet, I'd recommend you to read this article first, as it contains all the test tables that ....
<br><br><a href="http://thinktibits.blogspot.com/2010/03/oracle-1z0-047-certification-book.html" rel="bookmark" title="Oracle 1Z0-047 Certification - Book or Ebook">Oracle 1Z0-047 Certification - Book or Ebook</a><br><br>This certification exam [ 1Z0-047 - OCA Oracle Database, SQL Certified Expert Exam Guide ] is one of the most prestigious one and not many materials are available in the web for preparing for this examination. I recently purchased a book from Amazon for this certification and it covers the complete syllabus of the exam in a concise manner. This book is authored by Steve O'Hearn and I must ....
<br><br><a href="http://thinktibits.blogspot.com/2009/06/oracle-dynamically-drop-constraints.html" rel="bookmark" title="Oracle - Dynamically Drop constraints from a table">Oracle - Dynamically Drop constraints from a table</a><br><br>I was faced with a task of dropping a constraint from a column in a table dynamically. i.e. the name of the constraint would vary between different environments and the challenge would be to know the name of the constraint by querying the dictionary tables in Oracle and drop them on the fly. As an example, consider the following create table statement; CREATE TABLE TEST1( COL1 VARCHAR2(20) NOT ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/xmltable-function-with-xquery-on.html" rel="bookmark" title="XMLTABLE Function with XQuery on XMLTYPE Column–Part 9">XMLTABLE Function with XQuery on XMLTYPE Column–Part 9</a><br><br>Part 8 of our blog discussed about applying COLUMN clause to XMLTABLE function, that involved an XQuery. At the end of part 8, we had presented the user with four challenges for achieving the same output using different XML functions in Oracle. In this post, we will present the solution to each of the four approaches and compare the output. Approach 1 # How to get the output using EXTRACTVALUE ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/using-xmltable-with-passing-and-columns.html" rel="bookmark" title="Using XMLTABLE with PASSING and COLUMNS Clauses–Part 8">Using XMLTABLE with PASSING and COLUMNS Clauses–Part 8</a><br><br>One of the benefits of using XMLTABLE is the ability to get selected columns from the XQuery in the output directly. This will be very handy in cases where you need not parse the XML constructed from XQuery, but rather pull the columns directly from XMLTABLE itself. Sounds very technical? Not really, if this is understood with a very simple example. This time, let us take an example of portable ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-xmltable-on-xmltype-column.html" rel="bookmark" title="Oracle XMLTABLE on XMLTYPE Column Example–Part 7">Oracle XMLTABLE on XMLTYPE Column Example–Part 7</a><br><br>In this post, we will explain how to use XMLTABLE construct on a XMLTYPE column to pull data from a table in Oracle. We had earlier done this using XMLQuery and we will be using the same LED TV example that we used in the previous parts. So, make sure that you grab all the table details and insert statements before we move on.I’m giving them below in case you cannot find it in the blog elsewhere; ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-xmlquery-on-xmltype-column-part.html" rel="bookmark" title="Oracle XMLQuery on XMLTYPE Column Part 6">Oracle XMLQuery on XMLTYPE Column Part 6</a><br><br>In part 5 of this series, we discussed some basic examples that showed how to query data from XMLTYPE column using XMLQuery function in Oracle. In this post, let us discuss some more advanced examples which will explain how to pull data from XMLTYPE column. We will be using the same tables as provided in part 5, so make sure that you have the table in place before you try the examples. The ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-sql-xmlquery-usage-with-examples.html" rel="bookmark" title="Oracle SQL XMLQuery Usage With Examples Part 4">Oracle SQL XMLQuery Usage With Examples Part 4</a><br><br>This is part 4 of XMLQuery usage techniques and refer to the previous part (XMLQuery – Part 3) to grab the table data that we will be using for the examples in this post. We saw how to use nested for loops to grab XML Data from two tables. Just to compare XMLQuery with XMLTABLE, consider the following SQL below SELECT * from XMLTABLE('for $i in fn:collection(oradb:/HR/POPULAR_BRAND), ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-sql-xmlquery-usage-part-2.html" rel="bookmark" title="Oracle SQL XMLQuery Usage Part 2">Oracle SQL XMLQuery Usage Part 2</a><br><br>We earlier presented a simple introduction to XMLQuery usage in Oracle. We will present some more examples for XMLQuery in this post. Make sure that you have the required tables to test out these examples. The table details with insert scripts can be found in Part 1 (XMLQuery). The first sample XMLQuery example pulled out all the HP products with details such as BRAND ID, PRODUCT TYPE, PRODUCT ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-xmlquery-example-usage-part-1.html" rel="bookmark" title="Oracle XMLQuery Example Usage Part 1">Oracle XMLQuery Example Usage Part 1</a><br><br>In this short tutorial, we will explain the usage of XMLQuery function in Oracle, with a sample relational table. We will aim to keep this illustration as simple as possible so that it will enable an easier understanding of XMLQuery concepts. As we move on in subsequent tutorial sections, we will present some complex usages of XMLQuery and the benefits of XMLQuery. The examples presented in this ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-xmlforest-and-xmlcolattval.html" rel="bookmark" title="Oracle XMLFOREST and XMLCOLATTVAL Example">Oracle XMLFOREST and XMLCOLATTVAL Example</a><br><br>I got introduced to two functions that are slightly similar in behaviour. XMLFOREST and XMLCOLATTIVAL..To understand the finer difference between these these two, consider the following example; So, let us start with a table and some insert statements.. CREATE TABLE GPS_MODEL ( MODEL_NUMBER VARCHAR2(20), GPS_BRAND VARCHAR2(50), GPS_PRICE VARCHAR2(30) ) INSERT INTO GPS_MODEL VALUES('Nuvi 1450T', ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-xmltable-usage-part-2.html" rel="bookmark" title="Oracle XMLTABLE Usage Part 2">Oracle XMLTABLE Usage Part 2</a><br><br>We are trying to explain the usage of XMLTABLE to understand the benefits it offers over XMLSequence. If you had referred to part 1 of this series, we presented a simple XML and came up with a report by using XMLTable. We also presented an alternative approach to achieve the same objective using XMLSequence. Now, when using XMLTable you may get the following errors ORA-19228: XPST0008 - ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-xmltable-example-part-1.html" rel="bookmark" title="Oracle XMLTABLE Example Part 1">Oracle XMLTABLE Example Part 1</a><br><br>In this post, we will explain the usage of XMLTABLE construct in Oracle SQL with a simple example. As we move on this series, we will present some more challenging examples for XMLTABLE.This is second in our series after our early attempt to explain XMLSequence. To make sure that we remember XMLSequence as well, we will also see how to achieve the objectives in these posts with XMLSequence as ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/xmlsequence-sql-example-part-2.html" rel="bookmark" title="XMLSequence SQL Example - Part 2">XMLSequence SQL Example - Part 2</a><br><br>We are attempting to provide a solution for a querying a complex XML as provided in our example earlier, and come up with an approach that explains the ease of using Oracle XML functions. We start with creating a table with single XMLTYPE field as shown below create table prod_xml_Tab(demo_field xmltype) Grab the XML that we are going to use from the question page, and insert it into this ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/xmlsequence-sql-examplepart-1.html" rel="bookmark" title="XMLSequence SQL Example–Part 1">XMLSequence SQL Example–Part 1</a><br><br>We are starting a new series of blog post, that will explain the usage of XMLSequence with a complex XML. Refer to the XML provided below 640 GB Intel Core i7 4 GB ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/sql-query-xml-data-oracle-xmltypepart2.html" rel="bookmark" title="SQL Query XML Data Oracle XMLTYPE–Part2">SQL Query XML Data Oracle XMLTYPE–Part2</a><br><br>If you had referred to the XML SQL Question that we asked in previous post, we have been given a sizeable XML and told to produce a report out of it. The final resulting XML would be big, but the solution can be easily understood by breaking it into finer chunks. We will aim to provide the tiny blocks and come up with one final SQL in this post. We start with creating our test table and copy the ....
<br><br><a href="http://thinktibits.blogspot.com/2011/05/oracle-sqltxplain-sqlt-tutorial-example_8982.html" rel="bookmark" title="Oracle SQLTXPLAIN SQLT Tutorial Example Part 4">Oracle SQLTXPLAIN SQLT Tutorial Example Part 4</a><br><br>In the previous tutorial, we covered the diagnostic information that gets generated out of SQLTXPLAIN utility for a given SQL. The report was highly comprehensive that I have end up writing another part of my blog dedicated to the contents of the report. It is important that you read and understand the report elements before taking a decision to use this utility. But, I'm sure that once you start ....
<br><br><a href="http://thinktibits.blogspot.com/2011/05/oracle-sqltxplain-sqlt-tutorial-example_01.html" rel="bookmark" title="Oracle SQLTXPLAIN SQLT Tutorial Example Part 3">Oracle SQLTXPLAIN SQLT Tutorial Example Part 3</a><br><br>We executed XTRACT method of SQLTXPLAIN utility in the previous post and collected a big zip file in the output. The report is really exhaustive and will help a DBA to understand more about the system upon reading it. There will be three files main, lite and readme. The Main file is the one we will be looking into. In my case, the size of the main HTM file is about 500 kb. At a high level, let us ....
<br><br><a href="http://thinktibits.blogspot.com/2011/05/oracle-sqltxplain-sqlt-tutorial-example.html" rel="bookmark" title="Oracle SQLTXPLAIN SQLT Tutorial Example Part 2">Oracle SQLTXPLAIN SQLT Tutorial Example Part 2</a><br><br>In our first tutorial, we discussed how to setup and configure SQLTXPLAIN for your Oracle Database. We discussed the values we have to configure for this performance measuring tool during installation, and we have managed to install it successfully into our Oracle database. In this part, we will discuss how to use SQLT and collect diagnostic details; To generate statistics for a SQL SQLTXPLAIN ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-width-bucket-function-example.html" rel="bookmark" title="Oracle WIDTH BUCKET Function Example">Oracle WIDTH BUCKET Function Example</a><br><br>In this post, we will explain the usage of WIDTH_BUCKET analytic function in Oracle with a simple example. The function WIDTH_BUCKET may sound odd, but the example that we will provide to explain it would make sure that you crack its usage without complexities. To understand what WIDTH_BUCKET is all about, consider a scenario where you have a set of different brands of laptops with their Price ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-xmlexists-function-example.html" rel="bookmark" title="Oracle XMLExists Function Example">Oracle XMLExists Function Example</a><br><br>We earlier discussed existsNode function in Oracle with examples. During our discussion, we mentioned that existsNode is deprecated with 11g Release 2 version of Oracle and Oracle recommends using XMLExists function instead. In this blog post, we will see what XMLExists is all about and run through some examples of using XMLExists against an XMLTYPE column in Oracle. To start with, consider a ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-insertchildxml-function-example.html" rel="bookmark" title="Oracle insertChildXML Function Example">Oracle insertChildXML Function Example</a><br><br>In this post, we will explain the insertChildXML function in Oracle with a simple example. We will use the XML provided below : HP G62X-400 LD644AAR 15.6 450 Sony VAIO VPCEE45FX/WI 15.5 ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-existsnode-extract-function.html" rel="bookmark" title="Oracle ExistsNode Extract Function Examples - Part 3">Oracle ExistsNode Extract Function Examples - Part 3</a><br><br>While discussing UpdateXML with ExistsNode in our previous blog post, we got introduced to ExistsNode and in this post, we will see how to make use of this function to the fullest with some very good examples. To cover off all the examples of using ExistsNode in a comprehensive way, we need a good XML to make use of. For explaining the usage of ExistsNode function, we will be using the XML as ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-updatexml-with-existnode-example.html" rel="bookmark" title="Oracle UpdateXML with ExistNode - Example - Part 2">Oracle UpdateXML with ExistNode - Example - Part 2</a><br><br>Our introduction post to UpdateXML contained a basic example that explained how to use UpdateXML on XMLType Column in Oracle. That is an eye-opener to a beginner who thinks this is a complex function to use in Oracle. We continue our UpdateXML explanations with some more examples in this post. Before we start to explain some more features inside UpdateXML, let us add some more rows into our ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/ora-01555-snapshot-too-old-error-in.html" rel="bookmark" title="ORA-01555: Snapshot too old - Error in Oracle">ORA-01555: Snapshot too old - Error in Oracle</a><br><br>Some ORA errors are easy to understand for common user..Some are technical..This post aims to help a beginner to understand what this snapshot too old error is about and what are the common causes for this error. I will try to keep this as simple as possible so that it can be easily understood. Before we understand much about this error, we have to understand a few jargons which we will be ....
<br><br><a href="http://thinktibits.blogspot.com/2008/08/oca-oracle-database-11g-administration.html" rel="bookmark" title="OCA Oracle Database 11g: Administration I Exam Guide">OCA Oracle Database 11g: Administration I Exam Guide</a><br><br>I found this book when surfing across the web..OCA Oracle Database 11g: Administration I Exam Guide..If you are preparing for your OCA certification, and seriously looking for a suitable book, then this is the one for you..Check out the link given below for more information..Link ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/sql-query-on-xml-data-xmlsequence-part.html" rel="bookmark" title="SQL Query on XML Data - XMLSequence - Part 1">SQL Query on XML Data - XMLSequence - Part 1</a><br><br>In this post, we will demonstrate a slighlty complex method of querying XML data from Oracle using XMLSequence. If you'd gone through the previous posts for querying XML Data using XMLSequence, this post would be easy enough to read through. We will continue to use our iphone XML example for this post, and the challenge would be to write a SQL Query based on this XML provided below: <product ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-xmlsequence-usage-solution.html" rel="bookmark" title="Oracle XMLSequence Usage - Solution">Oracle XMLSequence Usage - Solution</a><br><br>For our XMLSequence example question posted in this blog earlier, I've been getting a lot of responses and felt I would give my solution to this problem. If you are landing on this page directly, I would suggest you to go through the question first. The XML we outlined is produced again below: IPhones <modeldetails ModelID ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/oracle-xmlsequence-example.html" rel="bookmark" title="Oracle XMLSequence Example">Oracle XMLSequence Example</a><br><br>In our previous post, we presented a simple example that showed how to pull out data from XMLTYPE field using XMLSequence function. The example shown in the previous post was a basic one that illustrated how simple the usage of XMLSequence could be, if it is used correctly.The XMLs used in the post were simple, and in this post, I'm going to provide a slightly complex example of using XMLSequence ....
<br><br><a href="http://thinktibits.blogspot.com/2011/03/xmltype-table-xmlsequence-example.html" rel="bookmark" title="XMLType Table XMLSequence Example - Oracle">XMLType Table XMLSequence Example - Oracle</a><br><br>Everybody loves XML..but when it comes to extracting data from Oracle XMLTYPE column, some of us could have a fear of starting. I will try to explain how to use XMLSequence in your SQL queries to pull data out from a table in Oracle, with an example. For the purpose of our demonstration, we will create a simple product table holding some products.. CREATE TABLE PROD_XML_TAB ( TEST_FIELD XMLTYPE ....
<br><br><a href="http://thinktibits.blogspot.com/2008/04/oracle-integrity-constraints-part-i.html" rel="bookmark" title="Oracle - Integrity Constraints - Part I">Oracle - Integrity Constraints - Part I</a><br><br>I always wanted to write an article about integrity constraints in Oracle. Today is the right day to express my thoughts on this. Constraint means Limitation or Restriction. With Oracle, I'd put it as Restriction to do something. A database user is prevented from doing an action to a column in table where a restriction is in place. The restriction to perform the action is conveyed to Oracle ....
<br><br><a href="http://thinktibits.blogspot.com/2008/04/excel-to-oracle-oraexcel.html" rel="bookmark" title="Excel to Oracle - OraExcel">Excel to Oracle - OraExcel</a><br><br>When I was searching in the web, to figure out a utility that would help me in pushing the Oracle data to Excel, I came to know about OraExcel. OraExcel is a very neat utility through which you can easily send your data in a Excel sheet to a table in Oracle. It has many more features also, but in this short tutorial I will cover how to insert data into a Oracle table using OraExcel. Before you ....
Good work indeed !!
ReplyDelete