Old Oracle APEX "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" Error

Oracle Application Express 3.1.0.00.32

I added some display items to a large page and all of sudden I started getting "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" when I attempted to save changes.

Too make a long debug process short, I discovered (more...)

Oracle SQL Developer: Switching between tabs.

There is a nice feature of Oracle SQL Developer that allows once to assign numbers to tabs and then via a keystrokes one can switch between the tabs like Windows ALT-Tab does for applications in the Windows OS.

Assign a number like 1 to a tab by pressing SHIFT-ALT-1 while (more...)

Using External Tables in a Reltional Model in Oracle SQL Developer Data Modeler

As of Oracle SQL Developer Data Modeler (SDDM) version 3.1.00.700 you cannot use external tables in Relational Models.  When you import external tables from the Oracle data dictionary into SDDM you get the external tables in the Physical Model, but you cannot use them in a Relational Model.

I have figured out a work around until this is supported in SDDM.  In SDDM create a view which SELECTs each column from the external table.  You can then use the view in Relational Models.  You can name the view in SDDM the same as (more...)

Microsoft really does not like Oracle

Well I decided to install the "Oracle Developer Day VM" on my work Microsoft Windows XP Pro laptop to play around with Oracle 11g and APEX and got the following message.  Since this was my work laptop I of course aborted the installation.  Microsoft wins this one.




If Oracle’s Cost Based Optimizer were a GPS, where would it take you?

Similar to the "If the CBO were a car, what would be it's bumper sticker?"

What would you add to Oracle? response

Lewis Cummingham posed the question, "What would you add to Oracle?" in his blog.  He purposed the ability to support multiple languages within the database.  Thinking completely out of the box, I would enlarge that concept to include support of other database vendors' environments within an Oracle database.  Instead of just allowing users to hop from Oracle over to another database vendor's database via a "gateway", why not just put that other vendor's database inside of an Oracle database.  Oracle could experiment with MySql first because they own it.

I know that this is (more...)

Oracle SQL Developer Output Hints

I was just reviewing through presentations and papers from the ODTUG KScope 2012 in June and noticed a very nice little feature of Oracle SQL Developer that formats SQL output.  There are hints that SQL Developer recognizes.  The hints cannot contain any spaces.  Also, the hints are case sensitive.  The formatting is done in the "Script Output" window, so you have to use "Run Script" (F5) to execute the SELECT statement.

select /*csv*/ *
  from dictionary
 where table_name like 'USER_TAB%'

would produce the following after "Run Script" (F5):

"TABLE_NAME","COMMENTS"
"USER_TABLES","Description of the user's own relational (more...)

Oracle Openworld 2011 Schedule Set

Yes!! I finally get to go to an Oracle Openworld. I have been to many IOUG users weeks before and after the IOUG conference split from Oracle in 1995 along with a couple ODTUG conferences, but not to an Oracle Openworld conferences out in San Francisco.

I got my schedule all set. Here is the process I went through to get it my schedule loaded on my phone via my Google calendar. (I know there is an Oracle Openworld application, but I have an old Windows Mobile phone and the Android port for my phone eats my battery and I (more...)

Oracle Forms Builder will not start

Well I ran into an error that I found posted out on the Internet with no solution listed. I just wanted to post my solution in case someone else is running into this.

Problem: "All of sudden" today when I started Oracle Forms Builder 9i (yes we are using 1996 technology) I got Microsoft Visual C++ Runtime Library error Assertion failed! FRM-10039:



Cause: A new Java Virtual Machine (JVM.DLL) was pushed out to our PCs. Users were having problems with it. While working out the user's JVM problems, I made a copy of JVM.DLL in the directory where (more...)

How Oracle got to where it is today

Here is a great presentation about the history of Oracle got to where it is today.

Oracle Forms – 2011 Conference Missing In Action (MIA)

I was doing some 2011 training planning and was looking at some Oracle-related conferences for this year. I noticed there are no Oracle Forms sessions at the IOUG Collaborate 11 and one "migrating off of Oracle Forms" session at ODTUG KSCOPE11. Not a good indication for the future of Oracle Forms.

If you know of any please post them as comments to the blog entry.

Apex 4.0 Upgrade, Not Possible

Well I started looking at my 2011 projects and was getting excited about upgrading of our Oracle Apex version 3.1 to version 4.0 until I found out that Apex version 4.0 requires 10.2.0.3 or higher. Unfortunately the main Apex repository we have is Oracle 9i!! Barring moving the Apex repository (applications) to a different 10g database and setting up database links to "bridge" back to the data in the 9i database (ugly and messy), I am stuck with Apex 3.1 until the 9i database is upgraded to 10g. Unfortunately that is dependent on (more...)

Oracle A.I.M. Retired

This morning I started doing some reading on Oracle Application Implementation Method (A.I.M.) 3.0. I ran across the multitude of documents associated with it. I remember back reading books on Oracle's CASE*Method. Well while surfing for more information about A.I.M. I noticed that Oracle is retiring A.I.M. as of the end of this month (January). Now I need to see if I can dig up information about ORACLE® UNIFIED METHOD (OUM)

Oracle’s Database History

I have been working with Oracle since version 5 (1986). It is always interesting to try to remember when certain feature was added to the database. I found a link to an Oracle Magazine article which lists Oracle's database history and more:

http://www.oracle.com/technology/oramag/oracle/07-jul/o4730.html

No code changed, but now ORA-01841. Mistery Solved

A report that is coded in PL/SQL that has existed for years "all of sudden" started generating a "ORA-01841: (full) year must be between -4713 and +9999, and not be 0" error. Why and why all of sudden?

I discovered that a data problem has existed for years. When reports run they save results to a shared database table. Later on in the reports, the reports delete some of the data that does not meet a given time period. The table has both YEAR and MONTH columns which are both numeric along with a unique primary key value generated from (more...)

ODTUG 2010



Is this correct? I found a link with a session schedule for the ODTUG Kaleidoscope 2010 conference in Washington, DC June 27 to July 1, 2010. Is this accurate? I do not see anything on the ODTUG web page.


http://www.technicalconferencesolutions.com/pls/caat/caat_abstract_reports.schedule?conference_id=68

NULLs in subqueries.

I ran into a basic query today that perplexed me. I wanted to list all the values in one table (TABLE_A) that were not in another table (TABLE_B).

SELECT value
FROM table_a a
WHERE a.value NOT IN (SELECT DISTINCT b.value
FROM table_b b);


TABLE_A had the value '82' in it. TABLE_B did not have '82'. The query listed no rows. It should have listed '82' right? So thinking I was wrong and TABLE_B did have '82' in it, I tried:

SELECT value
FROM table_a a
WHERE a.value IN (SELECT DISTINCT b.value
FROM table_b b);


and it (more...)

Reserved Words as Oracle Column Names

I saw a post on the web asking if one could create an Oracle table in which a column name was an Oracle reserved word like "TO" or "FROM". Now, not that I would do this, but it intrigued me, so I tried:

SQL> create table bogus (to varchar2(2));
create table bogus (to varchar2(2))
*
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> CREATE TABLE bogus AS
2 SELECT dummy "TO", dummy "FROM" FROM DUAL;

Table created.

SQL> desc bogus
Name Null? Type
----- ----- --------------------------
TO VARCHAR2(1)
FROM VARCHAR2(1)

SQL> select * from bogus;

TO FROM
-- ----
(more...)

APEX SQL Workshop Insufficient Privileges Error

An APEX user was attempting to create a table in the SQL Workshop and received the "ORA-01031 insufficient privileges" error message. I went into SQL Developer and was able to create the table. So why did it not work in APEX SQL Workshop?

After some "data dictionary" digging comparing this schema to one that works, I realized that the CREATE TABLE privilege was granted to the schema via a role and not a direct grant. Since APEX runs within PL/SQL you have to have the CREATE TABLE grant issued directly to the schema to be able to create a table (more...)

Oracle Reserved word as a Table name

I ran into a table today that was named GROUP, so I attempted to do a
SELECT * FROM GROUP;
Since GROUP is a reserved word in Oracle, I got an "ORA-00903: invalid table name" error. So how do I query the table? I went into SQL Developer and was able to view the data in the table via the "Data" tab. So how was that possible? It ended up SQL Developer was doing a

SELECT * FROM "GROUP";

I knew that SQL Developer placed double quotes around the schema, table, and column names in the CREATE TABLE statements in (more...)