Hello Experts,
I am trying to use the code based on the example here so that I can use it in my Azure Pipeline. Based on the example, I came up with the following code but it is throwing me an error that the Table doesn't exist. Could someone please help me in getting this fixed?
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
l_blob_file_nm VARCHAR2(200 CHAR);
l_tbl_nm VARCHAR2(50 CHAR) := 'EDW.ENTITY_VERSION_DATA';
l_blob_col_nm VARCHAR2(30 CHAR) := 'ENTITY_DATA';
l_file_type_col VARCHAR2(20 CHAR) := 'FILE_TYPE';
l_file_type_val VARCHAR2(10 CHAR) := '.pdf';
l_id_col VARCHAR2(30 CHAR) := 'ENTITY_VERSION_ID';
l_id_val VARCHAR2(50 CHAR);
l_order_col VARCHAR2(30 CHAR) := 'ENTITY_VERSION_DATE';
BEGIN
-- Get LOB locator
SELECT l_id_col, l_blob_col_nm
INTO l_id_val, l_blob
FROM l_tbl_nm
WHERE l_file_type_col = l_file_type
AND ROWNUM = 1
ORDER BY l_order_col DESC
;
-- Set BLOB Name and extension
l_blob_file_nm := l_tbl_nm || '__' || l_blob_col_nm || '__' || l_file_type_val || '__' || l_id_val || l_file_type_val;
l_blob_len := DBMS_LOB.getlength(l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen('UTL_DIR',l_blob_file_nm,'wb', 32767);
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos <= l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose(l_file);
EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
/
Error report -
ORA-06550: line 21, column 10:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 19, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
In addition, is it possible to select the values by looping through another column for variable l_file_type_val as these types exist in the same table so that I don't have to manually pass them?
To add to the above, would it be possible to determine which column actually contains BLOB datatype if I pass a table name and do the operation accordingly for me instead of me passing column name in addition to table name?
Please let me know if you need any other details to help me.
Here are my environment details:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
Thanks for your help!