Anupam Bhattacharya

How to Detect Hidden Camera in Trial Room?

2011-09-25T07:04:00.000-07:00

How to Detect Hidden Camera in Trial Room?

In fron...t of the trial room take your mobile and make sure that mobile can make calls........
Then enter into the trail room, take your mobile and make a call.....
If u can't make a call......!!!!
There is a hidden camera......
This is due to the interference of fiber optic cable during the signal transfer......

Please forward this to your friends to educate this issue to the
public......To prevent our innocent ladies from HIDDEN CAMERA...........

Pinhole Cameras in Changing Rooms of Big Bazaar, Shoppers Stop?

A few days ago, I received this text message:
Please don't use Trial room of BIG BAZAAR there are pinhole cameras to make MMS of young girls.
So, please forward to all girls. Also forward to all boys who have sisters and girlfriends.

Don't be shy in forwarding this message. Because its about protecting the integrity of all girls & ladies.

HOW TO DETECT A 2-WAY MIRROR?

When we visit toilets, bathrooms, hotel rooms, changing rooms, etc., How many of you know for sure that the seemingly ordinary mirror hanging on the wall is a real mirror, or actually a 2-way mirror I.e., they can see you, but you can't see them. There have been many cases of people installing 2-way mirrors in female changing rooms or bathroom or bedrooms.

It is very difficult to positively identify the surface by just looking at it. So, how do we determine with any amount of certainty what type of Mirror we are looking at?

CONDUCT THIS SIMPLE TEST:

Place the tip of your fingernail against the reflective surface and if there is a GAP between your fingernail and the image of the nail, then it is a GENUINE mirror.

However, if your fingernail DIRECTLY TOUCHES the image of your nail, then BEWARE, IT IS A 2-WAY MIRROR! (There may be someone seeing you from the other side). So remember, every time you see a mirror, do the "fingernail test." It doesn't cost you anything. It is simple to do.

This is a really good thing to do. The reason there is a gap on a real mirror, is because the silver is on the back of the mirror UNDER the glass.

Whereas with a two-way mirror, the silver is on the surface. Keep it in mind! Make sure and check every time you enter in hotel rooms.

Volcano

2011-06-10T09:28:00.000-07:00

Random Number Generation Based on Time Salt Value

2011-06-04T00:00:00.000-07:00

package random;

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.TimeZone;
import javax.swing.JOptionPane;

public class randomnumber
{
    public static int count=0;

    public static void main(String[] args) throws NoSuchAlgorithmException, ParseException
    {
            convert_encrypt_single cenc=new convert_encrypt_single();

            for(count=0;count<1;count++)
            {
                int encount=0;


                GMTDateTime d_obj=new GMTDateTime();

                String targetname= d_obj.getdatetimegmt();

                String[] name = {
                                    null,
                                    targetname,
                                    null
                                };

                String encryptstring=cenc.encrypt(cenc.encrypt(name[1]).toString().toUpperCase()).toString().toUpperCase();
for(encount=-1;encount<encryptstring.length()+15469;encount++)
                {
                    encryptstring=cenc.encrypt(encryptstring).toString().toUpperCase();
                }
                System.out.println(encryptstring);


                StringSeperate seperate_obj=new StringSeperate();
                String randomnumber=seperate_obj.strseperate(encryptstring);
                System.out.println(randomnumber);

                JOptionPane output = new JOptionPane();
                JOptionPane.showMessageDialog(output,randomnumber, "     Password     ",JOptionPane.INFORMATION_MESSAGE);
            }
            System.exit(0);        //Runtime.getRuntime().exit(0);
     }
}

class convert_encrypt_single {

public StringBuilder encrypt(String plaintext) throws NoSuchAlgorithmException
{

        String algorithm = "SHA-512";     //MD5

        byte[] middletext = plaintext.getBytes();

        MessageDigest md = MessageDigest.getInstance(algorithm);

        md.reset();
        md.update(middletext);
        byte[] encodedPassword = md.digest();

        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < encodedPassword.length; i++) {
          if ((encodedPassword[i] & 0xff) < 0x10) {
            sb.append("0");
          }
          sb.append(Long.toString(encodedPassword[i] & 0xff, 16));
        }
    return sb;
}
}

class GMTDateTime {

    public String getdatetimegmt() throws ParseException {

        SimpleDateFormat dateFormatGmt = new SimpleDateFormat("yyyy-MMM-dd HH:mm:ss");
        dateFormatGmt.setTimeZone(TimeZone.getTimeZone("GMT"));


        SimpleDateFormat dateFormatLocal = new SimpleDateFormat("yyyy-MMM-dd HH:mm:ss");
        System.out.println(dateFormatLocal.parse(dateFormatGmt.format(new Date())));
        System.out.println(dateFormatGmt.format(new Date()));

        Calendar aGMTCalendar = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
        System.out.println(aGMTCalendar.getTime());
        System.out.println(aGMTCalendar.getTimeInMillis());

        //return aGMTCalendar.getTime().toString()+aGMTCalendar.getTimeInMillis() ;
        return dateFormatLocal.parse( dateFormatGmt.format(new Date()))+dateFormatGmt.format(new Date()).toString()+aGMTCalendar.getTime().toString()+aGMTCalendar.getTimeInMillis() ;

    }

}

class StringSeperate{
    public String strseperate(String source) throws ParseException {
        Integer SeperateInterval=8;
        String destination ="";
        String mergeinterval="";
        Integer strlength=source.length();
        Integer counterseperator=0;
        for(int interval=0;interval
            mergeinterval=mergeinterval+source.substring(interval, interval+SeperateInterval)+'-';

        destination=mergeinterval.substring(0,strlength+counterseperator-1);
        return destination;
        }
        }

3D-Love Symbol

2011-05-28T07:24:00.000-07:00

Defination of Gorgeous

2011-05-16T09:03:00.000-07:00

2011 Target

2011-05-15T07:06:00.000-07:00

TablePackageStoredProcedureCreation

2011-05-14T08:55:00.001-07:00

package TablePkgSPCreation;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JOptionPane;

import configConnection.JdbcConnectionSettings;

public class TablePackageStoredProcedureCreation {
    private static Connection con = null;
    public static void main(String[] args) throws Exception {
        TablePackageStoredProcedureCreation sp_creation_obj=new TablePackageStoredProcedureCreation();
        sp_creation_obj.ProcedureCreation();
        sp_creation_obj.PkgProcedureCreation();
        sp_creation_obj.TableCreation();
    }

    private void connectionEstblished() throws SQLException, Exception {
        JdbcConnectionSettings conn_db_obj = new JdbcConnectionSettings();
        con = conn_db_obj.connection_setting();
    }

    private void ProcedureCreation() throws Exception
    {
        TablePackageStoredProcedureCreation connection_obj=new TablePackageStoredProcedureCreation();
        connection_obj.connectionEstblished();

        JOptionPane pane_procedure_creation_obj=new JOptionPane("Jdbc_Procedure_Creation");

        try{

             Statement stmt_user_select = con.createStatement();
             String procedure_user_select = "CREATE OR REPLACE PROCEDURE sp_myempinfo_user_select(input_aid IN VARCHAR2,output_name OUT VARCHAR2,output_salary OUT NUMBER) AS \n"
                + "BEGIN \n"
                + "SELECT name,salary INTO output_name,output_salary FROM myempinfo WHERE aid = input_aid ; \n"
                +"EXCEPTION \n"
                +"    WHEN OTHERS THEN \n"
                +"RAISE_APPLICATION_ERROR (-20004,input_aid || ':$:' || SQLERRM, TRUE) ; \n"
                + "END sp_myempinfo_user_select;";
              stmt_user_select.executeUpdate(procedure_user_select);
              stmt_user_select.close();


              Statement stmt_cursor_select = con.createStatement();
            String procedure_cursor_select = "CREATE OR REPLACE PROCEDURE sp_myempinfo_cursor_select(IO_CURSOR OUT SYS_REFCURSOR) AS \n"
                    + "BEGIN \n"
                    + "OPEN IO_CURSOR FOR SELECT * FROM myempinfo ; \n"
                    + "END sp_myempinfo_cursor_select;";
            stmt_cursor_select.executeUpdate(procedure_cursor_select);
            stmt_cursor_select.close();


            Statement stmt_insert = con.createStatement();
            String procedure_insert = "CREATE OR REPLACE PROCEDURE sp_myempinfo_insert(input_aid IN VARCHAR2,input_name IN VARCHAR2,input_salary IN NUMBER) AS \n"
                        + "BEGIN \n"
                        + "INSERT INTO myempinfo VALUES(input_aid,input_name,input_salary); \n"
                        +"EXCEPTION \n"
                        +"WHEN OTHERS THEN \n"
                        +"RAISE_APPLICATION_ERROR (-20008,input_aid || ':$:' || SQLERRM, TRUE) ; \n"
                        +"RAISE_APPLICATION_ERROR (-200012,input_name|| ':$:' || SQLERRM, TRUE) ; \n"
                        +"RAISE_APPLICATION_ERROR (-200016,input_salary|| ':$:' || SQLERRM, TRUE); \n"
                        + "END sp_myempinfo_insert;";
                stmt_insert.executeUpdate(procedure_insert);
                stmt_insert.close();


                Statement stmt_update = con.createStatement();
                String procedure_update = "CREATE OR REPLACE PROCEDURE sp_myempinfo_update(input_aid IN VARCHAR2,input_name IN VARCHAR2,input_salary IN NUMBER) AS \n"
                            + "BEGIN \n"
                            + "UPDATE myempinfo SET salary=input_salary WHERE aid=input_aid AND name=input_name; \n"
                            +"EXCEPTION \n"
                            +"    WHEN OTHERS THEN \n"
                            +"RAISE_APPLICATION_ERROR (-20008,input_aid || ':$:' || SQLERRM, TRUE) ; \n"
                            +"RAISE_APPLICATION_ERROR (-200012,input_name|| ':$:' || SQLERRM, TRUE) ; \n"
                            +"RAISE_APPLICATION_ERROR (-200016,input_salary|| ':$:' || SQLERRM, TRUE); \n"
                            + "END sp_myempinfo_update;";
                stmt_update.executeUpdate(procedure_update);
                stmt_update.close();


                Statement stmt_delete = con.createStatement();
                String procedure_delete = "CREATE OR REPLACE PROCEDURE sp_myempinfo_delete(input_aid IN VARCHAR2) AS \n"
                                + "BEGIN \n"
                                + "DELETE FROM myempinfo WHERE aid=input_aid ; \n"
                                +"EXCEPTION \n"
                                +"    WHEN OTHERS THEN \n"
                                +"RAISE_APPLICATION_ERROR (-20008,input_aid || ':$:' || SQLERRM, TRUE) ; \n"
                                + "END sp_myempinfo_delete;";
                stmt_delete.executeUpdate(procedure_delete);
                stmt_delete.close();

        }
                 catch(Exception e)
                    {
                        System.out.println(e.getMessage());
                        JOptionPane pane_jdbc_pane_procedure_creation_exception_obj=new JOptionPane("Jdbc_Exception");
                        JOptionPane.showMessageDialog(pane_jdbc_pane_procedure_creation_exception_obj, e.getMessage(),"Jdbc Insert Operation Failed" ,JOptionPane.ERROR_MESSAGE);
                        System.exit(1);
                    }
                    finally
                    {
                        con.close();
                        System.out.println("Jdbc Stored Procedure Creation Succeed");
                        JOptionPane.showMessageDialog(pane_procedure_creation_obj,"Jdbc Stored Procedure Creation Succeed" , con.toString(),JOptionPane.INFORMATION_MESSAGE);
                    }
    }


    private void PkgProcedureCreation() throws Exception
    {
        TablePackageStoredProcedureCreation connection_obj=new TablePackageStoredProcedureCreation();
        connection_obj.connectionEstblished();

        JOptionPane pane_package_procedure_creation_obj=new JOptionPane("Jdbc_Package_Procedure_Creation");

        try{

              Statement stmt_pkg_declaration_select = con.createStatement();
            String pkg_declaration_procedure_select = "CREATE OR REPLACE PACKAGE pkgmyempinfo IS \n"
                    + "PROCEDURE sp_myempinfo_pkg_user_select(input_aid IN VARCHAR2,output_name OUT VARCHAR2,output_salary OUT NUMBER);\n"
                    + "PROCEDURE sp_myempinfo_pkg_cursor_select(IO_CURSOR OUT SYS_REFCURSOR);\n"
                    + "END pkgmyempinfo;";
            stmt_pkg_declaration_select.executeUpdate(pkg_declaration_procedure_select);
            stmt_pkg_declaration_select.close();


               Statement stmt_pkg_body_select = con.createStatement();
                String pkg_body_procedure_select = "CREATE OR REPLACE PACKAGE BODY pkgmyempinfo IS \n"
                    + "PROCEDURE sp_myempinfo_pkg_user_select(input_aid IN VARCHAR2,output_name OUT VARCHAR2,output_salary OUT NUMBER) AS \n"
                    + "BEGIN \n"
                    + "SELECT name,salary INTO output_name,output_salary FROM myempinfo WHERE aid = input_aid ;\n"
                    + "EXCEPTION \n"
                     + "WHEN OTHERS THEN \n"
                     + "RAISE_APPLICATION_ERROR (-20004,input_aid || ':$:' || SQLERRM, TRUE) ; \n"
                     + "END sp_myempinfo_pkg_user_select;\n"
                     + "PROCEDURE sp_myempinfo_pkg_cursor_select(IO_CURSOR OUT SYS_REFCURSOR) IS\n"
                     + "BEGIN \n"
                    + "OPEN IO_CURSOR FOR SELECT * FROM myempinfo ;\n"
                    + "END sp_myempinfo_pkg_cursor_select;\n"
                    + "END pkgmyempinfo;";
                  stmt_pkg_body_select.executeUpdate(pkg_body_procedure_select);
                  stmt_pkg_body_select.close();

        }
                 catch(Exception e)
                    {
                        System.out.println(e.getMessage());
                        JOptionPane pane_jdbc_pane_package_procedure_creation_exception_obj=new JOptionPane("Jdbc_Exception");
                        JOptionPane.showMessageDialog(pane_jdbc_pane_package_procedure_creation_exception_obj, e.getMessage(),"Jdbc Insert Operation Failed" ,JOptionPane.ERROR_MESSAGE);
                        System.exit(1);
                    }
                    finally
                    {
                        con.close();
                        System.out.println("Jdbc Package Stored Procedure Creation Succeed");
                        JOptionPane.showMessageDialog(pane_package_procedure_creation_obj,"Jdbc Package Stored Procedure Creation Succeed" , con.toString(),JOptionPane.INFORMATION_MESSAGE);
                    }
    }

    private void TableCreation() throws Exception
    {
        TablePackageStoredProcedureCreation connection_obj=new TablePackageStoredProcedureCreation();
        connection_obj.connectionEstblished();
        JOptionPane pane_table_creation_obj=new JOptionPane("Jdbc_Table_Creation");


        try{
             Statement stmt_table_create = con.createStatement();
             String table_create = "CREATE TABLE myempinfo(aid VARCHAR2(20) PRIMARY KEY,name VARCHAR2(20),salary NUMBER(12,2))";
             stmt_table_create.executeUpdate(table_create);
             stmt_table_create.close();
        }
         catch(Exception e)
            {
                System.out.println(e.getMessage());
                JOptionPane pane_jdbc_pane_table_creation_exception_obj=new JOptionPane("Jdbc_Exception");
                JOptionPane.showMessageDialog(pane_jdbc_pane_table_creation_exception_obj, e.getMessage(),"Jdbc Table Creation Failed" ,JOptionPane.ERROR_MESSAGE);
                System.exit(1);
            }
            finally
            {
                con.close();
                System.out.println("Jdbc Table Creation Succeed");
                JOptionPane.showMessageDialog(pane_table_creation_obj,"Jdbc Table Creation Succeed" , con.toString(),JOptionPane.INFORMATION_MESSAGE);
            }
    }
}

OdbcDriverLoad

2011-05-14T08:54:00.002-07:00

package configConnection;

import javax.swing.JOptionPane;
public class OdbcDriverLoad {
    private static String driver_name="sun.jdbc.odbc.JdbcOdbcDriver";

    public static final void driver_load() {
    try {
            @SuppressWarnings({ "rawtypes", "unused" })
            Class oracle_driver=Class.forName(driver_name);
    } catch (ClassNotFoundException cnfe) {
        JOptionPane.showMessageDialog(null,"Driver Loading Failed", cnfe.toString(),JOptionPane.ERROR_MESSAGE);
    }
    }
}

OdbcConnectionSettings

2011-05-14T08:54:00.000-07:00

package configConnection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.swing.JOptionPane;

public class OdbcConnectionSettings {

         private static Connection con=null;
             private String user_schema_name="system";
             private String user_password="tiger";
            private String url = "jdbc:odbc:ODBCJavaOracleXE";


         public Connection connection_setting() throws SQLException , Exception {
             JdbcDriverLoad.driver_load();
             con = DriverManager.getConnection(url, user_schema_name, user_password);
            try{
                if (con.isClosed() == false) {
                       System.out.println(con);
                }
            }
            catch (SQLException sqle)
            {
                   System.out.println("sqle");
                   JOptionPane.showMessageDialog(null, "Connection Establishment Failed" ,sqle.toString(),JOptionPane.ERROR_MESSAGE);
            }
            return con;
            }

}

JdbcDriverLoad

2011-05-14T08:53:00.003-07:00

package configConnection;

import javax.swing.JOptionPane;
public class JdbcDriverLoad {
    private static String driver_name="oracle.jdbc.OracleDriver";

    public static final void driver_load() {
    try {
            @SuppressWarnings({ "unused", "rawtypes" })
            Class oracle_driver=Class.forName(driver_name);
    } catch (ClassNotFoundException cnfe) {
        JOptionPane.showMessageDialog(null,"Driver Loading Failed", cnfe.toString(),JOptionPane.ERROR_MESSAGE);
    }
    }
}

JdbcConnectionSettings

2011-05-14T08:53:00.001-07:00

package configConnection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.swing.JOptionPane;

public class JdbcConnectionSettings {

         private static Connection con=null;
             private String user_schema_name="system";
             private String user_password="tiger";
             private String url="jdbc:oracle:thin:@localhost:1521:XE";


         public Connection connection_setting() throws SQLException , Exception {
             JdbcDriverLoad.driver_load();
             con = DriverManager.getConnection(url, user_schema_name, user_password);
            try{
                if (con.isClosed() == false) {
                       System.out.println(con);
                }
            }
            catch (SQLException sqle)
            {
                   System.out.println("sqle");
                   JOptionPane.showMessageDialog(null, "Connection Establishment Failed" ,sqle.toString(),JOptionPane.ERROR_MESSAGE);
            }
            return con;
            }

}

OdbcInsertSelect

2011-05-14T08:37:00.000-07:00

package JdbcOdbcOracleXE;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import javax.swing.JOptionPane;
import configConnection.OdbcConnectionSettings;

public class OdbcInsertSelect {

    private static Connection con = null;

    public static void main(String[] args) throws SQLException, Exception {

        OdbcInsertSelect oselectinsert_obj=new OdbcInsertSelect();
        oselectinsert_obj.OdbcSelectOpearation();

        oselectinsert_obj.OdbcInsertOpearation();

        oselectinsert_obj.OdbcSelectOpearation();
    }

    private void connectionEstblished() throws SQLException, Exception {
        OdbcConnectionSettings conn_db_obj = new OdbcConnectionSettings();
        con = conn_db_obj.connection_setting();
    }

    private void OdbcInsertOpearation() throws Exception
    {
        OdbcInsertSelect connection_obj=new OdbcInsertSelect();
        connection_obj.connectionEstblished();

        JOptionPane pane_odbc_insert_obj=new JOptionPane("Odbc_Insert");

        String Query="INSERT INTO myempinfo VALUES(?,?,?)";

        String jname=null,jaid=null;
        Integer jsalary=0;

        Scanner sc=new Scanner(System.in);

        System.out.println("Associate ID : ");
        jaid=sc.next().toUpperCase();

        System.out.println("Associate Name : ");
        jname=sc.next().toUpperCase();

        System.out.println("Associate Salary : ");
        jsalary=Integer.parseInt(sc.next());

        PreparedStatement pst=con.prepareStatement(Query);
        try{
            con.setAutoCommit(false);
            pst.setString(1,jaid);
            pst.setString(2,jname);
            pst.setInt(3,jsalary);
            pst.executeUpdate();
            con.commit();
        }
        catch(SQLException e)
        {
            System.err.print("Transaction is being rolled back \n");
            con.rollback();
            System.out.println(e.getMessage());
            JOptionPane pane_odbc_insert_exception_obj=new JOptionPane("Odbc_Exception");
            JOptionPane.showMessageDialog(pane_odbc_insert_exception_obj, e.getMessage(),"Odbc InsertSelect Operation Failed" ,JOptionPane.ERROR_MESSAGE);
            System.exit(1);
        }
        finally
        {
            con.setAutoCommit(true);
            pst.close();
            con.close();
            System.out.println("Odbc Insert Operation Succeed");
            JOptionPane.showMessageDialog(pane_odbc_insert_obj,"Odbc Insert Operation Succeed" ,con.toString(),JOptionPane.INFORMATION_MESSAGE);
        }
    }
    private void OdbcSelectOpearation() throws Exception
    {
        OdbcInsertSelect connection_obj=new OdbcInsertSelect();
        connection_obj.connectionEstblished();

        JOptionPane pane_odbc_select_obj=new JOptionPane("Odbc_Select");

        String Query="SELECT * FROM myempinfo";

        PreparedStatement pst=con.prepareStatement(Query);
        ResultSet rs=pst.executeQuery();

        try{
            while (rs.next()) {
                System.out.println(rs.getString(1) + "    " + rs.getString(2) + "    "+ rs.getInt(3));
            }

        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
             JOptionPane pane_odbc_select_exception_obj=new JOptionPane("Odbc_Exception");
            JOptionPane.showMessageDialog(pane_odbc_select_exception_obj, e.getMessage(),"Odbc Select Operation Failed",JOptionPane.ERROR_MESSAGE);
            System.exit(1);
        }
        finally
        {
            pst.close();
            con.close();
            System.out.println("Odbc Select Operation Succeed");
            JOptionPane.showMessageDialog(pane_odbc_select_obj,"Odbc Select Operation Succeed" , con.toString(),JOptionPane.INFORMATION_MESSAGE);
        }
    }
}

JdbcStoredProcedureUserSelect

2011-05-14T08:36:00.001-07:00

/*
    Oracle 10 Express Edition Stored Procedure
    ===========================================================

           Table Schema
        -------------
             CREATE TABLE myempinfo(aid VARCHAR2(20) PRIMARY KEY,name VARCHAR2(20),salary NUMBER(12,2))

        Stored Procedure
        ----------------

        CREATE OR REPLACE PROCEDURE sp_myempinfo_user_select(input_aid IN VARCHAR2,output_name OUT VARCHAR2,output_salary OUT NUMBER) AS
        BEGIN
        SELECT name,salary INTO output_name,output_salary FROM myempinfo WHERE aid = input_aid ;

        EXCEPTION
                WHEN OTHERS THEN
                     RAISE_APPLICATION_ERROR (-20004,input_aid || ':$:' || SQLERRM, TRUE) ;
        END sp_myempinfo_user_select;

        Execute
        -------

        DECLARE
        output_name VARCHAR2(20);
        output_salary NUMBER(12,2);
        BEGIN
            sp_myempinfo_USER_select('254692',output_name,output_salary);
                DBMS_OUTPUT.PUT_LINE(output_name);
                 DBMS_OUTPUT.PUT_LINE(output_salary);
           END;
    ----------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------
    Cursor Stored Procedure
    -----------------------
        CREATE OR REPLACE PROCEDURE sp_myempinfo_cursor_select(IO_CURSOR OUT SYS_REFCURSOR) IS
        BEGIN
            OPEN IO_CURSOR FOR SELECT * FROM myempinfo ;
        END sp_myempinfo_cursor_select;
*/
package JdbcOdbcOracleXE;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Scanner;
import javax.swing.JOptionPane;
import configConnection.JdbcConnectionSettings;

public class JdbcStoredProcedureUserSelect {
    private static Connection con = null;
    public static void main(String[] args) throws SQLException, Exception {

        JdbcStoredProcedureCursorSelect jcursorselect_obj=new JdbcStoredProcedureCursorSelect();
        jcursorselect_obj.CusrsorSelectOpearation();

        JdbcStoredProcedureUserSelect jselect_obj=new JdbcStoredProcedureUserSelect();
        jselect_obj.UserSelectOpearation();

        jcursorselect_obj.CusrsorSelectOpearation();
    }

    private void connectionEstblished() throws SQLException, Exception {
        JdbcConnectionSettings conn_db_obj = new JdbcConnectionSettings();
        con = conn_db_obj.connection_setting();
    }

    protected void UserSelectOpearation() throws Exception
    {
        JdbcStoredProcedureUserSelect connection_obj=new JdbcStoredProcedureUserSelect();
        connection_obj.connectionEstblished();

        JOptionPane pane_jdbc_user_select_obj=new JOptionPane("Jdbc_User_Select");

        String StoredProcedureQuesry = "{call sp_myempinfo_user_select(?,?,?)}";
        CallableStatement cst = con.prepareCall(StoredProcedureQuesry);

          String joutname=null,jinaid=null;
            Integer joutsalary=0;

            Scanner sc=new Scanner(System.in);

            System.out.println("Associate ID : ");
            jinaid=sc.next().toUpperCase();

        try{

            cst.setString(1,jinaid);
            cst.registerOutParameter(2,Types.VARCHAR);
            cst.registerOutParameter(3,Types.INTEGER);

            cst.execute();

            joutname = cst.getString(2);
            joutsalary = cst.getInt(3);
            System.out.println(joutname + "    " + joutsalary);

        }
        catch(SQLException e)
        {
            System.out.println(e.getMessage());
             JOptionPane pane_jdbc_user_select_exception_obj=new JOptionPane("Jdbc_Exception");
            JOptionPane.showMessageDialog(pane_jdbc_user_select_exception_obj, e.getMessage(),"Jdbc Stored Procedure User Select Operation Failed",JOptionPane.ERROR_MESSAGE);
            System.exit(1);
        }
        finally
        {
            cst.close();
            con.close();
            System.out.println("Jdbc Stored Procedure User Select Operation Succeed");
            JOptionPane.showMessageDialog(pane_jdbc_user_select_obj, "Jdbc Stored Procedure User Select Operation Succeed" ,con.toString(),JOptionPane.INFORMATION_MESSAGE);
        }
    }
}

JdbcStoredProcedureUpdate

2011-05-14T08:35:00.003-07:00

/*
    Oracle 10 Express Edition Stored Procedure
    ===========================================================

           Table Schema
        -------------
             CREATE TABLE myempinfo(aid VARCHAR2(20) PRIMARY KEY,name VARCHAR2(20),salary NUMBER(12,2))

        Stored Procedure
        ----------------
            CREATE OR REPLACE PROCEDURE sp_myempinfo_update(input_aid IN VARCHAR2,input_name IN VARCHAR2,input_salary IN NUMBER) AS
            BEGIN
            UPDATE myempinfo SET salary=input_salary WHERE aid=input_aid AND name=input_name;
            EXCEPTION
                    WHEN OTHERS THEN
                             RAISE_APPLICATION_ERROR (-20008,input_aid|| ':$:' || SQLERRM, TRUE) ;
                    RAISE_APPLICATION_ERROR (-200012,input_name|| ':$:' || SQLERRM, TRUE) ;
                    RAISE_APPLICATION_ERROR (-200016,input_salary|| ':$:' || SQLERRM, TRUE) ;
            END sp_myempinfo_update;

        Execute
        -------
            DECLARE
            BEGIN
                sp_myempinfo_update('254896','ABCDEF',40960);
            END;
*/

package JdbcOdbcOracleXE;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;
import javax.swing.JOptionPane;
import configConnection.JdbcConnectionSettings;

public class JdbcStoredProcedureUpdate {


    private static Connection con = null;
    public static void main(String[] args) throws SQLException, Exception {

        JdbcStoredProcedureCursorSelect jcursorselect_obj=new JdbcStoredProcedureCursorSelect();
        jcursorselect_obj.CusrsorSelectOpearation();

        JdbcStoredProcedureUpdate jselect_obj=new JdbcStoredProcedureUpdate();
        jselect_obj.InsertOpearation();

        jcursorselect_obj.CusrsorSelectOpearation();
    }

    private void connectionEstblished() throws SQLException, Exception {
        JdbcConnectionSettings conn_db_obj = new JdbcConnectionSettings();
        con = conn_db_obj.connection_setting();
    }

    protected void InsertOpearation() throws Exception
    {
        JdbcStoredProcedureUpdate connection_obj=new JdbcStoredProcedureUpdate();
        connection_obj.connectionEstblished();

        JOptionPane pane_jdbc_update_obj=new JOptionPane("Jdbc_Update");

        String StoredProcedureQuesry = "{call sp_myempinfo_update(?,?,?)}";
        CallableStatement cst = con.prepareCall(StoredProcedureQuesry);

            String jinname=null,jinaid=null;
            Integer jinsalary=0;

            Scanner sc=new Scanner(System.in);

            System.out.println("Associate ID : ");
            jinaid=sc.next().toUpperCase();

            System.out.println("Associate Name : ");
            jinname=sc.next().toUpperCase();

            System.out.println("Associate Salary : ");
            jinsalary=Integer.parseInt(sc.next());

        try{


            cst.setString(1,jinaid);
            cst.setString(2,jinname);
            cst.setInt(3,jinsalary);

            cst.executeUpdate();
        }
        catch(SQLException e)
        {
            System.out.println(e.getMessage());
             JOptionPane pane_jdbc_update_exception_obj=new JOptionPane("Jdbc_Exception");
            JOptionPane.showMessageDialog(pane_jdbc_update_exception_obj, e.getMessage(),"Jdbc Stored Procedure Update Operation Failed",JOptionPane.ERROR_MESSAGE);
            System.exit(1);
        }
        finally
        {
            cst.close();
            con.close();
            System.out.println("Jdbc Stored Procedure Update Operation Succeed");
            JOptionPane.showMessageDialog(pane_jdbc_update_obj, "Jdbc Stored Procedure Update Operation Succeed" ,con.toString(),JOptionPane.INFORMATION_MESSAGE);
        }
    }
}

JdbcStoredProcedureInsert

2011-05-14T08:35:00.001-07:00

/*
    Oracle 10 Express Edition Stored Procedure
    ===========================================================

           Table Schema
        -------------
             CREATE TABLE myempinfo(aid VARCHAR2(20) PRIMARY KEY,name VARCHAR2(20),salary NUMBER(12,2))

        Stored Procedure
        ----------------
            CREATE OR REPLACE PROCEDURE sp_myempinfo_insert(input_aid IN VARCHAR2,input_name IN VARCHAR2,input_salary IN NUMBER) AS
            BEGIN
            INSERT INTO myempinfo VALUES(input_aid,input_name,input_salary);
            EXCEPTION
                    WHEN OTHERS THEN
                             RAISE_APPLICATION_ERROR (-20008,input_aid|| ':$:' || SQLERRM, TRUE) ;
                    RAISE_APPLICATION_ERROR (-200012,input_name|| ':$:' || SQLERRM, TRUE) ;
                    RAISE_APPLICATION_ERROR (-200016,input_salary|| ':$:' || SQLERRM, TRUE) ;
            END sp_myempinfo_insert;

        Execute
        -------
            DECLARE
            BEGIN
                sp_myempinfo_insert('458921','ABCDEF',48795.00);
            END;
*/

package JdbcOdbcOracleXE;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;
import javax.swing.JOptionPane;
import configConnection.JdbcConnectionSettings;

public class JdbcStoredProcedureInsert {

    private static Connection con = null;
    public static void main(String[] args) throws SQLException, Exception {

        JdbcStoredProcedureCursorSelect jcursorselect_obj=new JdbcStoredProcedureCursorSelect();
        jcursorselect_obj.CusrsorSelectOpearation();

        JdbcStoredProcedureInsert jinsert_obj=new JdbcStoredProcedureInsert();
        jinsert_obj.InsertOpearation();

        jcursorselect_obj.CusrsorSelectOpearation();
    }

    private void connectionEstblished() throws SQLException, Exception {
        JdbcConnectionSettings conn_db_obj = new JdbcConnectionSettings();
        con = conn_db_obj.connection_setting();
    }

    protected void InsertOpearation() throws Exception
    {
        JdbcStoredProcedureInsert connection_obj=new JdbcStoredProcedureInsert();
        connection_obj.connectionEstblished();

        JOptionPane pane_jdbc_insert_obj=new JOptionPane("Jdbc_Insert");

        String StoredProcedureQuesry = "{call sp_myempinfo_insert(?,?,?)}";
        CallableStatement cst = con.prepareCall(StoredProcedureQuesry);

            String jinname=null,jinaid=null;
            Integer jinsalary=0;

            Scanner sc=new Scanner(System.in);

            System.out.println("Associate ID : ");
            jinaid=sc.next().toUpperCase();

            System.out.println("Associate Name : ");
            jinname=sc.next().toUpperCase();

            System.out.println("Associate Salary : ");
            jinsalary=Integer.parseInt(sc.next());

        try{

            cst.setString(1,jinaid);
            cst.setString(2,jinname);
            cst.setInt(3,jinsalary);

            cst.execute();
        }
        catch(SQLException e)
        {
            System.out.println(e.getMessage());
             JOptionPane pane_jdbc_insert_exception_obj=new JOptionPane("Jdbc_Exception");
            JOptionPane.showMessageDialog(pane_jdbc_insert_exception_obj, e.getMessage(),"Jdbc Stored Procedure Insert Operation Failed",JOptionPane.ERROR_MESSAGE);
            System.exit(1);
        }
        finally
        {
            cst.close();
            con.close();
            System.out.println("Jdbc Stored Procedure Insert Operation Succeed");
            JOptionPane.showMessageDialog(pane_jdbc_insert_obj, "Jdbc Stored Procedure Insert Operation Succeed" ,con.toString(),JOptionPane.INFORMATION_MESSAGE);
        }
    }
}

JdbcStoredProcedureDelete

2011-05-14T08:34:00.002-07:00

/*
    Oracle 10 Express Edition Stored Procedure
    ===========================================================

           Table Schema
        -------------
             CREATE TABLE myempinfo(aid VARCHAR2(20) PRIMARY KEY,name VARCHAR2(20),salary NUMBER(12,2))

        Stored Procedure
        ----------------
            CREATE OR REPLACE PROCEDURE sp_myempinfo_delete(input_aid IN VARCHAR2) AS
            BEGIN
            DELETE FROM myempinfo WHERE aid=input_aid ;
            EXCEPTION
                    WHEN OTHERS THEN
                             RAISE_APPLICATION_ERROR (-20008,input_aid|| ':$:' || SQLERRM, TRUE) ;
            END sp_myempinfo_DELETE;

        Execute
        -------
            DECLARE
            BEGIN
                sp_myempinfo_delete('548945');
            END;
*/

package JdbcOdbcOracleXE;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;
import javax.swing.JOptionPane;
import configConnection.JdbcConnectionSettings;

public class JdbcStoredProcedureDelete {

    private static Connection con = null;
    public static void main(String[] args) throws SQLException, Exception {

        JdbcStoredProcedureCursorSelect jcursorselect_obj=new JdbcStoredProcedureCursorSelect();
        jcursorselect_obj.CusrsorSelectOpearation();

        JdbcStoredProcedureDelete jselect_obj=new JdbcStoredProcedureDelete();
        jselect_obj.InsertOpearation();

        jcursorselect_obj.CusrsorSelectOpearation();
    }

    private void connectionEstblished() throws SQLException, Exception {
        JdbcConnectionSettings conn_db_obj = new JdbcConnectionSettings();
        con = conn_db_obj.connection_setting();
    }

    protected void InsertOpearation() throws Exception
    {
        JdbcStoredProcedureDelete connection_obj=new JdbcStoredProcedureDelete();
        connection_obj.connectionEstblished();

        JOptionPane pane_jdbc_delete_obj=new JOptionPane("Jdbc_Delete");

        String StoredProcedureQuesry = "{call sp_myempinfo_delete(?)}";
        CallableStatement cst = con.prepareCall(StoredProcedureQuesry);

            String jinaid=null;


            Scanner sc=new Scanner(System.in);

            System.out.println("Associate ID : ");
            jinaid=sc.next().toUpperCase();


        try{
            cst.setString(1,jinaid);
            cst.execute();
        }
        catch(SQLException e)
        {
            System.out.println(e.getMessage());
             JOptionPane pane_jdbc_delete_exception_obj=new JOptionPane("Jdbc_Exception");
            JOptionPane.showMessageDialog(pane_jdbc_delete_exception_obj, e.getMessage(),"Jdbc Stored Procedure Delete Operation Failed",JOptionPane.ERROR_MESSAGE);
            System.exit(1);
        }
        finally
        {
            cst.close();
            con.close();
            System.out.println("Jdbc Stored Procedure Delete Operation Succeed");
            JOptionPane.showMessageDialog(pane_jdbc_delete_obj, "Jdbc Stored Procedure Delete Operation Succeed" ,con.toString(),JOptionPane.INFORMATION_MESSAGE);
        }
    }
}

JdbcStoredProcedureCursorSelect

2011-05-14T08:34:00.000-07:00

/*
    Oracle 10 Express Edition Stored Procedure
    ===========================================================

           Table Schema
        -------------
             CREATE TABLE myempinfo(aid VARCHAR2(20) PRIMARY KEY,name VARCHAR2(20),salary NUMBER(12,2))

        Cursor Stored Procedure
         -----------------------
        CREATE OR REPLACE PROCEDURE sp_myempinfo_cursor_select(IO_CURSOR OUT SYS_REFCURSOR) IS
        BEGIN
            OPEN IO_CURSOR FOR SELECT * FROM myempinfo ;
        END sp_myempinfo_cursor_select;

        ------------------------------------------------------------------------------------------------
        ------------------------------------------------------------------------------------------------

        Stored Procedure
        ----------------

        CREATE OR REPLACE PROCEDURE sp_myempinfo_user_select(input_aid IN VARCHAR2,output_name OUT VARCHAR2,output_salary OUT NUMBER) AS
        BEGIN
        SELECT name,salary INTO output_name,output_salary FROM myempinfo WHERE aid = input_aid ;

        EXCEPTION
                WHEN OTHERS THEN
                     RAISE_APPLICATION_ERROR (-20004,input_aid || ':$:' || SQLERRM, TRUE) ;
        END sp_myempinfo_user_select;

        Execute
        -------

        DECLARE
        output_name VARCHAR2(20);
        output_salary NUMBER(12,2);
        BEGIN
            sp_myempinfo_USER_select('255658',output_name,output_salary);
                DBMS_OUTPUT.PUT_LINE(output_name);
                 DBMS_OUTPUT.PUT_LINE(output_salary);
           END;


*/
package JdbcOdbcOracleXE;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JOptionPane;
import oracle.jdbc.OracleTypes;
import configConnection.JdbcConnectionSettings;

public class JdbcStoredProcedureCursorSelect {
    private static Connection con = null;
    public static void main(String[] args) throws SQLException, Exception {
        JdbcStoredProcedureCursorSelect jselect_obj=new JdbcStoredProcedureCursorSelect();
        jselect_obj.CusrsorSelectOpearation();
    }

    private void connectionEstblished() throws SQLException, Exception {
        JdbcConnectionSettings conn_db_obj = new JdbcConnectionSettings();
        con = conn_db_obj.connection_setting();
    }

    protected void CusrsorSelectOpearation() throws Exception
    {
        JdbcStoredProcedureCursorSelect connection_obj=new JdbcStoredProcedureCursorSelect();
        connection_obj.connectionEstblished();

        JOptionPane pane_jdbc_cursor_select_obj=new JOptionPane("Jdbc_Cursor_Select");

        String StoredProcedurecCursorQuery = "{call sp_myempinfo_cursor_select(?)}";
        CallableStatement cst = con.prepareCall(StoredProcedurecCursorQuery);


        try{

            cst.registerOutParameter(1,OracleTypes.CURSOR);

            cst.execute();

            ResultSet rs = (ResultSet)cst.getObject(1);

            while (rs.next()) {
                 System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getInt(3));
             }

        }
        catch(SQLException e)
        {
            System.out.println(e.getMessage());
             JOptionPane pane_jdbc_cursor_select_exception_obj=new JOptionPane("Jdbc_Exception");
            JOptionPane.showMessageDialog(pane_jdbc_cursor_select_exception_obj, e.getMessage(),"Jdbc Stored Procedure Cursor Select Operation Failed",JOptionPane.ERROR_MESSAGE);
            System.exit(1);
        }
        finally
        {
            cst.close();
            con.close();
            System.out.println("Jdbc Stored Procedure Cursor Select Operation Succeed");
            JOptionPane.showMessageDialog(pane_jdbc_cursor_select_obj, "Jdbc Stored Procedure Cursor Select Operation Succeed" ,con.toString(),JOptionPane.INFORMATION_MESSAGE);
        }
    }
}

JdbcPakageStoredProcedureUserSelect

2011-05-14T08:33:00.001-07:00

/*
    Oracle 10 Express Edition With Package and Stored Procedure
    ===========================================================

        Table Schema
        -------------
        CREATE TABLE myempinfo(aid VARCHAR2(20) PRIMARY KEY,name VARCHAR2(20),salary NUMBER(12,2))

        Package Declaration
        --------------------
        CREATE OR REPLACE PACKAGE pkgmyempinfo IS
                PROCEDURE sp_myempinfo_pkg_user_select(input_aid IN VARCHAR2,output_name OUT VARCHAR2,output_salary OUT NUMBER);
                PROCEDURE sp_myempinfo_pkg_cursor_select(IO_CURSOR OUT SYS_REFCURSOR);
        END pkgmyempinfo;


        Package Body
        ------------
        CREATE OR REPLACE PACKAGE BODY pkgmyempinfo IS

            PROCEDURE sp_myempinfo_pkg_user_select(input_aid IN VARCHAR2,output_name OUT VARCHAR2,output_salary OUT NUMBER) AS
             BEGIN
             SELECT name,salary INTO output_name,output_salary FROM myempinfo WHERE aid = input_aid ;
                EXCEPTION
                        WHEN OTHERS THEN
                             RAISE_APPLICATION_ERROR (-20004,input_aid || ':$:' || SQLERRM, TRUE) ;
             END sp_myempinfo_pkg_user_select;


            PROCEDURE sp_myempinfo_pkg_cursor_select(IO_CURSOR OUT SYS_REFCURSOR) IS
            BEGIN
                OPEN IO_CURSOR FOR SELECT * FROM myempinfo ;
             END sp_myempinfo_pkg_cursor_select;

        END pkgmyempinfo;


        Execute
        -------
        DECLARE
        output_name VARCHAR2(20);
        output_salary NUMBER(12,2);
        BEGIN
            pkgmyempinfo.sp_myempinfo_pkg_user_select('266489',output_name,output_salary);
                DBMS_OUTPUT.PUT_LINE(output_name);
                 DBMS_OUTPUT.PUT_LINE(output_salary);
           END;

*/

package JdbcOdbcOracleXE;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Scanner;

import javax.swing.JOptionPane;

import configConnection.JdbcConnectionSettings;

public class JdbcPakageStoredProcedureUserSelect {

    private static Connection con = null;

    public static void main(String[] args) throws SQLException, Exception {

        JdbcPakageStoredProcedureCursorSelect jcursorselect_obj=new JdbcPakageStoredProcedureCursorSelect();
        jcursorselect_obj.PkgCursorSelectOpearation();

        JdbcPakageStoredProcedureUserSelect jselect_obj=new JdbcPakageStoredProcedureUserSelect();
        jselect_obj.PkgUserSelectOpearation();

        jcursorselect_obj.PkgCursorSelectOpearation();
    }

    private void connectionEstblished() throws SQLException, Exception {
        JdbcConnectionSettings conn_db_obj = new JdbcConnectionSettings();
        con = conn_db_obj.connection_setting();
    }

    protected void PkgUserSelectOpearation() throws Exception
    {
        JdbcPakageStoredProcedureUserSelect connection_obj=new JdbcPakageStoredProcedureUserSelect();
        connection_obj.connectionEstblished();

        JOptionPane pane_jdbc_pkg_user_select_obj=new JOptionPane("Jdbc_Package_User_Select");

        String StoredProcedureQuesry = "{call pkgmyempinfo.sp_myempinfo_pkg_user_select(?,?,?)}";
        CallableStatement cst = con.prepareCall(StoredProcedureQuesry);

          String joutname=null,jinaid=null;
            Integer joutsalary=0;

            Scanner sc=new Scanner(System.in);

            System.out.println("Associate ID : ");
            jinaid=sc.next().toUpperCase();

        try{

            cst.setString(1,jinaid);
            cst.registerOutParameter(2,Types.VARCHAR);
            cst.registerOutParameter(3,Types.INTEGER);

            cst.execute();

            joutname = cst.getString(2);
            joutsalary = cst.getInt(3);
            System.out.println(joutname + "    " + joutsalary);

        }
        catch(SQLException e)
        {
            System.out.println(e.getMessage());
             JOptionPane pane_jdbc_pkg_user_select_exception_obj=new JOptionPane("Jdbc_Exception");
            JOptionPane.showMessageDialog(pane_jdbc_pkg_user_select_exception_obj, e.getMessage(),"Jdbc Package Stored Procedure User Select Operation Failed",JOptionPane.ERROR_MESSAGE);
            System.exit(1);
        }
        finally
        {
            cst.close();
            con.close();
            System.out.println("Jdbc Package Stored Procedure User Select Operation Succeed");
            JOptionPane.showMessageDialog(pane_jdbc_pkg_user_select_obj, "Jdbc Package Stored Procedure User Select Operation Succeed" ,con.toString(),JOptionPane.INFORMATION_MESSAGE);
        }
    }
}

JdbcPakageStoredProcedureCursorSelect

2011-05-14T08:32:00.001-07:00

/*
Oracle 10 Express Edition With Package and Stored Procedure
===========================================================

    Table Schema
    -------------
    CREATE TABLE myempinfo(aid VARCHAR2(20) PRIMARY KEY,name VARCHAR2(20),salary NUMBER(12,2))

    Package Declaration
    --------------------
    CREATE OR REPLACE PACKAGE pkgmyempinfo IS
            PROCEDURE sp_myempinfo_pkg_user_select(input_aid IN VARCHAR2,output_name OUT VARCHAR2,output_salary OUT NUMBER);
            PROCEDURE sp_myempinfo_pkg_cursor_select(IO_CURSOR OUT SYS_REFCURSOR);
       END pkgmyempinfo;

    Package Body
    ------------
    CREATE OR REPLACE PACKAGE BODY pkgmyempinfo IS

        PROCEDURE sp_myempinfo_pkg_user_select(input_aid IN VARCHAR2,output_name OUT VARCHAR2,output_salary OUT NUMBER) AS
         BEGIN
         SELECT name,salary INTO output_name,output_salary FROM myempinfo WHERE aid = input_aid ;
            EXCEPTION
                    WHEN OTHERS THEN
                         RAISE_APPLICATION_ERROR (-20004,input_aid || ':$:' || SQLERRM, TRUE) ;
         END sp_myempinfo_pkg_user_select;


        PROCEDURE sp_myempinfo_pkg_cursor_select(IO_CURSOR OUT SYS_REFCURSOR) IS
        BEGIN
            OPEN IO_CURSOR FOR SELECT * FROM myempinfo ;
         END sp_myempinfo_pkg_cursor_select;

    END pkgmyempinfo;

    Execute
    -------
    DECLARE
    output_name VARCHAR2(20);
    output_salary NUMBER(12,2);
    BEGIN
        pkgmyempinfo.sp_myempinfo_pkg_user_select('240800',output_name,output_salary);
            DBMS_OUTPUT.PUT_LINE(output_name);
             DBMS_OUTPUT.PUT_LINE(output_salary);
       END;

*/

package JdbcOdbcOracleXE;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JOptionPane;
import configConnection.JdbcConnectionSettings;
import oracle.jdbc.OracleTypes;

public class JdbcPakageStoredProcedureCursorSelect {

    private static Connection con = null;

    public static void main(String[] args) throws Exception {
        JdbcPakageStoredProcedureCursorSelect jselect_obj=new JdbcPakageStoredProcedureCursorSelect();
        jselect_obj.PkgCursorSelectOpearation();
    }

    private void connectionEstblished() throws SQLException, Exception {
        JdbcConnectionSettings conn_db_obj = new JdbcConnectionSettings();
        con = conn_db_obj.connection_setting();
    }

    protected void PkgCursorSelectOpearation() throws Exception
    {
        JdbcPakageStoredProcedureCursorSelect connection_obj=new JdbcPakageStoredProcedureCursorSelect();
        connection_obj.connectionEstblished();

        JOptionPane pane_jdbc_cursor_select_obj=new JOptionPane("Jdbc_Pkg_Cursor_Select");

        String StoredProcedurecCursorQuery = "{call pkgmyempinfo.sp_myempinfo_pkg_cursor_select(?)}";
        CallableStatement cst = con.prepareCall(StoredProcedurecCursorQuery);


        try{

            cst.registerOutParameter(1,OracleTypes.CURSOR);

            cst.execute();

            ResultSet rs = (ResultSet)cst.getObject(1);

            while (rs.next()) {
                 System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getInt(3));
             }

        }
        catch(SQLException e)
        {
            System.out.println(e.getMessage());
             JOptionPane pane_jdbc_cursor_select_exception_obj=new JOptionPane("Jdbc_Exception");
            JOptionPane.showMessageDialog(pane_jdbc_cursor_select_exception_obj, e.getMessage(),"Jdbc Package Stored Procedure Cursor Select Operation Failed",JOptionPane.ERROR_MESSAGE);
            System.exit(1);
        }
        finally
        {
            cst.close();
            con.close();
            System.out.println("Jdbc Package Stored Procedure Cursor Select Operation Succeed");
            JOptionPane.showMessageDialog(pane_jdbc_cursor_select_obj, "Jdbc Package Stored Procedure Cursor Select Operation Succeed" ,con.toString(),JOptionPane.INFORMATION_MESSAGE);
        }
    }

}

JdbcUpdate

2011-05-14T08:30:00.003-07:00

package JdbcOdbcOracleXE;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import javax.swing.JOptionPane;
import configConnection.JdbcConnectionSettings;

public class JdbcUpdate {

    private static Connection con = null;

    public static void main(String[] args) throws SQLException, Exception {
        JdbcSelect jselect_obj=new JdbcSelect();
        jselect_obj.SelectOpearation();

        JdbcUpdate jupdate_obj=new JdbcUpdate();
        jupdate_obj.UpdateOpearation();

        jselect_obj.SelectOpearation();
    }

    private void connectionEstblished() throws SQLException, Exception {
        JdbcConnectionSettings conn_db_obj = new JdbcConnectionSettings();
        con = conn_db_obj.connection_setting();
    }

    private void UpdateOpearation() throws Exception
    {
        JdbcUpdate connection_obj=new JdbcUpdate();
        connection_obj.connectionEstblished();

        JOptionPane pane_jdbc_update_obj=new JOptionPane("jdbc_Update");

        String jname=null,jaid=null;
        Integer jsalary=0;

        Scanner sc=new Scanner(System.in);
        System.out.println("Associate ID : ");
        jaid=sc.next().toUpperCase();

        System.out.println("Associate Name : ");
        jname=sc.next().toUpperCase();

        String Query_count="SELECT COUNT(*) FROM myempinfo WHERE aid = '"+jaid+"' AND name = '"+jname+"' ";
        PreparedStatement pst_count=con.prepareStatement(Query_count);
        ResultSet rs_count=pst_count.executeQuery();

        if(rs_count.next())
        {
            if(rs_count.getInt(1)==0)
            {
               pst_count.close();
               rs_count.close();
               con.close();
               System.out.println("No Data Found");
               JOptionPane pane_update_no_data_found_obj=new JOptionPane("Invalid Entry");
               JOptionPane.showMessageDialog(pane_update_no_data_found_obj,"No Data Found for Specified Criteria"," Invalid Entry ",JOptionPane.ERROR_MESSAGE);
               System.exit(1);
            }
        }

        String Query="UPDATE myempinfo SET salary =? WHERE aid = ? AND name = ? ";
        System.out.println("Associate Salary : ");
        jsalary=Integer.parseInt(sc.next());

        PreparedStatement pst=con.prepareStatement(Query);


        try{
            con.setAutoCommit(false);
            pst.setInt(1,jsalary);
            pst.setString(2,jaid);
            pst.setString(3,jname);
            pst.executeUpdate();
            con.commit();
        }
        catch(SQLException e)
        {
            System.err.print("Transaction is being rolled back \n");
            con.rollback();
            System.out.println(e.getMessage());
             JOptionPane pane_jdbc_update_exception_obj=new JOptionPane("Jdbc_Exception");
            JOptionPane.showMessageDialog(pane_jdbc_update_exception_obj, e.getMessage(),"Jdbc Update Operation Failed",JOptionPane.ERROR_MESSAGE);
            System.exit(1);
        }
        finally
        {
            con.setAutoCommit(true);
            pst.close();
            con.close();
            System.out.println("Jdbc Update Operation Succeed");
            JOptionPane.showMessageDialog(pane_jdbc_update_obj,"Jdbc Update Operation Succeed" , con.toString(),JOptionPane.INFORMATION_MESSAGE);
        }
    }
}

JdbcSelect

2011-05-14T08:30:00.001-07:00

package JdbcOdbcOracleXE;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JOptionPane;
import configConnection.JdbcConnectionSettings;

public class JdbcSelect {

    private static Connection con = null;
    public static void main(String[] args) throws SQLException, Exception {
        JdbcSelect jselect_obj=new JdbcSelect();
        jselect_obj.SelectOpearation();
    }

    private void connectionEstblished() throws SQLException, Exception {
        JdbcConnectionSettings conn_db_obj = new JdbcConnectionSettings();
        con = conn_db_obj.connection_setting();
    }

    protected void SelectOpearation() throws Exception
    {
        JdbcSelect connection_obj=new JdbcSelect();
        connection_obj.connectionEstblished();

        JOptionPane pane_jdbc_select_obj=new JOptionPane("Jdbc_Select");

        String Query="SELECT * FROM myempinfo";

        PreparedStatement pst=con.prepareStatement(Query);
        ResultSet rs=pst.executeQuery();

        try{
            while (rs.next()) {
                System.out.println(rs.getString(1) +    "    "    + rs.getString(2) + "    " + rs.getInt(3));
            }

        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
             JOptionPane pane_jdbc_select_exception_obj=new JOptionPane("Jdbc_Exception");
            JOptionPane.showMessageDialog(pane_jdbc_select_exception_obj, e.getMessage(),"Jdbc Select Operation Failed",JOptionPane.ERROR_MESSAGE);
            System.exit(1);
        }
        finally
        {
            pst.close();
            rs.close();
            con.close();
            System.out.println("Jdbc Select Operation Succeed");
            JOptionPane.showMessageDialog(pane_jdbc_select_obj, "Jdbc Select Operation Succeed" ,con.toString(),JOptionPane.INFORMATION_MESSAGE);
        }
    }
}

JdbcInsert

2011-05-14T08:29:00.001-07:00

package JdbcOdbcOracleXE;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
import javax.swing.JOptionPane;
import configConnection.JdbcConnectionSettings;

public class JdbcInsert {

    private static Connection con = null;

    public static void main(String[] args) throws SQLException, Exception {
        JdbcSelect jselect_obj=new JdbcSelect();
        jselect_obj.SelectOpearation();

        JdbcInsert jinsert_obj=new JdbcInsert();
        jinsert_obj.InsertOpearation();

        jselect_obj.SelectOpearation();
    }

    private void connectionEstblished() throws SQLException, Exception {
        JdbcConnectionSettings conn_db_obj = new JdbcConnectionSettings();
        con = conn_db_obj.connection_setting();
    }

    private void InsertOpearation() throws Exception
    {
        JdbcInsert connection_obj=new JdbcInsert();
        connection_obj.connectionEstblished();

        JOptionPane pane_jdbc_insert_obj=new JOptionPane("Jdbc_Insert");

        String Query="INSERT INTO myempinfo VALUES(?,?,?)";

        String jname=null,jaid=null;
        Integer jsalary=0;

        Scanner sc=new Scanner(System.in);

        System.out.println("Associate ID : ");
        jaid=sc.next().toUpperCase();

        System.out.println("Associate Name : ");
        jname=sc.next().toUpperCase();

        System.out.println("Associate Salary : ");
        jsalary=Integer.parseInt(sc.next());

        PreparedStatement pst=con.prepareStatement(Query);
        try{
            con.setAutoCommit(false);
            pst.setString(1,jaid);
            pst.setString(2,jname);
            pst.setInt(3,jsalary);
            pst.executeUpdate();
            con.commit();
        }
        catch(Exception e)
        {
            System.err.print("Transaction is being rolled back \n");
            con.rollback();
            System.out.println(e.getMessage());
            JOptionPane pane_jdbc_insert_exception_obj=new JOptionPane("Jdbc_Exception");
            JOptionPane.showMessageDialog(pane_jdbc_insert_exception_obj, e.getMessage(),"Jdbc Insert Operation Failed" ,JOptionPane.ERROR_MESSAGE);
            System.exit(1);
        }
        finally
        {
            con.setAutoCommit(true);
            pst.close();
            con.close();
            System.out.println("Jdbc Insert Operation Succeed");
            JOptionPane.showMessageDialog(pane_jdbc_insert_obj,"Jdbc Insert Operation Succeed" , con.toString(),JOptionPane.INFORMATION_MESSAGE);
        }
    }
}

JdbcDelete

2011-05-14T08:27:00.001-07:00

package JdbcOdbcOracleXE;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import javax.swing.JOptionPane;
import configConnection.JdbcConnectionSettings;

public class JdbcDelete {

    private static Connection con = null;

    public static void main(String[] args) throws SQLException, Exception {
        JdbcSelect jselect_obj=new JdbcSelect();
        jselect_obj.SelectOpearation();

        JdbcDelete jdelete_obj=new JdbcDelete();
        jdelete_obj.DeleteOpearation();

        jselect_obj.SelectOpearation();
    }

    private void connectionEstblished() throws SQLException, Exception {
        JdbcConnectionSettings conn_db_obj = new JdbcConnectionSettings();
        con = conn_db_obj.connection_setting();
    }

    private void DeleteOpearation() throws Exception
    {
        JdbcDelete connection_obj=new JdbcDelete();
        connection_obj.connectionEstblished();

        JOptionPane pane_jdbc_delete_obj=new JOptionPane("Jdbc_Delete");

        String jname=null,jaid=null;


        Scanner sc=new Scanner(System.in);
        System.out.println("Associate ID : ");
        jaid=sc.next().toUpperCase();

        System.out.println("Associate Name : ");
        jname=sc.next().toUpperCase();

        String Query_count="SELECT COUNT(*) FROM myempinfo WHERE aid = '"+jaid+"' AND name = '"+jname+"' ";
        PreparedStatement pst_count=con.prepareStatement(Query_count);
        ResultSet rs_count=pst_count.executeQuery();

        if(rs_count.next())
        {
            if(rs_count.getInt(1)==0)
            {
               pst_count.close();
               rs_count.close();
               con.close();
               System.out.println("No Data Found");
               JOptionPane pane_insert_no_data_found_obj=new JOptionPane("Invalid Entry");
               JOptionPane.showMessageDialog(pane_insert_no_data_found_obj,"No Data Found for Specified Criteria"," Invalid Entry ",JOptionPane.ERROR_MESSAGE);
               System.exit(1);
            }
        }

        String Query="DELETE FROM myempinfo WHERE aid = '"+jaid+"' AND name = '"+jname+"' ";
        PreparedStatement pst=con.prepareStatement(Query);


        try{
            con.setAutoCommit(false);
            pst.executeUpdate();
            con.commit();
        }
        catch(SQLException e)
        {
            System.err.print("Transaction is being rolled back \n");
            con.rollback();
            System.out.println(e.getMessage());
             JOptionPane pane_jdbc_delete_exception_obj=new JOptionPane("Jdbc_Exception");
            JOptionPane.showMessageDialog(pane_jdbc_delete_exception_obj, e.getMessage(),"Jdbc Delete Operation Failed",JOptionPane.ERROR_MESSAGE);
            System.exit(1);
        }
        finally
        {
            con.setAutoCommit(true);
            pst.close();
            con.close();
            System.out.println("Jdbc Delete Operation Succeed");
            JOptionPane.showMessageDialog(pane_jdbc_delete_obj, "Jdbc Delete Operation Succeed" ,con.toString(),JOptionPane.INFORMATION_MESSAGE);
        }
    }
}

Idea of Package-Cursor-Stored Procedure

2011-05-14T08:25:00.001-07:00

------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE sp_newempselect(sid OUT NUMBER,sname OUT VARCHAR2) AS
BEGIN
SELECT * INTO sid,sname FROM newemp;

END sp_newempselect;

DECLARE
sid NUMBER
sname VARCHAR2
    BEGIN
     sp_newempselect(sid,sname);
    DBMS_OUTPUT.PUT_LINE(sid);
DBMS_OUTPUT.PUT_LINE(sname);
END;

-------------------------------------------------------------------------------------------
drop procedure sp_newempselect
select * from newemp
delete from newemp

---------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE sp_empinfo(uname in varchar2,oid out number) AS
BEGIN
SELECT id INTO oid FROM empinfo WHERE name = uname;
END sp_empinfo;

DECLARE
oid number;
BEGIN
    sp_empinfo('ANY',oid);
    DBMS_OUTPUT.PUT_LINE(oid);
END;

drop procedure sp_empinfo
desc user_tables;
delete from sp_empinfo;

select * from dba_objects where object_name = 'EMP_INFO';
select * from all_objects where object_name = 'EMP_INFO';
select * from user_objects where object_name = 'EMP_INFO';

select sys_context( 'userenv', 'current_schema' ) from dual;

--------------------------------

CREATE OR REPLACE PROCEDURE sp_empinfo(uname IN VARCHAR2,oid OUT NUMBER) AS
BEGIN
SELECT id INTO oid FROM empinfo WHERE name = uname;
END sp_empinfo;

DECLARE
oid NUMBER;
BEGIN
    sp_empinfo('ANY',oid);
    DBMS_OUTPUT.PUT_LINE(oid);
END;

DROP PROCEDURE sp_empinfo

------------------------------------------

CREATE OR REPLACE PROCEDURE emp_info(uname in varchar2,oid out number) AS
BEGIN
SELECT id INTO oid FROM empinfo WHERE name = uname;
END emp_info;

DECLARE
oid number;
BEGIN
    emp_info('ANY',oid);
    DBMS_OUTPUT.PUT_LINE(oid);
END;

drop procedure emp_info
desc user_tables;

select * from dba_objects where object_name = 'EMP_INFO';
select * from all_objects where object_name = 'EMP_INFO';
select * from user_objects where object_name = 'EMP_INFO';

select sys_context( 'userenv', 'current_schema' ) from dual;

----------------------------------------------------------------------

create user scott identified by tiger;
GRANT create session TO scott;

SELECT username, password, created FROM dba_users ORDER BY 1;
describe dba_users;

create table empstat(uname number,pwd varchar2(10))
drop table emp_status

CREATE OR REPLACE PROCEDURE sp_empstat(sid number,spwd varchar2)
AS
BEGIN
INSERT INTO empstat VALUES(sid,spwd);
END;

exec sp_empstat(48127,'ABCDEF');

DECLARE
   BEGIN
    sp_empstat(48127,'ABCDEF');
END;

select * from empstat

----------------------------------------------------------------------

desc emp_info();
Select text from user_source where name = 'emp_info';
desc user_source

CREATE OR REPLACE PROCEDURE PP(P1 IN OUT NUMBER,P2 IN OUT NUMBER) AS
BEGIN
P1:=12;
P2:=23;
DBMS_OUTPUT.PUT_LINE(P1);
DBMS_OUTPUT.PUT_LINE(P2);
END PP;

DECLARE
V1 NUMBER;
V2 NUMBER;
BEGIN
PP(V1,V2);
END;

----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE sp_empinfo(uname IN VARCHAR2,oid OUT NUMBER) AS
BEGIN
SELECT id INTO oid FROM empinfo;
END sp_empinfo;

DECLARE
oid NUMBER;
BEGIN
    sp_empinfo('ANY',oid);
    DBMS_OUTPUT.PUT_LINE(oid);
END;

DROP PROCEDURE sp_empinfo

------------------------------------------
CREATE OR REPLACE PROCEDURE sp_empcount(crecord OUT NUMBER) AS
BEGIN
   SELECT COUNT(*) INTO crecord FROM empinfo;
END sp_empcount;

DECLARE
crecord NUMBER;
BEGIN
sp_empcount(crecord);
DBMS_OUTPUT.PUT_LINE(crecord);
END;

----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE emp_info(uname in varchar2,oid out number) AS
BEGIN
SELECT id INTO oid FROM empinfo WHERE name = uname;
END emp_info;

DECLARE
oid number;
BEGIN
    emp_info('ANY',oid);
    DBMS_OUTPUT.PUT_LINE(oid);
END;

drop procedure emp_info
desc user_tables;

select * from dba_objects where object_name = 'EMP_INFO';

-------------------------------------------------------
create user scott identified by tiger

SELECT username, password, created FROM dba_users ORDER BY 1;

describe dba_users;

create table empstat(uname number,pwd varchar2(10))
drop table emp_status

CREATE OR REPLACE PROCEDURE sp_empstat(sid number,spwd varchar2)
AS
BEGIN
INSERT INTO empstat VALUES(sid,spwd);
END;

exec sp_empstat(48127,'ABCDEF');

DECLARE
   BEGIN
    sp_empstat(48127,'ABCDEF');
END;

select * from empstat

----------------------------------------------------------------------

desc emp_info();
Select text from user_source where name = 'emp_info';
desc user_source

CREATE OR REPLACE PROCEDURE PP(P1 IN OUT NUMBER,P2 IN OUT NUMBER) AS
BEGIN
P1:=12;
P2:=23;
DBMS_OUTPUT.PUT_LINE(P1);
DBMS_OUTPUT.PUT_LINE(P2);
END PP;

DECLARE
V1 NUMBER;
V2 NUMBER;
BEGIN
PP(V1,V2);
END;

----------------------------------------------------------------------

CREATE TABLE newemp(id number(12),name varchar2(12));

CREATE OR REPLACE PROCEDURE sp_empinsert(pid IN NUMBER,pname IN VARCHAR2) AS
BEGIN
INSERT INTO newemp values(pid,pname);
END sp_empinsert;

DECLARE
pid NUMBER,pnameVARCHAR2;
BEGIN
    sp_empinsert(pid,pname);
    DBMS_OUTPUT.PUT_LINE(oid);
END;

desc empinfo
DROP PROCEDURE sp_empinsert

----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE sp_empcount(crecord OUT NUMBER) AS
BEGIN
   SELECT COUNT(*) INTO crecord FROM newemp;
END sp_empcount;

DECLARE
crecord NUMBER;
BEGIN
sp_empcount(crecord);
DBMS_OUTPUT.PUT_LINE(crecord);
END;

----------------------------------------------------------------------

CREATE OR REPLACE PACKAGE pkgemp IS
    TYPE T_CURSOR IS REF CURSOR RETURN newemp%ROWTYPE;
    PROCEDURE sp_newempselect(IO_CURSOR OUT T_CURSOR);
   END pkgemp;
/

CREATE OR REPLACE PACKAGE BODY pkgemp IS
PROCEDURE sp_newempselect(IO_CURSOR OUT T_CURSOR) IS
BEGIN
OPEN IO_CURSOR FOR SELECT * FROM newemp;
CLOSE IO_CURSOR;
END sp_newempselect;
END pkgemp;

--------------------------------------------------------------------------

CREATE TABLE newemp(id number(12),name varchar2(12));

------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE sp_newempinsert(pid IN NUMBER,pname IN VARCHAR2,crecord OUT NUMBER) AS
BEGIN
INSERT INTO newemp values(pid,pname);
SELECT COUNT(*) INTO crecord FROM newemp;

END sp_newempinsert;

DECLARE
pid NUMBER,pname VARCHAR2,crecord NUMBER;
BEGIN
    sp_newempinsert(pid,pname,crecord);
    DBMS_OUTPUT.PUT_LINE(crecord);
END;

-------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE sp_newempcount(crecord OUT NUMBER) AS
BEGIN
   SELECT COUNT(*) INTO crecord FROM newemp;
END sp_newempcount;

DECLARE
crecord NUMBER;
BEGIN
sp_newempcount(crecord);
DBMS_OUTPUT.PUT_LINE(crecord);
END;
-------------------------------------------------------------------------------------------
drop procedure sp_newempinsert
select * from newemp
delete from newemp

-------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE pkgemp IS
    TYPE T_CURSOR IS REF CURSOR RETURN newemp%ROWTYPE;
    PROCEDURE sp_newempselect(IO_CURSOR OUT T_CURSOR);
   END pkgemp;
/

CREATE OR REPLACE PACKAGE BODY pkgemp IS
PROCEDURE sp_newempselect(IO_CURSOR OUT T_CURSOR) IS
BEGIN
OPEN IO_CURSOR FOR SELECT * FROM newemp;
CLOSE IO_CURSOR;
END sp_newempselect;
END pkgemp;

-------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE sp_newempselect(srow IN NUMBER,sid OUT NUMBER,sname OUT VARCHAR2) AS
BEGIN
SELECT id,name into sid,sname FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp) WHERE ROW_NUMBER =srow;
END sp_newempselect;

SELECT id,name INTO sid,sname FROM newemp where ROWNUM=2;
SELECT id,name into sid,sname from newemp;

SELECT * FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp)WHERE ROW_NUMBER =2;

SELECT * FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp)WHERE ROW_NUMBER <=4;

SELECT ID,NAME,ROWnum FROM NEWEMP;
DECLARE
sid NUMBER
sname VARCHAR2
    BEGIN
     sp_newempselect(sid,sname);
    DBMS_OUTPUT.PUT_LINE(sid);
DBMS_OUTPUT.PUT_LINE(sname);
END;

------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE sp_newempselect(srow IN NUMBER,sid OUT NUMBER,sname OUT VARCHAR2) AS
BEGIN
SELECT id,name into sid,sname FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp) WHERE ROW_NUMBER =srow;
END sp_newempselect;

SELECT id,name INTO sid,sname FROM newemp where ROWNUM=2;
SELECT id,name into sid,sname from newemp;

SELECT * FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp)WHERE ROW_NUMBER =2;

SELECT * FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp)WHERE ROW_NUMBER <=4;

SELECT ID,NAME,ROWnum FROM NEWEMP;
DECLARE
sid NUMBER
sname VARCHAR2
    BEGIN
     sp_newempselect(sid,sname);
    DBMS_OUTPUT.PUT_LINE(sid);
DBMS_OUTPUT.PUT_LINE(sname);
END;

-------------------------------------------------------------------------------------------
drop package pkgemp.sp_newempselect
select ROWNUM,id,name from newemp where ROWNUM>0
select ROWNUM from newemp
delete from newemp
SELECT id,name from newemp WHERE ROWNUM >0;

select ROWNUM,id,name from newemp where ROWNUM=2

SELECT * FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp) WHERE ROW_NUMBER = 2;

------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE sp_newempselect(srow IN NUMBER,sid OUT NUMBER,sname OUT VARCHAR2) AS
BEGIN
SELECT id,name into sid,sname FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp) WHERE ROW_NUMBER =srow;
END sp_newempselect;

SELECT id,name INTO sid,sname FROM newemp where ROWNUM=2;
SELECT id,name into sid,sname from newemp;

SELECT * FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp)WHERE ROW_NUMBER =2;

SELECT * FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp)WHERE ROW_NUMBER <=4;

SELECT ID,NAME,ROWnum FROM NEWEMP;
DECLARE
sid NUMBER
sname VARCHAR2
    BEGIN
     sp_newempselect(sid,sname);
    DBMS_OUTPUT.PUT_LINE(sid);
DBMS_OUTPUT.PUT_LINE(sname);
END;

-------------------------------------------------------------------------------------------
drop package pkgemp.sp_newempselect
select ROWNUM,id,name from newemp where ROWNUM>0
select ROWNUM from newemp
delete from newemp
SELECT id,name from newemp WHERE ROWNUM >0;

select ROWNUM,id,name from newemp where ROWNUM=2

SELECT * FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp) WHERE ROW_NUMBER = 2;

------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE sp_newempselect(srow IN NUMBER,sid OUT NUMBER,sname OUT VARCHAR2) AS
BEGIN
SELECT id,name into sid,sname FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp) WHERE ROW_NUMBER =srow;
END sp_newempselect;

SELECT id,name INTO sid,sname FROM newemp where ROWNUM=2;
SELECT id,name into sid,sname from newemp;

SELECT * FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp)WHERE ROW_NUMBER =2;

SELECT * FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp)WHERE ROW_NUMBER <=4;

SELECT ID,NAME,ROWnum FROM NEWEMP;
DECLARE
sid NUMBER
sname VARCHAR2
    BEGIN
     sp_newempselect(sid,sname);
    DBMS_OUTPUT.PUT_LINE(sid);
DBMS_OUTPUT.PUT_LINE(sname);
END;

-------------------------------------------------------------------------------------------
drop package pkgemp.sp_newempselect
select ROWNUM,id,name from newemp where ROWNUM>0
select ROWNUM from newemp
delete from newemp
SELECT id,name from newemp WHERE ROWNUM >0;

select ROWNUM,id,name from newemp where ROWNUM=2

SELECT * FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp) WHERE ROW_NUMBER = 2;

drop package pkgemp.sp_newempselect
select ROWNUM,id,name from newemp where ROWNUM>0
select ROWNUM from newemp
delete from newemp
SELECT id,name from newemp WHERE ROWNUM >0;

select ROWNUM,id,name from newemp where ROWNUM=2

SELECT * FROM (SELECT id,name,ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM newemp) WHERE ROW_NUMBER = 2;

-------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE pkgemp IS
    TYPE T_CURSOR IS REF CURSOR RETURN newemp%ROWTYPE;
    PROCEDURE sp_newempselect(IO_CURSOR OUT T_CURSOR);
   END pkgemp;
/

CREATE OR REPLACE PACKAGE BODY pkgemp IS
PROCEDURE sp_newempselect(IO_CURSOR OUT T_CURSOR) IS
BEGIN
OPEN IO_CURSOR FOR SELECT * FROM newemp;
CLOSE IO_CURSOR;
END sp_newempselect;
END pkgemp;

---------------------------------------------------
CREATE OR REPLACE PACKAGE BODY pkgemp IS
PROCEDURE sp_newempselect(IO_CURSOR OUT T_CURSOR)
BEGIN
OPEN IO_CURSOR FOR
   IO_CURSOR IS SELECT * FROM newemp;
CLOSE IO_CURSOR;
END sp_newempselect;

PROCEDURE parent (p_NumRecs PLS_INTEGER) IS
p_retcur T_CURSOR;
at_rec   newemp%ROWTYPE;
BEGIN
child(p_retcur);

FOR i IN 1 .. p_NumRecs
LOOP
    FETCH p_retcur
    INTO at_rec;

    dbms_output.put_line(at_rec.id|| ' - ' || at_rec.name );
END LOOP;
END parent;
END pkgemp;

DECLARE
sid NUMBER
sname VARCHAR2
    BEGIN
     sp_newempselect(sid,sname);
    DBMS_OUTPUT.PUT_LINE(sid);
DBMS_OUTPUT.PUT_LINE(sname);
END;

-------------------------------------------------------------------------------------------
drop procedure sp_newempselect
select * from newemp
delete from newemp

CREATE OR REPLACE PACKAGE strongly_typed IS
TYPE return_cur IS REF CURSOR RETURN all_tables%ROWTYPE;
PROCEDURE child(p_return_rec OUT return_cur);
PROCEDURE parent(p_NumRecs PLS_INTEGER);
END strongly_typed;

CREATE OR REPLACE PACKAGE BODY strongly_typed IS PROCEDURE child(p_return_rec OUT return_cur) IS
BEGIN
OPEN p_return_rec FOR
SELECT * FROM all_tables;
END child;

PROCEDURE parent (p_NumRecs PLS_INTEGER) IS
p_retcur return_cur;
at_rec   all_tables%ROWTYPE;
BEGIN
child(p_retcur);

FOR i IN 1 .. p_NumRecs
LOOP
    FETCH p_retcur
    INTO at_rec;

    dbms_output.put_line(at_rec.table_name ||
    ' - ' || at_rec.tablespace_name ||
    ' - ' || TO_CHAR(at_rec.initial_extent) ||
    ' - ' || TO_CHAR(at_rec.next_extent));
END LOOP;
END parent;
END strongly_typed;

DECLARE
    BEGIN
     strongly_typed.parent(1);
END;

----------------------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE pkgemp IS
    TYPE T_CURSOR IS REF CURSOR RETURN newemp%ROWTYPE;
    PROCEDURE sp_newempselect(IO_CURSOR OUT T_CURSOR);
   END pkgemp;
/

CREATE OR REPLACE PACKAGE BODY pkgemp IS
PROCEDURE sp_newempselect(IO_CURSOR OUT T_CURSOR) IS
BEGIN
OPEN IO_CURSOR FOR SELECT * FROM newemp;
CLOSE IO_CURSOR;
END sp_newempselect;
END pkgemp;

---------------------------------------------------
CREATE OR REPLACE PACKAGE BODY pkgemp IS
PROCEDURE sp_newempselect(IO_CURSOR OUT T_CURSOR)
BEGIN
OPEN IO_CURSOR FOR
   IO_CURSOR IS SELECT * FROM newemp;
CLOSE IO_CURSOR;
END sp_newempselect;

PROCEDURE parent (p_NumRecs PLS_INTEGER) IS
p_retcur T_CURSOR;
at_rec   newemp%ROWTYPE;
BEGIN
child(p_retcur);

FOR i IN 1 .. p_NumRecs
LOOP
    FETCH p_retcur
    INTO at_rec;

    dbms_output.put_line(at_rec.id|| ' - ' || at_rec.name );
END LOOP;
END parent;
END pkgemp;

DECLARE
sid NUMBER
sname VARCHAR2
    BEGIN
     sp_newempselect(sid,sname);
    DBMS_OUTPUT.PUT_LINE(sid);
DBMS_OUTPUT.PUT_LINE(sname);
END;

-------------------------------------------------------------------------------------------
drop procedure sp_newempselect
select * from newemp
delete from newemp

CREATE OR REPLACE PACKAGE strongly_typed IS
TYPE return_cur IS REF CURSOR RETURN all_tables%ROWTYPE;
PROCEDURE child(p_return_rec OUT return_cur);
PROCEDURE parent(p_NumRecs PLS_INTEGER);
END strongly_typed;

CREATE OR REPLACE PACKAGE BODY strongly_typed IS PROCEDURE child(p_return_rec OUT return_cur) IS
BEGIN
OPEN p_return_rec FOR
SELECT * FROM all_tables;
END child;

PROCEDURE parent (p_NumRecs PLS_INTEGER) IS
p_retcur return_cur;
at_rec   all_tables%ROWTYPE;
BEGIN
child(p_retcur);

FOR i IN 1 .. p_NumRecs
LOOP
    FETCH p_retcur
    INTO at_rec;

    dbms_output.put_line(at_rec.table_name ||
    ' - ' || at_rec.tablespace_name ||
    ' - ' || TO_CHAR(at_rec.initial_extent) ||
    ' - ' || TO_CHAR(at_rec.next_extent));
END LOOP;
END parent;
END strongly_typed;

DECLARE
    BEGIN
     strongly_typed.parent(1);
END;

-------------------------------------------------------------------------------------------

CREATE TABLE newemp(id number(12),name varchar2(12));

------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE sp_newempselect(recordall OUT newemp%ROWTYPE) AS
    CURSOR currecordall IS SELECT * FROM newemp;
    recordall newemp%ROWTYPE;
BEGIN
    open currecordall;
        loop
          fetch currecordall into recordall;
          exit when currecordall%NOTFOUND;
          DBMS_OUTPUT.put_line(recordall.id);
       end loop;
       close currecordall;
END sp_newempselect;

CREATE OR REPLACE PROCEDURE sp_newempselect(recordall OUT newemp%ROWTYPE) AS
BEGIN
   FOR recordall IN(SELECT * FROM newemp)
LOOP
    dbms_output.put_line(recordall.id);
END LOOP;

END sp_newempselect;

DECLARE
recordall %ROWTYPE;
BEGIN
sp_newempselect(recordall);
DBMS_OUTPUT.PUT_LINE(recordall.id);
END;

-------------------------------------------------------------------------------------------
drop procedure sp_newempselect
select * from newemp
delete from newemp

-------------------------------------------------------------------------------------------

Package-Cursor-Stored Procedure Cration

2011-05-14T08:24:00.001-07:00

CREATE TABLE myempinfo(aid VARCHAR2(20) PRIMARY KEY,name VARCHAR2(20),salary NUMBER(12,2))

======================================================================================================================

CREATE OR REPLACE PROCEDURE sp_myempinfo_user_select(input_aid IN VARCHAR2,output_name OUT VARCHAR2,output_salary OUT NUMBER) AS
BEGIN
SELECT name,salary INTO output_name,output_salary FROM myempinfo WHERE aid = input_aid ;

EXCEPTION
        WHEN OTHERS THEN
             RAISE_APPLICATION_ERROR (-20004,input_aid || ':$:' || SQLERRM, TRUE) ;
END sp_myempinfo_user_select;

---------------------------------------------

DECLARE
output_name VARCHAR2(20);
output_salary NUMBER(12,2);
BEGIN
    sp_myempinfo_USER_select('487922',output_name,output_salary);
        DBMS_OUTPUT.PUT_LINE(output_name);
         DBMS_OUTPUT.PUT_LINE(output_salary);
   END;
---------------------------------------------
---------------------------------------------

CREATE OR REPLACE PROCEDURE sp_myempinfo_cursor_select(IO_CURSOR OUT SYS_REFCURSOR) IS
BEGIN
    OPEN IO_CURSOR FOR SELECT * FROM myempinfo ;
END sp_myempinfo_cursor_select;

======================================================================================================================

CREATE OR REPLACE PROCEDURE sp_myempinfo_insert(input_aid IN VARCHAR2,input_name IN VARCHAR2,input_salary IN NUMBER) AS
BEGIN
INSERT INTO myempinfo VALUES(input_aid,input_name,input_salary);
EXCEPTION
        WHEN OTHERS THEN
                 RAISE_APPLICATION_ERROR (-20008,input_aid|| ':$:' || SQLERRM, TRUE) ;
        RAISE_APPLICATION_ERROR (-200012,input_name|| ':$:' || SQLERRM, TRUE) ;
        RAISE_APPLICATION_ERROR (-200016,input_salary|| ':$:' || SQLERRM, TRUE) ;
END sp_myempinfo_insert;

---------------------------------------------

DECLARE
BEGIN
    sp_myempinfo_insert('487922','ABCDEF',25487.00);
END;

======================================================================================================================

CREATE OR REPLACE PROCEDURE sp_myempinfo_update(input_aid IN VARCHAR2,input_name IN VARCHAR2,input_salary IN NUMBER) AS
BEGIN
UPDATE myempinfo SET salary=input_salary WHERE aid=input_aid AND name=input_name;
EXCEPTION
        WHEN OTHERS THEN
                 RAISE_APPLICATION_ERROR (-20008,input_aid|| ':$:' || SQLERRM, TRUE) ;
        RAISE_APPLICATION_ERROR (-200012,input_name|| ':$:' || SQLERRM, TRUE) ;
        RAISE_APPLICATION_ERROR (-200016,input_salary|| ':$:' || SQLERRM, TRUE) ;
END sp_myempinfo_update;

---------------------------------------------

    DECLARE
     BEGIN
         sp_myempinfo_update('487922','ABCDEF',40960);
    END;

=========================================================================================================================================

CREATE OR REPLACE PROCEDURE sp_myempinfo_delete(input_aid IN VARCHAR2) AS
BEGIN
DELETE FROM myempinfo WHERE aid=input_aid ;
EXCEPTION
        WHEN OTHERS THEN
                 RAISE_APPLICATION_ERROR (-20008,input_aid|| ':$:' || SQLERRM, TRUE) ;
END sp_myempinfo_DELETE;

---------------------------------------------

DECLARE
BEGIN
    sp_myempinfo_delete('487922');
END;

=========================================================================================================================================

CREATE OR REPLACE PACKAGE pkgmyempinfo IS
        PROCEDURE sp_myempinfo_pkg_user_select(input_aid IN VARCHAR2,output_name OUT VARCHAR2,output_salary OUT NUMBER);
        PROCEDURE sp_myempinfo_pkg_cursor_select(IO_CURSOR OUT SYS_REFCURSOR);
END pkgmyempinfo;

---------------------------------------------------

CREATE OR REPLACE PACKAGE BODY pkgmyempinfo IS

    PROCEDURE sp_myempinfo_pkg_user_select(input_aid IN VARCHAR2,output_name OUT VARCHAR2,output_salary OUT NUMBER) AS
     BEGIN
     SELECT name,salary INTO output_name,output_salary FROM myempinfo WHERE aid = input_aid ;
        EXCEPTION
                WHEN OTHERS THEN
                     RAISE_APPLICATION_ERROR (-20004,input_aid || ':$:' || SQLERRM, TRUE) ;
     END sp_myempinfo_pkg_user_select;

    PROCEDURE sp_myempinfo_pkg_cursor_select(IO_CURSOR OUT SYS_REFCURSOR) IS
    BEGIN
        OPEN IO_CURSOR FOR SELECT * FROM myempinfo ;
     END sp_myempinfo_pkg_cursor_select;

END pkgmyempinfo;

---------------------------------------------------

DECLARE
output_name VARCHAR2(20);
output_salary NUMBER(12,2);
BEGIN
    pkgmyempinfo.sp_myempinfo_pkg_user_select('487922',output_name,output_salary);
        DBMS_OUTPUT.PUT_LINE(output_name);
         DBMS_OUTPUT.PUT_LINE(output_salary);
   END;

===========================================================================================================================================
**********************************************************END-END-END**********************************************************************
===========================================================================================================================================


NOTE:
-----

CREATE OR REPLACE PACKAGE pkgmyempinfo IS
        PROCEDURE sp_myempinfo_pkg_user_select(input_aid IN VARCHAR2,output_name OUT VARCHAR2,output_salary OUT NUMBER);

   END pkgmyempinfo;

---------------------------------------------

CREATE OR REPLACE PACKAGE BODY pkgmyempinfo IS

    PROCEDURE sp_myempinfo_pkg_user_select(input_aid IN VARCHAR2,output_name OUT VARCHAR2,output_salary OUT NUMBER)AS
     BEGIN
     SELECT name,salary INTO output_name,output_salary FROM myempinfo WHERE aid = input_aid ;
        EXCEPTION
                WHEN OTHERS THEN
                     RAISE_APPLICATION_ERROR (-20004,input_aid || ':$:' || SQLERRM, TRUE) ;
     END sp_myempinfo_pkg_user_select;
END pkgmyempinfo;

---------------------------------------------

DECLARE
output_name VARCHAR2(20);
output_salary NUMBER(12,2);
BEGIN
    pkgmyempinfo.sp_myempinfo_pkg_user_select('487922',output_name,output_salary);
        DBMS_OUTPUT.PUT_LINE(output_name);
         DBMS_OUTPUT.PUT_LINE(output_salary);
   END;

===========================================================================================================================================

CREATE OR REPLACE PACKAGE pkgmyempinfo IS
    PROCEDURE sp_myempinfo_pkg_cursor_select(IO_CURSOR OUT SYS_REFCURSOR);
   END pkgmyempinfo;

---------------------------------------------

CREATE OR REPLACE PACKAGE BODY pkgmyempinfo IS
PROCEDURE sp_myempinfo_pkg_cursor_select(IO_CURSOR OUT SYS_REFCURSOR) IS
BEGIN
OPEN IO_CURSOR FOR SELECT * FROM myempinfo ;
END sp_myempinfo_pkg_cursor_select;
END pkgmyempinfo;

---------------------------------------------------------------------

DECLARE
   CURSOR c1 IS SELECT aid,name,salary FROM myempinfo;
   my_aid myempinfo.aid%TYPE;
   my_name myempinfo.name%TYPE;
my_salary myempinfo.salary%TYPE;
BEGIN
OPEN c1;
LOOP
    FETCH c1 INTO my_aid,my_name,my_salary;
    IF c1%NOTFOUND THEN -- fetch failed, so exit loop
-- Another form of this test is "EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;"
       EXIT;
    ELSE   -- fetch succeeded
      DBMS_OUTPUT.PUT_LINE('ID = ' || my_aid || ' name = ' || my_name ||' salary = '||my_salary);

    END IF;

   END LOOP;
END;