Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
The moose likes JDBC and Relational Databases and the fly likes Store Procedure Codes Badlu In need of Help!! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Store Procedure Codes Badlu In need of Help!!" Watch "Store Procedure Codes Badlu In need of Help!!" New topic

Store Procedure Codes Badlu In need of Help!!


Joined: May 10, 2001
Posts: 8
Dear Friends ,
I have got a table called city_master from which ia want to select its city_name,city_code,state_name to my JSP program.
There are total of 60 records in my city_master table. I want fetch all the 60 records.
Can Anyone help me how to write a suitable store procedure code
and java code for the same
Thanks in advance
Abhi here
Ambrose Tati
Ranch Hand

Joined: Oct 03, 2000
Posts: 55
Hi there,
I use Oracle and don't know much about other databases but I've got a feeling that the syntax for stored procedure are slightly different. It would be helpful if you mention the database for there is no point to give you an Oracle solution if you use something else.

Joined: May 10, 2001
Posts: 8
Thanks Ambrose,
I use Oracle only.I learnt java code how to call a storeprocedure
.But how to write a store procedure which fetch me 60 records using cursor etc has to be yet tackled.
any idea Pls reply
radhvi uppalapati

Joined: Dec 05, 2001
Posts: 1
procedure city_master_PROC
cursor city_master_cur Is select * from city_master;
ora_err_code NUMBER;
ora_err_mesg VARCHAR2(256);

FOR P1_rec IN city_master_cur LOOP
If you need to insert into another table then the code is like this
INSERT INTO table VALUES(P1_REC.city_name, P1_REC.city_code,

ora_err_code:= SQLCODE;
ora_err_mesg:= SQLERRM;
dbms_output.put_line(ora_err_code| |','| |ora_err_mesg);
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Is there a need for a StoredProcedure here? Is it part of the specifications that you have to abide by? It seems like a good old PreparedStatement will do for a simple select statement. Stored Procedures are usually used for executing a group of related queries/transactions so that many tasks can be accomplished in one call to the database. Or sometimes shops like to keep the data/data access solely in the hands of the DBA, but this isn't the case if you're writing the Stored Procedure!
in short, why not use a PreparedStatement in your java code?
I agree. Here's the link:
subject: Store Procedure Codes Badlu In need of Help!!
It's not a secret anymore!