Using TOraRef as parameters

NightProwler · Post by **NightProwler** » Thu 29 Jun 2006 14:26

Hi All.

May I use out parameter of TOraRef type of TOraSQL as In parameter in another TOraSQL?

I have:
--------------------------------------------------
create type base_typ as object
(
name varchar2(32)
)not final;

create type property_typ as object
(
entity ref base_typ
val float
)not final;

create table base_t of base_typ;
create table property_t of property_typ;
--------------------------------------------------
OraSQL1: TOraSQL1
OraSQL1.SQL =
'insert into base_t t values(:nam) returning REF(t) into :res'

OraSQL2: TOraSQL2
OraSQL2.SQL =
'insert into property_t t values(:obj, :val)'
--------------------------------------------------
And following code:

...
t := TOraType.Create(Session.OCISvcCtx, 'base_t');
OraSQL1.ParamByName('Res').AsRef.ObjectType := t;
t.Free;

OraSQL1.ParamByName('Nam').AsString := 'String';
OraSQL1.Execute;

OraSQL2.ParamByName('obj').AsRef := OraSQL1.ParamByName('Res').AsRef;
OraSQL2.ParamByName('Val').AsFloat := 100;
OraSQL2.Execute;
...

I have no error, but in property_t table field entity is Null.

What's wrong?

Thanx

Plash · Post by **Plash** » Fri 30 Jun 2006 11:20

You should call Pin method of TOraRef after obtaining an object reference to pin the object in OCI object cache. After finishing work with this object you should call Unpin method.

Code: Select all

  t := TOraType.Create(OraSession1.OCISvcCtx, 'base_typ');
  OraSQL1.ParamByName('Res').AsRef.ObjectType := t;
  t.Free;

  OraSQL1.ParamByName('Nam').AsString := 'String';
  OraSQL1.Execute;

  ref := OraSQL1.ParamByName('Res').AsRef;
  ref.Pin;

  OraSQL2.ParamByName('obj').AsRef := ref;
  OraSQL2.ParamByName('Val').AsFloat := 100;
  OraSQL2.Execute;
  ref.Unpin;

NightProwler · Post by **NightProwler** » Fri 30 Jun 2006 11:40

Thanks. It's work, but I got another trouble.

If I wrap thes code in cycle, I get Assertion in "OraObjects.pas, line 2522" on second pass on line of code

Code: Select all

ref.Pin

Can I use repeatedly same TOraSQL components to insert several rows with different params?

Thanks.

PS
I confused, that this approach read object in cache by call Pin, when I need only it's ref. May be there is another method?

Plash · Post by **Plash** » Mon 03 Jul 2006 08:57

To repeatedly use same TOraRef object for parameter call FreeObject method of TOraRef after calling Unpin.

Code: Select all

  ref.Unpin;
  ref.FreeObject;

When using REF input parameter OCI requires referenced object to be in cache. If you don't want to pin objects you shouldn't use REF parameters. In your case you can combine two SQL statements in one PL/SQL block.

NightProwler · Post by **NightProwler** » Mon 03 Jul 2006 10:42

Now all ok.
Thanks.

NightProwler · Post by **NightProwler** » Thu 20 Jul 2006 07:15

plash wrote:To repeatedly use same TOraRef object for parameter call FreeObject method of TOraRef after calling Unpin.
Code: Select all
  ref.Unpin;
  ref.FreeObject;
When using REF input parameter OCI requires referenced object to be in cache. If you don't want to pin objects you shouldn't use REF parameters. In your case you can combine two SQL statements in one PL/SQL block.

5.70.1.34 07.07.06
------------------
....
- Unpin method of TOraRef corrected
....

There is no needs to call ref.FreeObject; now?

Plash · Post by **Plash** » Mon 24 Jul 2006 08:03

Yes, now the call to FreeObject is embedded into Unpin method of TOraRef.