tag:blogger.com,1999:blog-87217772024-02-20T02:47:52.236-08:00Michael A. Rife's BlogI have developed Oracle based software since 1987 (Oracle V5.0 and SQL*Form v2.0).
<br>
I am now doing Enterprise Architecture.
<br><br>
<a href="http://flagcounter.com/more/5I0"><img src="http://flagcounter.com/count/5I0/bg=FFFFFF/txt=000000/border=CCCCCC/columns=2/maxflags=12/viewers=0/labels=0/" border="0"></a>
<br>Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.comBlogger50125tag:blogger.com,1999:blog-8721777.post-83075994030502620782020-04-30T06:27:00.003-07:002020-04-30T06:27:47.431-07:00Just testing to see if this gets posted on OraNA.Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-7411280837757515982020-02-12T07:37:00.003-08:002020-04-30T06:23:05.868-07:00Please remove my blog from OraNAThis is a test blog post to see if it is getting aggregated to the<a href="http://orana.info/"> OraNA</a> news aggregator. If it does, please post how to get a blog removed from OraNA.<br />
<br />
Still wondering how to get my blog off of the OraNA news aggregator. Anyone know? Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-23275600193165073482016-02-04T09:21:00.002-08:002016-02-04T09:21:30.824-08:00Recover Modified Oracle APEX PageI was working on a page in an Oracle APEX application and wanted to rollback all the changes I made in the last 30 minutes on one particular page without losing all the changes I made prior to then. I had no export of the application, so I used a very cool feature of Oracle and APEX as documented in <a href="http://www.oracle-and-apex.com/recover-deleted-page-with-apex-and-firebug/">Peter Raganitsch's blog</a> way back in 2011.<br />
<br />
I basically did a page export as of 30 minutes ago. Since APEX runs in the Oracle database and flashback query is enabled in the database, when the APEX Builder was querying the APEX application tables in the database for the page it was exporting, the Builder did a flashback query as of 30 minutes ago. Once the page export was completed, I did an import of the page and the page was rolled back to 30 minutes ago. Very nice.Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-5223888049757074882014-12-22T12:27:00.000-08:002014-12-22T12:27:10.800-08:00Rows to String<br />
<br />
Just a note about a powerful way to convert a column of values in a query into a coma separated list in a text string:<br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">select 'LISTAGG' fx, </span><br />
<span style="font-family: "Courier New", Courier, monospace;"> length(listagg(table_name, ',')</span><br />
<span style="font-family: "Courier New", Courier, monospace;"> within group (order by table_name) ), </span><br />
<span style="font-family: "Courier New", Courier, monospace;"> listagg(table_name, ',') within group (order by table_name)<br /> from user_tables;</span>Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-59890445552243806442013-08-14T05:34:00.001-07:002013-08-14T05:34:51.197-07:00Old Oracle APEX "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" ErrorOracle Application Express 3.1.0.00.32 <br />
<br />
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.<br />
<br />
Too make a long debug process short, I discovered that this was an error generated internally by APEX code not in any application code. Because I had too many items on the page I assume that APEX is assembling an INSERT statement in a local PL/SQL variable somewhere internal to APEX and the INSERT text string is too large in length and cannot be assigned to the local variable. If I cut the number of items down to 118 items the pages runs fine.<br />
<br />
I also noticed a very small message at the bottom of the items region of the page definition that says, "Item count exceeds maximum of 100." It looks informational and does not stop you in any way. It would be nice if that were an actual alert that popped up and said don't do this or else you will get a runtime ORA-06502 error upon submitting the page. <br />
<br />
I am posting this for my recollection and all others who are working in an old version of APEX like Application Express 3.1.0.00.32 and encounter this error. I have searched multiple times on the Internet and could not find any references to this. Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-51822467510208224612013-03-22T10:42:00.004-07:002013-03-22T10:42:28.319-07:00Oracle 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.<br />
<br />
Assign a number like 1 to a tab by pressing SHIFT-ALT-1 while that is the active tab. You will see a miniature "1" at the beginning of the tab. Then whenever you want to go to that tab press ALT-1. Nice!!Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-77836928127489815272012-12-05T06:01:00.001-08:002012-12-05T06:01:02.520-08:00Using External Tables in a Reltional Model in Oracle SQL Developer Data ModelerAs 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.<br />
<br />
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 the external table or differently. In my scenario I created the view with the same name as the external table and will not create the view in the database. I am just creating the view in SDDM to represent the external table in relation models.Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-77542404818792955842012-11-01T06:25:00.002-07:002012-11-01T06:25:58.098-07:00Microsoft really does not like Oracle<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
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.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNftpv1qBWB15Ac_mrwQrjb4EHfEuNaHy3o1CxuS2pdQsu_1bLwjuBaltqbV4Hb2q2qBqUMATSSg4nyRi9YNbHFXduqb2tsrdma1AgZohFh6vxi_a3SdpqxdGCVqO_wfTd0T8nNQ/s1600/warning.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="397" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNftpv1qBWB15Ac_mrwQrjb4EHfEuNaHy3o1CxuS2pdQsu_1bLwjuBaltqbV4Hb2q2qBqUMATSSg4nyRi9YNbHFXduqb2tsrdma1AgZohFh6vxi_a3SdpqxdGCVqO_wfTd0T8nNQ/s400/warning.jpg" width="400" /></a></div>
<br />
<br />Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-47743184630395469252012-10-05T06:31:00.000-07:002012-10-05T06:41:55.307-07:00If 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?"Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-49528085888655754472012-09-17T09:27:00.000-07:002012-09-17T09:27:11.658-07:00What would you add to Oracle? responseLewis Cummingham posed the question, "<a href="http://it.toolbox.com/blogs/oracle-guide/what-would-you-add-to-oracle-53048">What would you add to Oracle?</a>" 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.<br />
<br />
I know that this is "Pie in the sky" type of stuff, but you never know.Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-2598473473940672732012-09-13T10:00:00.000-07:002012-09-13T10:00:07.227-07:00Oracle SQL Developer Output HintsI 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.<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">select /*csv*/ *</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> from dictionary</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> where table_name like 'USER_TAB%'</span><br />
<br />
would produce the following after "Run Script" (F5):<br />
<span style="font-family: "Courier New",Courier,monospace;"><br /></span>
<span style="font-family: "Courier New",Courier,monospace;">"TABLE_NAME","COMMENTS"<br />"USER_TABLES","Description of the user's own relational tables"<br />"USER_TAB_COLUMNS","Columns of user's tables, views and clusters"<br />"USER_TAB_COL_STATISTICS","Columns of user's tables, views and clusters"<br />"USER_TAB_HISTOGRAMS","Histograms on columns of user's tables"<br />"USER_TAB_COMMENTS","Comments on the tables and views owned by the user"<br />"USER_TAB_PRIVS","Grants on objects for which the user is the owner, grantor or grantee"<br />"USER_TAB_PRIVS_MADE","All grants on objects owned by the user"<br />"USER_TAB_PRIVS_RECD","Grants on objects for which the user is the grantee"<br />"USER_TAB_MODIFICATIONS","Information regarding modifications to tables"<br />"USER_TAB_PARTITIONS",""<br />"USER_TAB_SUBPARTITIONS",""<br />"USER_TABLESPACES","Description of accessible tablespaces"<br />"USER_TAB_STATS_HISTORY","History of table statistics modifications"<br />"USER_TAB_STATISTICS","Optimizer statistics of the user's own tables"<br />"USER_TAB_COLS","Columns of user's tables, views and clusters"</span><br />
<br />
Hints include:<br />
<br />
<ul>
<li>/*csv*/</li>
<li>/*xml*/</li>
<li>/*html*/ </li>
<li>/*delimited*/</li>
<li>/*insert*/</li>
<li>/*loader*/ </li>
<li>/*fixed*/ </li>
<li>/*text*/ </li>
</ul>
Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com2tag:blogger.com,1999:blog-8721777.post-54138076085287851442011-09-28T10:35:00.000-07:002011-09-28T11:39:37.436-07:00Oracle Openworld 2011 Schedule SetYes!! 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.<br /><br />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 have no cellular data service...)<br /><ol><li>In the Openworld schedule content catalog I went through and populated my interest list.</li><li>I copied (cut and paste) the 69 events to my PC in Excel (date, time, location and event title).</li><li>In the Openworld schedule builder I selected my first choices for each time slot. I have 36 first choices. I prefixed my primary event names with "1)".<br /></li><li>Next I selected 33 secondary choices left on my interest list for each time slot. I prefixed my secondary event names with "2)".</li><li>I saved all the events to a CVS file.</li><li>I imported the CVS file into my Google calendar.</li><li>I synced my old Windows Mobile phone with my Google calendar.</li><li>Now I have to fit in time for the exhibit hall..<br /></li></ol>It was a long painful process, but I now have all the events I want go to in my phone.<br /><br />Here are my preferred events for each time slot. Unfortunately it shows the session ID and not the session title.<br /><br /><div style="text-align: left;"><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlJGQ-cdNTRjI0SdPA41_lSuwWumMzXQWQuviRnRQ0yHhotaTlqHMoeAg3c8w-H48TbcEPrHCef2_ulwlm1qNF8XbKiTIDeyDFueHLbEtDdYdzApSFoRhZ9B6s4jF0fwt8GfTvfA/s1600/sessions.jpg"><img style="cursor:pointer; cursor:hand;width: 202px; height: 320px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlJGQ-cdNTRjI0SdPA41_lSuwWumMzXQWQuviRnRQ0yHhotaTlqHMoeAg3c8w-H48TbcEPrHCef2_ulwlm1qNF8XbKiTIDeyDFueHLbEtDdYdzApSFoRhZ9B6s4jF0fwt8GfTvfA/s320/sessions.jpg" alt="" id="BLOGGER_PHOTO_ID_5657476722328236226" border="0" /></a><br /></div>Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-41793990182416243532011-08-17T10:27:00.000-07:002011-08-17T10:57:18.319-07:00Oracle Forms Builder will not startWell 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.
<br />
<br /><span style="font-weight: bold;">Problem:</span> "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:
<br />
<br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2Lsmf4MrtKOLaDz7QqOySg6v9f5wfznCafK4ddEqZPFzyPFmShUbOqU9_1rcoDmMyVzyDneuv4L3u0BrLILr07_4p3BDdYIWXkCJAhlv5eHO9o3ci412P2ipYWQ0m40VXjPHiCg/s1600/forms_startup_error.jpg"><img style="cursor:pointer; cursor:hand;width: 320px; height: 231px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2Lsmf4MrtKOLaDz7QqOySg6v9f5wfznCafK4ddEqZPFzyPFmShUbOqU9_1rcoDmMyVzyDneuv4L3u0BrLILr07_4p3BDdYIWXkCJAhlv5eHO9o3ci412P2ipYWQ0m40VXjPHiCg/s320/forms_startup_error.jpg" alt="" id="BLOGGER_PHOTO_ID_5641879293726608962" border="0" /></a>
<br />
<br /><span style="font-weight: bold;">Cause:</span> 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 I start Oracle Forms Builder. When Oracle Forms Builder was starting it was using the local copy of JVM.DLL and not the copy in the directory where I installed Oracle Forms.
<br />
<br /><span style="font-weight: bold;">Fix:</span> I deleted the copy of the JVM.DLL that was in my local directory. Now Oracle Forms Builder is using the JVM.DLL that was provided by the Forms installation.
<br />
<br /><span style="font-weight: bold;">Summary:</span> If you get the Assertion Error when starting Oracle Forms Builder, check to see what JVM.DLL file is being used by Oracle Forms.
<br />
<br />
<br />Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-56063018027466452122011-02-14T08:56:00.000-08:002011-02-14T08:58:43.068-08:00How Oracle got to where it is todayHere is a great <a href="http://www.oswoug.org/Slides/RN022010.pdf">presentation</a> about the history of Oracle got to where it is today.Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-13720266219273706292011-02-04T10:52:00.000-08:002011-02-04T10:58:02.686-08:00Oracle 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.<br /><br />If you know of any please post them as comments to the blog entry.Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-69457093007372671162011-02-02T10:43:00.000-08:002011-02-02T10:57:26.891-08:00Apex 4.0 Upgrade, Not PossibleWell 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 a lot of non-Apex applications completely out of my control getting modified/upgraded. Oh well.Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-62821572675883134062011-01-18T07:01:00.000-08:002011-01-18T07:06:25.069-08:00Oracle A.I.M. RetiredThis 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 <strong><span style="font-family:Arial, Helvetica, sans-serif;font-size:85%;"><a href="http://www.oracle.com/partners/campaign/eblasts/retirement-aim-adv-bus-flows-185418.html">ORACLE<sup>®</sup> UNIFIED METHOD (OUM)</a><br /></span></strong>Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-47512134500237895702010-04-27T09:10:00.000-07:002010-04-27T09:11:27.526-07:00Oracle's Database HistoryI 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:<br /><br />http://www.oracle.com/technology/oramag/oracle/07-jul/o4730.htmlMichael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-49280600060325708592009-12-18T12:03:00.000-08:002010-01-11T10:11:48.704-08:00No code changed, but now ORA-01841. Mistery SolvedA 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?<br /><br />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 a sequence. You guessed it, the YEAR column for a different type of report for some reason had a zero in it. A DELETE statement in the report assembles a date value from the YEAR and MONTH values. With a value of zero, this caused the error.<br /><br />What made it worse is I would then rerun the report and it would run through fine with no errors giving the correct expected values. What was happening behind the scenes was the report with the bad data had finished and deleted all its zero year rows out of the table by the time I reran the report. So the rerun went through fine with no errors.<br /><br />That explained why the error was occurring, but not why it had never appeared before. The answer to this is our friend the Cost Based Optimizer (CBO). I had computed new statistics on the report table. With the prior statistics the CBO was applying a primary key WHERE clause criteria before assembling the date value from the YEAR and MONTH columns. So the report never hit the "zero year" rows from the other reports running at the same time. When I recomputed statistics on the report table it caused the CBO to change it access path and assemble the date value before applying the primary key restriction causing the Oracle error to appear. The old CBO statistics were masking the fact that there was a data error in the table i.e. a year value of 0.Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-64741939498857146782009-09-22T08:09:00.000-07:002009-09-22T08:15:45.365-07:00ODTUG 2010<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhc11-cNAvpIzxCYY2rN7lOzaMXRURPVqj5HiC1XKNv7wyEwHdBjneCVc9EqDd7x5VY0UY6Xkr23HtXZq8yTesi5LhWzv00tpJeKL02qz4p51B2o9Ys9XiL5gJ5zwd16UZLGYHDhA/s1600-h/ODTUGKaleidoscope2010.jpg"><img style="cursor: pointer; width: 400px; height: 53px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhc11-cNAvpIzxCYY2rN7lOzaMXRURPVqj5HiC1XKNv7wyEwHdBjneCVc9EqDd7x5VY0UY6Xkr23HtXZq8yTesi5LhWzv00tpJeKL02qz4p51B2o9Ys9XiL5gJ5zwd16UZLGYHDhA/s400/ODTUGKaleidoscope2010.jpg" alt="" id="BLOGGER_PHOTO_ID_5384310429397288338" border="0" /></a><br /><br />Is this correct? I found a link with a session schedule for the <b>ODTUG Kaleidoscope 2010</b> conference in Washington, DC June 27 to July 1, 2010. Is this accurate? I do not see anything on the ODTUG web page.<br /><br /><br />http://www.technicalconferencesolutions.com/pls/caat/caat_abstract_reports.schedule?conference_id=68Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-31165966218423890822009-08-26T10:42:00.000-07:002009-08-26T11:55:00.695-07:00NULLs 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).<br /><br /><span style="font-family:courier new;"> </span><blockquote><span style="font-family:courier new;">SELECT value</span><br /><span style="font-family:courier new;"> FROM table_a a</span><br /><span style="font-family:courier new;"> WHERE a.value NOT IN (SELECT DISTINCT b.value</span><br /><span style="font-family:courier new;"> FROM table_b b); </span></blockquote><br /><br />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:<br /><br /><span style="font-family:courier new;"> </span><blockquote><span style="font-family:courier new;">SELECT value</span><br /><span style="font-family:courier new;"> FROM table_a a</span><br /><span style="font-family:courier new;"> WHERE a.value IN (SELECT DISTINCT b.value</span><br /><span style="font-family:courier new;"> FROM table_b b); </span></blockquote><br /><br />and it did not list '82'. '82' is in TABLE_A and it is either 'IN' or 'NOT IN' TABLE_B. Why doesn't either query list '82'? So I rewrote the query to:<br /><br /><span style="font-family:courier new;"> </span><blockquote><span style="font-family:courier new;">SELECT *</span><br /><span style="font-family:courier new;"> FROM table_a a</span><br /><span style="font-family:courier new;"> WHERE NOT EXISTS (SELECT *</span><br /><span style="font-family:courier new;"> FROM table_b b</span><br /><span style="font-family:courier new;"> WHERE a.value = b.value);</span></blockquote><br /><br />and '82' was listed. So I looked at the EXPLAIN PLANs for both queries and noticed that the 'NOT IN' query was using the LNNVL function. So I looked at the rows being returned by my subquery. One of the rows had a NULL value. The LNNVL was making the '82' equal to the NULL value causing it to not list. So I changed my query to:<br /><br /><span style="font-family:courier new;"> </span><blockquote><span style="font-family:courier new;">SELECT value</span><br /><span style="font-family:courier new;"> FROM table_a a</span><br /><span style="font-family:courier new;"> WHERE a.value NOT IN (SELECT DISTINCT b.value</span><br /><span style="font-family:courier new;"> FROM table_b b<br /> WHERE b.value IS NOT NULL); </span></blockquote><br /><br />That fixed it. The moral of the story is, 'Always be aware of NULL values'.Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-14996385814754515332009-05-13T12:18:00.000-07:002009-05-13T12:37:33.957-07:00Reserved Words as Oracle Column NamesI 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 <span id="query" class="query">intrigued me, so I tried:<br /><br /><span style="font-family:courier new;"></span><blockquote><span style="font-family:courier new;">SQL> create table bogus (to varchar2(2));<br />create table bogus (to varchar2(2))<br /> *<br />ERROR at line 1:<br />ORA-00904: : invalid identifier<br /><br /><br />SQL> CREATE TABLE bogus AS<br /> 2 SELECT dummy "TO", dummy "FROM" FROM DUAL;<br /><br />Table created.<br /><br />SQL> desc bogus<br />Name Null? Type<br />----- ----- --------------------------<br />TO VARCHAR2(1)<br />FROM VARCHAR2(1)<br /><br />SQL> select * from bogus;<br /><br />TO FROM<br />-- ----<br />X X<br /><br />SQL><br /></span><br /></blockquote><br /></span>I started up SQL Developer to see what the CREATE TABLE statement would be for the BOGUS table:<br /><br /><span style="font-family:courier new;"></span><blockquote><span style="font-family:courier new;">CREATE TABLE "BOGUS"</span><br /><span style="font-family:courier new;">("TO" VARCHAR2(1),</span><br /><span style="font-family:courier new;"> "FROM" VARCHAR2(1))</span>;<br /></blockquote><br /><br />To select from the BOGUS table:<br /><br /><span id="query" class="query"><span style="font-family:courier new;"></span></span><blockquote><span id="query" class="query"><span style="font-family:courier new;">SQL> select "TO", "FROM" from bogus;<br /><br />TO FROM<br />-- ----<br />X X<br /><br />SQL><br /></span></span></blockquote><br /><br />There it is.Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-75963154019211713832009-02-23T12:56:00.000-08:002009-02-23T13:03:59.698-08:00APEX SQL Workshop Insufficient Privileges ErrorAn 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?<br /><br />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 in APEX. You cannot grant the privilege via a database role.<br /><br />I just thought that I would post this in case someone else runs into this problem and so I will have the answer when the problem comes up again.Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com2tag:blogger.com,1999:blog-8721777.post-29486028988432811452009-02-17T12:10:00.001-08:002009-02-17T12:48:46.923-08:00Oracle Reserved word as a Table nameI ran into a table today that was named GROUP, so I attempted to do a<br /><blockquote>SELECT * FROM GROUP;</blockquote>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<br /><br /><blockquote>SELECT * FROM "GROUP";</blockquote><br />I knew that SQL Developer placed double quotes around the schema, table, and column names in the CREATE TABLE statements in the "SQL" tab because those names could be mixed case. But the double quotes also allow those names to be reserved words as well.<br /><br />NOTE: In my humble opinion naming tables or columns a reserved word is not a good thing to do.Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0tag:blogger.com,1999:blog-8721777.post-4019716471565140152009-02-03T09:40:00.000-08:002009-02-03T10:34:48.230-08:00ORDER BY Clause in "Filter" field In SQL DeveloperI was in SQL Developer today and discovered something by accident. I double clicked on a table name in the "Connections" tab and went to the "Data" tab to view rows of data. In the "Filter:" field I entered a WHERE clause but accidentally also included an ORDER BY clause, too. The rows of data displayed in the order I requested. This got me to thinking. The "Sort" button provides a basic column by column sorting order, but if you need more complicated sorting logic you could just type it into the "Filter:" field.<br /><br />This is useful if you want to edit a set of rows in a particular order. For example in our friend the EMP table, we could list all the rows where their salary is greater than 2000 listing the president first, analysts second, and all others third:<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhfK95w_KeaeJ0-0Y4u7DmM-5eG5aJ69JmzhvOiwVmTvtIVCQIBEOIGau0wNvtifgJyKhyJcCTzk-nNoTkNtg-MRcFlmeMUtyGHY_dvRg39JbH0xVy3jvZRg-yexPw1IVVcjzr3uQ/s1600-h/emp.jpg"><img style="cursor: pointer; width: 662px; height: 236px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhfK95w_KeaeJ0-0Y4u7DmM-5eG5aJ69JmzhvOiwVmTvtIVCQIBEOIGau0wNvtifgJyKhyJcCTzk-nNoTkNtg-MRcFlmeMUtyGHY_dvRg39JbH0xVy3jvZRg-yexPw1IVVcjzr3uQ/s320/emp.jpg" alt="" id="BLOGGER_PHOTO_ID_5298631148860756370" border="0" /></a>Michael A. Rifehttp://www.blogger.com/profile/04328877875508402156noreply@blogger.com0