Oracle: Calling Oracle Procedure from AUTOSYS and Detecting Failures

Oracle provides Oracle Jobs feature to create and schedule long running Jobs to execute particular piece of SQL script at scheduled time of interval with event notifications in case of any failures and success. But in our case we were asked not to use Oracle Jobs as triggering and scheduling was responsibility of AutoSys (Workload Automation Tool) as per framework. We were using stored procedure to execute an long running job which processes million of records.

I don’t know if there is any “proper” way to configure Oracle in AutoSys but we were only aware of how to trigger long running Oracle procedure in a .NET Console Application.

While working on the script to trigger Oracle stored procedure to detect if there are any errors and let AutoSys know following errors –

  1. All “ORA-” errors returned by SQL*Plus
  2. All “SP2-” errors returned by SQL*Plus

 

Follow below steps to configure batch file –

PRE-REQUISITES –

  1. SQL*Plus is installed on App Server
  2. Required credentials to execute stored procedure successfully

USAGE –
Modify execute_script.bat (deployable) –

@echo off
sqlplus USER_NAME/******@MYTNSNAME @script.sql | findstr “ORA- SP2- ERROR”
if %errorlevel% == 0 (exit /b 1) else (exit /b 0)

 

Modify script.sql (deployable) –

begin
      Process();
end;
/
quit;

Checklist –
Replace highlighted call with required stored procedure (may need to include package name as well)
Make sure that calling procedure is raising error with RAISE statement in EXCEPTION block.
Note –
Do not remove ‘/’ and ‘quit;’ as these are required
Run Test Batch File.exe
For errors, you will see following output –


Exit Code: 1

AUTOSYS should show : Failure
Please press any key to exit !!!
For success,   you will see following output –


Exit Code: 0


AUTOSYS should show : Success
Please press any key to exit !!!

How to: Rename Schema of Existing Table

Problem

If you want to change the schema name of an existing table, you will probably look for ALTER table script to do the same. However, the real problem is you cannot change name of the schema directly neither using of the table.

Solution

Solution to this problem is to use ALTER SCHEMA statement on target schema –

ALTER SCHEMA <target schema> TRANSFER <table_name>

Example

Earlier I had table name with schema as – Product.Attribute. Here, schema name is ‘Product’ and table name is ‘Attribute’.

Later I made a decision to rename this table to dbo.Attribute with default schema as ‘dbo’.

To do this I ran following statement –

ALTER
SCHEMA dbo TRANSFER Product.Attribute

Structured Programming vs. Object-Oriented Programming

Overview

Newbie programmers are often baffled with programming methodology and programming language. Methodology is concerned with the way you write a program. Commonly known programming methodologies are structured programming, procedural programming, object oriented programming, etc. It is possible to write a program using object-oriented programming language with structured programming methodology. There are two most commonly known programming methodologies –

  1. Structured Programming
  2. Object-Oriented Programming

Structured Programming

In 1970 and 1980, popular programming methodology was structured programming. Structured programming methodology is based on following process –

  1. Break down a large problem into several sub-parts
  2. Treat each part as a new problem and work on it
  3. Repeat the process with each new part until each part can be solved independently, without further decomposition

This idea of development is known as program development by stepwise refinement. As I stated earlier, it is possible to write a program in object-oriented programming language such as Java, C# with structured programming methodology.

Example (C Language) –

[code language="c"]

#include <stdio.h>
#define PI 3.141

float calculateTriangleArea(float base, float height)
{
float area;
area = 0.5 * base * height;
return area;
}

float calculateRectangleArea(float width, float height)
{
float area;
area = width * height;
return area;
}

float calculateCircleArea(float radius)
{
float area;
area = PI * radius * radius;
return area;
}

int main()
{
float area;

area = calculateRectangleArea(2.5, 3);

printf("Area of Rectangle: %f", area);

area = calculateCircleArea(2.5);
}

[/code]

Object-Oriented Programming

Most important features of object-oriented programming are inheritance, polymorphism and encapsulation.

System.Environment.SpecialFolder does not contain a definition for MyVideos

 

I was trying to set root folder of FolderBrowserDialog to MyVideos folder. I was using .NET Framework 3.5. While compiling I got error - ‘System.Environment.SpecialFolder’ does not contain a definition for ‘MyVideos’.

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace ChromaticBox.MobileHunt.Web.Wireframe
{
    public class Class1
    {
        public void Methof()
        {
            //compilation error
            //this.fldrBrowserDialog.RootFolder = System.Environment.SpecialFolder.MyVideos;

            //workaround
            object fldrBrowserDialog = null;

            if (fldrBrowserDialog != null)
            {
               
Type t = fldrBrowserDialog.GetType();

               
System.Reflection.FieldInfo fi = t.GetField("rootFolder", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);

               
fi.SetValue(fldrBrowserDialog, 0x000e); // 0x000e - represents My Videos folder
            }

           
            var showDialog = fldrBrowserDialog.ShowDialog();
        }
    }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace ChromaticBox.MobileHunt.Web.Wireframe
{
    public class Class1
    {
        public void Methof()
        {
            //compilation error

            //this.fldrBrowserDialog.RootFolder = System.Environment.SpecialFolder.MyVideos;


            //workaround

            Type t = fldrBrowserDialog.GetType();

            System.Reflection.FieldInfo fi = t.GetField("rootFolder", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);

            fi.SetValue(fldrBrowserDialog, 0x000e); // 0x000e - represents My Videos folder


            fldrBrowserDialog.ShowDialog();
        }
    }
}

 

   1: using System;

   2: using System.Collections.Generic;

   3: using System.Linq;

   4: using System.Web;

   5:  

   6: namespace ChromaticBox.MobileHunt.Web.Wireframe

   7: {

   8:     public class Class1

   9:     {

  10:         public void Methof()

  11:         {

  12:             //compilation error

  13:  

  14:             //this.fldrBrowserDialog.RootFolder = System.Environment.SpecialFolder.MyVideos;

  15:  

  16:  

  17:             //workaround

  18:  

  19:             Type t = fldrBrowserDialog.GetType();

  20:  

  21:             System.Reflection.FieldInfo fi = t.GetField("rootFolder", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);

  22:  

  23:             fi.SetValue(fldrBrowserDialog, 0x000e); // 0x000e - represents My Videos folder

  24:  

  25:  

  26:             fldrBrowserDialog.ShowDialog();

  27:         }

  28:     }

  29: }

   1: using System;

   2: using System.Collections.Generic;

   3: using System.Linq;

   4: using System.Web;

   5:  

   6: namespace ChromaticBox.MobileHunt.Web.Wireframe

   7: {

   8:     public class Class1

   9:     {

  10:         public void Methof()

  11:         {

  12:             //compilation error

  13:  

  14:             //this.fldrBrowserDialog.RootFolder = System.Environment.SpecialFolder.MyVideos;

  15:  

  16:  

  17:             //workaround

  18:  

  19:             Type t = fldrBrowserDialog.GetType();

  20:  

  21:             System.Reflection.FieldInfo fi = t.GetField("rootFolder", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);

  22:  

  23:             fi.SetValue(fldrBrowserDialog, 0x000e); // 0x000e - represents My Videos folder

  24:  

  25:  

  26:             fldrBrowserDialog.ShowDialog();

  27:         }

  28:     }

  29: }

Following is the workaround to above problem -

 

Untitled

   1: using System;

   2: using System.Collections.Generic;

   3: using System.Linq;

   4: using System.Web;

   5:  

   6: namespace ChromaticBox.MobileHunt.Web.Wireframe

   7: {

   8:     public class Class1

   9:     {

  10:         public void Methof()

  11:         {

  12:             //compilation error

  13:  

  14:             //this.fldrBrowserDialog.RootFolder = System.Environment.SpecialFolder.MyVideos;

  15:  

  16:  

  17:             //workaround

  18:  

  19:             Type t = fldrBrowserDialog.GetType();

  20:  

  21:             System.Reflection.FieldInfo fi = t.GetField("rootFolder", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);

  22:  

  23:             fi.SetValue(fldrBrowserDialog, 0x000e); // 0x000e - represents My Videos folder

  24:  

  25:  

  26:             fldrBrowserDialog.ShowDialog();

  27:         }

  28:     }

29: }

 

 

 

 

   1: using System;

   2: using System.Collections.Generic;

   3: using System.Linq;

   4: using System.Web;

   5:  

   6: namespace ChromaticBox.MobileHunt.Web.Wireframe

   7: {

   8:     public class Class1

   9:     {

  10:         public void Methof()

  11:         {

  12:             //compilation error

  13:  

  14:             //this.fldrBrowserDialog.RootFolder = System.Environment.SpecialFolder.MyVideos;

  15:  

  16:  

  17:             //workaround

  18:  

  19:             Type t = fldrBrowserDialog.GetType();

  20:  

  21:             System.Reflection.FieldInfo fi = t.GetField("rootFolder", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);

  22:  

  23:             fi.SetValue(fldrBrowserDialog, 0x000e); // 0x000e - represents My Videos folder

  24:  

  25:  

  26:             fldrBrowserDialog.ShowDialog();

  27:         }

  28:     }

  29: }

How to: View Other User Schema in Oracle SQL Developer

Overview

I have started learning Oracle 11g. As a Microsoft SQL Server developer it is obvious that I will try to find ways of doing same stuff that I used to do in Management Studio.

I came to know that Oracle is quite different the way it works and its internals. I will talk about internals in my upcoming articles.

It might sound silly but the problems I faced initially, were related to Oracle SQL Developer.  Oracle SQL Developer is an Integrated development environment (IDE) which can be compared to Management Studio.

Continue reading