• 488
    1

    In this blog I will give you a tip to how to find error line number in PL/SQL program easily. Suppose you have a lengthy PL/SQL procedure or function which might be a standalone or inside a package and while executing, it is throwing an exception / error. And because exception is also being handled properly ...
  • 295
    0

    Suppose you have a stored procedure in Oracle database which is using XML as parameter to process the data and you want to pass XML parameter to stored procedure to execute it. For this case below is the example PLSQL block showing that how you can pass XML as parameter to stored procedure in Oracle. ...
  • 236
    0

    It’s a good news for bloggers who are using Blogger platform for their blogs that Blogger introduced many beautiful themes on 20th March 2017. These themes are fully responsive and SEO friendly with all new fresh look. So you can use these blogger templates launched in 2017 for your blogs rather than taking blogger templates ...
  • 237
    0

    A simple example is given below to insert a long string into CLOB data type in Oracle. You can not insert data directly into CLOB data type, you have to use Empty_Clob() function with returning clause in insert statement then use dbms_lob.write procedure to write the data. Because if you will try to insert directly ...
  • 316
    0

    In the previous blog post I have given the example to use Oracle Bulk Collect Using Cursor Rowtype Type Object. Which first creates the cursor then type object of cursor type and then open the cursor, fetching into bulk collect and then finally process them using Forall. But sometimes you may be require to process ...
  • 333
    0

    In this post I am giving an example to perform an insert into using Oracle Bulk Collect and FORALL command. Created a PL SQL function which will take data from EMP table using a cursor then creates type array variable of that cursor row type and then it bulk collect and insert into BONUS table ...
  • 285
    0

    Oracle joins with examples are given below to describe Oracle Joins using new syntax. Which is very easy to understand and very good in SQL query readability. Because if query is larger and having too many tables with too  many joins in old syntax it becomes complex to understand. So it is better to use ...
  • 252
    0

    Below is the example of SQL query to read / extract data from XML in Oracle. You can query any XML easily by providing its namespace clause and the path information through SQL query. Suppose you have XML data in the following format: <main>  <DATA_RECORD>    <EMPNO>7369</EMPNO>    <ENAME>SMITH11V9</ENAME>    <JOB>CLERK</JOB>    <MGR>7698</MGR>    <HIREDATE>1980/12/17 ...
  • 195
    0

    Sometime happens that you posted a blog for a particular topic but you put the title which is not that relevant and after publishing the blog post you realize that you made a mistake and then you change the title of your blog post and publish it again but in Google search result it is ...
  • 305
    0

    The SQL example is given below to count number of character occurrences in a string in Oracle. You can more customize the SQL as per your requirement. The above SQL will count number of occurrences in a string and output would be as below: CHARACTER OCCURRENCES—————— ———–q                 ...
  • 450
    0

    Below are the steps to upgrade Oracle Apex from 5.0 to 5.1 version. As previously I already posted to Install Oracle Applicaiton Express 5.0 on Oracle 12c PDB. After downloading the Oracle Apex from Oracle extract the zip file and change the current directory to the directory where you extracted and then connect to Oracle database ...
  • 545
    0

    Generate Insert and Update SQL statements from Excel sheet is easy but sometimes seems very boring and tedious job, if table is having many columns and you need to do this for many Excel sheets to import the data into Oracle. Because there is also need to put lot of quotes and ampersands (&) and ...
  • 195
    0

    I have been to Hong Kong several times and I am very much familiar with most of the places in Hong Kong. But this time I got some new experience when I forgot my one bag in bus A41 which was from Airport to Shatin Central Bus Stop. I was very tired due to connecting ...
  • 932
    0

    Follow these just 5 simple steps to change an application title in Oracle Apex (Oracle Application Express). 1. In your application builder of Oracle Apex, click on the Edit Application Properties as shown below: 2. Then click on User Interface Tab as shown below: 3. After that click on Logo Tab, then change the application ...