SQL Objects, JDeveloper, JPublisher and Java
This is one for my application developer friends. Say your application uses stored procedures. Not just any stored procedures, but ones that accept arrays or SQL Object types (User Defined Types - UDTs). One thing you have to do is map a java object to that SQL Object before you can pass it back to the stored procedure. I can't remember the specifics, but I can show you how to "automatically" create the Java code that maps to the SQL Object. First, create type UDT. If will be a table of a record...is it called a multi-dimensional array? Whatever. CREATE TYPE r_record IS OBJECT
(
id NUMBER(10),
col1 VARCHAR2(30),
col2 VARCHAR2(30),
col3 VARCHAR2(30)
)
/
CREATE TYPE t_table AS TABLE OF R_RECORD
/ I'll create a procedure that accepts T_TABLE as an input parameter. It won't do anything, but just to give you an idea. CREATE OR REPLACE
PROCEDURE test( p_t_table IN T_TABLE )
IS
BEGIN
NULL;
END test;
/ Now go into JDeveloper and through the Database Navigator, select the schema you placed these objects.  Right click on the object and select Generate Java (as shown above). You'll then be presented with an options menu:  It's all foreign to me so I accept the defaults. I do notice however that it's pretty customizable, for instance, you can select how you want to Number Types to be generated, either objectjdbc, oracle, jdbc or bigdecimal. You'll know better about what this means than I will.  From that, you get 3 files, TTable.java which maps to the TABLE OF R_RECORD, RRecord.java which maps to the SQL Type R_RECORD, and finally RRecordRef.java...which I don't really know what it does. I'm sure you will though. Here's the code generated, in order. TTable.javaimport java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.jpub.runtime.MutableArray;
public class TTable implements ORAData, ORADataFactory
{
public static final String _SQL_NAME = "CJUSTICE.T_TABLE";
public static final int _SQL_TYPECODE = OracleTypes.ARRAY;
MutableArray _array;
private static final TTable _TTableFactory = new TTable();
public static ORADataFactory getORADataFactory()
{ return _TTableFactory; }
/* constructors */
public TTable()
{
this((RRecord[])null);
}
public TTable(RRecord[] a)
{
_array = new MutableArray(2002, a, RRecord.getORADataFactory());
}
/* ORAData interface */
public Datum toDatum(Connection c) throws SQLException
{
return _array.toDatum(c, _SQL_NAME);
}
/* ORADataFactory interface */
public ORAData create(Datum d, int sqlType) throws SQLException
{
if (d == null) return null;
TTable a = new TTable();
a._array = new MutableArray(2002, (ARRAY) d, RRecord.getORADataFactory());
return a;
}
public int length() throws SQLException
{
return _array.length();
}
public int getBaseType() throws SQLException
{
return _array.getBaseType();
}
public String getBaseTypeName() throws SQLException
{
return _array.getBaseTypeName();
}
public ArrayDescriptor getDescriptor() throws SQLException
{
return _array.getDescriptor();
}
/* array accessor methods */
public RRecord[] getArray() throws SQLException
{
return (RRecord[]) _array.getObjectArray(
new RRecord[_array.length()]);
}
public RRecord[] getArray(long index, int count) throws SQLException
{
return (RRecord[]) _array.getObjectArray(index,
new RRecord[_array.sliceLength(index, count)]);
}
public void setArray(RRecord[] a) throws SQLException
{
_array.setObjectArray(a);
}
public void setArray(RRecord[] a, long index) throws SQLException
{
_array.setObjectArray(a, index);
}
public RRecord getElement(long index) throws SQLException
{
return (RRecord) _array.getObjectElement(index);
}
public void setElement(RRecord a, long index) throws SQLException
{
_array.setObjectElement(a, index);
}
} RRecord.javaimport java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;
public class RRecord implements ORAData, ORADataFactory
{
public static final String _SQL_NAME = "CJUSTICE.R_RECORD";
public static final int _SQL_TYPECODE = OracleTypes.STRUCT;
protected MutableStruct _struct;
protected static int[] _sqlType = { 2,12,12,12 };
protected static ORADataFactory[] _factory = new ORADataFactory[4];
protected static final RRecord _RRecordFactory = new RRecord();
public static ORADataFactory getORADataFactory()
{ return _RRecordFactory; }
/* constructors */
protected void _init_struct(boolean init)
{ if (init) _struct = new MutableStruct(new Object[4], _sqlType, _factory); }
public RRecord()
{ _init_struct(true); }
public RRecord(java.math.BigDecimal id, String col1, String col2, String col3) throws SQLException
{ _init_struct(true);
setId(id);
setCol1(col1);
setCol2(col2);
setCol3(col3);
}
/* ORAData interface */
public Datum toDatum(Connection c) throws SQLException
{
return _struct.toDatum(c, _SQL_NAME);
}
/* ORADataFactory interface */
public ORAData create(Datum d, int sqlType) throws SQLException
{ return create(null, d, sqlType); }
protected ORAData create(RRecord o, Datum d, int sqlType) throws SQLException
{
if (d == null) return null;
if (o == null) o = new RRecord();
o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
return o;
}
/* accessor methods */
public java.math.BigDecimal getId() throws SQLException
{ return (java.math.BigDecimal) _struct.getAttribute(0); }
public void setId(java.math.BigDecimal id) throws SQLException
{ _struct.setAttribute(0, id); }
public String getCol1() throws SQLException
{ return (String) _struct.getAttribute(1); }
public void setCol1(String col1) throws SQLException
{ _struct.setAttribute(1, col1); }
public String getCol2() throws SQLException
{ return (String) _struct.getAttribute(2); }
public void setCol2(String col2) throws SQLException
{ _struct.setAttribute(2, col2); }
public String getCol3() throws SQLException
{ return (String) _struct.getAttribute(3); }
public void setCol3(String col3) throws SQLException
{ _struct.setAttribute(3, col3); }
} RRecordRef.javaimport java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.REF;
import oracle.sql.STRUCT;
public class RRecordRef implements ORAData, ORADataFactory
{
public static final String _SQL_BASETYPE = "CJUSTICE.R_RECORD";
public static final int _SQL_TYPECODE = OracleTypes.REF;
REF _ref;
private static final RRecordRef _RRecordRefFactory = new RRecordRef();
public static ORADataFactory getORADataFactory()
{ return _RRecordRefFactory; }
/* constructor */
public RRecordRef()
{
}
/* ORAData interface */
public Datum toDatum(Connection c) throws SQLException
{
return _ref;
}
/* ORADataFactory interface */
public ORAData create(Datum d, int sqlType) throws SQLException
{
if (d == null) return null;
RRecordRef r = new RRecordRef();
r._ref = (REF) d;
return r;
}
public static RRecordRef cast(ORAData o) throws SQLException
{
if (o == null) return null;
try { return (RRecordRef) getORADataFactory().create(o.toDatum(null), OracleTypes.REF); }
catch (Exception exn)
{ throw new SQLException("Unable to convert "+o.getClass().getName()+" to RRecordRef: "+exn.toString()); }
}
public RRecord getValue() throws SQLException
{
return (RRecord) RRecord.getORADataFactory().create(
_ref.getSTRUCT(), OracleTypes.REF);
}
public void setValue(RRecord c) throws SQLException
{
_ref.setValue((STRUCT) c.toDatum(_ref.getJavaSqlConnection()));
}
} How do you integrate all that? I'm not really sure, but Marc recently wrote up a piece on using this method to tie into SQLUnit, pretty nice one too. Labels: database, development, java, oracle, sqlunit
Classic: Application Developers vs. Database Developers II
The original (with all the fun comments) can be found here. Originally posted on December 9, 2008. This is the "followup" to yesterday's post.You can read the first article here. My application developer friend, Mr. M, emailed me and another fine gentleman this little blurb recently: Mr. M: OH YEAH BABY!!! TEN TIMES FASTER!!!! YEAH!!!!!!!!
Hey seriously, what a tub of shit Oracle is. Where does this myth come from that it's such a great platform? Their client tools suck balls and it's generally just a pain in the ass to work with from a developer's point of view. But devs for some reason are under this impression that from thew server perspective it's rock solid and performant. Well, it may be solid, but it's a fucking turd. Our dba here - definitely an Oracle guy - has been tasked with looking into moving us onto an oss db. He basically refuses to even look at MySQL, stating that it's a mickey mouse worthless pile of shit (can't really argue with him there lol), so that basically leaves Postgres. So it's been a few weeks now, and he will basically admit now that Postgres completely waxes Oracle as far as performance goes. LOL We run RAC in production too. He's looking at failover, replication, blah blah blah now, we'll see what the verdict on that is. Oh, and Oracle AQ? That's a worthless pile of shit too. Why do they tack a fucking message queue that doesn't play nice with jms onto the fucking database, which is probably already overworked? Oh wait, that's right, they're in the business of selling per cpu licenses! Cocksuckers. This was prompted by a recent Oracle email blast about the Exadata storage system/Warehouse. As I did before, I'll just put the email here. Me: Agreed, their client tools aren't all the great. Which ones are you using?
I use SQL*Plus (naturally), SQL Developer and JDeveloper. The latter 2 tie in very nicely with Subversion. With JDeveloper, when I want to try out Java, it's got a pretty good suite of tools.
Oracle starting out catering to businesses, Microsoft started with the consumer. Oracle has made pretty good strides in making their software more usable while Microsoft has made theirs more scalable.
I haven't used AQ a whole lot and definitely not with Java. I do know that it's billed as JMS compliant.
Postgres has it's place and so does Oracle. It is a great platform if you know how to leverage it. PL/SQL is a 3GL (or is it 4) and you can write entire applications using it...and if you do that right, an incredibly robust and scalable application at that. Mr. M:
"It is a great platform if you know how to leverage it. PL/SQL is a 3GL (or is it 4) and you can write entire applications using it...and if you do that right, an incredibly robust and scalable application at that."
NO!!! NO!!! NOOOOO!!!
I want to beat people like you who say this with a ball pean hammer. There are only a select few individuals on this earth who can write and read application logic written in SQL. AVOID THIS ANTI-PATTERN AT ALL COSTS! What is it with you f_cking database guys??? Why do you always want to stuff as much crap into the db as possible?
DUDE! SQL IS A PILE OF SHIT FOR EXPRESSING APPLICATION LOGIC!!!
It's fine for querying and manipulating sets of data, in a relational database. But it is a worthless sack of shit for expressing application logic!
I'm having to dig through this f_cking abortion right now because some Oracle f_ckhead thought "you can write entire applications using it!" Blog that, mofo!
This was followed by a package he had been working on. I wouldn't say it was the greatest, but it wasn't all bad either. Me: goodness gracious.
"DUDE! SQL IS A PILE OF SHIT FOR EXPRESSING APPLICATION LOGIC!!!"
disagree (naturally). It's incredibly easy to do, you just don't know how yet...and it seems even the Oracle professionals out there don't either.
I'll tell you this, the crazier the SQL or PL/SQL needed to derive and manipulate data the poorer the design. Start with a good design and it all becomes very simple. Of course note the use of "naturally" in my lexicon. Thanks Jake. Mr. M: well dude, we are back to our old discussion - you arguing that procedural sql code is perfectly fine for building apps, and by extension, that the last 20 years of computer science have basically been a misguided lost journey down the meandering, fruitless trail of oop. um.....no. select this from that. otherwise keep that sql crap caged up where it belongs.
btw, do the db guys here suck? seriously. i'm not competent enough to judge. (to be fair, apparently that crap i sent you is fairly old stuff, but still....) Me: I would say, based on limited knowledge of software development, that the OOP movement was started because the database (specifically Oracle) was not mature enough to do what was needed. Plus, I seem to recall that the OOP movement was supposed to have solved all the world's problems by now.
It's further propogated due to the needs you specified in our discussion that day at WellCare (i.e. performance). I still believe that if application developers better understood how a DB works (Oracle, MySQL, SQLServer, etc) they would end up writing less code. Database constraints alone force you to write less (and better) code simultaneously ensuring good data.
The code that I did look at (first 1000 lines or so) isn't great.
1. With all those IF THEN ELSE statements it's telling me that there's probably a better way to store the data. Looks like they're missing an attribute that should be contained with a track.
2. using Object (PL/SQL) types to store data in the way they seem to be doing it is not the best way. Again, probably a design issue.
3. When you do something like this:
UPDATE pb_album_metadata
SET primary_digital_flag = 0
WHERE album_id IN (SELECT b.album_id
FROM (SELECT a.album_id AS album_id,
MAX(a.album_id) OVER (PARTITION BY a.standard_upc) AS latest_album_id
FROM pb_album_metadata a
WHERE a.standard_upc = g_album_tab(1).standard_upc ) b
WHERE b.album_id <> b.latest_album_id )
AND primary_digital_flag <> 0;
They should probably have considered end_date as an attribute of the album metadata. While analytic functions are pretty cool, they're more for analyzing (OLAP) and not OLTP environments.
That's for starters and without table definitions... Me (again): oh yeah...and PL/SQL is/was built on top of ADA, FYI. Mr. M: "I still believe that if application developers better understood how a DB works (Oracle, MySQL, SQLServer, etc) they would end up writing less code. Database constraints alone force you to write less (and better) code simultaneously ensuring good data."
Huh? What are we not understanding? What would be an example of a constraint that would force us to write less and better code? Me: CHECK, NOT NULL (same as CHECK) and FOREIGN KEY constraints all fit into that category.
Instead of having to check if a value is NULL when submitting it to the database, just submit and let the database throw an error, which you'll conveniently capture in your Exception block. Same goes for CHECK constraints, columnA should be either one of three values (test, test1, test3), you won't have to check that in your application code, just catch the exception. FKs insure good data (proper relationships).
A different perspective. If you are going to pay for it, use that damn thing. If you don't care, don't do it. But over the course of an "enterprisey" application, you'll end up writing more code and make maintenance that much more difficult (did I do it there?). Just those constraints will force you and the teams of application developers to put good data into the database.
You can still do it in the application of course (form validation is a good place)... Mr. M: Ahh, jeez dude, I wasn't sure if you were referring to the literal "constraint" or not.
Dude, even f_cksticks like redacted I think have a decent enough understanding of when and how to use db constraints. It's when you get into things like cursors or cost plans of subselects and anonymous tables (i think that's the name for it - where I say select something from (select some other crap). Then we defer to db gurus like yourself.
But dude....."you won't have to check that in your application code, just catch the exception".......uh, don't ever repeat that off our little email group. And definitely don't go posting that on your blog. F_ck me man, it's a damn good thing we keep you db folks caged up in that rdbms box.... Me: So we've had this discussion at work...for a high transaction system, do Java/C/etc handle exceptions well or what?
Why is it bad to deal with exceptions rather than coding to avoid them?
I highly doubt even redacted understood database constraints...him and his cohorts believed all database software would be commoditized and MySQL would be king in short order. Mr. M: "for a high transaction system"
Or for any system really....
To use your example of check constraints (is this value one of....) or not null constraints, checking these rules in the Java code and in the database code would seem to violate DRY. But we do that alot, and it is acceptable in certain cases. For instance, we also probably violate DRY if we're checking this same rule say in Javascript on the front end. But we accept this tiny violation of DRY because it tends to make for a better user experience and as a performance gain too, for we avoid the round trip to the server. Now, what your advocating here is close to the same thing. You're basically saying, don't check for the not null constraint in Java code, just go ahead and hit the database, let the database throw back an exception to the Java code, presumably correct the problem, and then make another roundtrip to the database again. Dude, what are you thinking?!? This to say nothing of the fact that this also could be considered a violation of Fail Fast, and a violation of Defensive Programming - what happens if the dba forgot to add the not null constraint in production?
Dude, listen to this guy. For a "high transaction system" basically you want to treat the database, as much as you can, as just a dumb data holder. A f_cking dumpster, that you just throw sh_t into and pull shit back out, using no vendor-specific features if at all possible.
Again, for we've had this discussion, but even in everyday apps, not just on Wall Street, the database is the bottleneck. And the database is the hardest layer to scale. So given those facts, you should design your app to do as little work in the database as possible. I was laughing at this point because the link above points to one of our consulting architects (I'm not really sure what his role is at this point). Me: i agree in any application that you want to minimize the number of round trips...
shocker...he's one of our architects. he's spot on in many instances, but...
database is the bottleneck because people don't know how to write SQL. I'll certainly concede the wall street applications (for the time being anyway), but the rest of us with what we do on a daily basis...Oracle will scale far beyond the demands they place. When that bottleneck shows up, 9 times out of 10 some dumb-ass c#/java guy thought he could write a better query than i. besides, what's the idiot doing anything but a simple join anyway? probably poor data model to start with...and we're right back where we started (sort of). Mr. M: "database is the bottleneck because people don't know how to write SQL.....some dumb-ass c#/java guy thought he could write a better query than i."
Dude, I'll grant you, people don't know how to write SQL, myself included. But that's not always why the database is the bottleneck. I think it's safe to say that's not even the majority of the reason. Yes, there are apps written by people who were just idiots, and they needlessly pummel the database, but that's definitely not the majority of scenarios. At my work the database is the bottleneck, and we run RAC in production. It's my understanding that even with RAC, there is a limit to how much you can scale that out. But any layer up from the database we are basically unlimited in how much we can scale that out. So it's very easy to stick another Apache box in front, or fire up another Weblogic server. But I can't do that with the database. We have 24 Weblogic servers for redacted. The database is the bottleneck. And we don't have shitty sql code in the app. In fact, we have very few hand-written queries anywhere in the app. Persisting something to a database is really a low-level concern that as an application developer I shouldn't even have to be bothered with, except for the rare corner cases where the persistence abstraction I'm using is just a little bit too generic to handle things effectively. And we don't use these ORMs because we don't know how to write sql. To effectively use an ORM tool requires a deeper understanding of sql and databases than simply being able to write solid SQL code. (Not saying Java devs who use ORMs know SQL better than a dba, just that it requires better sql skills than is required of a Java dev to simply write JDBC/SQL.) Now, before you try to tell me that my ORM library isn't able to write as efficient of sql code as a dba of your caliber, keep in mind that ORM tools are pretty advanced. They're able to intelligently do things like batch sql updates, and let you apply transactional semantics much easier than with raw jdbc/sql. But the overwhelming reason developers have so thoroughly adopted ORM is because Structured Query Language is such a nasty piece of shit for expressing application logic. SQL is a declarative, procedural language. It's totally unsuited for writing application logic! This, more than anything else, is why organizations and dev teams should seek to restrict what is handled within a relational database as much as possible - because the programming interface to it is a fucking ancient backward dying dinosaur. Mr. V (note, not Mr. M): My 2 canadian cents:
The polyglot approach "... use different languages for different domain ..."
Database was developed to manipulate data and should remain there.
General purpose language was developed to encapsulate logic and should remain in that domain.
You should not use DB to encapsulate business logic (in my opinion) no more than you would use HTML to create complex logic.
While Java, C#, Python, etc are described as General Purpose languages, they, too, are really domain-constrained. Their expressiveness are confined (and should be) to express low-level, tersed, explicit, verbose, and repetive logic (if that makes any sense). Languages such as these are more suitable for low-level abstraction on top of which, richer, more expressive languages can be built. We are seeing this now with the emergence of languages on the JVM (and arguably on the .Net's CLR).
I think SQL as we know will take a back seat and a smart developer somewhere will create a new domain-specific language that lives on the VM and will push the SQL expression out of the RDBMS and closer to the code that needs it. We are not there yet, but Microsfot is trying (see LINQ and all ORM papers). This is not to say that there won't be isntances where tuning in the SQL-native language won't be necessary. However, 80 to 90% of simple CRUD cases will be handled closer to the logic code that uses the data code.
Again, that's my 2 canadian cents... I could go on. But I have a meeting with redacted. Mr. V, I believe, is a little bit more sensible. Mr. M on the other hand is just trying to rile (sp?) me up. Me: Someone will probably create something like that, but it still gets at the heart of one of my arguments, many developers don't know how to use a database thus will go to any means to circumvent it. Embrace it I say.
Ultimately for me, it comes down to simplicity. I can write an application using PL/SQL that will scale very well for 90% of the solutions. Of course that doesn't include the "fancy" javascript/Ajax stuff needed for the UI. In my ever so humble opinion, this is a much better solution for a business in the long run.
1. You're more likely to change the middle tier than the database tier. Java, asp, Ruby, what's next?
2. Fewer moving parts means lower cost of ownership, even with the CPU costs. One person, one, was able to build and maintain a 350 page website. Be hardpressed to do that with the more expressive languages.
I think all of us are purists and very passionate about what we do. It's probably the main reason we get along. I thoroughly enjoy these conversations because it does force me to think...and that's always a good thing. Mr. V: Haaa chet.
You sorta made my point than diverged away. Building an app in all PL/SQL is dangerous. It's no safer than me building an app in all Java. I can build very limited app in all Java. The moment I need to interact with other domain (UI, data, low-level native code, etc), I have to switch to something that is closer to what I am trying to do. If I need to create UI, I will pick a ui-centric environment, when I need to talk to DB, I will pass connect to a db and send in my SQL, and so forth. I will use Java as the glue to bring it all togher.
In the end, we may be saying the same thing, but using different accent. O well. And that's where it ended. I must say it's always fun. Mr. M and Mr. V are both very smart individuals and I highly respect what they do. We have different perspectives...but I think they listen, if only a little, as I listen to them. Their voices creep up on me especially now...which is definitely a good thing. Labels: design, development, funny, humility, ideas, java, plsql
Developing a J2EE based SOA Architecture
No, I haven't gone to the dark side. I just figured it would be best to know my enemy. ;) Tonight I went to the Tampa JUG (no, it's not a code word for the local strip joint so stop laughing), or, the Tampa Java Users Group. The guy that runs is the the not so infamous Mr. V, from Application Developers vs. Database Developers Part II. I also got to meet/see some former WellCare colleagues as well (hi everyone!). The presentation was done by Kevin Barfield from JBoss. Interesting tool which incorporates (I think I'll get this right) the JBoss server, BPM and just about anything else you can imagine. It also included an overview of the Enterprise Service Bus (ESB). That's also a new file type that you can load (as opposed to .ear, .war, etc). Overall pretty cool stuff, but I really can't put it into context. I'd have to monkey around with it for awhile before I could truly understand what to do with it. Anyway, that was my trip on the dark side. Not a whole lot of fun but an opportunity to expand my horizons. Labels: java, work
Application Developers vs. Database Developers: Part II
You can read the first article here. My application developer friend, Mr. M, emailed me and another fine gentleman this little blurb recently: Mr. M: OH YEAH BABY!!! TEN TIMES FASTER!!!! YEAH!!!!!!!!
Hey seriously, what a tub of shit Oracle is. Where does this myth come from that it's such a great platform? Their client tools suck balls and it's generally just a pain in the ass to work with from a developer's point of view. But devs for some reason are under this impression that from thew server perspective it's rock solid and performant. Well, it may be solid, but it's a fucking turd. Our dba here - definitely an Oracle guy - has been tasked with looking into moving us onto an oss db. He basically refuses to even look at MySQL, stating that it's a mickey mouse worthless pile of shit (can't really argue with him there lol), so that basically leaves Postgres. So it's been a few weeks now, and he will basically admit now that Postgres completely waxes Oracle as far as performance goes. LOL We run RAC in production too. He's looking at failover, replication, blah blah blah now, we'll see what the verdict on that is. Oh, and Oracle AQ? That's a worthless pile of shit too. Why do they tack a fucking message queue that doesn't play nice with jms onto the fucking database, which is probably already overworked? Oh wait, that's right, they're in the business of selling per cpu licenses! Cocksuckers. This was prompted by a recent Oracle email blast about the Exadata storage system/Warehouse. As I did before, I'll just put the email here. Me: Agreed, their client tools aren't all the great. Which ones are you using?
I use SQL*Plus (naturally), SQL Developer and JDeveloper. The latter 2 tie in very nicely with Subversion. With JDeveloper, when I want to try out Java, it's got a pretty good suite of tools.
Oracle starting out catering to businesses, Microsoft started with the consumer. Oracle has made pretty good strides in making their software more usable while Microsoft has made theirs more scalable.
I haven't used AQ a whole lot and definitely not with Java. I do know that it's billed as JMS compliant.
Postgres has it's place and so does Oracle. It is a great platform if you know how to leverage it. PL/SQL is a 3GL (or is it 4) and you can write entire applications using it...and if you do that right, an incredibly robust and scalable application at that. Mr. M:
"It is a great platform if you know how to leverage it. PL/SQL is a 3GL (or is it 4) and you can write entire applications using it...and if you do that right, an incredibly robust and scalable application at that."
NO!!! NO!!! NOOOOO!!!
I want to beat people like you who say this with a ball pean hammer. There are only a select few individuals on this earth who can write and read application logic written in SQL. AVOID THIS ANTI-PATTERN AT ALL COSTS! What is it with you f_cking database guys??? Why do you always want to stuff as much crap into the db as possible?
DUDE! SQL IS A PILE OF SHIT FOR EXPRESSING APPLICATION LOGIC!!!
It's fine for querying and manipulating sets of data, in a relational database. But it is a worthless sack of shit for expressing application logic!
I'm having to dig through this f_cking abortion right now because some Oracle f_ckhead thought "you can write entire applications using it!" Blog that, mofo!
This was followed by a package he had been working on. I wouldn't say it was the greatest, but it wasn't all bad either. Me: goodness gracious.
"DUDE! SQL IS A PILE OF SHIT FOR EXPRESSING APPLICATION LOGIC!!!"
disagree (naturally). It's incredibly easy to do, you just don't know how yet...and it seems even the Oracle professionals out there don't either.
I'll tell you this, the crazier the SQL or PL/SQL needed to derive and manipulate data the poorer the design. Start with a good design and it all becomes very simple. Of course note the use of "naturally" in my lexicon. Thanks Jake. Mr. M: well dude, we are back to our old discussion - you arguing that procedural sql code is perfectly fine for building apps, and by extension, that the last 20 years of computer science have basically been a misguided lost journey down the meandering, fruitless trail of oop. um.....no. select this from that. otherwise keep that sql crap caged up where it belongs.
btw, do the db guys here suck? seriously. i'm not competent enough to judge. (to be fair, apparently that crap i sent you is fairly old stuff, but still....) Me: I would say, based on limited knowledge of software development, that the OOP movement was started because the database (specifically Oracle) was not mature enough to do what was needed. Plus, I seem to recall that the OOP movement was supposed to have solved all the world's problems by now.
It's further propogated due to the needs you specified in our discussion that day at WellCare (i.e. performance). I still believe that if application developers better understood how a DB works (Oracle, MySQL, SQLServer, etc) they would end up writing less code. Database constraints alone force you to write less (and better) code simultaneously ensuring good data.
The code that I did look at (first 1000 lines or so) isn't great.
1. With all those IF THEN ELSE statements it's telling me that there's probably a better way to store the data. Looks like they're missing an attribute that should be contained with a track.
2. using Object (PL/SQL) types to store data in the way they seem to be doing it is not the best way. Again, probably a design issue.
3. When you do something like this:
UPDATE pb_album_metadata
SET primary_digital_flag = 0
WHERE album_id IN (SELECT b.album_id
FROM (SELECT a.album_id AS album_id,
MAX(a.album_id) OVER (PARTITION BY a.standard_upc) AS latest_album_id
FROM pb_album_metadata a
WHERE a.standard_upc = g_album_tab(1).standard_upc ) b
WHERE b.album_id <> b.latest_album_id )
AND primary_digital_flag <> 0;
They should probably have considered end_date as an attribute of the album metadata. While analytic functions are pretty cool, they're more for analyzing (OLAP) and not OLTP environments.
That's for starters and without table definitions... Me (again): oh yeah...and PL/SQL is/was built on top of ADA, FYI. Mr. M: "I still believe that if application developers better understood how a DB works (Oracle, MySQL, SQLServer, etc) they would end up writing less code. Database constraints alone force you to write less (and better) code simultaneously ensuring good data."
Huh? What are we not understanding? What would be an example of a constraint that would force us to write less and better code? Me: CHECK, NOT NULL (same as CHECK) and FOREIGN KEY constraints all fit into that category.
Instead of having to check if a value is NULL when submitting it to the database, just submit and let the database throw an error, which you'll conveniently capture in your Exception block. Same goes for CHECK constraints, columnA should be either one of three values (test, test1, test3), you won't have to check that in your application code, just catch the exception. FKs insure good data (proper relationships).
A different perspective. If you are going to pay for it, use that damn thing. If you don't care, don't do it. But over the course of an "enterprisey" application, you'll end up writing more code and make maintenance that much more difficult (did I do it there?). Just those constraints will force you and the teams of application developers to put good data into the database.
You can still do it in the application of course (form validation is a good place)... Mr. M: Ahh, jeez dude, I wasn't sure if you were referring to the literal "constraint" or not.
Dude, even f_cksticks like redacted I think have a decent enough understanding of when and how to use db constraints. It's when you get into things like cursors or cost plans of subselects and anonymous tables (i think that's the name for it - where I say select something from (select some other crap). Then we defer to db gurus like yourself.
But dude....."you won't have to check that in your application code, just catch the exception".......uh, don't ever repeat that off our little email group. And definitely don't go posting that on your blog. F_ck me man, it's a damn good thing we keep you db folks caged up in that rdbms box.... Me: So we've had this discussion at work...for a high transaction system, do Java/C/etc handle exceptions well or what?
Why is it bad to deal with exceptions rather than coding to avoid them?
I highly doubt even redacted understood database constraints...him and his cohorts believed all database software would be commoditized and MySQL would be king in short order. Mr. M: "for a high transaction system"
Or for any system really....
To use your example of check constraints (is this value one of....) or not null constraints, checking these rules in the Java code and in the database code would seem to violate DRY. But we do that alot, and it is acceptable in certain cases. For instance, we also probably violate DRY if we're checking this same rule say in Javascript on the front end. But we accept this tiny violation of DRY because it tends to make for a better user experience and as a performance gain too, for we avoid the round trip to the server. Now, what your advocating here is close to the same thing. You're basically saying, don't check for the not null constraint in Java code, just go ahead and hit the database, let the database throw back an exception to the Java code, presumably correct the problem, and then make another roundtrip to the database again. Dude, what are you thinking?!? This to say nothing of the fact that this also could be considered a violation of Fail Fast, and a violation of Defensive Programming - what happens if the dba forgot to add the not null constraint in production?
Dude, listen to this guy. For a "high transaction system" basically you want to treat the database, as much as you can, as just a dumb data holder. A f_cking dumpster, that you just throw sh_t into and pull shit back out, using no vendor-specific features if at all possible.
Again, for we've had this discussion, but even in everyday apps, not just on Wall Street, the database is the bottleneck. And the database is the hardest layer to scale. So given those facts, you should design your app to do as little work in the database as possible. I was laughing at this point because the link above points to one of our consulting architects (I'm not really sure what his role is at this point). Me: i agree in any application that you want to minimize the number of round trips...
shocker...he's one of our architects. he's spot on in many instances, but...
database is the bottleneck because people don't know how to write SQL. I'll certainly concede the wall street applications (for the time being anyway), but the rest of us with what we do on a daily basis...Oracle will scale far beyond the demands they place. When that bottleneck shows up, 9 times out of 10 some dumb-ass c#/java guy thought he could write a better query than i. besides, what's the idiot doing anything but a simple join anyway? probably poor data model to start with...and we're right back where we started (sort of). Mr. M: "database is the bottleneck because people don't know how to write SQL.....some dumb-ass c#/java guy thought he could write a better query than i."
Dude, I'll grant you, people don't know how to write SQL, myself included. But that's not always why the database is the bottleneck. I think it's safe to say that's not even the majority of the reason. Yes, there are apps written by people who were just idiots, and they needlessly pummel the database, but that's definitely not the majority of scenarios. At my work the database is the bottleneck, and we run RAC in production. It's my understanding that even with RAC, there is a limit to how much you can scale that out. But any layer up from the database we are basically unlimited in how much we can scale that out. So it's very easy to stick another Apache box in front, or fire up another Weblogic server. But I can't do that with the database. We have 24 Weblogic servers for redacted. The database is the bottleneck. And we don't have shitty sql code in the app. In fact, we have very few hand-written queries anywhere in the app. Persisting something to a database is really a low-level concern that as an application developer I shouldn't even have to be bothered with, except for the rare corner cases where the persistence abstraction I'm using is just a little bit too generic to handle things effectively. And we don't use these ORMs because we don't know how to write sql. To effectively use an ORM tool requires a deeper understanding of sql and databases than simply being able to write solid SQL code. (Not saying Java devs who use ORMs know SQL better than a dba, just that it requires better sql skills than is required of a Java dev to simply write JDBC/SQL.) Now, before you try to tell me that my ORM library isn't able to write as efficient of sql code as a dba of your caliber, keep in mind that ORM tools are pretty advanced. They're able to intelligently do things like batch sql updates, and let you apply transactional semantics much easier than with raw jdbc/sql. But the overwhelming reason developers have so thoroughly adopted ORM is because Structured Query Language is such a nasty piece of shit for expressing application logic. SQL is a declarative, procedural language. It's totally unsuited for writing application logic! This, more than anything else, is why organizations and dev teams should seek to restrict what is handled within a relational database as much as possible - because the programming interface to it is a fucking ancient backward dying dinosaur. Mr. V (note, not Mr. M): My 2 canadian cents:
The polyglot approach "... use different languages for different domain ..."
Database was developed to manipulate data and should remain there.
General purpose language was developed to encapsulate logic and should remain in that domain.
You should not use DB to encapsulate business logic (in my opinion) no more than you would use HTML to create complex logic.
While Java, C#, Python, etc are described as General Purpose languages, they, too, are really domain-constrained. Their expressiveness are confined (and should be) to express low-level, tersed, explicit, verbose, and repetive logic (if that makes any sense). Languages such as these are more suitable for low-level abstraction on top of which, richer, more expressive languages can be built. We are seeing this now with the emergence of languages on the JVM (and arguably on the .Net's CLR).
I think SQL as we know will take a back seat and a smart developer somewhere will create a new domain-specific language that lives on the VM and will push the SQL expression out of the RDBMS and closer to the code that needs it. We are not there yet, but Microsfot is trying (see LINQ and all ORM papers). This is not to say that there won't be isntances where tuning in the SQL-native language won't be necessary. However, 80 to 90% of simple CRUD cases will be handled closer to the logic code that uses the data code.
Again, that's my 2 canadian cents... I could go on. But I have a meeting with redacted. Mr. V, I believe, is a little bit more sensible. Mr. M on the other hand is just trying to rile (sp?) me up. Me: Someone will probably create something like that, but it still gets at the heart of one of my arguments, many developers don't know how to use a database thus will go to any means to circumvent it. Embrace it I say.
Ultimately for me, it comes down to simplicity. I can write an application using PL/SQL that will scale very well for 90% of the solutions. Of course that doesn't include the "fancy" javascript/Ajax stuff needed for the UI. In my ever so humble opinion, this is a much better solution for a business in the long run.
1. You're more likely to change the middle tier than the database tier. Java, asp, Ruby, what's next?
2. Fewer moving parts means lower cost of ownership, even with the CPU costs. One person, one, was able to build and maintain a 350 page website. Be hardpressed to do that with the more expressive languages.
I think all of us are purists and very passionate about what we do. It's probably the main reason we get along. I thoroughly enjoy these conversations because it does force me to think...and that's always a good thing. Mr. V: Haaa chet.
You sorta made my point than diverged away. Building an app in all PL/SQL is dangerous. It's no safer than me building an app in all Java. I can build very limited app in all Java. The moment I need to interact with other domain (UI, data, low-level native code, etc), I have to switch to something that is closer to what I am trying to do. If I need to create UI, I will pick a ui-centric environment, when I need to talk to DB, I will pass connect to a db and send in my SQL, and so forth. I will use Java as the glue to bring it all togher.
In the end, we may be saying the same thing, but using different accent. O well. And that's where it ended. I must say it's always fun. Mr. M and Mr. V are both very smart individuals and I highly respect what they do. We have different perspectives...but I think they listen, if only a little, as I listen to them. Their voices creep up on me especially now...which is definitely a good thing. Labels: design, development, funny, humility, ideas, java, plsql
A Java/PL/SQL Port Scanner II
In a post last week, I detailed my efforts in trying to use Java and the UTL_TCP to determine open ports on my DVR. From that I learned a valuable lesson in infinite loops and also that you could DELETE from dba_jobs. Since I was too lazy to diagnose the TNS errors I was receiving from using UTL_TCP and I knew that the Java class was working, I decided to combine the 2 approaches on Friday night. There are 5 cable boxes in the house which means 5 IPs to check. Looking more closely at the MAC addresses, I realize 4 are the same (or roughly the same). That led me to believe that the 5th was the DVR. But I'm going to check them all for fun. Start with the table to capture those open ports: CREATE TABLE open_ports
(
ip VARCHAR2(20),
port NUMBER(5,0)
); Then I take the java class (with the Main procedure) and convert it into a standard Java class that can be loaded into the database: CREATE OR REPLACE AND COMPILE
JAVA SOURCE NAMED "CheckPorts"
AS
import java.io.IOException;
import java.net.Socket;
import java.net.UnknownHostException;
public class CheckPorts
{
public static String CheckPorts( String ip, String port ) throws UnknownHostException, IOException
{
try
{
Socket socket = new Socket( ip, Integer.parseInt( port ) );
return "Y";
}
catch ( UnknownHostException e ){ return "N"; }
catch ( IOException e ) { return "N"; }
finally { }
}
}
/ Then I create a PL/SQL wrapper around the Java class: CREATE OR REPLACE
FUNCTION check_port( p_ip IN VARCHAR2, p_port IN VARCHAR2 ) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'CheckPorts.CheckPorts( java.lang.String, java.lang.String ) return String';
/
show errors Then I wrap that call up in a standalone procedure (remember, this is just for fun). CREATE OR REPLACE
PROCEDURE check_port_insert
( p_ip IN VARCHAR2,
p_port IN INTEGER )
IS
l_port VARCHAR2(1);
BEGIN
l_port := check_port( p_ip, TO_CHAR( p_port ) );
IF l_port = 'Y' THEN
INSERT INTO open_ports( ip, port )
VALUES( p_ip, p_port );
END IF;
EXCEPTION
WHEN others THEN
NULL;
END check_port_insert;
/ No, I didn't re-raise after the WHEN OTHERS, had I been doing this seriously, I would have trapped it or better yet, would have caught the specific exception. Finally my anonymous block to call check_port_insert and submit as a job (for threading). I picked 30 processes at a time so as not to overload my laptop...on the first go anyway. DECLARE
TYPE r_record IS TABLE OF OPEN_PORTS.IP%TYPE;
l_table R_RECORD := R_RECORD();
job_count PLS_INTEGER;
job_number PLS_INTEGER;
BEGIN
l_table.EXTEND;
l_table(1) := '192.168.1.102';
l_table.EXTEND;
l_table(2) := '192.168.1.100';
l_table.EXTEND;
l_table(3) := '192.168.1.101';
l_table.EXTEND;
l_table(4) := '192.168.1.103';
l_table.EXTEND;
l_table(5) := '192.168.1.104';
<>
FOR i IN 1..l_table.COUNT LOOP
<>
FOR j IN 1..10000 LOOP
SELECT COUNT(*)
INTO job_count
FROM user_jobs;
IF job_count < 30 THEN
dbms_job.submit
( job => job_number,
what => 'BEGIN ' ||
' check_port_insert( ''' || l_table(i) || ''',' || j || '); ' ||
' COMMIT; ' ||
'END;',
next_date => SYSDATE );
COMMIT;
ELSE
dbms_lock.sleep( 5 );
END IF;
END LOOP;
END LOOP;
END;
/ A table of IP addresses to check each of the 5 known IPs. I used 10,000 as the number of ports to check (I think it goes higher...again, just being a lot lazy). Then a check against USER_JOBS to see if there were 30 jobs or not, if so, wait 5 seconds (DBMS_LOCK.SLEEP) otherwise submit another job. I probably could have sped things up and not brought down my system by submitting 100 jobs...I'll probably try that tonight. So here are the results: PL/SQL procedure successfully completed.
Elapsed: 02:33:18.98
CJUSTICE@ELEVEN>SELECT * FROM open_ports;
IP PORT
-------------------- ----------
192.168.1.102 7501
192.168.1.102 8082
2 rows selected. 50K ports in 2 hours and 33 minutes. Is that good? I never let the free port scanner I downloaded go this far so I have no idea. 2 ports open out of 50K. Not a lot. Then I used Telnet to connect to those ports: Microsoft Telnet> o 192.168.1.102 7501
Connecting To 192.168.1.102... No go. How about 8082? Microsoft Telnet> o 192.168.1.102 8082
Connecting To 192.168.1.102...
HTTP/1.1 200 OK
Server: gSOAP/2.7
Content-Type: text/xml; charset=utf-8
Content-Length: 0
Connection: close
Connection to host lost.
Press any key to continue... OK, so that's probably the port used to pull down the schedule/user guide. Anyway, I got to try something new and learn a little bit about ports/sockets. I'm not done yet. Next I need to figure out how many possible ports there are...well, this tells me. So I guess I'll have to expand my search. At least now I can concentrate on the DVR (.102) but I still have to check out about 50K ports it looks like. Labels: java, utl_tcp
Debugging ApEx with FOP
I've been helping a friend out, Daniel McGhan, with an ApEx application he's created. Now by "friend" I mean he's the annoying heckler from my very first ApEx presentation. We also co-presented in September. Now when I say "co-presented" I mean he did everything. I was completely unprepared (and unemployed at the time). Anyway, he's off galavanting in Europe right now and I offered to help support his application is his absence (slacker). Of course he leaves me with a whopper of a problem...the PDFs won't generate correctly. I've been out of the daily ApEx world for going on 2 years now. I try to keep up by reading blogs and such, but there's no replacement for doing it every day. So we've got the latest version of ApEx (3.1.2) running on Oracle XE (dev environment) to be deployed on a 10.2.0.3 Enterprise Edition Oracle Server running on Windows. At least I understand Windows. Dan is using FOP to render the PDFs. I have no idea what it means, only that it is Java based. Do I put it in the database? No. It runs from the OC4J server. OK, I have a little experience with that...but it was mostly command line, at some point they (being Oracle) finally made a nice and pretty front end (where the hell was that before?). Back to ApEx. Interactive Reports. Check the box for PDFs (I'm skipping the whole setting it up through the administrative interface on purpose, but rest-assured, it was set up). I open the page, click on the Interactive Reports thingy, select PDF and Open. Invalid File Type. What? Tried saving it to file, same error. Maybe this latest greatest Adobe Reader (9 something) can't read...but their latest greatest should be able to read any pdf created by any version right? I'll just open up the file in Notepad. Big clue...right at the top: ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1186
ORA-12570: TNS:packet reader failure Awesome! It's an Oracle error...I can fix that (hopefully). I went through the forums which let me here. Didn't really help me much. But I did realize the OC4J server wasn't running. Barnacles. Started it through the provided .bat file. Go through the steps again, still invalid. But I have a new error in the file: 500 Internal Server Error
OracleJSP:
An error occurred. Consult your application/system administrator for support.
Programmers should consider setting the init-param debug_mode to "true" to see the
complete exception message. Closer. This is where my old command line skills came in handy. .bat file looks like this: set ORACLE_HOME=C:\OAS\oc4j_101340
set JAVA_HOME=C:\Program Files\Java\jre1.6.0_07\
cd C:\OAS\oc4j_101340\bin
oc4j -start I know where the oc4j file is now, let's start it from the command line: c:\java -jar c:\oas\oc4j_101340\j2ee\home\oc4j.jar
08/11/19 21:57:01 Oracle Containers for J2EE 10g (10.1.3.4.0) initialized Run the PDF again, check console to see what happened. An error! Some sort of jsp exception which of course I don't have and can't repeat now. So I add -Dbuild.debug=true to the command. Right in the middle of it I see !!!!!cannot find javac compiler!!!!!! If ever a clue there was. Which java is it using? The JRE? Of course there's no compiler, that's a runtime environment. Where's JDeveloper? It has a compiler. c:\jdeveloper\jdk Plug it in a voila! It works. So apparently it has to be compiled on first use (it's been awhile since I've used J2EE), and that's the reason I can't (or won't) recreate the problem. A small lesson learned in...something. Labels: apex, debug, java
Fraud Analysis?
So we are pondering how to go about fraud analysis real time. One thing currently holding us back is that all the key fields are encrypted (obviously). How do you go about doing fraud analysis with such seemingly high overhead? My question to you is, do you know of any fraud related off-the-shelf tools or libraries out there? Preferably written in PL/SQL, but Java or C will do as well. Labels: java, oracle, plsql
Validating a Process Part II
Continued from my previous post. While discussing external tables with my feisty colleague some time back, I explained that I liked using them but I couldn't figure out how to change the file name to match that of what was defined in the table definition. Colleage to the rescue:
ALTER TABLE table_name LOCATION ( 'new_file_name.csv' );
Cool! As I mentioned before, I had 4 files types I had to read: 820 and 835, both of the x12 format and two custom file layouts (flat files essentially). Since no one in the group knows Java yet, I wanted to keep the Java portion of the application as small as possible. So with the two custom files, I decided to use external tables. I could then put into practice the above ALTER TABLE statement. As I looped through the list of files to be processed, I would issue an EXECUTE IMMEDIATE so that I could then SELECT from the table in the next step. It worked like a charm. As I was doing some testing, I would issue the ROLLBACK statement to clear the tables for the next run. When I verified, there was still data there. WTF? Oh wait, there's an EXECUTE IMMEDIATE...which runs DDL...which COMMITs...barnacles! So I couldn't use that new thing I learned, oh well. Fortunately UTL_FILE does have the ability to rename files so I picked a name like 'external_table_file_name.txt' and rename the incoming file to that, then SELECT. Works like a charm. Labels: ddl, java, sql, utl_file
Validating a Process
I mentioned sometime back that I would be posting the code. I can't post the entire set of code but I can post the relevant parts. ProblemInbound Remittance Advice files are loaded into our Operational Data Store (ODS). We have absolutely no control over this, it lies with another group. On occasion, those files are double, or even triple, loaded. GoalTo provide the business (and ourselves) with a way to track when files came in and if the entire processed worked as expected (no lost dollars, no lost record counts). What do we do?1. Read the files when they first come in (on disk). 2. Query the appropriate tables at certain intervals to verify it matches the file amounts. 3. Load those results into a table and then fail the normal load process if we detect any incongruities. Solution (proposed)1. Create a directory object on the Oracle server. 2. Copy inbound files to that directory. 3. Read files from that directory (Java anyone?) 4. Load files into CLOBs (so that I don't have to spend half my day finding the damn things, simple APEX app and I'm good to go). 5. Parse files to find relevant information (Java) 6. Query tables at various stages, blah blah blah. SolutionSince the UTL_FILE doesn't have a function to read the contents of a directory, Java comes into play. I've done it before and I found the code originally on asktom (where else?). For those of you too lazy (like me) to click the link, here's the important stuff: snip...
File file = new File( path );
list = file.list();
for ( int i = 0; i < list.length; i++ )
{
File indvidualFile = new File( path + list[i] );
if ( indvidualFile.isFile() )
{
element = list[i];
statement.setString( 1, element );
}
}
snip...
Since I'm using Java, I might as well use the StringTokenizer, makes like so much easier. But wait, since I'm reading it as a CLOB (and not a String), what do I do? I tried clob.toString(). Nope, it's just a pointer to the actual CLOB. I have to use Reader and CharacterStream, getting well beyond my knowledge of Java. With the help of a fellow Java developer, I was pointed towards StreamTokenizer which works in a similar fashion to StringTokenizer, or so I thought. Apparently StreamTokenizer is one of the lower level classes...so I had to figure out the ASCII values of the character I wanted to split on (a tilde: ~). I think my Java friend was surprised I figured this out...
Reader characterStream = clob.getCharacterStream();
StreamTokenizer stream = new StreamTokenizer( characterStream );
stream.resetSyntax();
stream.wordChars( 32, 125 );
stream.parseNumbers();
Fun. After I got that as a String, I could then use the StringTokenizer, which I knew. Could I have split the CLOB using PL/SQL? Yes. Did I want to? Not really. I was already using Java so why not just use the Tokenizer? The five classes were jarred and loaded into Oracle via the loadjava command. Wrap it all up in PL/SQL and life is easy!
PROCEDURE get_directory_contents( i_directory IN VARCHAR2 )
AS
LANGUAGE JAVA NAME 'com.hmocompany.dw.LoadFileNames.getFileNames( java.lang.String )';
Labels: java, oracle, work
EDI Fun
or Electronic Data Interchange, just a fancy phrase for sending and receiving files. We (IT) do love to complicate things don't we? I've put the change data capture stuff on hold as my never-ending project goes into it's 12th week past deadline. It's at nine 9s: 99.9999999. It's finance related stuff and nothing less than 100% is acceptable. I'm tired. Part of my project was to move from an already built in house table to the raw (files) tables. My feisty colleague took on that fun challenge for 6 months or so. He's heading up a new project though so he's had to pass the baton on to me. I've accepted it...reluctantly. ;) Anyway, we store these inbound files all over the place (seemingly to me). I started writing a little Java application that would scan a directory, read these x12-820 files and tell me the interchange date, control number, total amount and some other useful information. I plan on either putting this in the database and wrapping it up in PL/SQL or creating a service ( Java Service Wrapper) and pushing this useful data to a table. So if you have to deal with the wonderful x12/820 formats, you may want to check back soon for the code. I can't promise it will be good, but it will work! Labels: java, plsql, tools, utilities, work
|
August 2007 /
September 2007 /
October 2007 /
November 2007 /
December 2007 /
January 2008 /
February 2008 /
March 2008 /
April 2008 /
May 2008 /
June 2008 /
July 2008 /
August 2008 /
September 2008 /
October 2008 /
November 2008 /
December 2008 /
January 2009 /
February 2009 /
March 2009 /
April 2009 /
May 2009 /
June 2009 /
July 2009 /
August 2009 /
September 2009 /
October 2009 /
November 2009 /
December 2009 /
January 2010 /
February 2010 /
March 2010 /
|
