Home » Developer & Programmer » Forms » ORA-21560 Argument 3 is null, invalid, or out of range 1 (Oracle forms 12c Oracle Database 11.0.2.4)
ORA-21560 Argument 3 is null, invalid, or out of range 1 [message #669635] Fri, 04 May 2018 10:03 Go to next message
ganesh_jadhav0509
Messages: 74
Registered: May 2007
Location: Chester
Member

I am trying to send email with attachment. Attachment file is there in database in one of directory. As part of sending email following is a procedure to upload file into database.

CREATE OR REPLACE PROCEDURE LOAD_FILE (pi_id IN INTEGER, pfname IN VARCHAR2, 

p_dir IN VARCHAR2 DEFAULT 'LOB_FILE_DIR') IS
   src_file BFILE;
   dst_file BLOB;
   lgh_file BINARY_INTEGER;
 begin
   src_file := bfilename(p_dir, pfname);

   insert into image_store
     (id_pk,fname,image,dir)
   values
     (pi_id,pfname, EMPTY_BLOB(),p_dir)
   RETURNING image INTO dst_file;

   DBMS_LOB.OPEN(src_file, dbms_lob.file_readonly);
   lgh_file := dbms_lob.getlength(src_file);
   DBMS_LOB.LOADFROMFILE(dst_file, src_file, lgh_file);
   dbms_lob.close(src_file);
   commit;

 END load_file;

When i try to send email through database anonymous block it works perfectly. But when we try to send email through forms 12c it pop ups with error.

ORA-21560 Argument 3 is null, invalid, or out of range 1

This same procedure works perfectly in forms 6i with 10g database. Which works perfectly.

One more thing i have noticed is when i comment out the following line system does not pop ups error message

DBMS_LOB.LOADFROMFILE(dst_file, src_file, lgh_file);



Oracle Forms Version: 12.1.2.3
Database Version: 11.0.2.4
Does anybody have any clue
Re: ORA-21560 Argument 3 is null, invalid, or out of range 1 [message #669636 is a reply to message #669635] Fri, 04 May 2018 10:12 Go to previous messageGo to next message
BlackSwan
Messages: 26703
Registered: January 2009
Location: SoCal
Senior Member
privilege acquired via ROLE does NOT apply within named PL/SQL procedure
a direct GRANT is required
GRANT EXECUTE ON DBMS_LOB TO <your_schema>;
Re: ORA-21560 Argument 3 is null, invalid, or out of range 1 [message #669637 is a reply to message #669636] Fri, 04 May 2018 10:43 Go to previous messageGo to next message
ganesh_jadhav0509
Messages: 74
Registered: May 2007
Location: Chester
Member

Thank you for reply.

Tried that but no success.
Re: ORA-21560 Argument 3 is null, invalid, or out of range 1 [message #669639 is a reply to message #669637] Fri, 04 May 2018 11:43 Go to previous messageGo to next message
John Watson
Messages: 8268
Registered: January 2010
Location: Global Village
Senior Member
What if you change the procedure to run with invoker's rights?
Re: ORA-21560 Argument 3 is null, invalid, or out of range 1 [message #669645 is a reply to message #669635] Fri, 04 May 2018 13:06 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
I think it has something to do with needed a directory object as the third parameter.

When you call a anonymous block, you are probably using LOB_FILE_DIR (a directory object), but your procedure is using the literal string 'LOB_FILE_DIR' if you do not send it in. Even if you send it in, it's a string, not a directory object.

However, you did not show us how you are calling your procedure, so it's only a guess.
Re: ORA-21560 Argument 3 is null, invalid, or out of range 1 [message #669764 is a reply to message #669635] Thu, 10 May 2018 16:13 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
This error occurs when the data file is empty. Please see the demonstration below.

-- table and directory object for testing your procedure:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE image_store
  2    (id_pk   INTEGER PRIMARY KEY,
  3     fname   VARCHAR2(30),
  4     image   BLOB,
  5     dir     VARCHAR2(30))
  6  /

Table created.

SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE DIRECTORY lob_file_dir AS 'c:\my_oracle_files'
  2  /

Directory created.

-- your procedure:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE LOAD_FILE (pi_id IN INTEGER, pfname IN VARCHAR2,
  2
  3  p_dir IN VARCHAR2 DEFAULT 'LOB_FILE_DIR') IS
  4     src_file BFILE;
  5     dst_file BLOB;
  6     lgh_file BINARY_INTEGER;
  7   begin
  8     src_file := bfilename(p_dir, pfname);
  9
 10     insert into image_store
 11       (id_pk,fname,image,dir)
 12     values
 13       (pi_id,pfname, EMPTY_BLOB(),p_dir)
 14     RETURNING image INTO dst_file;
 15
 16     DBMS_LOB.OPEN(src_file, dbms_lob.file_readonly);
 17     lgh_file := dbms_lob.getlength(src_file);
 18     DBMS_LOB.LOADFROMFILE(dst_file, src_file, lgh_file);
 19     dbms_lob.close(src_file);
 20     commit;
 21
 22   END load_file;
 23  /

Procedure created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.

-- test using file with data in it loads:
SCOTT@orcl_12.1.0.2.0> HOST DIR test.dat
 Volume in drive C is TI10716100D
 Volume Serial Number is 30B9-619B

 Directory of C:\my_oracle_files

05/10/2018  02:07 PM                72 test.dat
               1 File(s)             72 bytes
               0 Dir(s)  736,517,943,296 bytes free

SCOTT@orcl_12.1.0.2.0> HOST TYPE test.dat
this is the first test file for loading
using the load_file procedure

SCOTT@orcl_12.1.0.2.0> EXEC scott.load_file (1, 'test.dat')

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> SELECT id_pk, fname, LENGTH(image), dir
  2  FROM   image_store
  3  /

     ID_PK FNAME                          LENGTH(IMAGE) DIR
---------- ------------------------------ ------------- ------------------------------
         1 test.dat                                  72 LOB_FILE_DIR

1 row selected.

-- test using empty file reproduces your error:
SCOTT@orcl_12.1.0.2.0> HOST DIR test2.dat
 Volume in drive C is TI10716100D
 Volume Serial Number is 30B9-619B

 Directory of C:\my_oracle_files

05/10/2018  02:07 PM                 0 test2.dat
               1 File(s)              0 bytes
               0 Dir(s)  736,517,943,296 bytes free

SCOTT@orcl_12.1.0.2.0> HOST TYPE test2.dat

SCOTT@orcl_12.1.0.2.0> EXEC scott.load_file (2, 'test2.dat')
BEGIN scott.load_file (2, 'test2.dat'); END;

*
ERROR at line 1:
ORA-21560: argument 3 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 928
ORA-06512: at "SCOTT.LOAD_FILE", line 18
ORA-06512: at line 1


SCOTT@orcl_12.1.0.2.0>
Previous Topic: Need to know that how we edit default smart bar its icon and naming without creating custom menu bar
Next Topic: How to remove Gray title bar
Goto Forum:
  


Current Time: Fri May 29 08:24:07 CDT 2020