It could work, but i need know the id before insert. In whole =
application, there is many many places with the same algorithm: 1. get =
id, 2. use it in insert query. This is unfortunately not possible to =
change it. This application works on mssql too with the same problem =
(also not possible to use DML within functions). But for mssql I know =
the way how to do it (using 'openquery' statement)...
J.S.
- Original Message -----=20
From: <arul.kumar_at_bt.com>
To: <salva_at_avlas.cz>
Cc: <oracle-l_at_freelists.org>
Sent: Tuesday, November 16, 2004 2:38 PM
Subject: RE: DML within a function
> How about encapsulating the same in a INSERT Trigger?
>=20
> The trigger will check the last id & increment the value by 1 & insert =
=3D
> into the table!
>=20
>=20
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org =3D
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jan SALVA
> Sent: 16 November 2004 13:36
> To: oracle-l_at_freelists.org
> Subject: DML within a function
>=20
>=20
> Hello all,
> when I use update command within a user defined function, I got =3D3D
> ORA-06512 error: Can't use DML within a function.
> Is ANY possibility to modify data within a function? Some dynamic sql =
or =3D
> =3D3D
> other feature?
> Why I need it:
> In my db - primary key values are set by application, it mean that =
last =3D
> =3D3D
> used Id is in "counters" table. Unfortunatelly sequence objects aren't =
=3D
> =3D3D
> used. When I need new Id, i get it with stored procedure, which =
handles =3D
> =3D3D
> my "counters" table. The big disadvantage is that I can't use this =
=3D3D
> schema for multi-row inserts, I must use cursors :-(. I know that this =
=3D
> =3D3D
> is bad solution, but I can't do anything with it now and I need =
improve =3D
> =3D3D
> db performance. If I have function, which handles my counters table, I =
=3D
> =3D3D
> would use it directly in insert command without cursor need.
> Thank you for any answers.
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l
>
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 16 2004 - 07:45:39 CST
