• 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:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Java - Passing Object to Storeprocedure (Spring JDBCTemplate)

 
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


 
I'm a lumberjack and I'm okay, I sleep all night and work all day. Lumberjack ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic