Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Database Metadata

 
Arjun Shastry
Ranch Hand
Posts: 1899
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DatabaseMetaData interface is used to get the info about Database.There is a funtion getProcedures(arg1,arg2,arg3) to get list of Stored Procedures.But its Result Set gives procedure Names,types .
How to retrieve the actual stored procedure's content?
Thanks in advance
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If it can't be done via DatabaseMetadata then, if you really need to do it, you'll need to use database-specific code. For example, in MS SQL Server and Sybase there is a system stored procedure called sp_helptext that does this (after a fashion). You might be better off selecting from the syscomments system table yourself, however. I think Oracle has views on the underlying system tables, usersource being one of the ones for stored procedure code. Note that the source can be encrypted so you may not be able to read it. It can also be deleted independently of the stored procedure, so there's no guarantee of finding any source for a given SP.

Hope that helps.

Jules
 
Arjun Shastry
Ranch Hand
Posts: 1899
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks.
So basically we can't retrieve any stored procedure/SQL jobs residing on databse using JDBC(unless we use the method mentioned by you).?
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, I can't see anything in the standard API (java.sql.DatabaseMetaData), so I would say no. Your database driver may provide specific functionality to do that, but I doubt it. Why do you want to retrieve the stored procedure source through Java anyway? Are you writing some sort of database coding UI?

Jules
 
Arjun Shastry
Ranch Hand
Posts: 1899
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mainly to see browse stored procs/sql jobs on different database servers.I wanted to see those in browser.I will use JSP/Servlet later to complete that.
 
Jyothi Lature
Ranch Hand
Posts: 60
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Arjun,

You can print the source code of a Stored procedure in Oracle using the statement:

select text from user_source where name='PROCEDURE NAME';

So, i guess, you could use the same statement in Java and execute it using JDBC n should work!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic