Very helpfull - It doesn't work with 8.1.7 svrmgr also
Depreter Frederic, January 18, 2002 - 3:04 pm UTC
You have to use sqlplus.
The svrmgr program delivered with 8.1.7 is not able
to compile java sources.
Not mentionned in Oracle documentation (I think)
=> very useful
Generate Java code in SQLPLUS
Lynn, May 07, 2002 - 2:46 pm UTC
version 8.1.7 on Windows 2000
I tried to create/compile your javamail source code and got the following error:
create or replace and compile java source named "mail"
*
ERROR at line 1:
ORA-29536: badly formed source: oracle.aurora.sqljdecl.TokenMgrError: Lexical
error at line 34, column 33. Encountered: "@" (64), after : ""
Please help! Thanks, Lynn
May 07, 2002 - 7:19 pm UTC
what is on your line 34? Do an "list" in the script right after the slash or run it with SET ECHO ON
Lynn, May 07, 2002 - 8:01 pm UTC
Thanks for following up on this.
Please see line 34 below:
33 props.put("mail host", SMTPHost);
34 Message msg = new MimeMessage(Session.getDefaultInstance(props, null));
35 msg.setFrom(new InternetAddress(from));
36 if (to != null && to.length() > 0)
37 msg.setRecipients(Message.RecipientType.TO,
38 InternetAddress.parse(to, false));
When compile, it expects to enter variables like to, cc, bcc, and subject. For example,
it prompts: Enter value for to: email@address.com
Then, I got this error if entered without <>:
ORA-29536: badly formed source: Encountered "to" at line 34, column 24.
Was expecting one of:
"instanceof" ...
")" ...
">" ...
"<" ...
If I entered values with < >, for example,
<email@address.com> when it prompts
I'll get ora-29536: badly formed source: oracle.aurora.sqljdecl.tokenmgrerror: lexical error.
Thanks in advance.
Lynn
May 07, 2002 - 8:23 pm UTC
Lets see the entire thing from start to finish I guess. Looks like you might have lost a quote or something
lynn, May 07, 2002 - 8:38 pm UTC
Thanks for your quick response.
Here it is:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
SQL> create or replace and compile java source named "mail"
2 as
3 import java.io.*;
4 import java.sql.*;
5 import java.util.Properties;
6 import java.util.Date;
7 import javax.activation.*;
8 import javax.mail.*;
9 import javax.mail.internet.*;
10 import oracle.jdbc.driver.*;
11 import oracle.sql.*;
12
13 public class mail
14 {
15 static String dftMime = "application/octet-stream";
16 static String dftName = "filename.dat";
17 public static oracle.sql.NUMBER
18 send(String from,
19 String to,
20 String cc,
21 String bcc,
22 String subject,
23 String body,
24 String SMTPHost,
25 oracle.sql.BLOB attachmentData,
26 String attachmentType,
27 String attachmentFileName)
28 {
29 int rc = 0;
30 try
31 {
32 Properties props = System.getProperties();
33 props.put("mail1server", SMTPHost);
34 Message msg = new MimeMessage(Session.getDefaultInstance(props, null));
35 msg.setFrom(new InternetAddress(from));
36 if (to != null && to.length() > 0)
37 msg.setRecipients(Message.RecipientType.TO,
38 InternetAddress.parse(to, false));
39 if (cc != null && cc.length() > 0)
40 msg.setRecipients(Message.RecipientType.CC,
41 InternetAddress.parse(cc, false));
42 if (bcc != null && bcc.length() > 0)
43 msg.setRecipients(Message.RecipientType.BCC,
44 InternetAddress.parse(bcc, false));
45 if ( subject != null && subject.length() > 0 )
46 msg.setSubject(subject);
47 else msg.setSubject("(no subject)");
48 msg.setSentDate(new Date());
49 if (attachmentData != null)
50 {
51 MimeBodyPart mbp1 = new MimeBodyPart();
52 mbp1.setText((body != null ? body : ""));
53 mbp1.setDisposition(Part.INLINE);
54 MimeBodyPart mbp2 = new MimeBodyPart();
55 String type = (attachmentType != null ? attachmentType : dftMime);
56 String fileName = (attachmentFileName != null ? attachmentFileName : dftName);
57 mbp2.setDisposition(Part.ATTACHMENT);
58 mbp2.setFileName(fileName);
59 mbp2.setDataHandler(new DataHandler(new BLOBDataSource(attachmentData, type)));
60 MimeMultipart mp = new MimeMultipart();
61 mp.addBodyPart(mbp1);
62 mp.addBodyPart(mbp2);
63 msg.setContent(mp);
64 }
65 else
66 {
67 msg.setText((body != null ? body : ""));
68 }
69 Transport.send(msg);
70 rc = 1;
71 } catch (Exception e)
72 {
73 e.printStackTrace();
74 rc = 0;
75 } finally
76 {
77 return new oracle.sql.NUMBER(rc);
78 }
79 }
80 static class BLOBDataSource implements DataSource
81 {
82 private BLOB data;
83 private String type;
84 BLOBDataSource(BLOB data, String type)
85 {
86 this.type = type;
87 this.data = data;
88 }
89 public InputStream getInputStream() throws IOException
90 {
91 try
92 {
93 if (data == null)
94 throw new IOException("No data.");
95 return data.getBinaryStream();
96 } catch(SQLException e)
97 {
98 throw new
99 IOException("Cannot get binary input stream from BLOB.");
100 }
101 }
102 public OutputStream getOutputStream() throws IOException
103 {
104 throw new IOException("Cannot do this.");
105 }
106 public String getContentType()
107 {
108 return type;
109 }
110 public String getName()
111 {
112 return "BLOBDataSource";
113 }
114 }
115 }
116 /
Enter value for to: email@address.com
old 36: if (to != null && to.length() > 0)
new 36: if (to != null email@address.comlength() > 0)
Enter value for cc: email@address.com
old 39: if (cc != null && cc.length() > 0)
new 39: if (cc != null email@address.comlength() > 0)
Enter value for bcc: email@address.com
old 42: if (bcc != null && bcc.length() > 0)
new 42: if (bcc != null email@address.comlength() > 0)
Enter value for subject: test
old 45: if ( subject != null && subject.length() > 0 )
new 45: if ( subject != null testlength() > 0 )
create or replace and compile java source named "mail"
*
ERROR at line 1:
ORA-29536: badly formed source: Encountered "email" at line 34, column 24.
Was expecting one of:
"instanceof" ...
")" ...
">" ...
"<" ...
"?" ...
"==" ...
"<=" ...
">=" ...
"!=" ...
"||" ...
"&&" ...
"++" ...
"--" ...
"+" ...
"-" ...
"*" ...
"/" ...
"&" ...
"|" ...
"^" ...
"%" ...
"<<" ...
">>" ...
">>>" ...
"." ...
"[" ...
"(" ...
and if <> was entered with the variables, it gave me this:
Enter value for to: <email@address.com>
old 36: if (to != null && to.length() > 0)
new 36: if (to != null <email@address.com>length() > 0)
Enter value for cc: <email@address.com>
old 39: if (cc != null && cc.length() > 0)
new 39: if (cc != null <email@address.com>length() > 0)
Enter value for bcc: <email@address.com>
old 42: if (bcc != null && bcc.length() > 0)
new 42: if (bcc != null <email@address.com>length() > 0)
Enter value for subject: test
old 45: if ( subject != null && subject.length() > 0 )
new 45: if ( subject != null testlength() > 0 )
create or replace and compile java source named "mail"
*
ERROR at line 1:
ORA-29536: badly formed source: oracle.aurora.sqljdecl.TokenMgrError: Lexical
error at line 34, column 30. Encountered: "@" (64), after : ""
May 08, 2002 - 7:05 am UTC
set define OFF
before running that. That'll stop the substitution variable processing sqlplus is doing.
oops ----------> &&
Yogeeraj, May 08, 2002 - 12:22 am UTC
hi,
is it not the "&&" causing the problems here? ;)
set define off;
regards
Yogeeraj
May 08, 2002 - 7:17 am UTC
exactly, very clear when the entire thing is pasted in...
A reader, September 23, 2003 - 11:16 am UTC
Same issue, with corrections....
jfpark, May 05, 2004 - 5:00 pm UTC
Tom,
Trying to learn my way around Java Stored Procedures with your examples in 'Expert One-on-One', but am stuck on the same issue as above.
The example is from pg. 852-856, on a 9iR2 DB with 9i SQL*Plus and I did remember 'set define off'. I've even tried it across several DB's, with no success. Any assistance you could provide would be greatly appreciated!
SQL*Plus: Release 9.2.0.1.0 - Production on Wed May 5 16:45:42 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release 9.2.0.4.0 - Production
JServer Release 9.2.0.4.0 - Production
SQL> set define off;
SQL> create or replace and compile
2 java source named "demo_passing_pkg"
3 as
4 import java.io.*;
5 import java.sql.*;
6 import java.math.*;
7 import oracle.sql.*;
8 import oracle.jdbc.driver.*;
9
10 public class demo_passing_pkg extends Object
11 {
12 public static void pass( java.math.BigDecimal p_in,
13 java.math.BigDecimal[] p_out )
14 {
15 if ( p_in != null )
16 {
17 System.out.println
18 ("The first parameter is " + p_in.toString() );
19
20 p_out[0] = p_in.negate();
21
22 System.out.println
23 ("Set out parameter to " + p_out[0].toString() );
24 }
25 }
26
27 public static void pass( java.sql.Timestamp p_in,
28 java.sql.Timestamp[] p_out )
29 {
30 if ( p_in != null )
31 {
32 System.out.println
33 ( "The first parameter is " + p_in.toString() );
34
35 p_out[0] = p_in;
36
37 if ( p_out[0].getMonth() < 11 )
38 p_out[0].setMonth( p_out[0].getMonth()+1 );
39 else
40 (
41 p_out[0].setMonth( 0 );
42 p_out[0].setYear( p_out[0].getYear()+1 );
43 )
44 System.out.println
45 ( "Set out parameter to " + p_out[0].toString() );
46 }
47 }
48
49 public static void pass( java.lang.String p_in,
50 java.lang.String[] p_out)
51 {
52 if ( p_in != null )
53 {
54 System.out.println
55 ( "The first parameter is " + p_in.toString() );
56
57 p_out[0] = p_in.toUpperCase();
58
59 System.out.println
60 ( "Set out paramter to " + p_out[0].toString() );
61 }
62 }
63
64 public static void pass( oracle.sql.CLOB p_in,
65 oracle.sql.CLOB[] p_out)
66 throws SQLException, IOException
67 {
68 if ( p_in != null && p_out[0] != null )
69 {
70 System.out.println
71 ( "The first parameter is " + p_in.length() );
72 System.out.println
73 ("The first parameter is '" +
74 p_in.getSubString(1,80) + "'" );
75
76 Reader is = p_in.getCharacterStream();
77 Writer os = p_out[0].getCharacterOutputStream();
78
79 char buffer[] = new char[8192];
80 int length;
81
82 while ( (length=is.read(buffer,0,8192)) != -1 )
83 os.write(buffer,0,length);
84
85 is.close();
86 os.close();
87
88 System.out.println
89 ( "Set out parameter to " +
90 p_out[0].getSubString(1,80) );
91 }
92 }
93
94 private static void show_array_info( oracle.sql.ARRAY p_in )
95 throws SQLException
96 {
97 System.out.println( "Array is of type " +
98 p_in.getSQLTypeName() );
99 System.out.println( "Array is of type code " +
100 p_in.getBaseType() );
101 System.out.println( "Array is of length " +
102 p_in.length() );
103 }
104
105 public static void pass_num_array( oracle.sql.ARRAY p_in,
106 oracle.sql.ARRAY[] p_out)
107 throws SQLException
108 {
109 show_array_info( p_in );
110 java.math.BigDecimal[] values = (BigDecimal[])p_in.getArray();
111
112 for ( int i = 0; i < p_in.length(); i++ )
113 System.out.println( "p_in["+i+"] = " + values[i].toString() );
114
115 Connection conn = new OracleDriver().defaultConnection();
116 ArrayDescriptor descriptor =
117 ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );
118
119 p_out[0] = new ARRAY(descriptor, conn, values);
120
121 }
122
123 public static void
124 pass_date_array( oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out )
125 throws SQLException
126 {
127 show_array_info( p_in );
128 java.sql.Timestamp[] values = (Timestamp[])p_in.getArray();
129
130 for ( int i = 0; i < p_in.length(); i++ )
131 System.out.println( "p_in["+i+"] = " + values[i].toString() );
132
133 Connection conn = new OracleDriver().defaultConnection();
134 ArrayDescriptor descriptor =
135 ArrayDescriptor.createDescriptor(p_in.getSQLTypeName(), conn );
136
137 p_out[0] = new ARRAY( descriptor, conn, values );
138
139 }
140
141 public static void
142 pass_str_array( oracle.sql.ARRAY p_in, oracle.sql.ARRAY[] p_out)
143 throws java.sql.SQLException,IOException
144 {
145 show_array_info( p_in );
146 String[] values = (String[])p_in.getArray();
147
148 for( int i = 0; i < p_in.length(); i++ )
149 System.out.println( "p_in["+i+"] = " + values[i] );
150
151 Connection conn = new OracleDriver().defaultConnection();
152 ArrayDescriptor descriptor =
153 ArrayDescriptor.createDescriptor( p_in.getSQLTypeName(), conn );
154
155 p_out[0] = new ARRAY( descriptor, conn, values);
156
157 }
158
159 public static void pass( byte[] p_in, byte[][] p_out)
160 {
161 if ( p_in != null )
162 p_out[0] = p_in;
163 }
164
165 public static void pass_int( int p_in, int[] p_out )
166 {
167 System.out.println
168 ( "The in parameter was " + p_in );
169
170 p_out[0] = p_in;
171
172 System.out.println
173 ( "The out parameter is " + p_out[0] );
174 }
175
176 public static String return_string()
177 {
178 return "Hello World";
179 }
180
181 public static java.sql.Timestamp return_date()
182 {
183 return new java.sql.Timestamp(0);
184 }
185
186 public static java.math.BigDecimal return_num()
187 {
188 return new java.math.BigDecimal( "44.3543" );
189 }
190
191 }
192 /
java source named "demo_passing_pkg"
*
ERROR at line 2:
ORA-29536: badly formed source: Encountered ";" at line 38, column 37.
Was expecting one of:
"instanceof" ...
")" ...
">" ...
"<" ...
"?" ...
"==" ...
"<=" ...
">=" ...
"!=" ...
"||" ...
"&&" ...
"++" ...
"--" ...
"+" ...
"-" ...
"*" ...
"/" ...
"&" ...
"|" ...
"^" ...
"%" ...
"<<" ...
">>" ...
">>>" ...
"." ...
"[" ...
"(" ...
"=" ...
"*=" ...
"/=" ...
"%=" ...
"+=" ...
"-=" ...
"<<=" ...
">>=" ...
">>>=" ...
"&=" ...
"^=" ...
"|=" ...
SQL>
Help!
May 05, 2004 - 7:50 pm UTC
27 public static void pass( java.sql.Timestamp p_in,
28 java.sql.Timestamp[] p_out )
29 {
30 if ( p_in != null )
31 {
32 System.out.println
33 ( "The first parameter is " + p_in.toString() );
34
35 p_out[0] = p_in;
36
37 if ( p_out[0].getMonth() < 11 )
38 p_out[0].setMonth( p_out[0].getMonth()+1 );
39 else
40 ( <<<<<==== should be curly brace {
41 p_out[0].setMonth( 0 );
42 p_out[0].setYear( p_out[0].getYear()+1 );
43 ) <<<<<==== should be curly brace }
44 System.out.println
45 ( "Set out parameter to " + p_out[0].toString() );
46 }
47 }
ARGH!
jfpark, May 06, 2004 - 10:29 am UTC
Thanks Tom, best wishes and blessings!
Compilation issues
Mike, March 04, 2010 - 7:45 am UTC
Tom,
We have been compiling the enclosed code on numerous Oracle environments across multiple platforms and versions, but have the same issue with HP UX every time.
We deploy our code as build scripts to customers.
The following contained in a file myList.java when run from SQL*Plus on HP produces (below)
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "myList" AS
import java.util.ArrayList;
public class myList
{
private ArrayList list = new ArrayList();
public int addElement(Object element)
{
for (int i=0; i<list.size(); i++)
{
if (list.get(i) == null)
{
list.add(i,element);
return i;
}
}
list.add(element);
return list.size()-1;
}
public void removeElement(int index)
{
list.add(index, null);
}
public Object get(int index)
{
return list.get(index);
}
}
/
show errors java source "myList"
CREATE myList.java
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "myList" AS
*
ERROR at line 1:
ORA-29536: badly formed source: Encountered "<EOF>" at line 1, column 27.
Was expecting one of:
"." ...
";" ...
etc...
This compiles on our Windows development machine Oracle 9.0.2 using SQL*Plus 8.1.7 or 9.0.2 and on our RedHat Enterprise using SQL*Plus 9.0.2. Other successful platforms include IBM using SQL*Plus 10g2 (not sure of exact version), Oracles own Linux (both 'scratch build' and Oracle VM deployed versions) using SQL*Plus 10g2 (not sure of exact version), and any Windows environment we have encountered.
In fact this impacts all of our java build scripts in the same way, this just happens to be the smallest example available.
For clarity; when the same script is run from Windows SQL*plus (any version 8.1.7 to 10g2) connected to their HP based Oracle, it compiles successfully.
I note that it fails at line 1 in each case, but at a different column position, and always 'points' at the name of the package.
Our customer isn't much interested in 'our' issue, and Oracle can't really support us on a platform we don't have access to. I have seen many unsolved references to this issue (googling) where code changes haven't apparently resolved the problem. It appears to be platform specific, Oracle doesn't appear to recognise this as a bug, so we are looking for a 'workaround' (or the obvious mistake :) ), if you (or anyone else) know of one...
Thanks
Mike
PS This is the first time I've posted a question (rather than an answer) in 15 years of Oracle. That may say something about the quality of your work here :)
March 04, 2010 - 10:02 am UTC
wondering if this is a newline issue - you mentioned windows.
can you do an
$ od -c <file>
on this and see if your end of lines are \n or \r\n - if they are \r\n - then cleanup the file to have \n only (unix end of lines)...
supporting an environment you don't have access to is going to be worse than hard. It will not be truly possible.
Compilation issues
Mike, March 04, 2010 - 11:12 am UTC
Tom,
Thanks for the quick response.
Just when you think you thought of almost everything...
I forgot to mention that we tried explicitly converting the files from windows format to UNIX before a run (as a test). Good old PFE32.
But to be safe, the DBA also tested as you suggested. He confirms the files are clean.
The odd thing about the newline idea is that the column it reports is invariably in the middle of 'create or replace and compile'. Clearly it is very confused at this point.
Fortunately the (the customer) do give us feedback on simple tests and checks that they can do for us. I don't mean to indicate that any customer of ours is less than reasonable :)
March 04, 2010 - 11:33 am UTC
can you have the CUSTOMER run the od -c on their files, since their files are the ones breaking...
look for any weird characters anywhere.
can you explain the output:
CREATE myList.java
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "myList" AS
*
ERROR at line 1:
where does the bit in bold come from? What else is in the script.
Not strange to be in middle of something unrelated. How many times has a missing semi-colon in a program caused a compile error many lines before or after the actual error?
Compilation issues
Mike, March 04, 2010 - 12:12 pm UTC
Tom,
Apologies, the extra CREATE myList.java is a prompt from our build script/log. Sorry about that. Misleading to say the least. Each script is prompted before running to help us find any errors quickly while testing our build scripts.
The build scripts create around 1400 (named) database objects when it installs, so you can imagine the time it can take otherwise.
Unfortunately, as we are some 6 hours ahead of you, the DBA has long gone for the day.
I will ask him to check his files in the morning.
Absolutely right about the column position I would guess.
I suppose it's just that I can usually work it out when it's PL/SQL or SQL falling over in SQL*Plus :)
March 04, 2010 - 12:43 pm UTC
you might ask them to delete the empty lines and try that snippet of a script - blank lines "mean" something to sqlplus - they should be OK in the middle of a create or replace java - but just to be sure...
Compilation issues
Mike, March 08, 2010 - 11:13 am UTC
Tom,
We have tried your last suggestions.
All to no avail.
I think it is a dead issue and must be put down to a platform specific SQL*Plus bug.
It wouldn't be the first time that SQL*Plus behaved somewhat differently across platforms.
Dereferencing file paths in SQL*Plus 8.1.7 using '@@' generally was an issue.
More recently we have come accross some versions that refuse to accept '\' in file paths; specifically Oracle's 'unbreakable' linux :)
(No, we don't make a habit of '\' in paths, one slipped into a new build script)
Fortunately PL/SQL wrappers to java are very late binding, if that is the way to put it, and it only causes an issue during the install. As embarrassing at it may be, it seems we have to live with it.
Thanks again for your efforts.
Mike
Johnson, November 15, 2010 - 9:20 pm UTC
Hi Tom,
How are you? I got the following error.
CREATE JAVA SOURCE NAMED 'Hello' AS
*
ERROR at line 1:
ORA-29501: invalid or missing Java source, class, or resource name
My code is:-
CREATE JAVA SOURCE NAMED 'Hello' AS
public class Hello {
public static String hello() {
return 'Hello World'; } };
/
Even change the quote to double quote. Still have error.
SQL> @Hello
CREATE JAVA SOURCE NAMED "Hello" AS
*
ERROR at line 1:
ORA-29536: badly formed source: oracle.aurora.sqljdecl.TokenMgrError: Lexical
error at line 3, column 19. Encountered: "e" (101), after : "\'H"
Thanks
November 16, 2010 - 3:36 am UTC
'Hello' is a string, a character string
"Hello" is an identifier - with case preserved.
Use ", not '
Error Create function Java
Fernando, January 31, 2024 - 5:44 pm UTC
Hi Tom
Help me!
This error is get when execute this script.
Informe de error -
ORA-29536: origen especificado incorrectamente: oracle.aurora.sqljdecl.TokenMgrError: Lexical error at line 12, column 26. Encountered: "\'" (39), after : "\'"
29536. 00000 - "badly formed source: %s"
Code
================================================
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED HR."PDFUtilsFile" as
import java.lang.*;
import java.util.*;
import java.io.*;
import java.sql.*;
import oracle.sql.ARRAY;
import java.util.StringTokenizer;
import oracle.sql.BLOB;
import com.lowagie.*;
public class PDFUtilsFile
{
static String error = '';
static PdfCopyFields copy;
static ByteArrayOutputStream bout = new ByteArrayOutputStream();
public static String getError() {
return error;
}
public static void concatPdf(oracle.sql.BLOB dokument) {
if (copy == null) return;
if (dokument == null) return;
try {
copy.addDocument(new PdfReader(dokument.getBinaryStream()));
} catch (Exception ex) {
error += "(zdruziPdfBlob) " + ex.toString();
}
}
public static void createPdf() {
try {
error = "";
bout = new ByteArrayOutputStream();
copy = new PdfCopyFields(bout);
} catch (Exception ex) {
error = ex.toString();
}
}
public static BLOB closePdf() {
BLOB lvblob = null;
if (copy == null) return null;
copy.close();
try {
OracleConnection conn = (OracleConnection) new OracleDriver().defaultConnection();
lvblob = BLOB.createTemporary(conn, true, BLOB.DURATION_SESSION);
OutputStream os = blob.setBinaryStream(0);
os.write(bout.toByteArray());
os.flush();
} catch (Exception ex) {
error = ex.tostring();
}
return lvblob;
}
public static String list (String path) {
String list = "";
File myFile = new File (path);
String[] arrayList = myFile.list();
Arrays.sort(arrayList, String.CASE_INSENSITIVE_ORDER);
for (int i=0; i < arrayList.length; i++) {
// Prevent directory listing expanding if we will blow VARCHAR2 limit.
if ((list.length() + arrayList[i].length() + 1) > 32767)
break;
if (!list.equals(""))
list += "," + arrayList[i];
else
list += arrayList[i];
}
return list;
}
}
/
February 08, 2024 - 2:31 pm UTC
Look at line 12 in the code:
static String error = '';
That is invalid Java code (hint - look specifically at the value you're assigning).