https://www.sqlines.com/ 2026-04-16T04:56:44+00:00 https://www.sqlines.com/ https://www.sqlines.com/lib/images/favicon.ico text/html 2021-05-13T18:37:08+00:00 https://www.sqlines.com/db2-to-oracle/cursor_with_hold?rev=1620931028&do=diff In DB2 you can use WITH HOLD clause in DECLARE CURSOR statement to specify that the cursor remains open when a COMMIT is issued. By default, DB2 closes a cursor on COMMIT. Oracle does not provide WITH HOLD and WITHOUT HOLD clauses in cursor declaration, but a cursor remains open after COMMIT unless FOR UPDATE is specified. text/html 2018-01-02T18:39:13+00:00 https://www.sqlines.com/db2-to-oracle/fetch_first_rows_only?rev=1514918353&do=diff In DB2, you can use FETCH FIRST n ROWS ONLY clause in a SELECT statement to return only n rows, and this limit is applied after sorting the rows as specified in the ORDER BY clause. IBM DB2: -- Sample table CREATE TABLE cities (name VARCHAR(70)); -- Sample data INSERT INTO cities VALUES ('Paris'); INSERT INTO cities VALUES ('New York'); INSERT INTO cities VALUES ('Prague'); INSERT INTO cities VALUES ('Alanya'); INSERT INTO cities VALUES ('London'); text/html 2013-09-25T14:01:55+00:00 https://www.sqlines.com/db2-to-oracle/if_select_count?rev=1380117715&do=diff DB2 allows you to use a SELECT statement returning a scalar value, for example SELECT COUNT(*), in the IF statement. The result of the query can be used in a boolean expression. DB2: --#SET TERMINATOR @ CREATE OR REPLACE FUNCTION fn_color_exists(p_name VARCHAR(70)) RETURNS CHAR(1) BEGIN -- Execute the query and compare the count with 0 in IF statement IF (SELECT COUNT(*) FROM colors WHERE name = p_name FETCH FIRST ROW ONLY) > 0 THEN RETURN 'Y'… text/html 2013-09-23T13:19:32+00:00 https://www.sqlines.com/db2-to-oracle/new_reference_in_trigger?rev=1379942372&do=diff In a FOR EACH ROW trigger, you can use NEW correlation name in DB2 and :NEW in Oracle to refer to the new values of columns. DB2 Oracle NEW.column :NEW.column in the trigger body NEW.column in WHEN condition NEW can be omitted in assignment :NEW must be specified in assignment Note: DB2 and Oracle allow you to change alias NEW to any other identifier using REFERENCING NEW AS name clause. text/html 2023-06-01T16:57:29+00:00 https://www.sqlines.com/db2-to-oracle/process_result_sets_in_procedures?rev=1685638649&do=diff In DB2, a stored procedure can process result set(s) returned by another stored procedure using RESULT_SET_LOCATOR type, ASSOCIATE RESULT SET LOCATORS and ALLOCATE CURSOR FOR RESULT SET statements. In Oracle you can use SYS_REFCURSOR type to pass result sets between stored procedures. text/html 2013-04-24T08:44:46+00:00 https://www.sqlines.com/db2-to-oracle/select_isolation?rev=1366793086&do=diff In DB2, you can specify the isolation level clause in a SELECT statement: DB2: -- Uncommitted read (mostly used to avoid read locks) SELECT name FROM cities WITH UR; -- Cursor stability (read committed) SELECT name FROM cities WITH CS; text/html 2013-09-23T13:20:01+00:00 https://www.sqlines.com/db2-to-oracle/update?rev=1379942401&do=diff DB2 and Oracle provide the UPDATE statement to update existing rows in a table, but there are differences in the syntax. Assume there is a sample table: CREATE TABLE colors ( name VARCHAR(30), hex CHAR(7), rgb CHAR(11) ); INSERT INTO colors VALUES ('Red', NULL, NULL);