PL SQL on DBVis ERROR

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

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