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.


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

Yes,

image


 

Hi,


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


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,


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 don`t understand... I need the delimiter command? l_buffer row is not wrote correctly?

Sorry for my english.

Regards.

Thnks beforehand.

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

Login or Signup to post a comment