CODEWITHSUNDEEP
oracle-databaseoracle11goracle10gupgrade
Bilgin Kılıç
Bilgin Kılıç

Reputation: 9119

upgrading 10 g to 11g checklist

There are plenty of stored procedure on 10g platform . ( Almost 500 SPs) Each SP might have loop, fetch and etc.

I'd like to ask you if there is a cool method to control all the SPs which are currently running on 10g, and guarantee that it works on 11 g.

I have a development server 1 which is 10 g and the other development server is 11 g. I can use both of them to testify the propose above.

For instance I know that on 10 g if you use loop, and during the loop the update statements do not affect the loop data but 11g. There might be more cases that I have to consider. Please tell me if you have any brillant idea , otherwise I will check them up one by one manually and it is a lot of time and human control might be weak sometimes.

important note: It is said that if you select some data from a table or tables, and if you use it in a loop, then during de loop, if you update and commit between loop case, it affects the selected data in cursor.(@11g) But this did not happen @10g version. Please correct me if you heard something like that.

The Example Case;

CREATE TABLE vty_musteri(
  musterino    NUMBER  NOT NULL,
  subeadi      VARCHAR2(61),
  kayitzamani  VARCHAR2(20)
);

INSERT INTO vty_musteri (musterino, subeadi, kayitzamani )
  VALUES (12345, 'AMSTERDAM', '05/30/2012 15:11:13');
COMMIT;



CREATE UNIQUE INDEX vty_musteri_idx ON vty_musteri (musterino);

SELECT * FROM vty_musteri;


CREATE OR REPLACE PROCEDURE krd_upd_silseomusteri_sp(RC1 in out  SYS_REFCURSOR) AS
  v_musterino NUMBER := 12345;

BEGIN

 OPEN RC1 FOR
    SELECT  m.musterino, m.subeadi, m.kayitzamani
      FROM vty_musteri m
     WHERE m.musterino = v_musterino;


   update vty_musteri
     set subeadi     = 'PORTO',
         kayitzamani = (SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS')
                          FROM dual)
   where musterino = v_musterino;

  COMMIT;

After all run this test on PLSQL:

    DECLARE
       --test
       vRecTip   SYS_REFCURSOR;
       TYPE vRecTipK IS RECORD(
          musterino   NUMBER,
          subeadi     VARCHAR2(61),
          kayitzamani VARCHAR2(20)

          );
       v_SeoTip vRecTipK;
    BEGIN

       krd_upd_silseomusteri_sp(rc1 => vRecTip);

       IF vRecTip%ISOPEN THEN
          LOOP
             FETCH vRecTip
                INTO v_SeoTip;
             EXIT WHEN vRecTip%NOTFOUND;

             dbms_output.put_line('The Value : ' || v_SeoTip.musterino || ' - ' || v_SeoTip.subeadi || ' - ' || v_SeoTip.kayitzamani);

          END LOOP;
       END IF;
       COMMIT;
    END;   
END;

If you run this on 10g you will see AMSTERDAM, but on 11G, it is PORTO.

To fix it; I put a hint in the sp like the following: 

  SELECT /*+ full(m)*/ m.musterino, m.subeadi, m.kayitzamani

Isn't it weird? any alternative sugesstion to get AMSTERDAM ?

Upvotes: 0

Views: 1476

Answers (4)

Shailesh Pratapwar
Shailesh Pratapwar

Reputation: 4224

Recently I have done migration to Oracle 11g. Faced few unprecedented issues. I have written a blog post on this. Have a look http://learncodewrite.blogspot.in/2017/04/migrating-to-oracle-11g-from-oracle-10g.html?m=1.

Upvotes: 0

Bilgin Kılıç
Bilgin Kılıç

Reputation: 9119

To fix it; I put a hint in the sp like the following:

SELECT /+ full(m)/ m.musterino, m.subeadi, m.kayitzamani

Upvotes: 0

user330315
user330315

Reputation:

One thing we stumbled upon during a migration were queries that weren't supposed to work on 10.x (but did anyway) did no longer work on 11.x

This happens if you have ambigous column references in your query.

Something like this:

SELECT name,
       f.some_col,
       b.other_col
FROM foo f, 
  JOIN bar b ON f.id = b.fid

If the column name exists in both tables, 10.x would run the statement - which was a bug.

This bug (BugID: 6760937) was fixed and makes the statement (rightfully) fail in 11.x

Upvotes: 2

Rene
Rene

Reputation: 10551

Basic PLSQL structures should work exactly the same. Some pitfalls are listed here:

http://www.help2ora.com/index.php/2011/08/04/be-careful-when-migrating-difference-between-oracle-10g-and-11g/

Upvotes: 1

Related Questions