Two Laptop Bag*
The moose likes JDBC and the fly likes Java - Passing Object to Storeprocedure (Spring JDBCTemplate) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Java - Passing Object to Storeprocedure (Spring JDBCTemplate) " Watch "Java - Passing Object to Storeprocedure (Spring JDBCTemplate) " New topic
Author

Java - Passing Object to Storeprocedure (Spring JDBCTemplate)

Balachandran Vijayarajan
Greenhorn

Joined: Jun 23, 2005
Posts: 29
Hi All,


I am trying to call a oracle storeprocedure by passing object as parameter(tablename%rowtype) using spring template. When I am doing so, getting below exception

[code=DB]
Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call group_selection_store1(?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to 'GROUP_SELECTION_STORE1'

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored


[b]Storeprocedure[/b]
create or replace procedure group_store1(group_sel in group_selection%rowtype,

group_selection_out out group_selection.RENEWAL_ID%type

)

is

begin

insert into group_selection(RENEWAL_ID, CATEGORY )

values(group_sel.RENEWAL_ID, group_sel.CATEGORY )

returning RENEWAL_ID into group_selection_out;

end group_selection_store1;


[b]Table [/b]

CREATE TABLE "GROUP_SELECTION"

( "RENEWAL_ID" INTEGER NOT NULL ENABLE,

"CATEGORY" VARCHAR2(50) NOT NULL ENABLE,

PRIMARY KEY ("RENEWAL_ID") ENABLE

)

[b]Type[/b]

CREATE OR REPLACE TYPE "ITEMTYPE" AS OBJECT(id INTEGER, descr VARCHAR2(50))[/code]

[b]Code Snippet [/b]
[code=java]
declareParameter(new SqlParameter("group_sel", OracleTypes.STRUCT, "ITEMTYPE"));

declareParameter(new SqlOutParameter("group_selection_id_out", OracleTypes.NUMBER));

public Integer execute(final TestItem testItem) throws SQLException{

SqlTypeValue value = new AbstractSqlTypeValue() {



protected Object createTypeValue( Connection conn, int sqlType, String typeName) throws SQLException {

conn = DriverManager.getConnection("jdbc:oracle:thin:system/password-1@localhost:1521:instance");

StructDescriptor itemDescriptor = new StructDescriptor(typeName, conn);

Struct item = new STRUCT(itemDescriptor, conn,

new Object[] {

testItem.getId(),

testItem.getDescription(),



});

return item;

}

};

Map inParam = new HashMap(1);

inParam.put("group_sel", value);


Map outValues = execute(inParam);

return (Integer)outValues.get("group_selection_out");

}[/code]
Please let me know, where I am going wrong

Thanks
Bala


 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Java - Passing Object to Storeprocedure (Spring JDBCTemplate)
 
Similar Threads
Can a oracle Stored procedure return a cursor as a return value?
VARRAY is Stored Proc out param, throws exception ORA-06550
Need Help with pl/sql (Compilation Error)
OUT as Table in StoreProcedure
I have a pl/sql function that returns an Array