[DB-SIG] DCOracle and handling of package OUT parameters broken ?!

Andreas Jung ajung@sz-sb.de
Wed, 17 May 2000 13:59:42 +0200

Dear all,

I have the following stored proc:

create or replace procedure select_lob(
	tabname		IN varchar2,
	primkey		IN varchar2,
	primval		IN varchar2,
	retstr		OUT varchar2) IS

	mylen 		integer;
	myclob 		clob;
	sql_smt		varchar(1024);
	bufb		varchar2(32767);


	sql_smt := 'select inhalt from  ojs_de where docnum='''||primval||''''; 
	execute immediate sql_smt into myclob;

	mylen := DBMS_LOB.GETLENGTH(myclob);
	DBMS_LOB.READ(myclob , mylen, 1,bufb);

	retstr := substr(bufb,1,2900);


Its task is to deliver the content of a CLOB (XML files < 32000 characters).
The procedure is being called from search.py:

curs.execute('select docnum from ojs_de order by docnum')
data = curs.fetchall()

for d in data:
	docnum = d[0]
	print docnum	
	res= curs.procedures.select_lob('ojs_de','docnum',docnum)
	print res


Python fails with the following traceback:

Traceback (innermost last):
  File "./search.py", line 23, in ?
    res= curs.procedures.select_lob('ojs_de','docnum',docnum)
  File "DCOracle/DCOracle/ociProc.py", line 133, in __call__
    if oci_.oexec(oc): self._error()
  File "DCOracle/DCOracle/ociProc.py", line 106, in _error
    raise error, (rc, oci_.OracleErrorMessage(c.lda, rc))
oci.error: (6502, 'ORA-06502: PL/SQL: numeric or value error: character string buffer too small\012ORA-06512: at "AJUNG.SELECT_LOB", line 22\012ORA-06512: at line 2\012')

When I reduce the size of the output string (substr()) to less than about 2000 I receive
the data correctly. 

Is this DCOracle related or a problem of Oracle ?!

Our environment: Oracle 8.16 EE, Solaris 2.7, Python 1.5.2, DCOracle 1.3

Thanks for helping,
Andreas Jung