How to get Script of a stored procedure

[gopi krishna <go...@gmail.com>]

Hi

  This is Krishna , i need to edit an existing stored procedure from one of
the client. So i need to get script of that stored procudure.How can i get
that.
Please can you help me here

Thanks
Krishna

Re: How to get Script of a stored procedure

[Rick Hillegas <ri...@oracle.com>]

On 11/22/11 11:21 PM, gopi krishna wrote:
> Hi
>
>   This is Krishna , i need to edit an existing stored procedure from 
> one of the client. So i need to get script of that stored 
> procudure.How can i get that.
> Please can you help me here
>
> Thanks
> Krishna
Hi Krishna,

Derby stored procedures are just public static methods in user-supplied 
classes. The following code will show you what static method is bound to 
the procedure name you're interested in. Here's how you run this program:

java z $connectionURL $schemaName $procedureName

e.g.:

java z jdbc:derby:db APP P

Here is the program:

import java.sql.*;

import org.apache.derby.catalog.types.MethodAliasInfo;

public  class   z
{
     public  static  void    main( String[] args ) throws Exception
     {
         String  connectionURL = args[ 0 ];
         String  schemaName = args[ 1 ];
         String  procedureName = args[ 2 ];

         Class.forName( "org.apache.derby.jdbc.EmbeddedDriver" );
         Class.forName( "org.apache.derby.jdbc.ClientDriver" );
         Connection  conn = DriverManager.getConnection( connectionURL );

         PreparedStatement ps = conn.prepareStatement
             (
              "select javaclassname, aliasinfo\n" +
              "from sys.sysaliases a, sys.sysschemas s\n" +
              "where s.schemaid = a.schemaid\n" +
              "and s.schemaname = ?\n" +
              "and a.alias = ?\n"
              );
         ps.setString( 1, schemaName );
         ps.setString( 2, procedureName );
         ResultSet   rs = ps.executeQuery();

         rs.next();
         String  className = rs.getString( 1 );
         String  methodName = ((MethodAliasInfo) rs.getObject( 2 
)).getMethodName();

         System.out.println( className + "." + methodName );
     }
}

Hope this helps,
-Rick