Getting started with Oracle Stored Procedure and parse clob xml data and cursor

This Post will having the details to create stored procedure in oracle and parsing the clob data through stored procedure.

employee.xml
-------------


  siva
  32
  M

Above xml will be stored as clob data in Person table

Person table Schema -

Id NUMBER,
Name Varchar2(50),
employee_details clob

Above xml will be stored as clob data in Person table

create table PERSON (Id NUMBER, Name varchar2(50), employee_details clob);
  create table EMPLOYEE (Name varchar2(50), age NUMBER ,sex varchar2(10));

We need to parse the clob data using oracle procedure

create or replace PROCEDURE SP_PARSE_CLOB (person_id NUMBER) AS
  PERSION_ID Person.id%type;
  PERSON_NAME Person.name%type;
  EMPLOYEE_DETAILS Person.employee_details%type;
  name varchar2(50);
  age varchar2(50);
  sex varchar2(50);
  EMPLOYEE_exc EXCEPTION;
  -- Create a cursor
 cursor personDataCursor IS SELECT id, Name,employee_details from PERSON;
 BEGIN
   OPEN personDataCursor;
 LOOP
 FETCH personDataCursor into PERSION_ID,PERSON_NAME,EMPLOYEE_DETAILS;
 EXIT WHEN personDataCursor%notfound;
 IF XMLTYPE(EMPLOYEE_DETAILS).existSNode('/Employee/name/text()') > 0 THEN
   name := XMLTYPE(EMPLOYEE_DETAILS).extract('/Employee/name/text()').getStringVal();
 END IF;
 IF XMLTYPE(EMPLOYEE_DETAILS).existSNode('/Employee/age/text()') > 0 THEN
  age := XMLTYPE(EMPLOYEE_DETAILS).extract('/Employee/age/text()').getStringVal();
 END IF;
 IF XMLTYPE(EMPLOYEE_DETAILS).existSNode('/Employee/sex/text()') > 0 THEN
  sex := XMLTYPE(EMPLOYEE_DETAILS).extract('/Employee/sex/text()').getStringVal();
 END IF;
 dbms_output.put_line('Name:'|| name|| ' ' || 'Age:' || ' ' ||age || ' ' || 'Sex:' || ' ' || sex);
 -- Inset into another table
BEGIN
  insert into EMPLOYEE(name,age,sex) values(name,age,sex);
 EXCEPTION
    WHEN OTHERS
   THEN
    RAISE EMPLOYEE_exc;
  END;
  commit;
  END LOOP;

EXCEPTION
    WHEN EMPLOYEE_exc
      THEN
        rollback;
     -- Do what ever you want like insert log details in any another table
    commit;
    DBMS_OUTPUT.PUT_LINE ('Insertion failed in EMPLOYEE : '|| '' || name);
    END;

ArithmeticOperations Example using Python

Arithmetic operation using python in windows.
In my previous post , I have written how to install python in windows and add plugin into eclipse.


In this post I am going to explain simple arithmetic operations.

Step 1: Open eclipse - File - New - PyDev Project - arithmeticoperation_test
Step 2: Right click on the - src - New- PyDevModule- ArithmeticOperation
Step 3:Paste below code into ArithmeticOperation.py file.

'''
Created on 07-Dec-2015


@author: rajusiva

'''
a = input("Enter a value:")
b = input("Enter b value:")
print("Addition of 2 values is[",int(a)+int(b),"]")
print("subtract of 2 values is[",int(a)-int(b),"]")
print("Multiply of 2 values is[",int(a)*int(b),"]")
print("Division of 2 values is[",int(a)/int(b),"]")
print("Exponent of 2 values is[",int(a)**int(b),"]")
print("Modules of 2 values is[",int(a)%int(b),"]")
print("Floor Division of 2 values is[",int(a)//int(b),"]")

Step 4: Right click on the program-Run As- Python Run
Step 5: output as follows

Enter a value:25
Enter b value:10
Addition of 2 values is[ 35 ]
subtract of 2 values is[ 15 ]
Multiply of 2 values is[ 250 ]
Division of 2 values is[ 2.5 ]
Exponent of 2 values is[ 95367431640625 ]
Modules of 2 values is[ 5 ]
Floor Division of 2 values is[ 2 ]

Getting started with python using eclipse

Getting started with Python using eclipse

Step1: Download latest Python from the python site.

                 https://www.python.org/downloads/

Step2:  Click on the downloaded exe file. Then it will open like below screen

           

Step 3: While installing , you need to  check the both check box.

Step 4: Click the Customize installation, .

Step 5: Click on the Next button,

Step 6: Change the Customize install location(C:\Python32), you can give any location where ever

                  you need python to be install. Then click finish

Step 7:  Python installed successfully on your machine.

Step 8: Now we need to download eclipse latest version for windows from this link.

                    http://www.eclipse.org/downloads/

Step 9 : unzip the downloaded eclipse.

Step 10:  open eclipse and select workspace

Step 11:   Click on help – Install New Software

Step 12: Provide   http://pydev.org/updates as mentioned below screenshot select all checkboxes, then click finish.

Step 13 :Select the checkboxes to install the python plugin in eclipse

Step 14: Need to configure python in eclipse.

  Go to eclipse – Windows- preferences-

Step 15: Check for PyDev or python – Python Interpreter – Click on the new button

Add the Interpreter Name and Interpreter Executable – where ever your python installed location

Python plugin has been configured in your eclipse successfully.

Step 16 : create PyDev project- Give any project name (helloworld_python)

    In eclipse- File- New - PyDev project

Provide name as  - helloworld_python then click finish, But make sure – Create src folder and add it to the PYTHONPATH- radio button selected

Step 17: Now python project created. Need to create Python Module in order to start the coding.

              Right click on the src folder of the python project- >new -> pyDevModule

Step 18: Click finish

Copy below code into your program

def hello(userInput):
    return "Hello World:"+userInput
userInput= input("Please type your name:")
print(hello(userInput))

Step 19: How to run the python program – Right click on the program

  Run As – Python Run

Input - Please type your name: siva

Output- Hello World: siva