By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,536 Members | 1,504 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,536 IT Pros & Developers. It's quick & easy.

ORACLE MIGRATION ISSUES

P: n/a

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?

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
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)
Nov 12 '05 #3

P: n/a
Thanks Serge, I have sent you the code as requested

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.