stored procedure with blob-parameter

[Lauritz]

I have a procedure in a package:

PROCEDURE GetObject(p_id IN number, o_state OUT number, p_object OUT blob) IS
BEGIN
  select o_state, o_object into p_object from t_object where o_id=p_id;
END GetObject;

But I can't figure out how to access this proc using doad, if I only have execute-rights to the package. When running as dba there is no problem (other than the obvious security-problem

).

When executing the code below, i get a "ORA-00942: table or view does not exists" at "LOBLocator.Size" (What is TLobLocator doing in my tables?):

OracleQuery:=TOracleQuery.Create(self);
OracleQuery.Session:=OracleSession;
OracleQuery.SQL.Clear;
OracleQuery.SQL.Add('BEGIN');
OracleQuery.SQL.Add('SOMEUSER.SOMEPACKAGE.GETOBJECT(:P_ID, :P_STATE, :P_OBJECT);');
OracleQuery.SQL.Add('END;');
OracleQuery.DeleteVariables;
OracleQuery.DeclareVariable('P_ID', otInteger);
OracleQuery.DeclareVariable('P_STATE', otInteger);
OracleQuery.DeclareVariable('P_OBJECT', otBlob);
OracleQuery.SetVariable('P_ID', 1);

LOBLocator:=TLOBLocator.Create(OracleSession, otBLOB);
OracleQuery.SetComplexVariable('P_OBJECT', LOBLocator);
OracleQuery.Execute;
ShowMessage('You will see this dialog: '+IntToStr(OracleQuery.GetVariable('P_STATE')));
ShowMessage('You will *not* see this dialog: '+IntToStr(LOBLocator.Size));
LOBLocator.Free;

OracleQuery.Free;

[This message has been edited by Lauritz (edited 12 November 1999).]

 

[Marco Kalter]

Interesting problem. I tested this, and it turns out that if you want to read data through a LOB Locator, you must have select privilege on the table that holds the CLOB or BLOB column. This implies that you cannot provide access to such a table through a stored program unit without having select privileges on it. The only work around I can think of is to read the BLOB or CLOB data inside the package as well (using the dbms_lob package) and to pass back the data in some other form to the caller. You are then limited by the 32KB PL/SQL Variable limit of course.

------------------
Marco Kalter
Allround Automations