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
|
-- #################################### updateBlob ####################################
PROCEDURE updateBlob( tablename varchar2,abbreviation varchar2,sequenceKey number,dataKey number,clobname varchar2,name varchar2,descr varchar2,fileType varchar2,category varchar2,blobData BLOB, url varchar2, print number )
IS
sqlStatement VARCHAR2(2000) := NULL;
PROCEDURE concatQuery( query IN OUT VARCHAR2, colBaseName VARCHAR2, colValue VARCHAR2, quote INTEGER )
IS
separator CHAR;
BEGIN
IF colValue IS NOT NULL THEN
IF query IS NOT NULL THEN
query := query || ', ';
END IF;
IF quote = 0 THEN
separator := '';
ELSE
separator := '''';
END IF;
query := query || abbreviation || '_' || colBaseName || ' = ' || separator || REPLACE( colValue, '''', '''''' ) || separator;
END IF;
END concatQuery;
BEGIN
concatQuery( sqlStatement, tablename || '_PK', dataKey, 0 );
concatQuery( sqlStatement, 'CLOB_NAME', clobname, 1 );
concatQuery( sqlStatement, 'NAME', name, 1 );
concatQuery( sqlStatement, 'DESCRIPTION', descr, 1 );
concatQuery( sqlStatement, 'BLOB', ':blob', 0 );
concatQuery( sqlStatement, 'URL', url, 1 );
concatQuery( sqlStatement, 'PRINTABLE',print, 0 );
concatQuery( sqlStatement, 'FILE_TYPE', fileType, 1 );
concatQuery( sqlStatement, 'CATEGORY', category, 1 );
sqlStatement := 'UPDATE ' || tablename || '_BLOB SET ' || sqlStatement || ' WHERE ' || 'PK_' || tablename || '_BLOB = ' || sequenceKey;
EXECUTE IMMEDIATE sqlStatement USING blobData;
END updateBlob; |
Partager