I'm busy deriving file layouts from PL/SQL. Probably close to 100 file definitions...each of them slightly different, each of them defined in the code. Fun!
There are a mixture of types too, fixed width, csv, etc. Thankfully, I've read enough of the code now that it's relatively easy to figure out. The fixed width variety is what this is about though.
In much of the code, there's a type that's defined, something like this:
type my_record is record
I just found this little piece of code to create DDL source code.
SELECT dbms_metadata.get_ddl(replace(OBJECT_TYPE, ' ', '_'), OBJECT_NAME,OWNER) as DDL_SOURCE_CODE
WHERE OBJECT_TYPE IN
('SEQUENCE', 'TABLE', 'INDEX',
'VIEW', 'DATABASE LINK', 'MATERIALIZED VIEW',
'FUNCTION', 'PROCEDURE', 'PACKAGE',
AND OWNER = '#SCHMEA_NAME#';
I've taken on an effort to port a custom data integration (PL/SQL, Java, etc) application.
In that regard, I'm doing a fair amount of analysis right now. So I need help finding two tools:
1. A tool that will allow me to map (visually or otherwise) a single data point from source to target(s). I typically use Excel. It's easy to use and available everywhere. Where it falls apart, slightly, is that a single data point may have one or more middle steps (i.e. not target) and one or more targets. I think I want something like this:
During one of my projects I had an issue when I copied the DDL from my test environment into my productive system. Unfortunately I needed some of the test data in the prod system as well. For that I had to migrate most of the sequences starting with their last number. SQL Developer created those sequences starting with 1. This simple code fixed my issue.
select 'DROP SEQUENCE "'||SEQUENCE_NAME||'";' ||
' CREATE SEQUENCE "'||SEQUENCE_NAME||'"' ||
' MINVALUE 1 MAXVALUE 999999999999999999999999999' ||
Have you seen this State Farm ad?
I think it's hilarious.
Riding to batting practice with LC, he starts up with me...
LC: (in response to some statement I made) "Where'd you hear that?"
Me: "The Internet"
LC: "And you believed it?"
I've been scratching my eyes out lately trying to reverse engineer
some lots of PL/SQL.
One thing I've seen a lot of is calls to dbms_output.put_line. Fortunately, I've seen some dbms_application_info.set_module and other system calls too. But back to that first one.
1. When I used dbms_output, I would typically only use it in development. Once done, I would remove all calls to it, test and promote to QA. It would never survive the trip to production.
KSCOPE 13 is coming up quickly, and so is the deadline for early-bird registration.
If you haven't ever been, KSCOPE is, dare I say, the best technical Oracle conference in the country, and potentially the world. Especially if you're interested in Oracle APEX. Along with excellent in-depth APEX coverage the agenda include coverage of:
If you need to create a dynamic SQL for a select list or any other type of a LOV, your query should not exceed 4000 characters. In case it does, you will get the "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" error. That is the internal limitation of the APEX table storing the item attribute information. In case you can't rewrite your SQL to satisfy this limitation, you may use the package I created for that purpose. It is quite simple and it does the following:
1. It will use the apex_collection package to parse the query and crate a collection
Eine der häufigen Anforderungen in der APEX Entwicklung ist es, einen änderbaren Report (Tabular Form) anzulegen bei dem einzelne Spaltenwerte verändert werden dürfen. Dies funktioniert in 90% der Fälle sehr gut. In manchen Fällen ist der Standardmechanismus leider nicht die 100% Lösung. Wenn Sie besonders viele LOV Spalten in Ihrem änderbaren Report verwenden, dann kann dies zu Performance Problemen führen. Genau auf dieses Problem gehen wir in diesem Blogeintrag näher ein.