view counter
Home

APEX Feed

Oracle Application Express (APEX) resources, news, and support articles.

Fun with CHAR

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
(
  column_01 CHAR(10),
  column_02 CHAR(10),
  column_03 CHAR(10)
Apr 04, 2013 2:28pm

Generate DDL Source Code with SQL

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
FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN 
   ('SEQUENCE',   'TABLE',           'INDEX',
    'VIEW',       'DATABASE LINK',   'MATERIALIZED VIEW',
    'FUNCTION',   'PROCEDURE',       'PACKAGE',
    'PACKAGE BODY'
    )
AND OWNER = '#SCHMEA_NAME#';
Mar 27, 2013 2:42am

On Work/Life Balance

Nolan Bushnell, founder of Atari, had this to say via his new book, Find the Next Steve Jobs.

(It’s been said that many people in high tech cannot balance their personal and work lives. Here’s another way to look at it: Their jobs are so interesting that it’s difficult to figure out what is work and what is play. Creative projects produce this kind of excitement.)
Mar 26, 2013 8:29am

Analysis Tools...

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:

Mar 25, 2013 8:04pm

Migrate Sequences

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' ||
Mar 22, 2013 3:57am

The Internet

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?"

Mar 20, 2013 6:56pm

dbms_output.put_line

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.

Mar 19, 2013 7:08pm

Your Days Are Numbered … (for early-bird KSCOPE registration)

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: 

Mar 19, 2013 7:11am

Select List with Dynamic LOV and ORA-06502: PL/SQL: numeric or value error: character string buffer too small

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

Mar 19, 2013 12:45am

APEX Tabular Form auf Basis einer View

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.

Mar 18, 2013 1:00am
view counter