aspose file tools*
The moose likes JDBC 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 Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Store Procedure Codes Badlu In need of Help!!" Watch "Store Procedure Codes Badlu In need of Help!!" New topic
Author

Store Procedure Codes Badlu In need of Help!!

nabhilash
Greenhorn

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.
Cheers
Ambrose
nabhilash
Greenhorn

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
Greenhorn

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

begin
FOR P1_rec IN city_master_cur LOOP
BEGIN
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,
P1_REC.state_name);

END LOOP;
commit;
EXCEPTION
WHEN OTHERS THEN
ora_err_code:= SQLCODE;
ora_err_mesg:= SQLERRM;
dbms_output.put_line(ora_err_code| |','| |ora_err_mesg);
commit;
end;
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?
Jamie
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Store Procedure Codes Badlu In need of Help!!
 
Similar Threads
Gettting multiple Rows from stored proc
OUT as Table in StoreProcedure
Implement store procedure in EJB !!!!
Store Procedure
Hibernate: Dynamic Table Mapping