-
Notifications
You must be signed in to change notification settings - Fork 345
/
DbmsLob.py
163 lines (154 loc) · 6.2 KB
/
DbmsLob.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
#!/usr/bin/python
# -*- coding: utf-8 -*-
from DirectoryManagement import DirectoryManagement
import logging,cx_Oracle
#from OracleDatabase import OracleDatabase
from Utils import ErrorSQLRequest, checkOptionsGivenByTheUser
from Constants import *
class DbmsLob (DirectoryManagement):
'''
Allow the user to read file thanks to DBMS_LOB
'''
def __init__(self,args):
'''
Constructor
'''
logging.debug("DbmsLob object created")
DirectoryManagement.__init__(self,args)
self.__setDirectoryName__()
def getFile (self,remotePath, remoteNameFile, localFile):
'''
Create the localFile file containing data stored on the remoteNameFile (stored in the remotePath)
'''
data = ""
logging.info("Copy the {0} remote file (stored in {1}) to {2}".format(remoteNameFile,remotePath,localFile))
#Get data of the remote file
DBMS_LOB_GET_FILE ="""
DECLARE
-- Pointer to the BFILE
l_loc BFILE;
-- Current position in the file (file begins at position 1)
l_pos NUMBER := 1;
-- Amount of characters to read
l_sum BINARY_INTEGER;
-- Read Buffer
l_buf VARCHAR2(32767);
l_stat BINARY_INTEGER := 16383;
BEGIN
l_loc := BFILENAME('{0}','{1}');
DBMS_LOB.OPEN(l_loc,DBMS_LOB.LOB_READONLY);
l_sum := dbms_lob.getlength(l_loc);
LOOP
IF (l_sum < 16383) THEN
DBMS_LOB.READ(l_loc,l_sum,l_pos,l_buf);
dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf));
EXIT;
END IF;
l_sum := l_sum - 16383;
DBMS_LOB.READ(l_loc,l_stat,l_pos,l_buf);
l_pos := l_pos + 16383;
dbms_output.put_line(UTL_RAW.CAST_TO_VARCHAR2(l_buf));
END LOOP;
DBMS_LOB.CLOSE(l_loc);
END;
"""
isFileExist= self.getFileExist (remotePath, remoteNameFile)
if isFileExist == True :
status = self.__createOrRemplaceDirectory__(remotePath)
if isinstance(status,Exception): return status
cursor = cx_Oracle.Cursor(self.args['dbcon'])
cursor.callproc("dbms_output.enable")
try:
cursor.execute(DBMS_LOB_GET_FILE.format(self.directoryName, remoteNameFile))
except Exception as e:
logging.info("Impossible to execute the query `{0}`: {1}".format(DBMS_LOB_GET_FILE, self.cleanError(e)))
self.__dropDirectory__()
return ErrorSQLRequest(e)
else :
statusVar = cursor.var(cx_Oracle.NUMBER)
lineVar = cursor.var(cx_Oracle.STRING)
while True:
cursor.callproc("dbms_output.get_line", (lineVar, statusVar))
if statusVar.getvalue() != 0: break
line = lineVar.getvalue()
if line == None : line = ''
data += line
logging.info(repr(line))
cursor.close()
elif isFileExist == False : data = False
else : data = isFileExist
self.__dropDirectory__()
return data
def getFileExist (self, remotePath, remoteNameFile):
'''
Return true if file exists
'''
exist, returnedValue = False, False
logging.info("Test if the {1}{0} file exists".format(remoteNameFile,remotePath))
self.__setDirectoryName__()
status = self.__createOrRemplaceDirectory__(remotePath)
if isinstance(status,Exception): return status
DBMS_LOB_FILE_EXISTS = "DECLARE l_loc BFILE; l_ret BOOLEAN := FALSE; BEGIN l_loc := BFILENAME('{0}','{1}'); l_ret := DBMS_LOB.FILEEXISTS(l_loc) = 1; IF (l_ret) THEN dbms_output.put_line('True'); ELSE dbms_output.put_line('False'); END IF;END;"
cursor = cx_Oracle.Cursor(self.args['dbcon'])
try :
cursor.callproc("dbms_output.enable")
try:
cursor.execute(DBMS_LOB_FILE_EXISTS.format(self.directoryName, remoteNameFile))
except Exception as e:
logging.info("Impossible to execute the query `{0}`: {1}".format(DBMS_LOB_FILE_EXISTS, self.cleanError(e)))
returnedValue = ErrorSQLRequest(e)
else :
statusVar = cursor.var(cx_Oracle.NUMBER)
lineVar = cursor.var(cx_Oracle.STRING)
cursor.callproc("dbms_output.get_line", (lineVar, statusVar))
if statusVar.getvalue() != 0: returnedValue = False
line = lineVar.getvalue()
if line == None :
line = ''
if "True" in line :
logging.debug("The file exist: good news")
returnedValue = True
elif "False" in line :
logging.debug("The file doesn't exist")
returnedValue = False
else :
logging.warning("Can't know if the file exist. There is an error: {0}".format(line))
returnedValue = ErrorSQLRequest(line)
cursor.close()
except Exception as e:
returnedValue = ErrorSQLRequest(e)
self.__dropDirectory__()
return returnedValue
def testAll(self):
'''
Test all functions
'''
folder = self.__generateRandomString__()
self.args['print'].subtitle("DBMS_LOB to read files ?")
logging.info("Simulate the file reading in the {0} folder thanks to DBMS_LOB".format(folder))
status = self.getFile (remotePath=folder, remoteNameFile='data.txt', localFile="test.txt")
if status == True or status == False:
self.args['print'].goodNews("OK")
else :
self.args['print'].badNews("KO")
def runDbmsLob (args):
'''
Run the DbmsLob module
'''
status = True
if checkOptionsGivenByTheUser(args,["test-module","getFile"]) == False : return EXIT_MISS_ARGUMENT
dbmsLob = DbmsLob(args)
status = dbmsLob.connection(stopIfError=True)
if args['test-module'] == True :
args['print'].title("Test if the DbmsLob module can be used")
status = dbmsLob.testAll()
#Option 1: getFile
if args['getFile'] != None:
args['print'].title("Read the {0} file stored in the {1} path".format(args['getFile'][1],args['getFile'][0]))
data = dbmsLob.getFile (remotePath=args['getFile'][0], remoteNameFile=args['getFile'][1], localFile=args['getFile'][2])
if isinstance(data,Exception):
args['print'].badNews("There is an error: {0}".format(data))
elif data == False : args['print'].badNews("The {0} file in {1} doesn't exist".format(args['getFile'][1],args['getFile'][0]))
elif data == '' : args['print'].badNews("The {0} file is empty".format(args['getFile']))
else :
args['print'].goodNews("Data stored in the {0} file sored in {1} (copied in {2} locally):\n{3}".format(args['getFile'][1],args['getFile'][0],args['getFile'][2],data))