My code worked perfectly fine in Oracle 9i, but after our upgrade to 11g all of a sudden it was failing with ORA-01481 “Invalid number format model”.
The cause? A mixed-up number format model – I’d accidentally mixed locale-specific (“.“) and locale-safe (“G“) codes in the number format. I don’t know if this is due to a bug in 9i or an enhancement in 11g.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
- 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
SQL> select to_char(12345.678,'99G990.0') from dual;
select to_char(12345.678,'99G990.0') from dual
*
ERROR at line 1:
ORA-01481: invalid number format model
SQL> select to_char(12345.678,'99,990.0') from dual;
TO_CHAR(1
---------
12,345.7
SQL> select to_char(12345.678,'99G990D0') from dual;
TO_CHAR(1
---------
12,345.7
“Action: Consult the manual“… in retrospect, not of much help in this instance, unfortunately – however, these sorts of problems may usually be worked out by experimentation.
I like the “Vertical Images List” in APEX, which allows me to create a navigation bar of icons to give users quick access to various pages in my site. It’s easy to customise each item – you can select any image, add attributes for the image if necessary, and each item in the list has a URL which can point to another page in the application, or to an arbitrary URL.
My problem, however, was that some of the URLs in my list took the user to another site, or opened a PDF, and these would open in the same window. I wanted these particular items to open a new window, but the navigation item properties don’t allow this.
To solve this, I modified the Vertical Images List template, and used one of the User Defined Attributes to add “target=_blank” to the items that I wanted. While I was in there, I made a few tweaks to customise the template further to my liking.
A. Modify the Vertical Images List template.
- Go to Shared Components and open the Templates (under User Interface).
- Scroll down to Vertical Images List (in the Lists category) and open it for editing.
- Modify the Template Definition (WARNING: the code for different Apex templates may differ slightly; you’ll have to use a bit of nouse to customise it to your requirements) – you can add bits like #A01#, #A02#, etc – in my case I’ve used the following convention:
#A01# = extra text to appear below the icon & link;
#A02# = tooltip text for the hyperlink;
#A03# = extra attributes for the link (HTML <A> tag).
I’ve done this in both the “List Template Current” and “List Template Noncurrent” sections.
For example, for “List Template Noncurrent, I’ve modified the template code as follows:
<tr><td><a href=”#LINK#” TITLE=”#A02#” #A03#>
<img src=”#IMAGE_PREFIX##IMAGE#” #IMAGE_ATTR# />#TEXT#</a>
<BR>#A01#</td></tr>
B. Set User Defined Attributes.
- Open the Navigation List for editing (Shared Components -> Navigation -> Lists).
- Open the list item for editing that you wish to customise (or create a new one).
- In User Defined Attributes, attribute 1, add any text you wish to show beneath the link (but not highlighted as part of the ink)
- For attribute 2, add the title you wish to show up as a tooltip.
- For attribute 3, add the html attribute “target=_blank” if you wish this navigation entry to open a new window when invoked.
This is how it looks in a sample application:
If the user clicks on “Address Book”, the “target=_blank” attribute instructs the browser to open in a new window (or tab, in some cases).
A colleague needed to get the average from a set of data, but disregarding the top 25% and the bottom 25%. We didn’t know of any builtin Oracle function that would do this, but a review of the wikipedia page for Average yielded the technical term: truncated (or trimmed) mean. So we searched the Oracle docs and Google for ways to implement this function and didn’t come up with anything very useful. There were some SQL Server scripts which would have required two or three passes over the dataset.
After browsing through the aggregate functions documentation, I hit upon the NTILE function which I’ve used before, and realised that was the answer. The NTILE function takes a set of ordered data and divides it evenly (or as evenly as possible) between a number of buckets. In our case, we wanted to discard the top 25% and bottom 25%, so we simply divide the set into 4 buckets and discard the 1st and the 4th buckets; then take the standard average of the remainder:
SELECT AVG(mystat)
FROM (
SELECT mystat,
NTILE(4) OVER (ORDER BY mystat) n
FROM (SELECT mystat
FROM mytable)
)
WHERE n IN (2,3);
The benefit of this query is that it only does one pass over the data, and was easily modified to partition the data set into groups; a count was also added so that the average could be taken over the entire data set for any groups that had less than 4 items.
To get the truncated mean in groups, except for groups with <4 items (for which we’ll report the average over the entire group):
SELECT mycat, AVG(mystat)
FROM (
SELECT mycat, mystat,
NTILE(4) OVER (PARTITION BY mycat
ORDER BY mystat) n,
COUNT(1) OVER (PARTITION BY mycat) c
FROM (SELECT mycat, mystat
FROM mytable)
)
WHERE n IN (2,3) OR c < 4
GROUP BY mycat
ORDER BY mycat;
Is it possible to change the ORDER BY on a query, if the SQL is defined in a wrapped PL/SQL package? A colleague asked me this interesting question today – and the answer was quite simply, Yes and No.
If the SQL already has an ORDER BY, then the answer is No. Fortunately, in our case, the SQL didn’t have any ORDER BY clause:
SELECT a.addressid
FROM address a, addresstype t
WHERE a.locationid = :locationid
AND a.typeid = t.typeid
AND t.typecode = 'STREET';
The plan currently being picked by the CBO (Oracle 9.2.0.8) is:
NESTED LOOPS
TABLE ACCESS BY ROWID addresstype
INDEX RANGE SCAN pkx_addresstype
INDEX RANGE SCAN ix_address
For background, here are the relevant schema details:
TABLE address
(addressid NUMBER PRIMARY KEY
,locationid NUMBER NOT NULL
,typeid NUMBER NOT NULL --FOREIGN KEY to addresstype
);
INDEX idx_address (locationid, typeid, addressid);
TABLE addresstype
(typeid NUMBER PRIMARY KEY
,typecode VARCHAR2(30) NOT NULL
);
typecode is actually unique, but no constraint has been declared to the database.
Because the first step in the plan (the TABLE ACCESS on addresstype) only ever gets one row (in practice), the index range scan on ix_address causes this query to return the rows in ascending order of addressid.
The process that runs this query gets rows one at a time, does some work to “check” it against some other data, and once it finds a “match”, it stops getting more rows.
This design probably seemed like a good idea at the time, but now the business would like this process changed so that in the (rare) occurrence when more than one row would “match”, the process should favour a more recent address rather than an old one. For our purposes, “recent” may be approximated by assuming that bigger addressids are more recent than smaller addressids.
We can’t change the code without a lengthy and costly call to the application vendor; it would be better if we can change it ourselves. We cannot change the PL/SQL package in which this query is defined; so what can we do? Can we change the order in which rows are returned from this query?
The answer is yes – firstly, the query has no ORDER BY, so the order of rows is affected only by the query plan. If we change the query plan, which we can do with a stored outline, the order of rows can be changed.
So in this case, all we have to do is create a stored outline to add an INDEX_DESC hint:
SELECT /*+INDEX_DESC(a)*/ a.addressid
FROM address a, addresstype t
WHERE a.locationid = :locationid
AND a.typeid = t.typeid
AND t.typecode = 'STREET';
This causes the following plan to be used:
NESTED LOOPS
TABLE ACCESS BY ROWID addresstype
INDEX RANGE SCAN pkx_addresstype
INDEX RANGE SCAN DESCENDING ix_address
With the stored outline, the query now returns the rows in descending order of addressid.
NOTE:
If you want to use this approach, it is highly dependant on being able to find a query plan that produces the ordering that you want. This may mean creating a suitable index designed strategically for this purpose, if one doesn’t exist already. Even creating indexes won’t guarantee that you’ll be able to find a plan that gets the order that you want; generally I’d say this approach will only be feasible for relatively simple queries.
Problem: when Google indexes my APEX web site, it considers the following URLs to be different pages:
http://www.site.com/apex/f?p=100:1:1234567890::::
http://www.site.com/apex/f?p=100:1:0::::
http://www.site.com/apex/f?p=MYAPP:1:46346346346::::
http://www.site.com/apex/f?p=MYAPP:1:34634634636::::
http://www.site.com/apex/f?p=MYAPP:HOME:46346346346::::
http://www.site.com/apex/f?p=MYAPP:HOME:0::::
Notice how my application with ID 100 has an alias of MYAPP, and page 1 has an alias of HOME; also, more duplicates happen due to the session ID; all these URLs point to pretty much the same content, but Google indexes them all as separate pages.
Google provides two features that help webmasters solve the duplicate page problem.
Solution #1: Parameter Handling – not very useful (for us)
This solution involves telling Google which parameters to ignore when indexing URLs. This doesn’t help us with APEX, because apex only uses one parameter – “p”; if we were to tell Google to ignore the “p” parameter it would consider ALL pages in our site to be identical, which is not correct.
Solution #2: Specify Your Canonical – very useful!
Example:
<head>
<link rel="canonical" href="http://www.example.com/product.php?item=swedish-fish" />
</head>
This works nicely for us – for any page that we want we can tell Google what URL should be the “canonical” or “official” URL for that page. We can use this in our APEX applications in a number of ways. Each has advantages and disadvantages and YMMV, and it depends on how many different kinds of pages you have and whether you want the same canonical form for all pages, or if you want it customised for individual pages.
A. Custom canonical URL for each page.
This option will probably be the most generally useful, since some pages (e.g. multi-row paged results) won’t work so well with a canonical URL, so you’ll want to specify a canonical URL for just some key pages on your site.
To do this, go to the Page editor and edit the Page Attributes, edit the HTML Header and add the following:
<link rel="canonical" href="/apex/f?p=&APP_ID.:&APP_PAGE_ID.:0"/>
- You can add the full URL instead of a relative one if you want, but note if you do that it must be on the same domain (e.g. if your site is www.mysite.com, you can’t have a canonical URL pointing to myothersite.com). Anyway, Google don’t mind if you use relative URLs here, so that’s what I do.
- You don’t have to use the
&APP_xxx. substitution variables if you don’t want to – e.g. you could specify another application or page entirely if that makes sense for your app.
- If your application has an alias, you could use that as the canonical URL:
<link rel="canonical" href="/apex/f?p=&APP_ALIAS.:&APP_PAGE_ID.:0"/>
B. Global canonical URL for all pages in an application.
This option works well if you want all the pages to have the same form of canonical URL. Because we’ll use the &APP_PAGE_ID. substitution variable, it will still correctly give the correct URL for each page in the application.
To do this, go to the Shared Components, and open Themes. Open the theme in use by your application, then find the Page themes. Next to each Page Theme is a number that indicates how many pages use that Page Theme; those are the only ones you need to edit (although there’s nothing stopping you from editing all of them if you wish).
Click the Page Theme name to edit it. In the Header definition, add the canonical link – it must be inserted after the <head> tag, and prior to the </head> tag. For example:
<html lang="&BROWSER_LANGUAGE." xmlns:htmldb="http://htmldb.oracle.com">
<head>
<title>#TITLE#</title>
<link rel="stylesheet" href="#IMAGE_PREFIX#themes/theme_16/theme_V3.css" type="text/css" />
<!--[if IE]><link rel="stylesheet" href="#IMAGE_PREFIX#themes/theme_16/ie.css" type="text/css" /><![endif]-->
<link rel="canonical" href="/apex/f?p=&APP_ALIAS.:&APP_PAGE_ID.:0"/>
#HEAD#
</head>
<body #ONLOAD#>#FORM_OPEN#
Now, it’s important to test your changes thoroughly because many syntax errors you enter will not manifest in any obvious problems when browsing the site. Open your pages and View Source – check that the header section of the HTML includes the correct <link rel="canonical" ...> tag, and ensure that the URL resolves to the same page by copying it out and pasting it into your brower’s address bar.
Once that’s done that’s it! When Google next indexes your site it should honour your canonical URLs and remove duplicate pages from its indexes.
There are several reasons you might get this error in Oracle Forms, e.g. another user has modified the record before you saved it (as the error message suggests), or a table trigger has modified the record and your form’s DML Returning Value is set to No.
Another cause to chalk up is what one of my colleagues got today. He made a form with a simple table-based block, he would insert a record and save successfully, then try to modify it and consistently got FRM-40654 when he tried to save. No other user was trying to update the row, and there were no triggers on the table.
The answer? The table is an index-organised table, but the block on the form had Key Mode set to Automatic. I suspect the form is comparing the ROWID of the before-and-after change, but because the table is index-organised the ROWID is not necessarily constant. Whatever the reason, changing the Key Mode to Updateable or Non-Updateable solves the problem.
A work colleague needed to make a change to a report and came up against a brick wall. He knew what he wanted to express in SQL, but Oracle wouldn’t accept his syntax.
The original query used a WITH clause like this (note, I’ve removed a great deal of irrelevant detail here, the actual query had a lot of other stuff going on, but this will do to illustrate this point):
WITH q AS
(SELECT expensive_function1(:id) idpart1
,expensive_function2(:id) idpart2
FROM DUAL)
SELECT t.*
FROM q, some_table t
WHERE q.idpart1 = t.idpart1
AND q.idpart2 = t.idpart2;
This works well because the two expensive functions are run only once in the query, and the results are used to probe some_table on its compound key.
The requirements had changed, however, and my colleague needed to change it so that instead of calling the expensive functions, it now got multiple keys from another table. His first cut looked like this:
SELECT t.*
FROM some_table t
WHERE (t.idpart1, t.idpart2) IN
(SELECT idpart1, idpart2
FROM driving_table
WHERE id = :id);
This worked fine, but my colleague felt that the WITH clause should be retained, e.g.:
WITH q AS
(SELECT idpart1, idpart2
FROM driving_table
WHERE id = :id)
SELECT t.*
FROM some_table t
WHERE (t.idpart1, t.idpart2) IN q;
However, Oracle doesn’t like this and raises ORA-00920: invalid relational operator when the statement is parsed.
To my mind this alternative formulation was not required, but I was interested to know what this error message meant, and why can’t we use a WITH subquery as the target for an IN statement?
As it is, however, the Oracle documentation explains why this formulation is not allowed – look up the “SELECT” command in the SQL Reference 10g Release 2 (10.2), search for “WITH”, and under “subquery_factoring_clause” we read:
“Restrictions on Subquery Factoring: This clause is subject to the following restrictions:
…
In a compound query with set operators, you cannot use the query_name for any of the component queries, but you can use the query_name in the FROM clause of any of the component queries.”
In other words, we can refer to a WITH subquery in a FROM clause but not as part of a set operator like IN.
In the case of my colleague, I recommended he use this much simpler formulation, which works just fine for this report:
SELECT t.*
FROM some_table t, driving_table d
WHERE t.idpart1 = q.idpart1
AND t.idpart2 = q.idpart2;
This shows how, when maintaining existing code, sometimes you have to go back to basics and consider whether the change to the requirements (in this case, a seemingly minor change) means that the code can be refactored to give an appreciable benefit. Caveat: this is not always the case: making large changes to existing code always carries the risk of introducing more defects.
I had this problem with an APEX application I’m building, and finally found the cause this morning, so I thought I’d share it.
This particular application has some pages which are only available to authenticated users, and some pages which are visible to everyone. One nice thing about APEX is that it automatically redirects users to the Login screen if they try to navigate to a protected page.
After authentication, the user doesn’t have to login again – they can now see all pages of the application that I want them to see. This used to work fine.
Recently I noticed that sometimes I’d Login with my username and password, click on a Tab, and it would ask me to Login again. In these instances, it’d only ask me to Login just the second time – after that, it would be fine. I wrote it off as a random glitch on my home-grown server. It seemed to be random, and after a while I noticed it was happening once every day. I looked all through my application, trying to find any links that didn’t pass the &SESSION. through, but I couldn’t find any such problems. I looked at some other applications on the same server – no problems there, it was just this one application.
Just this morning I went in, and happened to notice something not quite right. Normally, when I go into an application, the URL looks something like this:
http://www.xyz.com/apex/f?p=100:1:318727495645403::NO
The site should generate the long numeric Session ID automatically. However, I noticed my URL looked like this:
http://www.xyz.com/apex/f?p=100:1:0::NO
The Session ID was zero. This is a relatively new feature of APEX which I use for my fully-public applications (i.e. ones which require no authentication), where no Session ID is required – it means users can bookmark individual pages without having a long Session ID embedded in the URL.
The cause? When I updated my index page of APEX applications, I copied another entry without thinking, and so included the “0” for the Session ID. So when I first logged in, it gave me a new session, but somewhere internally APEX still had my Session ID = 0, requiring me to Login again. After this, the internal reference to my session was updated. I don’t know if this is expected behaviour or a bug in APEX.
The fix? Remove the 0 from the initial link (e.g. now it looks like “http://www.xyz.com/apex/f?p=100:1”) – zero session IDs are only appropriate for applications that require no login at all anyway.
I came across an inexplicable error when bulk collecting into a PL/SQL table with the NOT NULL constraint the other day. What was confusing was that the code had been passing tests for quite some time.
In the end the only thing that had changed was that a VARCHAR2 which should have been non-null happened to be NULL for one particular row in the table.
Thanks to Connor for the simple test case, listed below.
If you know what might be the cause or reason behind this error, and why it doesn’t occur for dates, I’d be interested.
This was reproduced on Oracle 10.2.0.1.0.
SQL> declare
type t is table of number not null index by pls_integer;
r t;
begin
select case when rownum < 20 then rownum else null end
bulk collect into r from all_Objects
where rownum <= 20;
end;
/
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define
ORA-06512: at line 5
SQL> declare
type t is table of varchar2(80) not null index by pls_integer;
r t;
begin
select case when rownum < 20 then rownum else null end
bulk collect into r from all_Objects
where rownum <= 20;
end;
/
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define
ORA-06512: at line 5
SQL> declare
type t is table of date not null index by pls_integer;
r t;
begin
select case when rownum < 20 then sysdate else null end
bulk collect into r from all_Objects
where rownum <= 20;
end;
/
PL/SQL procedure successfully completed.
I upgraded Oracle SQL Developer from 1.0.14.67 to 1.0.15.27, just for the heck of it. Unfortunately, all my saved connections had disappeared! After a text search I found the connections are stored in a file called IDEConnections.xml under the folder sqldeveloper\jdev\system\oracle.onlinedb.11.0.0.37.25. I copied this across to a new folder that had been created (oracle.onlinedb.11.0.0.37.36) and bingo they’re back again.
Bonus – now I know what file to back up if I want to restore my connections later on.
