PL/SQL and JAVA (I'm a novice!)

[nino2000]

Hi, I'm a french novice about oracle.
I work with the table EMP supplied with ORACLE.
I want to create a page JSP which print on the screen the table EMP. I use oracle.jsp.dbutil.BeanUtil . This bean offers a method which return HTML table from a Java Resulset (see

http://globec.gso.uri.edu/jspdocs/javadoc/oracle/jsp/dbutil/BeanUtil.html

for more information)

I deploy BeanUtil into Oracle DataBase.
I create a package which will be called by the Javaserver Page. This package returns the HTML table to the page JSP

The package:
CREATE OR REPLACE PACKAGE TOOLS_HTML
AS
TYPE MYTYPEOFCURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
FUNCTION CALL_BEAN_UTIL (cur IN MYTYPEOFCURSOR)
RETURN VARCHAR2;
FUNCTION LISTE_EMPLOYE RETURN VARCHAR2
END TOOLS_HTML
;


CREATE OR REPLACE PACKAGE BODY TOOLS_HTML
AS

FUNCTION CALL_BEAN_UTIL (cur IN MYTYPEOFCURSOR)
RETURN VARCHAR2
AS LANGUAGE JAVA NAME 'oracle.jsp.dbutil.BeanUtil.translateToHTMLTable(java.
sql.ResultSet) return java.lang.String';

FUNCTION LISTE_EMPLOYE RETURN VARCHAR2 AS
machaine VARCHAR2(32000);
moncurseur MYTYPEOFCURSOR;
BEGIN
OPEN moncurseur FOR SELECT * FROM EMP;
machaine := TOOLS_HTML.CALL_BEAN_UTIL(moncurseur);
RETURN machaine;
END;
END;


The JSP:
[...]
cstmt = conn.prepareCall("begin ?:= TOOLS_HTML.LISTE_EMPLOYE(); end;"
cstmt.registerOutParameter(1,OracleTypes.VARCHAR);
cstmt.execute();
reponse = cstmt.getString(1);
System.out.println(reponse);
}
catch (SQLException e) {
%><%= e.getMessage()%><%}


Oracle compile the package with no error but when I execute with SQL*PLUS:
SQL> DECLARE
2 chaine varchar2(30000);
3 BEGIN
4 chaine:=TOOLS_HTML.LISTE_EMPLOYE();
5 END;
6 /
DECLARE
*
ERREUR à la ligne 1 :
ORA-00600: code erreur interne, arguments : [15419], [severe error during
PL/SQL execution], [], [], [], [], [], []
ORA-06544: PL/SQL : erreur interne, arguments : [pgm.cgmbiad2()], [102], [],
[], [], [], [], []
ORA-06553: PLS-801: erreur interne [0]

When I execute the JSP page, the server return the same error.
Could I help me please?

 

[Swany]

ORA-600 is an Oracle BUGcheck code. ORA-600s send chills down the spines of even the most experienced DBAs.

I am going to assume the problem is coming from returning a java.lang.string as a varchar2 in the procedure CALL_BEAN_UTIL. I don't have any experience with java and pl/sql interaction so I'm not sure about that.

The reason I surmise this is that the first error you get is: ORA-06553 PLS-string: string

Then you get
ORA-06544 PL/SQL: internal error, arguments: [string], [string], [string], [string], [string], [string], [string], [string]

Then you get the ORA-600 because the Oracle PL/SQL interpreter crashed trying to run your program (because of the above errors)

If you have Oracle support, give them a call and they can help you find where the bug is coming from and can probably provide you with a patch to fix the problem.

 

[DBAwhosaysNIE]

&quot;ORA-600s send chills down the spines of even the most experienced DBAs&quot; - LMAO!

we spent nearly NINE MONTHS w/OSS trying to stop AQ from spewing 600s which appears to be SOMEWHAT stable as of 8.1.6.3.0

my experience w/OSS has been that I can usually code around a problem in far less time than it takes them to fix something. if you're running Oracle Applications then you're completely SOL as there's very little a DBA can do to fix problems with them (which is why I got out of the Apps business; see bio blurb) except download one of the thousands of patches, run adpatch and pray.

admittidly this isn't the most useful post I've ever made but could actually save you some time for those w/skill to do workarounds.

 

[uparna]

HI ,
we were also getting a lot of ora-600 errors with a some similar messages ....with some parameters being shown and a lot of such brackets being shown [][][][]..........
after a long correspondance with ora support , we came to know that this is an internal bug of oracle and has been resolved in their next release , Oracle 8.1.7.x . If you already have a CSI number , mail oracle in the TAR format and they will look into the matter , and perhaps (as in myu case) give you a free upgrade to the aforesaid version !

Best of luck !
Regards,
Jayaram.