Create Error when using Named Parameter Notation call

Shaikh Nazer
Shaikh Nazer

The DBMS_LOB package lets you operate on large objects. EDB Postgres Advanced Server's implementation of DBMS_LOB is a partial implementation when compared to Oracle's version.

While READing the CLOB we have encountered the issue with offset keyword. The same is explained below along with reason and workaround:


dbms_lob.READ (lob_loc => p_clob,
amount => v_amount,
offset => v_offset,
buffer => v_buffer);

error: ERROR syntax error at or near «offset» ...


Using the named parameter notation to call results in error because of using offset:

select * from pg_get_keywords() where word='offset'; 
word | catcode | barelabel | catdesc | baredesc 
offset | R | f | reservado | requires AS

The same call works without using named parameter notation:

dbms_lob.READ (p_clob,v_amount,v_offset,v_buffer);

In source code offset values as defined in double quotes because of integer values.

Hence offset values should be defined between double quotes (" ").

Please refer below output of source code for same:

select * from dba_source where name = 'DBMS_LOB';

ENTERPRISEDB | SYS | DBMS_LOB | PACKAGE | 34 | PROCEDURE read(lob_loc bytea, INOUT amount integer, "offset" integer, OUT buffer bytea);
ENTERPRISEDB | SYS | DBMS_LOB | PACKAGE | 35 | PROCEDURE read(lob_loc clob, INOUT amount integer, "offset" integer, OUT buffer character va


You can define named parameter notation as below to call package.

dbms_lob.READ (lob_loc => p_clob,
amount => v_amount,
“offset” => v_offset,
buffer => v_buffer);

Was this article helpful?

0 out of 0 found this helpful