• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

Store Procedure Codes Badlu In need of Help!!

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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;
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
reply
    Bookmark Topic Watch Topic
  • New Topic