0

When I'm trying to query "SHOW PROCEDURE [<db-name>.]<stored-procedure-name> ;" in teradata, the very first line of the proc is splitting at dot. When I run the same query in teradata environment, I get something like :

REPLACE PROCEDURE .(IN P_AS_OF_DATE DATE) SQL SECURITY INVOKER

But with the following code, it shows :

Row 1: [REPLACE PROCEDURE  <database>.]
Row 2: [<procnamae>]

SHOW TABLE <databaseanme>.<tablename> works just fine, but not stored procedure.

I would appreciate some help on this. Thank you in advance.

db = str(input('Enter Database Name: '))
table = str(input('Enter Table Name: '))

udaExec = teradata.UdaExec (appName='test', version='1.0',logConsole=False)
with udaExec.connect(method='odbc', system='xyz', Authentication='LDAP', username='user', password='password', driver='Teradata Database ODBC Driver 16.20') as session:
    getinput = input('TABLE(T) desc or PROC desc(P) : ')

    if getinput.upper() == 'T' :
        rows = session.execute("SHOW TABLE " + db +"."+table+";")

        whand = open(table+'.sql','w')
        for row in rows:
            for line in re.split('\r\n|\n\r|\n|\r', row[0]):
                lines+=line+'\n'
        print(lines)
        whand.write(lines)
        whand.close()

The previous code works fine for show table

if getinput.upper() == 'P' :
    spname = db +'.Sp'+table+';'
    whand = open('Sp'+table+'.sql','w')

    rows = session.execute('SHOW PROCEDURE '+  spname +';').fetchall()
    for row in rows:
        print(row)
  • I am trying to see something like
    • REPLACE PROCEDURE <database>.<procname>(IN P_AS_OF_DATE DATE) SQL SECURITY INVOKER
  • But it's printing Row 1: [REPLACE PROCEDURE <tablename>.]
            # continued from previous code cell
            for line in row: 
                print(row)
                lines+=line+'\n'
        whand.write(lines)
        whand.close()

Actual Results:

    [<teradata.util.Row object at 0x0000029462664048>,
    <teradata.util.Row object at 0x0000029462664080>,
    <teradata.util.Row object at 0x00000294626640B8>,
    <teradata.util.Row object at 0x00000294626640F0>,
    <teradata.util.Row object at 0x0000029462664128>]

    Row 1: [REPLACE PROCEDURE  <DatabaseName>.]
    Row 1: [REPLACE PROCEDURE  <DatabaseName>.]
    Row 2: [<TableName>]
    Row 2: [<TableName>]
    #                                               001              2000-01-01                     xyz             Initial version of the proc
    #                                               002              2000-01-23                     abc             Changes as per the new DimAcct

Expecting to see:

  • Row 1:[REPLACE PROCEDURE <database>.<procname>(IN P_AS_OF_DATE DATE) SQL SECURITY INVOKER]
sheikh
  • 1
  • 2
  • I don't think I can help you with this exact issue, but if you just want to extract the SP's DDL, another option is to run a BTEQ script with the `SHOW PROCEDURE` request and capture the output: https://stackoverflow.com/questions/17172120/how-to-extract-stored-procedure-body-in-teradata – ravioli Sep 08 '19 at 10:30
  • Thank you @ravioli .That's a good idea. But I am trying see if anyone could at least point me out what is causing first line to split. – sheikh Sep 08 '19 at 17:10

0 Answers0