Avatar of raaj4354
raaj4354
 asked on

Script to execute a package in Oracle database

Need a script to execute a function in database.
PerlScripting LanguagesOracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
raaj4354

ASKER
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
raaj4354

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

can you provide your table structure that is been used in this package and code for PACKAGENAME.PACKAGE_TO _CHECK_INVALIDS  ?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Gerwin Jansen

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!
raaj4354

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

ASKER
#!/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";

}
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
raaj4354

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

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

Open in new window

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

callproc.cmd DBUser DBPass DBTNS F_CHECK_NAME
raaj4354

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question