Python script to call oracle function and sftp result as pipe delimited text file

I need help with the python script. new bee here.

  1. I want to create oracle function “ora_fnn” that can extract metadata of the database using the system table “all_tables”. Schema name should be passed as input to the oracle function. need to extract metadata from 5 different oracle database servers.
  2. Call the oracle function “ora_fnn” using python script and create a pipe delimited text file.
  3. sftp the pipe delimited text file to different unix server.
  4. load the text file to oracle table using python.

I need help with the python script. new bee here.

  1. I want to create oracle function “ora_fnn” that can extract metadata of the database using the system table “all_tables”. Schema name should be passed as input to the oracle function. need to extract metadata from 5 different oracle database servers.

You will need an oracle db library. There seem to be a few on PyPI:

https://pypi.org/search/?q=oracle

but I have no idea which is suitable.

Then you can query it directly, or perhaps via sqlalchemy, which has
modes to read metadata.

  1. Call the oracle function “ora_fnn” using python script and create a pipe delimited text file.

I presume you mean a file with columns delimited by the “|” vertical bar
character. That is not a “pipe”, it is just used in shells to indicate a
pipe. Precision matters.

The Python csv module will let you write such a file by specifying the
vertical bar character as the delimiter.

  1. sftp the pipe delimited text file to different unix server.

The “scp” command might be easier to use. There’s a library called
“paramiko” providing native ssh support, or you can use the subprocess
module to invoke the external “scp” command.

  1. load the text file to oracle table using python.

You can read the file back using the csv module again. Then load it into
oracle as you would any SQL database, using an insert.

Cheers,
Cameron Simpson cs@cskk.id.au