Execution of stored procedure using for loop

[Skanda Raman]

I want to use the below procedure in java.

Create or replace procedure getUser(superuserid in VARCHAR,usrcur OUT usrrefcursor)
IS   
username varchar2(1000);
begin    
OPEN usrcur FOR       
select userid,email from usertab where superuser=superuserid into username;
dbms_output.put_line('Names'||':'||usrcur.username);
end;  

But, while i executed query in sql console, it returned 300 records.

while i execute the proc, I am getting the ouput as just "Names: ". The usernames are displaying.

Please let me know, is this a correct way to display output while using query in for loop.

[Skanda Raman]

Any help for this..please

[Fatih Keles]

Honestly I did not understand what are you trying to do? Can you explain it a bit more? Are you trying to return values as ref cursor from pl/sql to java?

Regards,

Fatih.

[Skanda Raman]

Yes, i am trying to return values from pl/sql to java using ref cursor. I am getting those values and am displaying that in my java output also.

You can see that i am using "dbms_output.put_line" in this procedure. While i execute this procedure in oracle console, I am getting the ouput as just "Names: ". The usernames that were suppose to display in a loop are not displaying beside "Names:".

My question is am i using the "dbms_output.put_line" statement in a right place as in the below procedure or do i need to put all the retrieved values from a loop to some storage like cursor and then use dbms_output.put_line to display values

[Fatih Keles]

From java perspective dbms_output.put_line has no practical use. You should handle by getting resultset which is your out parameter and then iterate over the resulset and display or whatever you want.

From oracle perspective I have not seen such a usage till now. Does it really display all values from cursor? If I were you I would use a for loop for displaying results of cursor in sqlplus/toad/sql developer/etc. but again it has no benefit for java code.

But I guess your code is already working fine, or does not?

Regards,

Fatih.