Script to execute a package in Oracle database

I have a function in oracle database which is used to verify if a file is present in the invalid list in the database.
I have a text file which has the list of file names which needs to be passed to the script that tests this function.
When the function is executed it return 1 or 0... 1 meaning ] if the file that is passed is in the invalid list ----> echo file is invalid  or 0 if it not in the invalid list---echo file created no invalids.. Below is the script to test the function.

DECLARE
I_NEW_FILENAME VARCHAR2(200);
v_Return NUMBER;
BEGIN
I_NEW_FILENAME:='FILENAME HERE';

v_Return:= PACKAGENAME.PACKAGE_TO _CHECK_INVALIDS
I_NEW_FILENAME =>I_NEW_FILENAME
);
DBMS_OUTPUT.PUT_LINE('v_return = ' || v_Return);
END;


Need to pass each file name to the above script and echo the output for each file in the text file containing the list of file names

The database credentials are stored in a file on windows. The script should take the database name (SID) as parameter.

can you provide your table structure that is been used in this package and code for PACKAGENAME.PACKAGE_TO _CHECK_INVALIDS  ?

Hello raaj4354, think you should add some relevant details on what you want exactly here. Just saying "Need a script to execute a function in database." will make the experts have to guess what you want :) - Thanks!

Added the code for PACKAGENAME.PACKAGE_TO _CHECK_INVALIDS
code.invalids.txt

#!/usr/bin/perl  

#########################################################
#
#  perl_dbi_example.pl
#
#   Review of basic Perl DBI functionality for our
#   environment, including Oracle connectivity.
#
#  
#
#   Note(s):  Need to ensure that you have PROJHOME,
#             PROJROOT and PERL5LIB defined in your system environment
#             variables
#
#  Inputs:
#     -d:  output directory
#     -s:  database SID
#     -a:  debug mode flag
#
#  
#
#

use strict;
use warnings;
use Getopt::Long;          # get options from command line
use DBI;                   # database connectivity
use DBD::Oracle;           # database module for oracle
use sys_utils;             # custom utilities (in PERL5LIB)
use sql_utils;             # custom datbase utilities (in PERL5LIB)
use Carp;                  # preferred error handling
use POSIX qw(strftime);    # use for time functionality
use Data::Dumper;          # use for pretty printing of arrays, etc

# use these predefined system Environment variables
use Env qw(PROJROOT PROJHOME PERL5LIB ORACLE_HOME);

# global variables
use vars qw($DEBUG_FLAG $DBH $STH $SID $USR $PASSWD $OUTPUT_DIR $APP_NAME $FILE_NAME);
use vars qw($opt_d $opt_s $opt_a @results $opt_f);
# use vars qw($opt_d $opt_s $opt_a @results $FILE_NAME);

# Versioning for CVS
our ($VERSION) = '$Revision: 1.4 $' =~ m{ \$Revision: \s+ (\S+) }x;
$APP_NAME    = 'perl_dbi_example.pl';


#**********************************************
# export_data_to_file
#
# execute SQL and write results to a file
#**********************************************
sub export_data_to_file {
      my ( $sql, $rec, $rows, $filename );

      # put your SQL statement for INVALIDS here
      #      $sql = "SELECT object_name FROM dba_objects WHERE status='INVALID' ";
$sql = " DECLARE
I_NEW_FILENAME VARCHAR2(200);
v_Return NUMBER;
BEGIN
I_NEW_FILENAME:='FILENAME HERE';

v_Return:= PACKAGENAME.PACKAGE_TO _CHECK_INVALIDS
I_NEW_FILENAME =>I_NEW_FILENAME
);
DBMS_OUTPUT.PUT_LINE('v_return = ' || v_Return);
END;
             
    # if debugging, inspect our SQL statement
      if ( $DEBUG_FLAG == 1 ) {
            print "export_data_to_file SQL: $sql \n";
      }

      # Pure Perl DBI here - prepare a SQL statement for execution
      # and give control for it to a "statement handle" (STH)
      $STH = $DBH->prepare($sql);

      # once prepared (approved), tell Oracle to execute it
      $STH->execute;

    # if debugging, inspect the array that was returned
      if ( $DEBUG_FLAG == 1 ) {
            print Dumper \$STH->fetchrow_arrayref ;
      }
      
    # Build fully qualified filename (path and file name)
      $filename = $OUTPUT_DIR.'/'.$opt_f;    
          
    # Open the output file
      open FILE, ">$filename" or die "Can't open $filename: $!";

    # Dump the formatted results of the database SELECT to the file
   
      $rows = $STH->dump_results( 80, "\n", ",", \*FILE );

    # Close the output file
      close FILE or die "Error closing result file: $!\n";

      return;
}

###########################################################
#
# Usage!
#
#  prints out nice message reminding user what is needed
#  to run this script
###########################################################
sub usage {
      print("Usage for $APP_NAME(v. $VERSION): \n");

      print(
            "Manditory:
\t -s <database sid>
\t -d <directory for writing output file>
\t -f <file name to write the sql output to>
\n
Optional:
\t[-a <debug flag (0=no debug, 1=debug - debug mode when omitted>]
\n"
      );
}

MAIN:
{
      my ( $status, $error, $build_start, $build_end );

      # set up for build messaging
      $build_start = strftime(
                  "Build started -- $APP_NAME(v. $VERSION) at: %m-%d-%Y %H:%M:%S\n",
                  localtime );
      print "$build_start\n";

      #  get the user input from the command line
      GetOptions( "d|directory=s", "s|db_sid=s", "a|debug=i" , "f|filename=s" );

      # unless we have some key information, tell the user the expected format
      unless ( ($opt_d && $opt_s && $opt_f) ) {
            usage();
            exit 1;
      }

      # set the command line values into variables
      $OUTPUT_DIR = $opt_d;
      $SID        = $opt_s;
      $FILE_NAME  = $opt_f;
      $DEBUG_FLAG = $opt_a || 0;    # 0 is off, 1 is on

    # set up our output file name
    #$FILE_NAME   = "prebuild_invalids.txt";

      #-=-=-=-=-=-=-=-=--=-=--=-=---=-=-=-=-=-=-=-
      #  log into database
      #-=-=-=-=-=-=-=-=--=-=--=-=---=-=-=-=-=-=-=-
      # get the username and password from sys_users.pwd by calling
      # method getUsrPwdBuildAutomation
      #   (inside the sys_utils module that we imported on line 36 ~ish)
      ( $USR, $PASSWD ) = getUsrPwdBuildAutomation($SID);

      #Open an Oracle session - the "database handle" is $DBH - we can use this
      # all over our script since it is declared globally on line 46 ~ish)
      $DBH = DBI->connect(
            "DBI:Oracle:$SID",
            "$USR",
            "$PASSWD",
            {
              RaiseError => 1,    # we will handle errors locally
              PrintError => 0,
              PrintWarn  => 0
            }
      );

      # call the method to get the Oracle data and write to a file
    export_data_to_file();  

      # log out of the database
      $DBH->disconnect();

      # set up for build messaging
      $build_end = strftime( "Build ended at: %m-%d-%Y %H:%M:%S\n\n", localtime );
      print "$build_end\n";

}

The above code works fine when i execute a query but not for the declare statement above ..

Hi, I was thinking of a basic batch file, like this:

@ECHO off
SET DBUser=%1
SET DBPass=%2
SET DBTNS=%3
SET F_CHECK_NAME=%4
SET LOG=c:\temp\

ECHO DECLARE > %LOG%OraCall.sql
ECHO p_code_filename VARCHAR2(200); >> %LOG%OraCall.sql
ECHO v_Return NUMBER; >> %LOG%OraCall.sql
ECHO BEGIN >> %LOG%OraCall.sql
ECHO p_code_filename:=%F_CHECK_NAME%; >> %LOG%OraCall.sql
ECHO v_Return:=jb_f_check_name_invalids(p_code_filename); >> %LOG%OraCall.sql
ECHO DBMS_OUTPUT.PUT_LINE(v_Return); >> %LOG%OraCall.sql
ECHO END; >> %LOG%OraCall.sql
ECHO exit >> %LOG%OraCall.sql
sqlplusw -s "%DBUser%/%DBPass%@%DBTNS%" @%LOG%OraCall.sql > %LOG%OraCall.lst

ECHO Result of checking %F_CHECK_NAME%
ECHO.
TYPE %LOG%OraCall.lst

Select allOpen in new window

Save as "callproc.cmd" and call it like this:

callproc.cmd DBUser DBPass DBTNS F_CHECK_NAME

I executed the above script and it returned an error at line 17 "sqlplusw" I  removed the 'w' and then it returned Echo off.


The perl script attached i have the function being called but it does not return the values 0 or 1.  Its returing something like:

DBI::st=HASH<0X1c7cc8c>
perl-dbi.txt