• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

Java - Passing Object to Storeprocedure (Spring JDBCTemplate)  RSS feed

 
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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


 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!