• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Callable Statement

 
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yeh!! somebody help ma ya.In the callable statement I used 7 IN parameter and 1 OUT parameter.I can't get the OUT parameter value.But without IN parameter it is working.I used Oracle Stored Procedures for this.
Pls somebody help me .I'm totally confused by this.
 
Ranch Hand
Posts: 297
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Let see some code. What type of return value are you expecting, how do you register the out parameter?
 
Raajesh Chandran
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Mike!
just I want to get the integer value thru OUT parameter.Here my codings.
Java coding:
import java.io.*;
import java.sql.*;
public class callstatdemo
{
public static void main(String arg[])
{
Connection con;
CallableStatement cs;
Driver dr;
String str1,str2;
int in1,in2;
double d1;
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);
}
}
}
Oracle Procedure:
Create or Replace Procedure testproduct ( Action1 Number, ProdId Number, ProdName VarChar2,ProdAmount Number, ProdStock Number, ProdPercent Number, SellPrice Number,Action IN OUT Integer )
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;
I'm totaly confused by this.
 
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I notice when you are setting Action, you have quotes around it in the stored procedure. You would only do that if it were a String.
Try just Action := 0;
Dan
 
Raajesh Chandran
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi!!
Thanx for helping me.
I changed the codes.still the same errors is coming.
See i'm using Oracle8 and JDK 1.3
I want to know whether this version matches.
chandran..
 
Raajesh Chandran
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi!!
while using Oracle8 and JDK 1.3 I'm getting SQLException
saying
Nmeric or Value Error
while using Oracle8 and JDK 1.1.8 I'm getting SQLException saying

Specified Driver could not be loaded due to system error(Oracle ODBC Driver)
pls help me to trace my error.
Thankx
chandran
 
Raajesh Chandran
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi!!
Please help me ya!!. still i'm not get the result.
chandran

[This message has been edited by Raajesh Chandran (edited May 08, 2001).]
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Rajesh
Nothing wrong with your java code.I think something wrong with your storedprocedure.You have declared the varialble in Stored procedure as 'ProdName VarChar2', is that correct? .please check your Oracle stored procedure.
Vinay
 
Raajesh Chandran
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi vinay!!
It works fine in oracle.By using another procedure I called that one and receive the return value.I'm using oracle8 and JDK1.3 in windows 95.Is there is any problem by this.
Thankx for replying me
chandran
 
Daniel Dunleavy
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I see some of your in parms are set to number and not integer. Try changing all the proc parms to integer and see if it works. A integer is supposed to be like a number, but with the driver maybe not. Remember that a number can have decimal places, not an integer. So I would give it a try.
Dan
 
Raajesh Chandran
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Dani!!
Thankx.Still I'm getting the same Error.I'm using oracle8 JDK1.3 and windows 95.I don't know where I'm going wrong.Shall I try using Oracle8i instead of Oracle8.I think Oracle8 supports JDK 1.0 and Oracle 8i supports JDK1.2.Am I correct or not.
chandran..
 
Vinay Reddy
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Chandran,
While running your java program can you look into the Oracle database LOg.so that i can know what is goinng on In the Oracle database.I strongly feel that something wrong with your stored procedure.you can use oracle8 and JDK1.3 in windows 95.
 
Raajesh Chandran
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Vinay!!
Can't Get U.without OUT parameter it works fine in both Oracle and Java.why it is not working when I include OUT parameter.
chandran
 
Raajesh Chandran
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yeah guys!!

I got it.All my codings are correct.I just installed Oracle8i instead of Oracle8. As I said Oracle8i is supporting JDK1.3

Thankx a lot those who show interest to clear me.
chandran..
 
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello Rajesh,
Try by giving action as OUT only rather than IN OUT.
Smita
 
Raajesh Chandran
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Smitha!!

I got it.Nothing wrong in my StoredProcedure.Only Version is problem.Only Oracle8i sends the OUT parameter to Java.We can pass values through other version.But Only Oracle8i allows U to get the OUT parameter.

Thankx for U'r suggestion
Chandran
 
Daniel Dunleavy
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the update...its nice to know
Dan
 
This looks like a job for .... legal tender! It says so right in this tiny ad:
We need your help - Coderanch server fundraiser
https://coderanch.com/wiki/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic