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  |
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 #669645 is a reply to message #669635] |
Fri, 04 May 2018 13:06  |
joy_division
Messages: 4963
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 |
 |
Barbara Boehmer
Messages: 9104
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>
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 17:23:27 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
