Start a new topic

PL SQL on DBVis ERROR

 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.


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

I´m sorry roger, but DbVisualizer still show me the same 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 

Hi,


Please attach a screenshot showing all of the script being executed in the SQL Commander.


Regards


Roger

Yes,

image


 

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

Login or Signup to post a comment