Hi,
Please check the following in the users guide how to run complex SQL scripts:
http://confluence.dbvis.com/display/UG100/Executing+Complex+Statements
Regards
Roger
I don`t understand... I need the delimiter command? l_buffer row is not wrote correctly?
Sorry for my english.
Regards.
Thnks beforehand.
Hi,
Enclose all of you script in --/ ... / as below. Just paste all of the following in an SQL Commander and run it.
--/
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
BEGIN
-- Get LOB locator
--SELECT col1
--INTO l_blob
--FROM tab1
SELECT resp.F_CREACION, resp.F_PRESENTACION, cst.D_NOMBRE, cst.D_APELLIDO1, cst.D_APELLIDO2, cst.B_DOCUMENTO, cst.NOMFICH, cst.EXT_DOC, resp.ACT_X_ACTUACION, resp.X_RESPUESTA_ACT
INTO l_blob
FROM CST_RESPUESTA_CST cst, CST_RESPUESTAS_ACT resp, CST_ACTUACIONES act
WHERE cst.RSP_X_RESPUESTA_ACT = resp.X_RESPUESTA_ACT
AND act.X_ACTUACION = resp.ACT_X_ACTUACION
AND act.C_ACTUACION_ACCESO = 'SPRINT2MARZO-LAI'
ORDER BY resp.F_PRESENTACION desc
l_blob_len := DBMS_LOB.getlength(l_blob)
-- Open the destination file.
--l_file := UTL_FILE.fopen('BLOBS','MyImage.gif','w', 32767);
l_file := UTL_FILE.fopen('C:\Users\amartinb.ext\Documents\Pentaho\ETLPentaho','prueba.pdf','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
/
Regards
Roger
Hi,
Please attach a screenshot showing all of the script being executed in the SQL Commander.
Regards
Roger
Angel,
It seems your script is not well formed. There should be semicolons after all declarations. Here is an example:
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; BEGIN ...
Since the syntax is defined by Oracle (in this case) you need to consult the Oracle documentation for rules about the PL/SQL script format.
Regards
Roger
Angel Martin Bonilla
Hello, I try execute this code on DBVis and I saw this error:
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
BEGIN
-- Get LOB locator
--SELECT col1
--INTO l_blob
--FROM tab1
SELECT resp.F_CREACION, resp.F_PRESENTACION, cst.D_NOMBRE, cst.D_APELLIDO1, cst.D_APELLIDO2, cst.B_DOCUMENTO, cst.NOMFICH, cst.EXT_DOC, resp.ACT_X_ACTUACION, resp.X_RESPUESTA_ACT
INTO l_blob
FROM CST_RESPUESTA_CST cst, CST_RESPUESTAS_ACT resp, CST_ACTUACIONES act
WHERE cst.RSP_X_RESPUESTA_ACT = resp.X_RESPUESTA_ACT
AND act.X_ACTUACION = resp.ACT_X_ACTUACION
AND act.C_ACTUACION_ACCESO = 'SPRINT2MARZO-LAI'
ORDER BY resp.F_PRESENTACION desc
l_blob_len := DBMS_LOB.getlength(l_blob)
-- Open the destination file.
--l_file := UTL_FILE.fopen('BLOBS','MyImage.gif','w', 32767);
l_file := UTL_FILE.fopen('C:\Users\amartinb.ext\Documents\Pentaho\ETLPentaho','prueba.pdf','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:
[Code: 6550, SQL State: 65000] ORA-06550: línea 3, columna 3:
PLS-00103: Se ha encontrado el símbolo "L_BUFFER" cuando se esperaba uno de los siguientes:
:= . ( @ % ; not null range default character
I'm tottaly amateur with DBVis.