URGENT: stored procedures for Oracle using Java not working.

Hi how can i create a stored procedure using JDBC.

The procedure is like this
create or replace procedure new_Victim
(
CaseID IN Number,
FormNo IN Number,
Name  IN  Varchar2,
WardNo  IN Number,
VDC IN Varchar2,
Location IN Varchar2,
District IN Varchar2,
Zones IN Varchar2,
Region IN Varchar2,
Gender IN Varchar2,
Age IN Number,
Caste IN Varchar2,
Education IN Varchar2,
SubEducation IN Varchar2,
Classs IN Number,
Economy IN Varchar2,
Family IN Number,
Occupations IN Varchar2,
Designation IN Varchar2,
Affected IN Number,
Whodidcase IN Varchar2,
Typess IN Varchar2,
SubTypes IN Varchar2,
Description IN Varchar2,
Remrk IN Varchar2,
Status IN Varchar2,
SubStatus IN Varchar2,
Bigsix IN Varchar2,
Sources IN Varchar2,
Quantity IN Number,
Datee IN Varchar2,
Period IN Varchar2,
Marital IN Varchar2,
CaseType IN Varchar2,
PerpetatorID IN Number
)

is
DistrictID Number;
ZoneID Number;
RegionID Number;
casteID Number;
EducationID Number;
SubEducationID Number;
EconomyID Number;
OccupationID Number;
DesignationID Number;
WHOID Number;
TypeID Number;
SubTypeID Number;
SourceID Number;
StatusID Number;
SubStatusID Number;
BigSixID Number;
LocationID Number;
PeriodID Number;
RowCount Integer;

Begin

  Select DISTID into DistrictID from DISTRICT where DISTNAME = District ;
 
  Select ZONEID into ZoneID from ZONE where ZONENAME = Zones ;
 
  Select REGIONSID into RegionID from REGIONS where REGIONSNAME = Region ;
 
  Select CASTEID into casteID from CASTE where CASTEDESCRIPTION = Caste ;
 
  Select EDUID into EducationID from EDUCATIONALSTATUS where EDUDESCRIPTION = Education ;
 
  Select EDUSUBID into SubEducationID from EDUCATIONALSUBSTATUS where EDUSUBDESCRIPTION = SubEducation ;
 
  Select ECONOMICSTATUSID into EconomyID from ECONOMICSTATUS where ECONOMICSTATUSDESCRIPTION = Economy;
 
  select count(*) into RowCount from OCCUPATION where OCCUPATIONDESCRIPTION = Occupations ;
  If RowCount > 0 then
        Select OCCUPATIONID into OccupationID from OCCUPATION where OCCUPATIONDESCRIPTION = Occupations ;
  Else
        OccupationID :=0;
  End if;
 
  select count(*) into RowCount from OCCUPOSITION where OCCUPOSITIONDESCRIPTION = Designation ;
  If RowCount > 0 then
        Select OCCUPOSITIONID into DesignationID from OCCUPOSITION where OCCUPOSITIONDESCRIPTION = Designation ;
  Else
          DesignationID :=0;
  End if;
 
  Select DIDTHECASEID into WHOID from DIDTHECASE where DIDTHECASEDESCRIPTION = Whodidcase ;
 
  Select CASESOURCEID into SourceID from CASESOURCE where CASESOURCEDESCRIPTION = Sources;
 
  Select HEADAUTOID into TypeID from HEADINGS where HEADDESCRIPTION = Typess;  
 
  Select SUBHEADAUTOID into SubTypeID from SUBHEADINGS where SUBHEADDESCRIPTION = SubTypes;
 
  select count(*) into RowCount from VICTIMSTATUS where VICTIMSTATUSDESCRIPTION = Status;
  If RowCount > 0 then
        Select VICTIMSTATUSID into StatusID from VICTIMSTATUS where VICTIMSTATUSDESCRIPTION = Status;
   Else
            StatusID :=0;
  End if;
 
  select count(*) into RowCount from VICTIMSTATUSSUBHEAD where VICTIMSTATUSSUBHEADDESCRIPTION = SubStatus  and  VICTIMSTATUSHEADID = StatusID;
  If RowCount > 0 then
        Select VICTIMSTATUSSUBHEADID into SubStatusID from VICTIMSTATUSSUBHEAD where VICTIMSTATUSSUBHEADDESCRIPTION = SubStatus  and  VICTIMSTATUSHEADID = StatusID;
   Else
        SubStatusID :=0;
  End if;
  Select BIG6ID into BigSixID from BIG6 where BIG6DESCRIPTION = BigSix ;
 
  Select LOCATIONAUTOID into LocationID from LOCATION where  LOCATIONDESCRIPTION = Location ;      
 
  Select PERIODID into PeriodID from PERIOD where PERIODDESCRIPTION = Period ;  
 

  insert into TESTTABLE(PRCASENO,PRFORMNO,PRNAME,PRLOCATION,PRWARDNO,PRPRVDCMCP,PRDISTRICT,PRZONE,PRREGION,PRGENDER,PRAGE,
                        PRCAST,PREDUCATIONID,PRSUBEDUCATIONID,PRCLASS,PRECONOMICSTATUS,PROCCUPATION,
                        PRDESIGNATION,PRAFFECTEDNO,PRDIDTHECASE,PRKILLINGSTYPE,PRKILLINGSSUBTYPE,PRCASESOURCE,
                        PRREMARKS,
                        PRCASEDESCRIPTION,PRVICTIMSTATUS,PRVICTIMSTATUSSUBTYPE,PRBIG6,PRCASEQTY,
                        PRDATE,PRPERIOD,PRMARITALSTATUS,PRCASETYPE,PRPERPNO) values
                        (CaseID,FormNo,Name,LocationID,WardNo,VDC,DistrictID,ZoneID,RegionID,Gender,Age,casteID,
                        EducationID,SubEducationID, Classs,EconomyID,OccupationID,DesignationID,Affected,WHOID,
                        TypeID,SubTypeID,SourceID,Remrk,Description,StatusID,SubStatusID,BigSixID,Quantity,
                        to_date(Datee||'00:00:00','mm/dd/yyyyHH24:mi:ss'),PeriodID,Marital,CaseType,PerpetatorID);
      
                         
  commit;
/*EXCEPTION
WHEN NO_DATA_FOUND THEN
      newID := -1;*/

End;

I am just puting that in string and doing

String procperp = "the above long procedure";
try{
      Statement stmt = conn.createStatement();
      stmt.executeUpdate(procperp);
}
catch(SQLException e)
{
      e.printStackTrace();
}
            

But the procedure is not working. What am i missing?
Is there a newline issue?

regards
Manish Regmi

Start Free Trial