This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Store Procedure Codes Badlu In need of Help!!

 
nabhilash
Greenhorn
Posts: 8
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 55
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic