Subversion Repositories DevTools

########################################################################

# COPYRIGHT - VIX IP PTY LTD ("VIX"). ALL RIGHTS RESERVED.

#

# Module name   : jats_rm_play24.pl

# Module type   : Makefile system

# Compiler(s)   : n/a

# Environment(s): jats

#

# Description   : Exeriment with invoking a store proceedure

#                       Convert Package Name into pkgid

#

#

#......................................................................#

require 5.006_001;

use strict;

use warnings;

use JatsError;

use JatsSystem;

use Getopt::Long;

use Pod::Usage;                             # required for help support

use JatsRmApi;

use DBI;

my $VERSION = "1.2.3";                      # Update this

my $opt_verbose = 1;

my $opt_help = 0;

my $opt_manual;

my $RM_DB;

#-------------------------------------------------------------------------------

# Function        : Main Entry

#

# Description     :

#

# Inputs          :

#

# Returns         :

#

my $result = GetOptions (

                "help+"         => \$opt_help,          # flag, multiple use allowed

                "manual"        => \$opt_manual,        # flag

                "verbose+"      => \$opt_verbose,       # flag

                );

#

#   Process help and manual options

#

pod2usage(-verbose => 0, -message => "Version: $VERSION")  if ($opt_help == 1  || ! $result);

pod2usage(-verbose => 1)  if ($opt_help == 2 );

pod2usage(-verbose => 2)  if ($opt_manual || ($opt_help > 2));

$ENV{GBE_RM_LOCATION} = 'jdbc:oracle:thin:@auawsards001.vix.local:1521:RELEASEM';

$ENV{GBE_RM_USERNAME} = 'RM_READONLY'; 

$ENV{GBE_RM_PASSWORD} =  'RM_READONLY';

ErrorConfig( 'name'    =>'PLAY24' );

getInvokeStoredProcedure('core_devl');

exit;

sub getInvokeStoredProcedure

{

    my $foundDetails = 0;

    my (@row);

    # if we are not or cannot connect then return 0 as we have not found anything

    connectRM(\$RM_DB, 0);

    # First get all packages that are referenced in a Release

    # This will only get the top level packages

    # From non-archived releases

    # This select currently works

    #   Invoke a function and recover its return value

    my $pvid = 129265;

    my $m_sqlstr0 = "SELECT release_manager.PK_RMAPI.return_vcs_tag($pvid) FROM DUAL";

    # This begin does not yet work

    #   ORA-01008: not all variables bound

    my $m_sqlstr2 =  "begin ? := release_manager.PK_RMAPI.return_vcs_tag($pvid); end;";

    my $m_sqlstr4 =  "begin aa := release_manager.PK_RMAPI.return_vcs_tag($pvid); end;";

    # This works

    #   Invoke a stored proceedure. A true proceedure with no return value

    #   Trick - you MUST have suffiecient privilege in order to execute

    #           May not work on the real database, unless you have the right 

    #           password.

    my $m_sqlstr3 =  "begin RELEASE_MANAGER.PK_BUILDAPI.set_resume(); end;";

    #

    #   So far, the limits appera to be

    #       Can call a stored proceedure, but not one that returns stuff

    #       Can call a stored function

    #

print("$m_sqlstr\n");

    my $sth = $RM_DB->prepare($m_sqlstr);

    if ( defined($sth) )

    {

        if ( $sth->execute( ) )

        {

            print "--- Execute\n";

            if ( $sth->rows )

            {

                print "--- Execute ROWS\n";

                while ( @row = $sth->fetchrow_array )

                {

                    print "Data: @row\n";

                }

            }

            print "--- Finish\n";

            $sth->finish();

        }

        else

        {

            Error("Execute failure: $m_sqlstr", $sth->errstr() );

        }

    }

    else

    {

        Error("Prepare failure" );

    }

}