Home » Developer & Programmer » JDeveloper, Java & XML » Writing a stored procedure with java.
Writing a stored procedure with java. [message #92089] Thu, 04 March 2004 21:14 Go to next message
Ong
Messages: 5
Registered: January 2004
Junior Member
I am try to write a stored procedure which is to written using java and then loaded into oracle. What this stored procedure does is insert some values into an oracle table. Here is a piece of code that i have tested before doing the whole real thing. But the stored procedure compiled, loaded into oracle without problems. Once the stored procedure is executed, it does nothing.

Heres the code -

import java.sql.*;
import oracle.jdbc.driver.*;

public class test{
public static void insert()
{
Connection cn = null;
PreparedStatement ps = null;
String sql ="INSERT INTO Testing.test VALUES ('JEFF')";
try{
cn = new OracleDriver().defaultConnection();
ps.execute(sql);
ps.close();
cn.commit();
cn.close();
}catch(Exception e){

}
}
}

Here is the table structure in Oracle 9i -

Name Null? Type
------------------------------ --------

USER NOT NULL CHAR(12)

Hope u guys can help me out.
Re: Writing a stored procedure with java. [message #92109 is a reply to message #92089] Thu, 11 March 2004 18:23 Go to previous messageGo to next message
ajay gupta
Messages: 13
Registered: July 2003
Junior Member
Where is the stored procedure that you have created. This code is just a call to an SQL Statement which should enter the data in the database. Please elaborate your query with more code examples.
Re: Writing a stored procedure with java. [message #92114 is a reply to message #92109] Mon, 15 March 2004 00:53 Go to previous messageGo to next message
Ong
Messages: 5
Registered: January 2004
Junior Member
Thanks Ajay, I got the the problem solved. The source of the problem is because the PreparedStatement ps is not initialized. Anyway the problem is solved, however another comes out when i try to load the same class into another database which is also Oracle9i.

here is the error message-

C:Documents and SettingsAdministrator>loadjava -user map/map@geo
-resolve c:bdy.class
Error while computing shortname of bdy
ORA-06550: line 1, column 13:
PLS-00201: identifier 'DBMS_JAVA.SHORTNAME' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

errors : class bdy
ORA-29521: referenced name java/lang/Object could not be found
ORA-29521: referenced name java/lang/String could not be found
The following operations failed
class bdy: resolution
exiting : Failures occurred during processing

What gives? Thanks in advance!
Re: Writing a stored procedure with java. [message #92438 is a reply to message #92109] Mon, 19 July 2004 02:32 Go to previous messageGo to next message
harihara
Messages: 1
Registered: July 2004
Junior Member
i have written this program,but what are the correct code ,can any one tell me the actual code

public class Empsp
{
public static void main(String[[]] args)
{
Class.forName("sun.jdbc.odcc.JdbcOdbcDriver")
System.out.println(" ");

Connection conn=DriverManager.getConnection("jdbc:odbc:EMP_DSN "," "," ");
Statemnt stmt=conn.createStatement();
stmt.execute(create table Emp_Table("Emp_Name varchar(20),Emp_Qualification varchar(20),Emp_Age Number"));
stmt.execute(insert into Emp_Table values("('raj' ,'graduation' 20)"));

stmt.execute(insert into Emp_Table values("('ram' ,'PG' ,21)"));

PreparedStatement ps=conn.prepareCall(" ");

ResultSet rs =executeQuery("Select * from Emp_Table");

while(rs.next())
{
System.out.println(rs.getString("Emp_Name"));
System.out.println(rs.getString("Emp_Qualification"));
System.out.println(rs.getAge("Emp_Age"));
}
rs.close();

if(conn !=null)
{
conn.close();
}
}
}

thanks
how i connect with database(sql sever) using stored procedure in java [message #92439 is a reply to message #92438] Mon, 19 July 2004 02:37 Go to previous messageGo to next message
har
Messages: 1
Registered: July 2004
Junior Member
i have written a programm in stored procedure in java,
bot how i will connect with thje database ,i donot know,
how can i write it,
anyone can help me

thanks
Re: Writing a stored procedure with java. [message #109588 is a reply to message #92438] Sat, 26 February 2005 06:53 Go to previous messageGo to next message
Naresh Gujral
Messages: 1
Registered: February 2005
Junior Member
I have written a oracle java stored procedure, which access a sql server but I could not connect to sql server. I've got error connecting to socket. Any help??
Re: Writing a stored procedure with java. [message #116003 is a reply to message #109588] Fri, 15 April 2005 18:15 Go to previous message
sean_banna
Messages: 1
Registered: April 2005
Junior Member
package com.bannasoft.mssql;
import java.sql.*;
public class mssql_func
{
public static String doit(int p_something)
throws SQLException
{
Statement stmt = null;
String rs_val = null;

try
{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection c = DriverManager.getConnection ("jdbc:microsoft:sqlserver://<HOST>:<PORT>","<ID>","<PSWD>");
stmt = c.createStatement();
ResultSet rs = stmt.executeQuery("select <something where something = " + p_something);

if ((rs.next() == false) )
rs_val = "Not Found";
else
rs_val = (rs.getString(1));
}
catch(Exception e){rs_val = "Error: " + e.toString();}
finally
{
stmt.close();
return(rs_val);
}
}
}
Previous Topic: Convert XML to ORACLE using JAVA
Next Topic: How to Generate Xml file from Oracle
Goto Forum:
  


Current Time: Thu Feb 22 17:20:50 CST 2024