File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes plssssss  Help me!! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "plssssss  Help me!!" Watch "plssssss  Help me!!" New topic
Author

plssssss Help me!!

Raajesh Chandran
Ranch Hand

Joined: May 04, 2001
Posts: 103
Hi Friends.,
I can't able to retrive the value of OUT parameter.I can able able to insert values using callable statement.But I can't able to retrive the OUT parameter value.In the procedure I declared the OUT parameter as integer.
while running I got the SQL Error saying Numeric or value Error
(ORA-6502).
Here is my coding and Procedures.
Java coding:
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc dbc:raajesh","sys","yak");
if(con==null)
{
System.out.println("Connection not Established");
}
else
{
cs=con.prepareCall("{call testproduct(?,?,?,?,?,?,?,?)}");
cs.setInt(1,1);
cs.setInt(2,201);
cs.setString(3,"Raajesh");
cs.setInt(4,1);
cs.setInt(5,1);
cs.setInt(6,1);
cs.setInt(7,1);
System.out.println("After set");
cs.registerOutParameter(8,java.sql.Types.INTEGER);
cs.executeUpdate();
System.out.println("After execute");
in2=cs.getInt(8);
System.out.println(in2);
}
}
catch(Exception e)
{
System.out.println("Error Message : "+e);
}

Stored Procedure:
Create or Replace Procedure testproduct ( Action1 Number, ProdId Number, ProdName VarChar2,ProdAmount Number, ProdStock Number, ProdPercent Number, SellPrice Number,Action IN OUT Char )
As
V_CursorID Number;
V_NumRowsNumber;
V_UpdateTabVarChar2 ( 500 );
Begin
If ( Action1 = 1 ) Then

V_UpdateTab := 'Insert into PRODUCT
( ID, PROD_NAME, PROD_PURCHASE_AMOUNT, PROD_STOCK, PROD_PERCENT, PROD_SELLING_PRICE )
values
( '| | ProdId | | ', ''' | |ProdName | |''',' | | ProdAmount | | ',' | | ProdStock | | ',' | | ProdPercent | | ',' | | SellPrice | |' )';

Else
V_UpdateTab := 'Update PRODUCT
set
PROD_NAME = ''' | | ProdName | | ''' ,
PROD_PURCHASE_AMOUNT = ' | | ProdAmount | | ',
PROD_STOCK = ' | | ProdStock | | ',
PROD_PERCENT = ' | | ProdPercent | |',
PROD_SELLING_PRICE = ' | | SellPrice | | '
where
ID= '| | ProdId;

End If;
V_CursorID := Dbms_SQL.OPEN_CURSOR;
Dbms_SQL.Parse ( V_CursorID, V_UpdateTab, Dbms_SQL.native );
V_NumRows := Dbms_SQL.execute ( V_CursorID );

If V_NumRows = 0 And Action1 <> 1 Then
Dbms_Output.Put_Line ( 'No Matching Rows...');
End If;
Dbms_Output.Put_Line ( V_NumRows | | ' Rows Updated' );

Dbms_SQL.Close_Cursor ( V_CursorID );
If V_NumRows > 0 Then

Action := '0';
Else
Action := '1';
End If;
Exception

When Others Then
Dbms_Output.Put_Line ( 'Problem in Inserting Record...' );
Dbms_SQL.Close_Cursor ( V_CursorID );
Action := '-1';
end;
please anybody help me.Thanks in advance


chandran..
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
An int is not a char. You can define your Action as IN OUT INTEGER, and change your proc. Or you can Keep the char, and change your java code.
HTH
Dan
Also...hope you don't mind...you say "I can't retrieve" or "I am not able to retrieve"
Raajesh Chandran
Ranch Hand

Joined: May 04, 2001
Posts: 103
Thanks Daniel!

Sorry I placed my old codings.I changed that one still I'm getting SQLException (ORA-6502)PL/SQL: NUmeric or Value Error.I'm totally confused by this.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: plssssss Help me!!
 
Similar Threads
not all variables bound Error
How to use Usebean Tag and setProperty and getProperty
Retrieiving OUT parameters & return value from a Sybase Stored Procedure
Callable Statement
Problem with redirect after validation