Database Metadata

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

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

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).?

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

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.

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!