ORACLE MIGRATION ISSUES

[db2sysc]

1. In the oracle trigger we have a SINGLE INSERT STORED PROCEDURE
CALLED MUTLIPLE TIMES with different values.

But when changed to DB2, it gives SQL -746 ?? Any help??
2. In oracle we have

Create or replace TYPE TEST_TYPE AS OBJECT ( ....,....)

Create or replace TYPE TEST_TABLE AS TABLE OF TEST_TYPE

This is used in a FUNCTION to return RESULT SET HAVING DATA AS IN A
TABLE AND this TABLE IS USED in JOINS in the application.

How do we do it in DB2??
3. Can a UDF call a STORED PROCEDURE OR TRIGGER in DB2?

[Serge Rielau]

db*****@yahoo.com wrote:

1. In the oracle trigger we have a SINGLE INSERT STORED PROCEDURE
CALLED MUTLIPLE TIMES with different values.

But when changed to DB2, it gives SQL -746 ?? Any help?? Boy.. this is turning into a FAQ...
Can you send me the source by email? (I'll treat it with respect ;-)
I can take a quick peek.
2. In oracle we have

Create or replace TYPE TEST_TYPE AS OBJECT ( ....,....) CREATE TYPE employee_t AS (name varchar(10), id INTGER, ..)
Create or replace TYPE TEST_TABLE AS TABLE OF TEST_TYPE CREATE TABLE employee OF employee_t ....
This is used in a FUNCTION to return RESULT SET HAVING DATA AS IN A
TABLE AND this TABLE IS USED in JOINS in the application. Hah.... forget everything I wrote above.. you don't need it. The result
table is defined "on-the-fly":
CREATE FUNCTION table_foo(a INTEGER, b INTEGER)
RETURNS TABLE(c1 char(10), c2 char(10))
RETURN VALUES (char(a), char(b)),
(char(a + 1), char(b + 1));

SELECT * FROM TABLE(table_foo()) AS f;

(I didn't test that.. forgive me typos)
3. Can a UDF call a STORED PROCEDURE OR TRIGGER in DB2?

In DB2 V8.2 (which you have since you got the -746) you can call a
procedure from a UDF. You can not call a trigger directly (and I hope
Oracle is no different there), not from a UDF or anything.
Triggers "fire".

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[DA Morgan]

Serge Rielau wrote:

db*****@yahoo.com wrote:

1. In the oracle trigger we have a SINGLE INSERT STORED PROCEDURE
CALLED MUTLIPLE TIMES with different values.

But when changed to DB2, it gives SQL -746 ?? Any help??

Boy.. this is turning into a FAQ...
Can you send me the source by email? (I'll treat it with respect ;-)
I can take a quick peek.

2. In oracle we have

Create or replace TYPE TEST_TYPE AS OBJECT ( ....,....)

CREATE TYPE employee_t AS (name varchar(10), id INTGER, ..)

Create or replace TYPE TEST_TABLE AS TABLE OF TEST_TYPE

CREATE TABLE employee OF employee_t ....

This is used in a FUNCTION to return RESULT SET HAVING DATA AS IN A
TABLE AND this TABLE IS USED in JOINS in the application.

Hah.... forget everything I wrote above.. you don't need it. The result
table is defined "on-the-fly":
CREATE FUNCTION table_foo(a INTEGER, b INTEGER)
RETURNS TABLE(c1 char(10), c2 char(10))
RETURN VALUES (char(a), char(b)),
(char(a + 1), char(b + 1));

SELECT * FROM TABLE(table_foo()) AS f;

(I didn't test that.. forgive me typos)

3. Can a UDF call a STORED PROCEDURE OR TRIGGER in DB2?

In DB2 V8.2 (which you have since you got the -746) you can call a
procedure from a UDF. You can not call a trigger directly (and I hope
Oracle is no different there), not from a UDF or anything.
Triggers "fire".

Cheers
Serge

I am not aware of any RDBMS in which triggers are callable. Though what
one can do is put all of the trigger's code into a procedure and have
the trigger execute the proc.
--
Daniel A. Morgan
University of Washington
da******@x.washington.edu
(replace 'x' with 'u' to respond)

[db2sysc]

Thanks Serge, I have sent you the code as requested