Error in calling the Function

[Srinivasa Raghavan]

Friends i have a function in oracle thats returns a character Types.CHAR
I'm calling the function like this.

"stmt = conn.prepareCall("{ ? = call GET_SHIFT_ID()}"); 
stmt.registerOutParameter(1, Types.CHAR);
 

Is this the correct way to call. I'm getting the following exception

java.sql.SQLException: Invalid column index

[Shailesh Chandra]

Srini,

Just noticed one thing,
is it typing error

follwoing line has 3 doube quote(")

"stmt = conn.prepareCall("{ ? = call GET_SHIFT_ID()}");


please confirm ???

thanks

[Srinivasa Raghavan]

Originally posted by Shailesh Chandra:



follwoing line has 3 doube quote(")

"stmt = conn.prepareCall("{ ? = call GET_SHIFT_ID()}");

please confirm ???

thanks

Sahilesh Sorry that double quotes is a typo .. it's not in the code.
Also My code got compiled .

[Shailesh Chandra]

Thats what I was thinking about compiling error,
since complete code was not here so I thought might be some other quote is complementing it.

I dont find anything wrong with given code,

Can u paste more code or stack trace of exception

I think error is at some other line
[ January 07, 2005: Message edited by: Shailesh Chandra ]

[Srinivasa Raghavan]

Hi all i even tried this way

  
Connection conn = null;
CallableStatement stmt = null;
String shiftId = "";
try
{
conn = getConnection();
stmt = conn.prepareCall("{ ? := call GET_SHIFT_ID()}"); stmt.registerOutParameter(1, Types.CHAR);
stmt.execute();
shiftId = stmt.getString(1);
}
catch ( Exception e )
{
System.out.println("Exception in getting the Shift Ids " + e);
}

But still i'm getting error

[ January 07, 2005: Message edited by: srini vasan ]
[ January 07, 2005: Message edited by: srini vasan ]

[Shailesh Chandra]

Srini,

can you check if colon " : " is really needed while calling method,


I am not very much sure about it , but try same.

  
stmt = conn.prepareCall("{ ? = call GET_SHIFT_ID()}"); 
stmt.registerOutParameter(1, Types.CHAR);

and give system.out on every alternate line so you can get exact place of error.


thanks
[ January 07, 2005: Message edited by: Shailesh Chandra ]

[Srinivasa Raghavan]

Originally posted by Shailesh Chandra:
[QB]Srini,

can you check if colon " : " is really needed while calling method,

I am not very much sure about it , but try same.

and give
system.out only every alternate line


Yes Shailesh..
I'm trying to give SOP in alternate lines.
But ii dint use the ":" before, after seeing google
i tried with that also ..

[Srinivasa Raghavan]

One thing i'wd like to add.
The return type of the function is Char.
So i registered the out Parameter as Types.CHAR.
But for getting the value i user stmt.getString(1), will this wrk.

[Shailesh Chandra]

In my view It will work, but I will prefer

Char c = (Char) stmt.getObject(1);
or
String s = (String) stmt.getObject(1);

thanks

[Srinivasa Raghavan]

Yes it worked.. But still i cant understand the problem.
Oracle returns a single Character.
I got the value in this way, shiftId = stmt.getString(1);
But in the console i saw that the value of shiftID is of length 255 in which the first character is the return value & the rest is padded with empty spaces, then i used the trim() method it's working.
Any reason for this.

[Shailesh Chandra]

I had faced similar problem few month back,

if using oracle execute follwing command on sqlplus

show parameter cursor_sharing;

and if value of parameter is SIMILAR then might be you are having same reason for this.

thanks

[Srinivasa Raghavan]

What does this "show parameter cursor_sharing;" means ?

[Shailesh Chandra]

it is one of oracle's parameter, I had faced similar problem because of this parameter

you can refer my old post here


just a thought that you may be having similar problem

thanks
[ January 07, 2005: Message edited by: Shailesh Chandra ]

[Srinivasa Raghavan]

Thanks Shailesh ..