How can java run the oracle procedure?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: How can java run the oracle procedure?

  1. #1
    Yun Guest

    How can java run the oracle procedure?


    I defined a procedure in my oracle db, and I try to run it through my java
    program.
    con = DriverManager.getConnection(URL,"webuser","webuser");
    sql = "execute main_job_wxi.job_initial('FEB2002')";
    prepStmt = con.prepareStatement(sql);
    prepStmt.execute();
    but failed

    java.sql.SQLException: ORA-00900: invalid SQL statement

    How could I conquer this problem?

    Thanks in advance!


  2. #2
    jj Guest

    Re: How can java run the oracle procedure?


    Your statement type is wrong
    you might want to use CallableStatment

    con = DriverManager.getConnection(URL,"webuser","webuser");
    sql = "Call {main_job_wxi.job_initial(?)}";
    sql.setString(1, 'FEB2002');
    stmt = con.prepareCall(sql);
    stmt.executeQuary();

    try this.


    "Yun" <Yun.Y.Hu@seagate.com> wrote:
    >
    >I defined a procedure in my oracle db, and I try to run it through my java
    >program.
    > con = DriverManager.getConnection(URL,"webuser","webuser");
    > sql = "execute main_job_wxi.job_initial('FEB2002')";
    > prepStmt = con.prepareStatement(sql);
    > prepStmt.execute();
    >but failed
    >
    >java.sql.SQLException: ORA-00900: invalid SQL statement
    >
    >How could I conquer this problem?
    >
    >Thanks in advance!
    >



  3. #3
    beez Guest

    Re: How can java run the oracle procedure?


    Hi Yun

    I am assuming that you have tested your DBProc by calling it from
    the sqlplus command prompt and that it works o.k.
    If the assumption is true, then you are probably grappling with
    the same problem that I did a while ago.
    I finally got the code bellow to actually work.
    I looked everywhere for Oracle information and to be honest,
    in the end, it was a case of trial and error.
    Give it a shot; I hope it solves your problem.

    You should only catch a specific Exception so change the
    code below where there is a general exception being caught
    to that of a more specific one.

    CallableStatement cstmt = null;
    try
    {
    cstmt = conn.prepareCall("{call pkg_wildnet.P_MainAll()}");
    cstmt.execute();
    }
    catch (Exception e)
    {
    System.out.println (e);
    } // catch (Exception e)
    finally
    {
    try
    {
    cstmt.close();
    }
    catch (Exception ex)
    {
    } // catch (Exception ex)
    } // finally



    "Yun" <Yun.Y.Hu@seagate.com> wrote:
    >
    >I defined a procedure in my oracle db, and I try to run it through my java
    >program.
    > con = DriverManager.getConnection(URL,"webuser","webuser");
    > sql = "execute main_job_wxi.job_initial('FEB2002')";
    > prepStmt = con.prepareStatement(sql);
    > prepStmt.execute();
    >but failed
    >
    >java.sql.SQLException: ORA-00900: invalid SQL statement
    >
    >How could I conquer this problem?
    >
    >Thanks in advance!
    >



  4. #4
    Gouri Sankar Patnaik Guest

    Re: How can java run the oracle procedure?


    Hi! Shahrik,

    That's a nice reply for Yun.
    But i have a question to ask,your answer will be highly appreciated.

    Let's assume there is a table "UserInfo" in the database having 2 columns
    "UserID" and "Password".
    I have 4 records in that table.

    Now using Procedures of oracle in Java i want to retrieve all the records.
    (This could be very well achieved through 'Select' statement,but performance
    wise procedures are better options.)
    I think it's possible but i need the solution for this.

    Thanks in advance.

    Gouri Sankar

    "beez" <shahrik@yahoo.com> wrote:
    >
    >Hi Yun
    >
    >I am assuming that you have tested your DBProc by calling it from
    >the sqlplus command prompt and that it works o.k.
    >If the assumption is true, then you are probably grappling with
    >the same problem that I did a while ago.
    >I finally got the code bellow to actually work.
    >I looked everywhere for Oracle information and to be honest,
    >in the end, it was a case of trial and error.
    >Give it a shot; I hope it solves your problem.
    >
    >You should only catch a specific Exception so change the
    >code below where there is a general exception being caught
    >to that of a more specific one.
    >
    > CallableStatement cstmt = null;
    > try
    > {
    > cstmt = conn.prepareCall("{call pkg_wildnet.P_MainAll()}");
    > cstmt.execute();
    > }
    > catch (Exception e)
    > {
    > System.out.println (e);
    > } // catch (Exception e)
    > finally
    > {
    > try
    > {
    > cstmt.close();
    > }
    > catch (Exception ex)
    > {
    > } // catch (Exception ex)
    > } // finally
    >
    >
    >
    >"Yun" <Yun.Y.Hu@seagate.com> wrote:
    >>
    >>I defined a procedure in my oracle db, and I try to run it through my java
    >>program.
    >> con = DriverManager.getConnection(URL,"webuser","webuser");
    >> sql = "execute main_job_wxi.job_initial('FEB2002')";
    >> prepStmt = con.prepareStatement(sql);
    >> prepStmt.execute();
    >>but failed
    >>
    >>java.sql.SQLException: ORA-00900: invalid SQL statement
    >>
    >>How could I conquer this problem?
    >>
    >>Thanks in advance!
    >>

    >



  5. #5
    MarkN Guest

    Re: How can java run the oracle procedure?


    Architectually, stored procs are not good. They tend to tie you to a specific
    database. And for the most part, the speed gained by using them is not that
    much. It is better and more useful to perform the data access as close to
    the database as possible but not use stored procs. In cases where the performance
    is necessary use them. Make them the exception not the rule.

    Mark


    "Gouri Sankar Patnaik" <gsankarp@in.ceeyes.com> wrote:
    >
    >Hi! Shahrik,
    >
    >That's a nice reply for Yun.
    >But i have a question to ask,your answer will be highly appreciated.
    >
    >Let's assume there is a table "UserInfo" in the database having 2 columns
    >"UserID" and "Password".
    >I have 4 records in that table.
    >
    >Now using Procedures of oracle in Java i want to retrieve all the records.
    >(This could be very well achieved through 'Select' statement,but performance
    >wise procedures are better options.)
    >I think it's possible but i need the solution for this.
    >
    >Thanks in advance.
    >
    >Gouri Sankar
    >
    >"beez" <shahrik@yahoo.com> wrote:
    >>
    >>Hi Yun
    >>
    >>I am assuming that you have tested your DBProc by calling it from
    >>the sqlplus command prompt and that it works o.k.
    >>If the assumption is true, then you are probably grappling with
    >>the same problem that I did a while ago.
    >>I finally got the code bellow to actually work.
    >>I looked everywhere for Oracle information and to be honest,
    >>in the end, it was a case of trial and error.
    >>Give it a shot; I hope it solves your problem.
    >>
    >>You should only catch a specific Exception so change the
    >>code below where there is a general exception being caught
    >>to that of a more specific one.
    >>
    >> CallableStatement cstmt = null;
    >> try
    >> {
    >> cstmt = conn.prepareCall("{call pkg_wildnet.P_MainAll()}");
    >> cstmt.execute();
    >> }
    >> catch (Exception e)
    >> {
    >> System.out.println (e);
    >> } // catch (Exception e)
    >> finally
    >> {
    >> try
    >> {
    >> cstmt.close();
    >> }
    >> catch (Exception ex)
    >> {
    >> } // catch (Exception ex)
    >> } // finally
    >>
    >>
    >>
    >>"Yun" <Yun.Y.Hu@seagate.com> wrote:
    >>>
    >>>I defined a procedure in my oracle db, and I try to run it through my

    java
    >>>program.
    >>> con = DriverManager.getConnection(URL,"webuser","webuser");
    >>> sql = "execute main_job_wxi.job_initial('FEB2002')";
    >>> prepStmt = con.prepareStatement(sql);
    >>> prepStmt.execute();
    >>>but failed
    >>>
    >>>java.sql.SQLException: ORA-00900: invalid SQL statement
    >>>
    >>>How could I conquer this problem?
    >>>
    >>>Thanks in advance!
    >>>

    >>

    >



  6. #6
    Mike Guest

    Re: How can java run the oracle procedure?


    "Gouri Sankar Patnaik" <gsankarp@in.ceeyes.com> wrote:
    >
    >Hi! Shahrik,
    >
    >That's a nice reply for Yun.
    >But i have a question to ask,your answer will be highly appreciated.
    >
    >Let's assume there is a table "UserInfo" in the database having 2 columns
    >"UserID" and "Password".
    >I have 4 records in that table.
    >
    >Now using Procedures of oracle in Java i want to retrieve all the records.
    >(This could be very well achieved through 'Select' statement,but performance
    >wise procedures are better options.)
    >I think it's possible but i need the solution for this.
    >
    >Thanks in advance.
    >
    >Gouri Sankar
    >


    Hi Gouri!
    I struggled with this for a while, and came up with a solution.
    I had a general database component that received any database request
    to any ODBC source in XML format and runned it. So naturaly i did´nt
    want to make any oracle specific calls.
    This is how i did it.

    This example creates a simple stored procedure that reads out data for a
    specified employee.

    First you need to create a simple table to run against:
    CREATE TABLE emp(empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9));

    Then you create the package that is used for the stored procedures and the
    TABLE variables that will be used by the procedure and the prototype of the
    procedure.

    CREATE OR REPLACE PACKAGE Employee_Pkg
    AS
    TYPE tblEmpNo IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
    TYPE tblEName IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
    TYPE tblJob IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER;

    PROCEDURE EmployeeSearch
    (i_EName IN VARCHAR2,
    o_EmpNo OUT tblEmpNo,
    o_EName OUT tblEName,
    o_Job OUT tblJob);
    END Employee_Pkg;
    /
    (The slash is needed to run the command in SQL Plus.)

    Now you implement the procedure using one in parameter, the employer name
    and the three out parameters. The names of the OUT parameters will become
    the “column names” in the record set.

    CREATE OR REPLACE PACKAGE BODY Employee_Pkg
    AS
    PROCEDURE EmployeeSearch
    (i_EName IN VARCHAR2,
    o_EmpNo OUT tblEmpNo,
    o_EName OUT tblEName,
    o_Job OUT tblJob)
    IS
    CURSOR cur_employee (curName VARCHAR2) IS
    SELECT empno,
    ename,
    job
    FROM emp
    WHERE UPPER(ename) LIKE '%' || UPPER(curName) || '%'
    ORDER BY ename;
    RecordCount NUMBER DEFAULT 0;
    BEGIN
    FOR curRecEmployee IN cur_employee(i_EName) LOOP
    RecordCount:= RecordCount + 1;
    o_EmpNo(RecordCount):= curRecEmployee.empno;
    o_EName(RecordCount):= curRecEmployee.ename;
    o_Job(RecordCount):= curRecEmployee.job;
    END LOOP;
    END EmployeeSearch;
    END Employee_Pkg;
    /

    We start off by adding the word BODY before the package name, dropping the
    PL/SQL table definitions, and adding the word IS to start the implementation.
    We then need to declare any variables or cursors that are going to be used
    by our procedure. We define a cursor called cur_employee that has its own
    input parameter called curName and a number variable called RecordCount to
    store a count of the records processed.
    Our cursor isn't that sophisticated: it uses || to add the wildcard character
    '%' to the beginning and the end of the required search name. In SQL Server,
    we would have used the + string concatenation operator. This enables the
    LIKE statement to find any employee's names that contain the specified characters.
    As we populate each of the PL/SQL table parameters we need to keep a track
    of the current element being set, so we use RecordCount. PL/SQL tables are
    1-based so we must increment the RecordCount first as it starts from 0 initially.
    Then we define the actual implementation of the EmployeeSearch procedure.
    We simply open the cursor and ask it to transfer each record into a cursor
    variable called curRecEmployee. Notice that we didn't actually define the
    variable curRecEmployee, as this is simply a reference name to the record
    structure for the cursor. We can still refer to it within our cursor FOR...LOOP
    as though it was declared.
    Then it's just a case of moving through each record, incrementing the record
    count, and transferring each individual field into each output parameter
    in the identical element position using RecordCount.


    Commit what we done so far.
    Commit;

    Insert one line in the emp table
    insert into emp(empno,ename,job) values(1,'Kalle','Waiter');
    Commit;

    Make sure that you have a ODBC data source against your database, open the
    Oracle ODBC Test application or execute it from your java program.

    {call Employee_Pkg.EmployeeSearch(‘Kalle’, {resultset 100, o_EmpNo, o_EName,
    o_Job})}

    This woked for me.
    Kind regard
    Mike


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center