Error executing script using BLOB SQL

Question

Error executing script using BLOB SQL

Navigation

#1 by (1 votes)

0

I have the following script that does what it does: it takes out the contents of a word, and puts it in a blob. But when I run it, I get these errors.

-ORA22275 - > LOB locator not valid -ORA06512 - > Line 29 (DBMS_LOB.fileopen (l_bfile, Dbms_Lob.File_Readonly);)

DIRECTORY PLANTILLAS AS 'D:/Plantillas' 

DECLARE
  l_bfile  BFILE;
  l_blob   BLOB;
  v_blob   BLOB; 

  existe integer;
BEGIN
      v_blob := EMPTY_BLOB();

    SELECT MAX(id) INTO existe FROM plantillas WHERE NOMBRE = 'plantilla';

    IF(existe is null) THEN
      INSERT INTO plantillas_ex 
       (ID, NBYTES, BLOQUE, MIMETYPE)
      VALUES 
       (existe,135680,v_blob,'application/msword')
      RETURN BLOQUE INTO l_blob;
    END IF;

  l_bfile := BFILENAME('PLANTILLAS', 'plantilla1');
  DBMS_LOB.fileopen(l_bfile, Dbms_Lob.File_Readonly);
  DBMS_LOB.loadfromfile(l_blob,l_bfile,DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.fileclose(l_bfile);
  COMMIT;

EXCEPTION WHEN OTHERS THEN
   ROLLBACK;
   RAISE;
END;

If someone could or could orient me in this, I would appreciate it. Greetings

----------------------------------------------- -----// SOLUTION \----------------------------------------- ------------------

CREATE OR REPLACE DIRECTORY PLANTILLAS AS 'D:/Plantillas';


DECLARE
 l_bfile  BFILE;
 l_blob   BLOB;
 v_blob   BLOB;
 l_length integer;

 existe integer;
 existe_blp integer;
BEGIN
     v_blob := EMPTY_BLOB();

   SELECT MAX(id) INTO existe FROM SPAC_P_PLANTDOC WHERE NOMBRE = 'plantilla';
   SELECT ID INTO existe_blp FROM SPAC_P_BLP WHERE ID = existe;

   IF(existe is not null AND existe_blp <> existe) THEN
     INSERT INTO SPAC_P_BLP 
      (ID, NBYTES, BLOQUE, MIMETYPE)
     VALUES 
      (existe,0,v_blob,'application/msword')
     RETURN BLOQUE INTO l_blob;


     l_bfile := BFILENAME('PLANTILLAS', 'plantilla1.doc');
     DBMS_LOB.fileopen(l_bfile, Dbms_Lob.File_Readonly);
     l_length := Dbms_Lob.getlength(l_bfile);
     DBMS_LOB.loadfromfile(l_blob,l_bfile,DBMS_LOB.getlength(l_bfile));    
     DBMS_LOB.fileclose(l_bfile);

     update SPAC_P_BLP set nbytes = l_length where id = existe;


  END IF;


EXCEPTION WHEN OTHERS THEN
  ROLLBACK;
  RAISE;
END;

sql blob

asked by urrutias 08.09.2017 в 12:47

source

1 answer

Sort by dates GROUP BY Problem with file submission through ajaxFormData

score 1 · Answer 1

I see two things in this script:

The first; the IF(existe is null) THEN is not the other way around ??? IF(existe is not null) THEN because if existe is null how will the insert work? It's going to thunder because it's a null key. I think the insert should be done when it's not null.

The second one I see is; You are reading files from the disk. My question is, have you already given your user permission to read files from the disk? This instruction allows us to manage directories within the DB: GRANT create any directory TO usuario;

Once done that you have to do this other: To create the directory you must give it a name to use it within the DB, in this example xmldirectorio and the path of the directory location in the operating system. Subsequently, they are given read, write and execute permission to use it.

CREATE OR REPLACE DIRECTORY XMLDIRECTORIO AS 'C:\XSDs';

GRANT EXECUTE, READ, WRITE ON DIRECTORY XMLDIRECTORIO TO usuario WITH GRANT OPTION;

Once this is done, you could use XMLDIRECTORIO in this example to access the directory. Did you do this ???