Home » SQL & PL/SQL » SQL & PL/SQL » execution of pl/sql code, 9i versus 10g
execution of pl/sql code, 9i versus 10g [message #226022] Thu, 22 March 2007 05:49 Go to next message
JulieB
Messages: 3
Registered: March 2007
Junior Member
Hi all,


I wrote some pl/sql procedures. They are compiled with no error and with no warning in sqlplus Release 9.2.0.1.0.

I can execute these procedures on Oracle Database 10g. Everything is fine.

However, if I want to execute them on Oracle 9i Release 9.2.0.1.0, an error happens.
####
SQL> exec myFunction(x,y);
BEGIN myFunction(x,y);END;
*
ERROR at line 1;
ORA-00600: internal error code, arguments: [%s] [%s] [%s]
####
The same execution does work with 10g.

Do you have any idea or work-around to execute everything I need with 9g ?

Thanks !
Re: execution of pl/sql code, 9i versus 10g [message #226036 is a reply to message #226022] Thu, 22 March 2007 06:42 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Post the procedure code.
Re: execution of pl/sql code, 9i versus 10g [message #226051 is a reply to message #226036] Thu, 22 March 2007 07:25 Go to previous messageGo to next message
JulieB
Messages: 3
Registered: March 2007
Junior Member
Here are some lines that I cannot execute with Oracle 9i.
However, I think this is a "general" problem as I can execute these procedures with Oracle 10g.

Thanks !

------------------------------------------------------------------------
-- spot_update1instr(proc_percent IN FLOAT,
-- proc_jour_modif IN DATE,
-- proc_sicovam IN NUMBER,
-- proc_last IN NUMBER)

-- Update le jour proc_jour_modif le last de l'instrument de sicovam proc_sico de proc_percent % ou le met a proc_last si pas de valeur la veille pour le last
------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE spot_update1instr(proc_percent IN FLOAT,
proc_jour_modif IN NUMBER,
proc_sicovam IN NUMBER,
proc_last IN NUMBER)
IS
jour_modif NUMBER;
BEGIN
jour_modif:= proc_jour_modif;
MERGE INTO Historique h
USING ( SELECT proc_sicovam AS sicovam,
num_to_date(jour_modif) AS jour,
(SELECT h.d*(1+proc_percent/100)
FROM Historique h
WHERE (jour=num_to_date(jour_modif-1) AND sicovam=proc_sicovam)
) AS d
FROM dual
) hh
ON (h.sicovam=hh.sicovam AND h.jour=hh.jour)
WHEN MATCHED THEN
UPDATE SET h.d=hh.d
WHEN NOT MATCHED THEN INSERT (h.sicovam, h.jour, h.d)
VALUES (hh.sicovam, hh.jour, proc_last);
END;
/





------------------------------------------------------------------------
-- spot_update1jour(proc_type_inst IN VARCHAR2,
-- proc_percent IN FLOAT,
-- proc_jour_modif IN DATE,
-- proc_last IN NUMBER)

-- Update le jour proc_jour_modif le last des instruments de type proc_type_inst de proc_percent % ou le met a proc_last si pas de valeur la veille pour le last
------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE spot_update1jour(proc_type_inst IN VARCHAR2,
proc_percent IN FLOAT,
proc_jour_modif IN NUMBER,
proc_last IN NUMBER)
IS
BEGIN
FOR instrument in (SELECT * FROM titres WHERE type=proc_type_inst ) LOOP
spot_update1instr(proc_percent,
proc_jour_modif,
instrument.sicovam,
proc_last);
END LOOP;
END;
/


------------------------------------------------------------------------
-- spot_update(proc_type_inst IN VARCHAR2,
-- proc_percent IN FLOAT,
-- proc_jour_depart IN DATE,
-- proc_nb_jour IN INTEGER,
-- proc_last IN NUMBER)

-- Update de proc_jour_depart a proc_jour_depart+proc_nb_jour le last des instruments de type proc_type_inst de proc_percent % chaque jour ou le met a proc_last si pas de valeur la veille pour le last
------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE spot_update(proc_type_inst IN VARCHAR2,
proc_percent IN FLOAT,
proc_jour_depart IN NUMBER,
proc_nb_jour IN INTEGER,
proc_last IN NUMBER)
IS
BEGIN
FOR i in 0..proc_nb_jour-1 LOOP
spot_update1jour(proc_type_inst,
proc_percent*(i+1),
proc_jour_depart+i,
proc_last);
END LOOP;
END;
/





-- main //
exec spot_update('A', 1, 37685, 1, 10)
Re: execution of pl/sql code, 9i versus 10g [message #226056 is a reply to message #226022] Thu, 22 March 2007 07:39 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Since you are on rel 9.2.0.1.0

I think that your are hitting one of the merge bugs, which
have been solved in later patches.

The only solution is to "rethink" your approach or upgrade your
9.2.0.1.0.

I have a 9.2.0.6.0 myself and I have no problem with this.
Thou I have expericened problems with MERGE in 9.2.0.1.0

There should be information about this on metalink aswell

[Updated on: Thu, 22 March 2007 07:40]

Report message to a moderator

Re: execution of pl/sql code, 9i versus 10g [message #226137 is a reply to message #226022] Thu, 22 March 2007 11:24 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
ORA-600 means Oracle bug.

So, as tahpush suggested, call support or search metalink.

HTH.
Re: execution of pl/sql code, 9i versus 10g [message #226213 is a reply to message #226137] Thu, 22 March 2007 20:39 Go to previous messageGo to next message
JulieB
Messages: 3
Registered: March 2007
Junior Member
Thanks a lot tahpush ! Smile
icon14.gif  Re: execution of pl/sql code, 9i versus 10g [message #226288 is a reply to message #226213] Fri, 23 March 2007 03:17 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Anytime Smile
Previous Topic: Can someone explain what does this sql statement do?
Next Topic: ORA-01722: invalid number after setting user preferences
Goto Forum:
  


Current Time: Sun Dec 11 00:34:05 CST 2016

Total time taken to generate the page: 0.06085 seconds