PLS-00103: Encountered the symbol “ ” when expecting one of the following

0
votes

I am trying to insert documents, which present one folder in local system to oracle table. I am getting error as below:

Error (15,7): PLS-00103: Encountered the symbol " " when expecting one of the following: constant exception table LONG_ double ref char time timestamp interval date binary national character nchar

DECLARE
  l_bfile BFILE;
  l_blob  BLOB;
BEGIN
  INSERT INTO member_document 
  VALUES (
    SELECT empty_blob(), jf.file_created, jf.file_file_extension
      FROM FILES JF 
      JOIN MEMBER M ON M.MIG_OLD_ID=JF.PERSON_ID
     WHERE JF.FILE_ID = 1054
  )

  RETURN document_file INTO l_blob;

  l_bfile := BFILENAME(exampledir, '000d779789.pdf');
  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;
END;

Can anyone please help me.

2
oracleoracle11goracle10gblob
Is that the entire script you're trying to run, or is there another command after this? The error is reported on line 15 column 7, and you've only shown 14 lines. So do you have another statement afterwards perhaps, without a / inbetween? – Alex Poole

2 Answers

0
votes

We use INSERT INTO ... VALUES (...) when inserting a row of literals. But you are inserting from a query, so you don't need the VALUES bit:

INSERT INTO MEMBER_DOCUMENT 
      select empty_blob(),JF.FILE_CREATED,JF.FILE_FILE_EXTENSION
      FROM FILES JF JOIN MEMBER M 
           ON M.MIG_OLD_ID=JF.PERSON_ID 
      where JF.FILE  ....

Getting beyond the syntax bloomer your logic is awry. Presumably you want to load the file into your database table. Your code won't achieve this. Try this instead:

DECLARE
    l_bfile BFILE;
    l_blob   BLOB;
BEGIN
    l_bfile := BFILENAME(exampleDir,'000d779789.pdf');
    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);

    INSERT INTO MEMBER_DOCUMENT 
    select l_blob,JF.FILE_CREATED,JF.FILE_FILE_EXTENSION
    FROM FILES JF JOIN MEMBER M 
    ON M.MIG_OLD_ID=JF.PERSON_ID 
    where JF.FILE_ID=1054;

    COMMIT;
END;
0
votes

try this:

Declare
   L_bfile                       Bfile;
   L_blob                        Blob;
Begin
   Insert Into Member_document( Col1, Col2, Col3)
      (Select Empty_blob(), Jf.File_created, Jf.File_file_extension
         From Files Jf Join Member M On M.Mig_old_id = Jf.Person_id
        Where Jf.File_id = 1054)
   Return Document_file
     Into L_blob;
   L_bfile := Bfilename( Exampledir, '000d779789.pdf');
   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;
End;

where col1, col2 and col3 are your Member_document's columns name.

It is a good practice to put column names in insert into, because when table structure change, you will get an error because the columns on table won't be same as columns in select used to insert the table, hope this helps!