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" );
    }
}