0

I am trying to automate below scenario using Python.

Machine-A is Windows Server 2012 where Python 3 is available. Connected with Machine-B (Hp-UX) using paramiko SSHClient.

Now, trying to run a SQL file by connecting SQLPlus on Machine-B. I am able to connect SQLPLUS on Machine-B.

However after DB connection got established, I am unable to run the SQL file and failed with below exception.

I am very sure that this file is available under this path – verified manually in Machine-B.

bash-4.1$ ls -lrt /usr/app/ABM/ABMC4/DEPLOYMENTS/GGHJK/08092019/Kelvin_3-2_PATCH.sql

-rwxrwxrwx   1 abmc04     c4apps         223 Sep  8 20:00 /usr/app/ABM/ABMC4/DEPLOYMENTS/GGHJK/08092019/Kelvin_3-2_PATCH.sql

bash-4.1$

SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 8 19:58:25 2019

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> SQL> Connected.

SQL>

SQL> SQL> SP2-0310: unable to open file "/usr/app/ABM/ABMC4/DEPLOYMENTS/GGHJK/08092019/Kelvin_3-2_PATCH.sql"

SQL> SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

. . .

. . .

    try:

      ssh.exec_command('cd ' + rCatalogSQLFilePathWithCurrentDate)

    except:

        print ('unable to navigate current date folder')

 sqlplus_output = runRCSQLQuery(rcSQLConnectionStatement)

for line in sqlplus_output:
      print(line)

. . .

. . .

def runRCSQLQuery(rcSQLConnectionStatement):

session = subprocess.Popen(['sqlplus', '/nolog'],stdin=subprocess.PIPE,stdout=subprocess.PIPE,stderr=subprocess.PIPE)

    (stdout,stderr) = session.communicate(rcSQLConnectionStatement.encode('utf-8'))

    stdout_lines = stdout.decode('utf-8').split("\n")

    return stdout_lines

 

rcSQLConnectionStatement=f"""

    connect {oracledbuid}/{oracledbpwd}@{oracledbschema};

    host pwd;

    spool rcRunSQL.out;

    @{rCatalogSQLFilePathWithCurrentDate}/{rCatalogSQLFile};

    spool off;

    """

Note:

rCatalogSQLFilePathWithCurrentDate refers to [/usr/app/ABM/ABMC4/DEPLOYMENTS/GGHJK/08092019/]

rCatalogSQLFile refers to [Kelvin_3-2_PATCH.sql]

A few concerns here,

  1. Cause of not printing the current working directory for the statement ‘host pwd;’. I couldn’t find the spool file at anywhere in Machine-B – not sure that this statement got executed.

  2. Cause of getting unable to open file exception, even if, I refer the absolute path on the SQL command; also, I have changed the directory (cd) to the respective file path before connecting DB.

Kindly advice, if I am doing anywhere any mistake.

Above Database connection statements are written after exploring some Python sites.

PraveenKS
  • 1,145
  • 3
  • 13
  • 28
  • 3
    `session = subprocess.Popen(['sqlplus',...` It looks like you're running sqlplus on the local system (machine A?) when you should be using ssh to invoke it on the remote system (machine B?). – Kenster Sep 08 '19 at 20:18
  • Thanks for highlighting. Can you advice how to run SQL in machine B after invoked via SSH from machine A – PraveenKS Sep 08 '19 at 20:41
  • The same way you execute the `cd`. Though you need to run both `cd` and `sqlplus` in the same shell session. See [Execute multiple commands in Paramiko so that commands are affected by their predecessors](https://stackoverflow.com/q/49492621/850848#49496127). – Martin Prikryl Sep 09 '19 at 06:21
  • You can use a shell script to call sqlplus script on remote machine. – alexherm Sep 10 '19 at 18:37

0 Answers0