Announcement Announcement Module
Collapse
No announcement yet.
stored Procedure execution Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • stored Procedure execution

    As i am reading Apress Spring 2.5, i made this example
    subprogram.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
    http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

    <bean id="dataSource"
    class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close" >
    <property name="driverClassName"
    value="oracle.jdbc.driver.OracleDriver" />
    <property name="url"
    value="jdbc:oracle:thin:@localhost:1521:XE" />
    <property name="username" value="scott" />
    <property name="password" value="tiger" />
    </bean>

    <bean id="subprog"
    class="com.htc.spring25.appls.jdbc.SubprogramsDemo ">
    <constructor-arg ref="dataSource" />
    </bean>
    </beans>

    package com.htc.spring25.appls.jdbc;
    import org.springframework.dao.DataAccessException;
    import org.springframework.jdbc.core.SqlParameter;
    import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.object.StoredProcedure;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlAp plicationContext;
    import org.apache.log4j.Logger;
    import org.apache.log4j.FileAppender;
    import org.apache.log4j.HTMLLayout;
    import java.sql.SQLException;
    import javax.sql.DataSource;
    import static java.sql.Types.*;
    import java.util.Map;
    import java.util.HashMap;
    public class SubprogramsDemo {
    private OutParamsProcedure proc;

    public SubprogramsDemo(DataSource ds) {
    super();
    proc = new OutParamsProcedure(ds);
    }
    public static class OutParamsProcedure extends StoredProcedure {
    public static final String PROCNAME = "SENDPAYDTLS";


    public OutParamsProcedure(DataSource ds) {
    super(ds,PROCNAME);
    declareParameter(new SqlParameter("ENO",INTEGER));
    declareParameter(new SqlOutParameter("SALARY",DOUBLE));
    declareParameter(new SqlOutParameter("COMMISSION",DOUBLE));
    compile();
    }

    public void execute(int eno) {
    Map<String,Object> params =
    new HashMap<String,Object>(1);
    params.put("ENO",new Integer(eno));
    System.out.println("....."+eno);
    Map result = execute(params);
    System.out.println("Total Salary:"+(Double)result.get("SALARY"));
    System.out.println("Total Commission:"+(Double)result.get("COMMISSION"));
    }
    }

    public void executeProc(int eno){
    this.proc.execute(eno);
    }

    public static void main(String[] args) {
    try {
    Logger logger = Logger.getRootLogger();
    logger.addAppender(new FileAppender(new HTMLLayout(),
    "log1.log",true));
    ApplicationContext factory =
    new ClassPathXmlApplicationContext("subprogram.xml");
    SubprogramsDemo demo = (SubprogramsDemo)factory.getBean("subprog");

    demo.executeProc(7654);

    }
    catch(Exception e) {
    System.out.println(e);
    }
    }
    }

    oracle database
    OS:Windows Vista

    procedure used is
    create or replace procedure sendpaydtls(eno in number,salary out number,commission out number) is
    x number;
    begin
    select count(1) into x from emp
    where empno=eno;
    if(x=0) then
    salary:=0;
    commission:=0;
    else
    select sal*12,nvl(comm,0)*12 into salary,commission from emp
    where empno=eno;
    end if;
    end;
    /

    when i run on command line it is showing out-of-memory and generating 2.5GB log1.log file
    can anybody help me in debugging this
Working...
X