TLobLocator parameter in a writing Package function

[Stefan Heymann]

I have the following problem: I have a package function which expects a CLOB as an IN parameter and writes the passed data to the DB.

The Package Wizard creates the following prototype for this function:

PL/SLQ:
function CL_Write( arg_key in varchar2,
                   arg_name in varchar2,
                   arg_value in clob )
                   return integer;

Delphi:
function ClWrite(const ArgKey: string;
                 const ArgName: string;
                 ArgValue: TLOBLocator):
               Double;

When I try to write data using this function I get an access violation error. My test code is:

var
  LL: TLobLocator;
begin
  LL := TLobLocator.Create(Session, otClob);
  try
    ClWrite (aKey, aName, LL);
    LL.AsString := 'A';
  finally
    LL.Free;
  end;
end;

What's going wrong here?

 

[Marco Kalter]

What does the PL/SQL code look like that sets the CLOB?

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

 

[Stefan Heymann]

function CL_Write( arg_key in varchar2,
                  arg_name in varchar2,
                  arg_value in out clob )
                  return integer
  ...
  ... Retrieve lnPrefKey ...

  insert into g$prefvalues
  ( prefkey_id, name, datatype, clobvalue )
    values
  ( lnPrefKey, bv_arg_name, 'CL', arg_value );

  else

  update g$prefvalues
  set clobvalue = arg_value
  where bv_arg_name = name
        and
        prefkey_id = lnPrefKey;

  ...

[This message has been edited by Stefan Heymann (edited 20 October 2000).]

 

[Marco Kalter]

In this PL/SQL Code the arg_value (which has now become in out by the way

) is never assigned a value. You must make sure that the LOB Locator is initialized in the PL/SQL Code. For example:

update g$prefvalues
  set clobvalue = empty_clob()
  where bv_arg_name = name
  and prefkey_id = lnPrefKey
  returning clobvalue into arg_value;

The returning .. into .. clause assigns the initialized CLOB to the parameter, which you can subsequently use to write the data.

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