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: edi, java, plsql, tools, utilities, work
Instrumentation: DEBUG/LOGGING
In my
previous entry on instrumenting code I detailed the way in which you could use the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure to help monitor your long running code.
In this one, I will detail my home grown version of
Tom Kyte's debug routine. I do know that others have similar code but can't seem to find them right now.
You can find the source code
here.
Contents:
2 tables
2 sequences
1 package
1 procedure
1 build file
debug_tab allows you to turn the debugging on and off. debug_details_tab will store each line that you write to the debug routine when turned on.
Here's an example of it in practice:
CREATE OR REPLACE
FUNCTION get_age_in_months( p_id IN NUMBER ) RETURN NUMBER
IS
l_age_in_months INTEGER;
BEGIN
--instrumentation calls
debug( 'GET_AGE_IN_MONTHS' );
debug( 'P_ID: ' || p_id );
debug( 'select value into variable' );
SELECT age_in_months
INTO l_age_in_months
FROM people_tab
WHERE id = p_id;
debug( 'L_AGE_IN_MONTHS: ' || l_age_in_months );
RETURN l_age_in_months;
EXCEPTION
WHEN no_data_found THEN
debug( 'no data found' );
RETURN NULL;
END get_age_in_months;
/
show errors
I mentioned in the previous article that I had had difficulty grasping this concept initially. I think once I related it to DBMS_OUTPUT.PUT_LINE it became much more clear to me.
This simple debug routine has helped me tremendously in the last year or two that I have used it. Especially when you get nested levels of logic. It gets very hard to keep track of where you are, but with this you can run your procedure or function and then issue a SELECT against the debug_details_tab and see what was happening when.
I even began using this in my APEX applications. I would preface each line with "APEX: " and then write whatever was necessary so that I could step through the various pieces of code. It became crucial when I was doing validation on a table of records in a collection...oh so much easier.
On large systems this
will generate a lot of data. I definitely would not put this inside a loop doing some sort of batch processing, but it is helpful to find where things fail out.
It can certainly be improved on. I basically took the idea of DBMS_OUTPUT.PUT_LINE and wrote it to a table, nothing fancy there. Mr. Kyte mentions writing it to the file system as well. Since I don't have ready access to the database file system, this was the easiest.
Make your life easier and use a debug/logging routine in your code. No longer will you have to comment, the debug statements should do it for you!
Labels: code, development, howto, instrumentation, plsql, tools, utilities, work
Code Style
Having a specific coding style has become more apparent to me now that I work in a team environment. I have my way, which is of course the best, and everybody else has there way, which isn't nearly as good.
I'm sure if I could make the rules, I'd have everyone writing code according to my ways. Power corrupts and all that.
I have learned to accept others' ways though. It's been difficult at times. If the code is readable and it works, I usually just bite my tongue.
Strangely, I love to type. I manually create all of my scripts. I make my code "pretty." I don't use GUI tools to auto-generate table definitions, and I especially don't use tools to format my code.
Toad Formatter, or whatever it's called, is my enemy. I'm sure Toad is not the only one with some sort of auto-formatter and I don't like any of them. For some reason I believe it is an abomination.
When I get to do the technical interview, I scan their resume for SQL*Plus. If I don't see it, I ask them what tools they use. If they don't mention SQL*Plus, I'm skeptical of their abilities...until I get to the five constraints question.
This is not to say that people that use Toad or SQLDeveloper are bad coders or anything, it's just my preference.
So what's your preference?
Labels: tools, work
Oracle Tools I've Used
Tools:SQL*PlusAPEX - Application Express (formerly HTMLDB)JDeveloperSQL DeveloperReports BuilderOC4JDiscovererOracle Server 8i, 9i, 10g (Windows)Oracle Application Server 9i, 10g (Windows)rmanoradimlsnrctltkprofComing Soon:Oracle Warehouse BuilderDatabase Features:JavaObject TypesWorkflowAdvanced Queueing (in conjunction with Workflow)Heterogenous ServicesFeatures I'd like to use:Change Data CaptureXMLSpatialinterMediaRegular ExpressionsDatabase Supplied Packages:DBMS_OUTPUTDBMS_LOBUTL_FILEDBMS_APPLICATION_INFODBMS_CRYPTODBMS_UTILITYDBMS_METADATADBMS_EPGDBMS_HS_PASSTHROUGHDBMS_JAVADBMS_JOBDBMS_LOCKDBMS_MVIEWDBMS_OBFUSCATION_TOOLKITDBMS_RANDOMDBMS_SESSIONDBMS_SQLDBMS_STATSDBMS_XDBDBMS_XPLANHTMLDB_APPLICATIONHTMLDB_ITEMHTMLDB_UTILHTPOWA_COOKIEUTL_MAILUTL_RAWUTL_SMTPWPG_DOCLOADPackages I'd like learn to use (10g):UTL_DBWSDBMS_CDC_PUBLISHDBMS_CDC_SUBSCRIBEDBMS_DATA_MININGDBMS_DATAPUMPDBMS_SCHEDULERDBMS_OLAPDBMS_PIPEDBMS_STREAMSDBMS_STREAMS_ADMDBMS_STREAMS_AUTHDBMS_STREAMS_MESSAGINGLabels: database, oracle, tools, utilities