SQL Developer (Raptor) build 1.2.0.29.98 (using JDK1.5.0_06) for Windows platform.
Every time i try to save a package source using the context menu Export DDL/Save to file, SQL Developer hangs (but if i choose Save to Clipboard it works).
I don't know if this applies to other object types as well.
I tried using the built-in Check Updates feature but for some reason no available upgrade was reported, however, after visiting the download page, i realized that a newer version was available indeed (1.2.1.32.13) and, after installing it, the problem is gone.
Interestingly enough there is no such bug entry in the fixed bugs list, so i wonder if this is a machine configuration dependent bug.
Last but not least, let me praise the upgrade mechanism of SQLDeveloper: unpack the zip file on top of the existing folder and you're done.
Wonderful!
Just to let you know.
Thursday, September 27, 2007
Wednesday, September 26, 2007
ASCII, ASCIISTR, CHR, UNISTR: many ways of converting characters
Let me state first that this is not meant to be an academic discussion about Unicode, UTF-8, UCS-2 and other interesting encoding problems.
If you are looking for a concise yet funny guide to the differences between UTF-8, UTF-16, UCS-2, UCS-4 and so on, you'd better off checking out "the Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets".
So, this morning i needed to find out the Unicode value for the special punctuation character "…".
This character is catalogued in the Unicode character index by name as horizontal ellipsis and its hex code point is 2026.
As the database character set is defined as "AL32UTF8", my first temptation was to convert this hex number into its decimal equivalent:
At first one could think that this caused by a wrong NLS setting or by the fact that perhaps function CHR does not return multibyte characters.
Nothing of the kind.
This result is easily explained trying out the following:
But what has to do 14844070 with 8230?
My short answer is nothing.
I presume 14844070 is the numeric code in the internal UCS2 representation of this Unicode character that I've got from:
Another interesting function is ASCIISTR that is converting a string into a plain ASCII string where non-ASCII characters are represented by their UTF-16 code unit.
For an Oracle official document discussing the topic, especially for the maximum string length implications for database columns and PL/SQL variables, see the Oracle Globalization Support Guide.
PS: on Windows another way of getting a unicode code point (or copying the character to the clipboard directly for pasting into a SQL source...) is to search for the desired character using the accessories/system/character map.
If you are looking for a concise yet funny guide to the differences between UTF-8, UTF-16, UCS-2, UCS-4 and so on, you'd better off checking out "the Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets".
So, this morning i needed to find out the Unicode value for the special punctuation character "…".
This character is catalogued in the Unicode character index by name as horizontal ellipsis and its hex code point is 2026.
As the database character set is defined as "AL32UTF8", my first temptation was to convert this hex number into its decimal equivalent:
select chr(8230) c from dual;But what I've got here is the ASCII character 38.
C
-
&
At first one could think that this caused by a wrong NLS setting or by the fact that perhaps function CHR does not return multibyte characters.
Nothing of the kind.
This result is easily explained trying out the following:
select chr(mod(8230,256)) cMoreover, if the db character set is AL32UTF8:
from dual;
C
-
&
select chr(14844070) cSo, CHR can return multibyte characters, indeed it returns any character in the database character set.
from dual;
C
-
…
But what has to do 14844070 with 8230?
My short answer is nothing.
I presume 14844070 is the numeric code in the internal UCS2 representation of this Unicode character that I've got from:
select ascii('…') nAnyway, while it could be interesting to know where this 14844070 comes from in the end, there is an easy way to get a character from its unicode code point:
from dual;
N
-
14844070
select unistr('\2026') cThen, if for some reason you need to know the numeric code of a character starting from its unicode code point, the easiest way is to run:
from dual;
C
-
…
select ascii(unistr('\2026')) n
from dual;
N
-
14844070
Again, the numeric value returned may vary with the database character set, although i could not try this out because i couldn't find any database in a different character set.Another interesting function is ASCIISTR that is converting a string into a plain ASCII string where non-ASCII characters are represented by their UTF-16 code unit.
select asciistr('hello world…') sThis function can be especially useful if you need to dump data into a plain ASCII file (i.e. not UTF-8 or UTF-16 encoded).
from dual;
S
-
hello world\2026
For an Oracle official document discussing the topic, especially for the maximum string length implications for database columns and PL/SQL variables, see the Oracle Globalization Support Guide.
PS: on Windows another way of getting a unicode code point (or copying the character to the clipboard directly for pasting into a SQL source...) is to search for the desired character using the accessories/system/character map.
Wednesday, September 19, 2007
ORA-01830, Apex and custom date formats
One of the frequent mistakes that one can make in Apex, especially when one is a beginner, is to forget the fact that page items in the session cache are stored as strings.
As a consequence, sometimes you see processes where PL/SQL code does not contain a proper function for converting the values from the displayed format to the required oracle type before storing them into a table or into variable.
Storing numbers and dates as strings brings about some consequences that you must bear in mind when implementing the underlying db procedures, especially if the application is aimed at a multinational public, where numeric and time/date separators can vary with each user.
A classic error message raised in such situations is:
It's easy to explain why it occurs.
Create a page item of type date as a pop-up calendar.
Pick a long date format like "DD-MON-YYYY HH:MI".
Create an after submit page process bound to some button that inserts the page item into a table without explicitly converting the value, something like:
When you handle P1_DATE as a bind variable you are handling just a string value, not a date value, so if you don't specify an explicit conversion in the insert statement, Oracle is assuming that it is formatted as DD-MON-RR, hence the error message.
However there are situations where Apex is much smarter and there also techniques that allow you to write code that is not bound to a format hard-coded into the page definition, which results in a reduced risk of getting ORA-01830.
Let's examine them.
Forms written using Apex DML processes perform an automatic conversion, basing on the format mask specified in the Tabular Form Element Date Picker Format Mask.
You can spot the underlying NLS_DATE_FORMAT (DD-MON-RR) located in the upper part of this test page.
If you turn off debugging and try to add a row containing a date formatted as DD/MM/YYYY, Apex will perform the required conversion automatically and without writing a line of code.
Note also that as of Apex 3.0, it is possible to define a custom date format mask for a pop-up calendar item and this parameterized value can be set in different ways.
A first way is to define an application substitution string and call it PICK_DATE_FORMAT_MASK then select "Date Picker (use application format mask)" as Date Picker Format Mask. Please note that this applies to both page items and column attributes in reports.
This method is very static and is good for ensuring consistency throughout all the application on all pop-up items defined in the same way. It only need to be defined centrally, in the Shared Components/Application Definition substitution strings.
A more flexibile way is to define an application item called PICK_DATE_FORMAT_MASK.
This will enable a dynamic setting of the date format in contrast with the static value shown earlier. Indeed you can create a page with a LOV based select list where you allow a user to pick the desired format or even write it manually, exactly as it happens in the Application Builder itself. Whatever you decide to with the users, you'll need to initialize the value of the item in some way before using it.
A third way, described rather hastily in the on-line help, is to choose "Date Picker (use item format mask)". This method is the most flexible as the "date picker will take the date format from the Format Mask attribute on the Edit Page Item page".
The first time i read this topic, it sounded rather confusing because it doesn't explain well what's the difference with the method shown earlier, moreover it doesn't mention how to do that in reports.
Let's try to fill out the gap.
First of all the difference lies in the fact that you can use page items to store the date format mask and this means allowing multiple formats at the same time for the same user within the same page (which can sound a rather odd requirement, but it does have sense actually).
Secondly it allows to specify this page item as a substitution string in the Format Mask attribute in the Source section of the Edit Page Item page:
or in the Column Formatting Date Format attribute of the Report Attibutes page:
Please note that in the latter case the attribute Number/Date Format in the Report Attributes page will be initially grayed out. It will become editable as soon as you pick "Date Picker (use item format mask)" in the Date Picker, which is located further down in the page.
Also in the case of the page item, don't forget to specify the default value or initialize the item with a computation.
You can see here the three different methods at work.
In order to achieve a dynamic effect, one could use an AJAX process to update the report after changing the format mask.
As a final note, keep in mind that when defining a default value for a page item of type date picker (like TRUNC(SYSDATE) for instance), which requires also changing the source type from Static Assignment to PL/SQL Expression by the way, requires an explicit conversion because the format mask is applied when picking the value from the pop-up window, not when retrieving the default value. So, instead of writing just TRUNC(SYDATE), you are better off writing TO_CHAR(SYSDATE,:P12_DATE_FORMAT) or whatever is the name of the item holding the date format in your case.
As a consequence, sometimes you see processes where PL/SQL code does not contain a proper function for converting the values from the displayed format to the required oracle type before storing them into a table or into variable.
Storing numbers and dates as strings brings about some consequences that you must bear in mind when implementing the underlying db procedures, especially if the application is aimed at a multinational public, where numeric and time/date separators can vary with each user.
A classic error message raised in such situations is:
ORA-01830: date format picture ends before converting entire input stringFor a generic explanation of this error at the db server level, see my previous posting.
It's easy to explain why it occurs.
Create a page item of type date as a pop-up calendar.
Pick a long date format like "DD-MON-YYYY HH:MI".
Create an after submit page process bound to some button that inserts the page item into a table without explicitly converting the value, something like:
insert into test (date_inserted) values (:P1_DATE);finally run the page and voilà:
ORA-01830: date format picture ends before converting entire input stringThe fact is that Apex is internally using the default NLS_DATE_FORMAT, "DD-MON-RR", whereas the string stored in P1_DATE is in the format specified in the page item attribute.
When you handle P1_DATE as a bind variable you are handling just a string value, not a date value, so if you don't specify an explicit conversion in the insert statement, Oracle is assuming that it is formatted as DD-MON-RR, hence the error message.
However there are situations where Apex is much smarter and there also techniques that allow you to write code that is not bound to a format hard-coded into the page definition, which results in a reduced risk of getting ORA-01830.
Let's examine them.
Forms written using Apex DML processes perform an automatic conversion, basing on the format mask specified in the Tabular Form Element Date Picker Format Mask.
You can spot the underlying NLS_DATE_FORMAT (DD-MON-RR) located in the upper part of this test page.
If you turn off debugging and try to add a row containing a date formatted as DD/MM/YYYY, Apex will perform the required conversion automatically and without writing a line of code.
Note also that as of Apex 3.0, it is possible to define a custom date format mask for a pop-up calendar item and this parameterized value can be set in different ways.
A first way is to define an application substitution string and call it PICK_DATE_FORMAT_MASK then select "Date Picker (use application format mask)" as Date Picker Format Mask. Please note that this applies to both page items and column attributes in reports.
This method is very static and is good for ensuring consistency throughout all the application on all pop-up items defined in the same way. It only need to be defined centrally, in the Shared Components/Application Definition substitution strings.
A more flexibile way is to define an application item called PICK_DATE_FORMAT_MASK.
This will enable a dynamic setting of the date format in contrast with the static value shown earlier. Indeed you can create a page with a LOV based select list where you allow a user to pick the desired format or even write it manually, exactly as it happens in the Application Builder itself. Whatever you decide to with the users, you'll need to initialize the value of the item in some way before using it.
A third way, described rather hastily in the on-line help, is to choose "Date Picker (use item format mask)". This method is the most flexible as the "date picker will take the date format from the Format Mask attribute on the Edit Page Item page".
The first time i read this topic, it sounded rather confusing because it doesn't explain well what's the difference with the method shown earlier, moreover it doesn't mention how to do that in reports.
Let's try to fill out the gap.
First of all the difference lies in the fact that you can use page items to store the date format mask and this means allowing multiple formats at the same time for the same user within the same page (which can sound a rather odd requirement, but it does have sense actually).
Secondly it allows to specify this page item as a substitution string in the Format Mask attribute in the Source section of the Edit Page Item page:
or in the Column Formatting Date Format attribute of the Report Attibutes page:
Please note that in the latter case the attribute Number/Date Format in the Report Attributes page will be initially grayed out. It will become editable as soon as you pick "Date Picker (use item format mask)" in the Date Picker, which is located further down in the page.
Also in the case of the page item, don't forget to specify the default value or initialize the item with a computation.
You can see here the three different methods at work.
In order to achieve a dynamic effect, one could use an AJAX process to update the report after changing the format mask.
As a final note, keep in mind that when defining a default value for a page item of type date picker (like TRUNC(SYSDATE) for instance), which requires also changing the source type from Static Assignment to PL/SQL Expression by the way, requires an explicit conversion because the format mask is applied when picking the value from the pop-up window, not when retrieving the default value. So, instead of writing just TRUNC(SYDATE), you are better off writing TO_CHAR(SYSDATE,:P12_DATE_FORMAT) or whatever is the name of the item holding the date format in your case.
ORA-01830: date format picture ends before converting entire input string
If you are getting this error in Oracle Application Express, see this other topic otherwise, carry on.
This type of error can be easily demonstrated:
In this scenario it's very easy to spot the problem, because it's clearly caused by an input string longer than the date format mask (16 char string instead of the expected 10 char string), however it may be less obvious when an implicit conversion is occurring.
for instance:
So, when you get ORA-01830, you must always determine what the current NLS_DATE_FORMAT is and see if there is some implicit conversion going on.
See message translations for ORA-01830 and search additional resources
----------------------------
ORA-01830: Il formato data termina prima di convertire l'intera stringa in input
ORA-01830: la máscara de formato de fecha termina antes de convertir toda la cadena de entrada
ORA-01830: el format de la data no coincideix amb la cadena a convertir que s'ha entrat
ORA-01830: données surnuméraires après la conversion correcte d'une chaîne en entrée
ORA-01830: Datumsformatstruktur endet vor Umwandlung der gesamten Eingabezeichenfolge
ORA-01830: πρότυπο μορφής ημερομηνίας τελειώνει πριν να γίνει μετατροπή όλου του αλφαριθμητικού εισόδου
ORA-01830: datoformatbillede slutter før konvertering af hele inputstrengen
ORA-01830: datumformatmasken slutar före hela inmatningssträngen omvandlats
ORA-01830: datoformatbildet slutter før hele inndatastrengen er konvertert
ORA-01830: päivämäärämuodon kuvain päättyy ennen koko syötemerkkijonon muunnosta
ORA-01830: a dátumformátum véget ért a teljes bemeneti karakterlánc konverziója előtt
ORA-01830: imaginea formatului de dată se termină înaintea conversiei şirului de intrare
ORA-01830: Datumnotatieafbeelding eindigt voordat de gehele invoerstring is geconverteerd.
ORA-01830: a imagem do formato da data termina antes de converter a string de entrada inteira
ORA-01830: imagem de formato de data termina antes de converter toda a cadeia de caracteres
ORA-01830: шаблон формата даты завершается перед преобразованием всей строки ввода
ORA-01830: obraz formátu pro daum končí před převodem celého vstupního řetězce
ORA-01830: zadaný formát dátumu nepostačuje pre úplné konvertovanie vstupného reťazca
ORA-01830: wzorzec formatu daty kończy się przed konwersją całego ciągu wejściowego
ORA-01830: tarih formatı resmi tüm girdi dizesi dönüştürülmeden önce son buldu
ORA-01830: date format picture ends before converting entire input string
This type of error can be easily demonstrated:
select TO_DATE('2007/09/19 10:00', 'YYYY/MM/DD') date_fmt
from dual;
ORA-01830: date format picture ends before converting entire input stringIn this scenario it's very easy to spot the problem, because it's clearly caused by an input string longer than the date format mask (16 char string instead of the expected 10 char string), however it may be less obvious when an implicit conversion is occurring.
for instance:
CREATE TABLE TEST1 (d DATE)
/
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY'
/
declare
a varchar2(20) := '19/09/2007 12:00:00';
begin
INSERT INTO TEST1 (d) VALUES (a);
end;
/
ORA-01830: date format picture ends before converting entire input string
So, when you get ORA-01830, you must always determine what the current NLS_DATE_FORMAT is and see if there is some implicit conversion going on.
See message translations for ORA-01830 and search additional resources
----------------------------
ORA-01830: Il formato data termina prima di convertire l'intera stringa in input
ORA-01830: la máscara de formato de fecha termina antes de convertir toda la cadena de entrada
ORA-01830: el format de la data no coincideix amb la cadena a convertir que s'ha entrat
ORA-01830: données surnuméraires après la conversion correcte d'une chaîne en entrée
ORA-01830: Datumsformatstruktur endet vor Umwandlung der gesamten Eingabezeichenfolge
ORA-01830: πρότυπο μορφής ημερομηνίας τελειώνει πριν να γίνει μετατροπή όλου του αλφαριθμητικού εισόδου
ORA-01830: datoformatbillede slutter før konvertering af hele inputstrengen
ORA-01830: datumformatmasken slutar före hela inmatningssträngen omvandlats
ORA-01830: datoformatbildet slutter før hele inndatastrengen er konvertert
ORA-01830: päivämäärämuodon kuvain päättyy ennen koko syötemerkkijonon muunnosta
ORA-01830: a dátumformátum véget ért a teljes bemeneti karakterlánc konverziója előtt
ORA-01830: imaginea formatului de dată se termină înaintea conversiei şirului de intrare
ORA-01830: Datumnotatieafbeelding eindigt voordat de gehele invoerstring is geconverteerd.
ORA-01830: a imagem do formato da data termina antes de converter a string de entrada inteira
ORA-01830: imagem de formato de data termina antes de converter toda a cadeia de caracteres
ORA-01830: шаблон формата даты завершается перед преобразованием всей строки ввода
ORA-01830: obraz formátu pro daum končí před převodem celého vstupního řetězce
ORA-01830: zadaný formát dátumu nepostačuje pre úplné konvertovanie vstupného reťazca
ORA-01830: wzorzec formatu daty kończy się przed konwersją całego ciągu wejściowego
ORA-01830: tarih formatı resmi tüm girdi dizesi dönüştürülmeden önce son buldu
ORA-01830: date format picture ends before converting entire input string
Monday, September 17, 2007
ORA-02273: this unique/primary key is referenced by some foreign keys
It's very easy to reproduce this situation:
As the error message suggests, there is no difference between primary key and unique constraints:
See message translations for ORA-02273 and search additional resources.
ORA-02273: a questa chiave primaria o unica fanno riferimento chiavi esterne
ORA-02273: a esta clave única/primaria hacen referencia algunas claves ajenas
ORA-02273: aquesta clau única o primària està referenciada per alguna clau forana
ORA-02273: cette clé unique/primaire est référencée par des clés étrangères
ORA-02273: Einige Fremdschlüssel beziehen sich auf diesen Primär-/eindeutigen Schlüssel
ORA-02273: αυτό το μοναδικό/πρωτεύον κλειδί αναφέρεται από μερικά ξένα κλειδιά
ORA-02273: denne unikke/primære nøgle er refereret af fremmede nøgler
ORA-02273: denna UNIQUE/PRIMARY KEY refereras från några främmande nycklar
ORA-02273: denne entydige/primære nøkkelen blir referert av fremmednøkler
ORA-02273: tähän yksikäsitteiseen tai perusavaimeen viittaa viiteavaimia
ORA-02273: erre az egyedi/elsődleges kulcsra más idegen kulcsok hivatkoznak
ORA-02273: această cheie unică/primară este referită de câteva chei externe
ORA-02273: Naar deze unieke/primaire sleutel wordt verwezen door verwijzende sleutels.
ORA-02273: esta chave exclusiva/primária é referenciada por algumas chaves externas
ORA-02273: esta chave exclusiva/primária é referenciada por algumas chaves externas
ORA-02273: на этот уникальный/первичный ключ ссылаются вторичные ключи
ORA-02273: na tento jednoznačný/primární klíč se odkazují cizí klíče
ORA-02273: tento jednoznačný/primárny kľúč je odkazovaný niektorými cudzími kľúčmi
ORA-02273: obce klucze odwołują się do tego unikatowego/głównego klucza
ORA-02273: bu benzersiz/birincil anahtara bazı yabancı anahtarlar tarafından başvuruluyor
ORA-02273: this unique/primary key is referenced by some foreign keys
create table test1(
test1_parent_id number,
col1 varchar2 (30),
constraint test1_pk primary key (test1_parent_id)
)
/
create table test2(
test2_id number,
test1_child_id number,
col2 varchar2(30),
constraint test2_fk foreign key (test1_child_id) references test1(test1_parent_id)
)
/
alter table test1 drop constraint test1_pk
/
ORA-02273: this unique/primary key is referenced by some foreign keys
As the error message suggests, there is no difference between primary key and unique constraints:
create table test3(
test3_parent_id number,
col1 varchar2 (30),
constraint test3_uq unique (test3_parent_id)
)
/
create table test4(
test4_id number,
test3_child_id number,
col2 varchar2(30),
constraint test4_fk foreign key (test3_child_id) references test3(test3_parent_id)
)
/
alter table test3 drop constraint test3_uq
/
ORA-02273: this unique/primary key is referenced by some foreign keys
See message translations for ORA-02273 and search additional resources.
ORA-02273: a questa chiave primaria o unica fanno riferimento chiavi esterne
ORA-02273: a esta clave única/primaria hacen referencia algunas claves ajenas
ORA-02273: aquesta clau única o primària està referenciada per alguna clau forana
ORA-02273: cette clé unique/primaire est référencée par des clés étrangères
ORA-02273: Einige Fremdschlüssel beziehen sich auf diesen Primär-/eindeutigen Schlüssel
ORA-02273: αυτό το μοναδικό/πρωτεύον κλειδί αναφέρεται από μερικά ξένα κλειδιά
ORA-02273: denne unikke/primære nøgle er refereret af fremmede nøgler
ORA-02273: denna UNIQUE/PRIMARY KEY refereras från några främmande nycklar
ORA-02273: denne entydige/primære nøkkelen blir referert av fremmednøkler
ORA-02273: tähän yksikäsitteiseen tai perusavaimeen viittaa viiteavaimia
ORA-02273: erre az egyedi/elsődleges kulcsra más idegen kulcsok hivatkoznak
ORA-02273: această cheie unică/primară este referită de câteva chei externe
ORA-02273: Naar deze unieke/primaire sleutel wordt verwezen door verwijzende sleutels.
ORA-02273: esta chave exclusiva/primária é referenciada por algumas chaves externas
ORA-02273: esta chave exclusiva/primária é referenciada por algumas chaves externas
ORA-02273: на этот уникальный/первичный ключ ссылаются вторичные ключи
ORA-02273: na tento jednoznačný/primární klíč se odkazují cizí klíče
ORA-02273: tento jednoznačný/primárny kľúč je odkazovaný niektorými cudzími kľúčmi
ORA-02273: obce klucze odwołują się do tego unikatowego/głównego klucza
ORA-02273: bu benzersiz/birincil anahtara bazı yabancı anahtarlar tarafından başvuruluyor
ORA-02273: this unique/primary key is referenced by some foreign keys
Friday, September 14, 2007
ORA-02429: cannot drop index used for enforcement of unique/primary key
This error condition is easily explained:
But there can be a subtler situation.
Let's create the following two tables and a non-unique index, supposing that we initially want this index for performance reasons.
Clearly, if we attempt to drop the index, ORA-02429 is raised.
This is just to demonstrate that this exception has nothing to do with the constraint and the associated index having the same name or being built at the same time.
See message translations for ORA-02429 and search additional resources.
ORA-02429: impossibile eliminare indice usato per imposizione di chiave unica/primaria
ORA-02429: no se puede borrar el índice utilizado para el forzado de clave única/primaria
ORA-02429: no es pot eliminar un índex que controla la unicitat de la clau, o la clau primària
ORA-02429: impossible abandonner index utilisé pour utilisation de clé primaire/unique
ORA-02429: Index zum Erzwingen des eindeutigen/Primärschlüssels nicht löschbar
ORA-02429: δεν είναι δυνατή η διαγραφή ευρετηρίου χρησιμοποιούμενο για την επιβολή μοναδικού/πρωτεύοντος κλειδιού
ORA-02429: indeks, som anvendes til forcering af unik/primær nøgle, kan ikke droppes
ORA-02429: kan inte ta bort index som används för tillämpning av unik/primär nyckel
ORA-02429: kan ikke fjerne indeks som brukes til gjennomføring av entydig/primær nøkkel
ORA-02429: ei voi poistaa indeksiä, jota käytetään vahvistamaan yksikäsitteistä avainta tai perusavainta
ORA-02429: az egyediséget/elsődlegességet biztosító index nem dobható el
ORA-02429: nu se poate elimina indexul utilizat pt punerea în val a cheii unice/primare
ORA-02429: Kan index niet verwijderen: gebruikt voor afdwingen unieke/primaire sleutel.
ORA-02429: não é possível eliminar o índice usado para imposição da chave exclusiva/primária
ORA-02429: não é possível suprimir índice utilizado para imposição de chave exclusiva/primária
ORA-02429: нельзя удалить индекс, использованный для реализации уникальн./первичн. ключа
ORA-02429: nelze zrušit index používaný pro prosazení jednoznačného/primárního klíče
ORA-02429: nemožno zrušiť index použitý pre implementáciu jedinečného alebo primárneho kľúča
ORA-02429: nie można usunąć indeksu odpowiedzialnego za klucz unikatowy/główny
ORA-02429: benzersiz/birincil anahtarların zorlanması için kullanılan dizin bırakılamaz
ORA-02429: cannot drop index used for enforcement of unique/primary key
create table test1(I attempted to drop an index (whose name is the same of the associated constraint) and Oracle prevents me from doing that.
test1_id number,
test1_parent_id number,
test1_child_id number,
col1 varchar2 (30),
constraint test1_pk primary key (test1_id)
)
/
drop index test1_pk
/
ORA-02429: cannot drop index used for enforcement of unique/primary key
But there can be a subtler situation.
Let's create the following two tables and a non-unique index, supposing that we initially want this index for performance reasons.
create table test1(Later on we realize that it makes sense to make that column pair a unique key so we add the following constraint on the first table (the data in the table must be consistent or we will get an exception...).
test1_id number,
test1_parent_id number,
test1_child_id number,
col1 varchar2 (30),
constraint test1_pk primary key (test1_id)
)
/
create index idx_test1 on test1( test1_parent_id, test1_child_id)
/
alter table test1 add constraint test1_uq unique (test1_parent_id, test1_child_id)If we inspect the dictionary view USER_CONSTRAINTS, you will see that Oracle didn't create a new index to enforce this unique constraint, but recycled the existing index that we allegedly created for performance reasons.
/
select owner, constraint_name, index_owner,index_name
from user_constraints
where table_name = 'TEST1'
| OWNER | CONSTRAINT_NAME | INDEX_OWNER | INDEX_NAME |
|---|---|---|---|
| TEST | TEST1_UQ | TEST | IDX_TEST1 |
Clearly, if we attempt to drop the index, ORA-02429 is raised.
This is just to demonstrate that this exception has nothing to do with the constraint and the associated index having the same name or being built at the same time.
See message translations for ORA-02429 and search additional resources.
ORA-02429: impossibile eliminare indice usato per imposizione di chiave unica/primaria
ORA-02429: no se puede borrar el índice utilizado para el forzado de clave única/primaria
ORA-02429: no es pot eliminar un índex que controla la unicitat de la clau, o la clau primària
ORA-02429: impossible abandonner index utilisé pour utilisation de clé primaire/unique
ORA-02429: Index zum Erzwingen des eindeutigen/Primärschlüssels nicht löschbar
ORA-02429: δεν είναι δυνατή η διαγραφή ευρετηρίου χρησιμοποιούμενο για την επιβολή μοναδικού/πρωτεύοντος κλειδιού
ORA-02429: indeks, som anvendes til forcering af unik/primær nøgle, kan ikke droppes
ORA-02429: kan inte ta bort index som används för tillämpning av unik/primär nyckel
ORA-02429: kan ikke fjerne indeks som brukes til gjennomføring av entydig/primær nøkkel
ORA-02429: ei voi poistaa indeksiä, jota käytetään vahvistamaan yksikäsitteistä avainta tai perusavainta
ORA-02429: az egyediséget/elsődlegességet biztosító index nem dobható el
ORA-02429: nu se poate elimina indexul utilizat pt punerea în val a cheii unice/primare
ORA-02429: Kan index niet verwijderen: gebruikt voor afdwingen unieke/primaire sleutel.
ORA-02429: não é possível eliminar o índice usado para imposição da chave exclusiva/primária
ORA-02429: não é possível suprimir índice utilizado para imposição de chave exclusiva/primária
ORA-02429: нельзя удалить индекс, использованный для реализации уникальн./первичн. ключа
ORA-02429: nelze zrušit index používaný pro prosazení jednoznačného/primárního klíče
ORA-02429: nemožno zrušiť index použitý pre implementáciu jedinečného alebo primárneho kľúča
ORA-02429: nie można usunąć indeksu odpowiedzialnego za klucz unikatowy/główny
ORA-02429: benzersiz/birincil anahtarların zorlanması için kullanılan dizin bırakılamaz
ORA-02429: cannot drop index used for enforcement of unique/primary key
Thursday, September 13, 2007
ORA-02270: no matching unique or primary key for this column-list
If you see this error, it simply means that the target table (the table name following the REFERENCES keyword) lacks a constraint, either a primary key or a unique key defined on the columns listed inside the parentheses.
An example will clarify the matter:
What if we add a unique index?
Nope, we must add a unique constraint on TEST1 as well, referencing the unique index we've just created:
Notes:
i created a separate index just to show that it was not sufficient for the purpose, but it can be done at the time of table creation or when the constraint is added at a later time.
What's the point of having a non-unique index associated with a unique constraint?
Well, it is a requirement for deferrable constraints.
So, as you see, there is room for making things quite complicated.
You can brush up your knowledge about constraints in the following documents:
Another thing worth mentioning is that column order is not important, so we could have swapped the two columns either in the index or in the constraint specification.
Of course this doesn't mean that you should specify the column order randomly, because column order affects the way the optimizer picks indexes, so be sure to design indexes properly.
See message translations for ORA-02270 and search additional resources.
ORA-02270: per questa lista-colonna non vi sono chiavi uniche o primarie corrispondenti
ORA-02270: no hay ninguna clave única o primaria correspondiente para esta lista de columnas
ORA-02270: la llista de columnes no coincideix amb una clau única o primària
ORA-02270: pas de correspondance de clé primaire ou unique pr cette liste de colonne
ORA-02270: kein entsprechender Primär- o. eindeutiger Schlüssel für diese Spaltenliste
ORA-02270: δεν υπάρχει μοναδικό ή πρωτεύον κλειδί που να ταιριάζει για αυτή τη λίστα στηλών
ORA-02270: der er ingen matchende unik eller primær nøgle til denne kolonneliste
ORA-02270: ingen matchande unik eller primär nyckel för kolumnlistan
ORA-02270: det finnes ingen tilsvarende entydig eller primær nøkkel for kolonnelisten
ORA-02270: yksilöivää avainta tai perusavainta ei ole tätä sarakeluetteloa varten
ORA-02270: erre az oszlop-listára nem illeszthető egyedi vagy elsődleges kulcs
ORA-02270: nu exista chei primare sau unice potrivite pentru această listă-coloană
ORA-02270: Geen overeenkomende unieke of primaire sleutel voor deze kolomlijst.
ORA-02270: não há chave exclusiva ou primária compatível para esta lista de colunas
ORA-02270: nenhuma correspondência de chaves exclusivas/primárias para esta lista de colunas
ORA-02270: для этого списка-столбца нет подходящего уникального или первичного ключа
ORA-02270: pro tento seznam sloupců neexistuje odpovídající jednoznačný či primární klíč
ORA-02270: takýto jedinečný alebo primárny kľúč neexistuje v odkazovanej tabuľke
ORA-02270: niezgodność klucza unikatowego lub głównego dla tej listy kolumn
ORA-02270: bu sütun listesine karşılık gelen benzersiz veya birincil anahtar yok
ORA-02270: no matching unique or primary key for this column-list
An example will clarify the matter:
create table test1(Now, suppose you want to add a foreign key constraint on TEST2 as follows:
test1_id number,
test1_parent_id number,
test1_child_id number,
col1 varchar2 (30),
constraint test1_pk primary key (test1_id)
)
/
create table test2(
test2_id number,
test1_parent_id number,
test1_child_id number,
col2 varchar2(30),
constraint test2_pk primary key (test2_id)
)
/
alter table test2 add constraint test2_fk
foreign key (test1_parent_id, test1_child_id)
references test1(test1_parent_id, test1_child_id)
/
ORA-02270: no matching unique or primary key for this column-listIndeed there is no unique key (or primary key) on table TEST1 defined for columns test1_parent_id and test1_child_id.
What if we add a unique index?
create unique index test1_uq_idx on test1(test1_parent_id, test1_child_id)
/
alter table test2 add constraint test2_fk
foreign key (test1_parent_id, test1_child_id)
references test1(test1_parent_id, test1_child_id)
/
ORA-02270: no matching unique or primary key for this column-list
Nope, we must add a unique constraint on TEST1 as well, referencing the unique index we've just created:
alter table test1 add constraint test1_uqand repeat
unique (test1_parent_id, test1_child_id)
using index test1_uq_idx
/
alter table test2 add constraint test2_fkand finally succeed.
foreign key (test1_parent_id, test1_child_id)
references test1(test1_parent_id, test1_child_id)
/
Notes:
i created a separate index just to show that it was not sufficient for the purpose, but it can be done at the time of table creation or when the constraint is added at a later time.
create table test2(Creating the indexes separately from the constraints impacts on how Oracle will handle the disabling or dropping of the constraints. For instance, if Oracle created the index automatically, it will also be dropped when you drop or disable the associated constraint, unless you specify specific options like KEEP INDEX (Oracle 9 and above). Also the uniqueness of the index can play a role, because a non-unique index associated with a unique constraint will not be dropped by default (unless you specify DROP INDEX...), whereas a unique index will be.
test2_id number,
test1_parent_id number,
test1_child_id number,
col2 varchar2(30),
constraint test2_pk primary key (test2_id),
constraint test1_uq unique (test1_parent_id, test1_child_id)
)
/
What's the point of having a non-unique index associated with a unique constraint?
Well, it is a requirement for deferrable constraints.
So, as you see, there is room for making things quite complicated.
You can brush up your knowledge about constraints in the following documents:
- Managing constraints in Oracle 8i
- Managing constraints in Oracle 9i
- Managing constraints in Oracle 10G
Another thing worth mentioning is that column order is not important, so we could have swapped the two columns either in the index or in the constraint specification.
Of course this doesn't mean that you should specify the column order randomly, because column order affects the way the optimizer picks indexes, so be sure to design indexes properly.
See message translations for ORA-02270 and search additional resources.
ORA-02270: per questa lista-colonna non vi sono chiavi uniche o primarie corrispondenti
ORA-02270: no hay ninguna clave única o primaria correspondiente para esta lista de columnas
ORA-02270: la llista de columnes no coincideix amb una clau única o primària
ORA-02270: pas de correspondance de clé primaire ou unique pr cette liste de colonne
ORA-02270: kein entsprechender Primär- o. eindeutiger Schlüssel für diese Spaltenliste
ORA-02270: δεν υπάρχει μοναδικό ή πρωτεύον κλειδί που να ταιριάζει για αυτή τη λίστα στηλών
ORA-02270: der er ingen matchende unik eller primær nøgle til denne kolonneliste
ORA-02270: ingen matchande unik eller primär nyckel för kolumnlistan
ORA-02270: det finnes ingen tilsvarende entydig eller primær nøkkel for kolonnelisten
ORA-02270: yksilöivää avainta tai perusavainta ei ole tätä sarakeluetteloa varten
ORA-02270: erre az oszlop-listára nem illeszthető egyedi vagy elsődleges kulcs
ORA-02270: nu exista chei primare sau unice potrivite pentru această listă-coloană
ORA-02270: Geen overeenkomende unieke of primaire sleutel voor deze kolomlijst.
ORA-02270: não há chave exclusiva ou primária compatível para esta lista de colunas
ORA-02270: nenhuma correspondência de chaves exclusivas/primárias para esta lista de colunas
ORA-02270: для этого списка-столбца нет подходящего уникального или первичного ключа
ORA-02270: pro tento seznam sloupců neexistuje odpovídající jednoznačný či primární klíč
ORA-02270: takýto jedinečný alebo primárny kľúč neexistuje v odkazovanej tabuľke
ORA-02270: niezgodność klucza unikatowego lub głównego dla tej listy kolumn
ORA-02270: bu sütun listesine karşılık gelen benzersiz veya birincil anahtar yok
ORA-02270: no matching unique or primary key for this column-list
Making Apex Tabs taste good for Google and other search engines
This is not meant to be a tutorial like how to build a successful a website.
I am assuming that you know well the rules, the tips and techniques to make your website appear in the top 10 list of Google, given certain search keywords ;-)
I want to show you instead how to make your Oracle Application Express powered website easy to be navigated by a web spider, one of those programs that drill down your pages in search for linked URLs.
One of the biggest problem with spider navigation in a typical Apex application is in that in order to move from page to page, one has to click on a tab and built-in templates are designed to submit the page whenever a user clicks on a tab and that is achieved by executing a tiny javascript program.
As far as i know web spiders do not execute javascript code, which means that an application basing on tabs for page navigation will not be analyzed beyond the entry page.
This can be a problem if you hope to catch users coming from search engine queries, but it can be a perfectly valid solution if you want to prevent spiders from wasting your bandwidth.
But assuming that you prefer to pull in new users in any possible way, then you must find a workaround to make hidden pages visible.
One technique i adopted in the past was to provide an alternate access path to the pages, in form of a supplementary link list or as URLs within the text or even with links pointing to the desired pages from external sites. Another possibility is to feed Google with a sitemap, but this is a topic i hope to cover in a dedicated white paper that for some reason is buried in my tray and at this time i am still not 100% convinced that Google is indexing a page URL that exists only in a sitemap.
At any rate, all these are perfectly legitimate solutions.
Lately however, for certain applications, i began to change tab sub-templates and turn them into links.
Here is a sample "Non standard tab sub-template" before the change:
and after the change:
To make all this work, the following conditions must be met:
Updated Sept. 14.
I realize only now that in the ordered list above i forgot to mention what is now numbered at #2.
Without matching names, it is not possible to build a proper link in the template.
See more articles about Oracle Application Express or download tools and utilities.
I am assuming that you know well the rules, the tips and techniques to make your website appear in the top 10 list of Google, given certain search keywords ;-)
I want to show you instead how to make your Oracle Application Express powered website easy to be navigated by a web spider, one of those programs that drill down your pages in search for linked URLs.
One of the biggest problem with spider navigation in a typical Apex application is in that in order to move from page to page, one has to click on a tab and built-in templates are designed to submit the page whenever a user clicks on a tab and that is achieved by executing a tiny javascript program.
As far as i know web spiders do not execute javascript code, which means that an application basing on tabs for page navigation will not be analyzed beyond the entry page.
This can be a problem if you hope to catch users coming from search engine queries, but it can be a perfectly valid solution if you want to prevent spiders from wasting your bandwidth.
But assuming that you prefer to pull in new users in any possible way, then you must find a workaround to make hidden pages visible.
One technique i adopted in the past was to provide an alternate access path to the pages, in form of a supplementary link list or as URLs within the text or even with links pointing to the desired pages from external sites. Another possibility is to feed Google with a sitemap, but this is a topic i hope to cover in a dedicated white paper that for some reason is buried in my tray and at this time i am still not 100% convinced that Google is indexing a page URL that exists only in a sitemap.
At any rate, all these are perfectly legitimate solutions.
Lately however, for certain applications, i began to change tab sub-templates and turn them into links.
Here is a sample "Non standard tab sub-template" before the change:
and after the change:
To make all this work, the following conditions must be met:
- All the default pages pointed to by tabs, must have a page alias.
- Each tab must have the same name as the alias it points to.
- If the application alias substitution string is to be used as in the example above, then the alias must be specified in the application attributes, alternatively the application id substitution string (&APP_ID.) must be used.
- any session items that were set by the page submit, must be passed in the URL. Not all Apex applications may be suitable for this change.
Updated Sept. 14.
I realize only now that in the ordered list above i forgot to mention what is now numbered at #2.
Without matching names, it is not possible to build a proper link in the template.
See more articles about Oracle Application Express or download tools and utilities.
Friday, September 07, 2007
When the label template copy doesn't work
Apex 3.0.1:
if you click on the "edit templates" icon from within a page definition, page 4003 opens up.
It's a report listing all the templates available for the application and on the right hand side there is a handy "copy this template" icon.
It turns out that for Label templates only the copy procedure will not work, although a success message is shown.
This has been confirmed as a bug in version 3.0.1 as per this OTN forum entry where also a quick workaround has been suggested by Carl Backstrom.
if you click on the "edit templates" icon from within a page definition, page 4003 opens up.
It's a report listing all the templates available for the application and on the right hand side there is a handy "copy this template" icon.
It turns out that for Label templates only the copy procedure will not work, although a success message is shown.
This has been confirmed as a bug in version 3.0.1 as per this OTN forum entry where also a quick workaround has been suggested by Carl Backstrom.
Wednesday, September 05, 2007
Removing Apex unreferenced templates
Did you ever noticed that removing unnecessary templates will make the application export file smaller?
I was evaluating the last entries in my to-do list prior to releasing the Global Notification Manager version 1.0 and i realized i had to do some clean up in the templates area as there was plenty of unused templates.
After manually deleting the first two items and seeing that i had still 50 to go, i began to think that perhaps there was some smarter way of doing this repetitive task.
I started off looking for some useful procedure in the package WWV_FLOW_API, an API package where one can find a lot of useful stuff that is not present in the main API packages (the APEX_XXXX ones) and that is granted execution to PUBLIC.
Indeed i soon found a very promising procedure: WWV_FLOW_API.DELETE_TEMPLATE
Unfortunately this procedure is not supported and not documented and, most important, it doesn't work at all in version 3.0.1 and i guess it doesn't work on previous versions either.
But not all is lost.
If you can log on to SQL*Plus as user FLOWS_030000, then you can use AT YOUR OWN RISK, this PL/SQL block and get the job done very quickly.
And don't forget to remove unused themes too!
I was evaluating the last entries in my to-do list prior to releasing the Global Notification Manager version 1.0 and i realized i had to do some clean up in the templates area as there was plenty of unused templates.
After manually deleting the first two items and seeing that i had still 50 to go, i began to think that perhaps there was some smarter way of doing this repetitive task.
I started off looking for some useful procedure in the package WWV_FLOW_API, an API package where one can find a lot of useful stuff that is not present in the main API packages (the APEX_XXXX ones) and that is granted execution to PUBLIC.
Indeed i soon found a very promising procedure: WWV_FLOW_API.DELETE_TEMPLATE
Unfortunately this procedure is not supported and not documented and, most important, it doesn't work at all in version 3.0.1 and i guess it doesn't work on previous versions either.
But not all is lost.
If you can log on to SQL*Plus as user FLOWS_030000, then you can use AT YOUR OWN RISK, this PL/SQL block and get the job done very quickly.
And don't forget to remove unused themes too!
Monday, September 03, 2007
Yet another click counting topic: counting clicks from blogspot
I do not promise this is going to be my final message on the click counting topic for the simple fact that click counting has so many facets!
The problem arose when i decided to log the ip address of the people downloading software from yocoya's web server, but not just those who do the operation from within Apex scope, but also those who perform the operation directly from this blog.
Theoretically there was nothing that prevented me from using Z function directly, however i quickly discovered that owing to a bug of blogger.com (or at least that is what i presume), the resulting link was bogus (it contained one escaped ampersand that i could not get rid of).
But not only. Having the site behind a firewall, the built-in Z function was logging the IP address of the firewall instead of recording the remote IP address. Luckily enough, John Scott provided me with an alternate custom CGI environment variable (a replacement for the native REMOTE_ADDR) where he stores the real remote IP address, so now i had two good reasons for developing my own Z function wrapper.
I do not claim this is the best solution, however it gets the job done very smoothly and allows me to have the data logged in the db where i can easily do whatever i like in terms of statistics, charting and so on. If you read a previous article, you should also be able to retrieve the corresponding domain name quite easily, if any is available.
Last but not least, the download folder is now hidden, so it gives you a minimal form of protection and it makes very easy to relocate the download folder elsewhere without having to go after each and every link in external documents. For additional protection you could even obfuscate this procedure using oracle wrap utility.
I'll not make it longer than necessary, you can download here the source code of this wrapper and make the necessary adjustments for your environment.
One more thing, do not forget to:
As you can see above, in my case it becomes:
As you see it's absolutely trivial, but you are free to make things more complicated ;-)
For instance, the first thing that comes to my mind is that you could use an oracle sequence to keep track of the total number of downloads and store that number in the click id which i left NULL.
By the way, by clicking on the link, you are using the DOWNLOAD_IT procedure in question.
See more articles about Oracle Application Express or download tools and utilities.
The problem arose when i decided to log the ip address of the people downloading software from yocoya's web server, but not just those who do the operation from within Apex scope, but also those who perform the operation directly from this blog.
Theoretically there was nothing that prevented me from using Z function directly, however i quickly discovered that owing to a bug of blogger.com (or at least that is what i presume), the resulting link was bogus (it contained one escaped ampersand that i could not get rid of).
But not only. Having the site behind a firewall, the built-in Z function was logging the IP address of the firewall instead of recording the remote IP address. Luckily enough, John Scott provided me with an alternate custom CGI environment variable (a replacement for the native REMOTE_ADDR) where he stores the real remote IP address, so now i had two good reasons for developing my own Z function wrapper.
I do not claim this is the best solution, however it gets the job done very smoothly and allows me to have the data logged in the db where i can easily do whatever i like in terms of statistics, charting and so on. If you read a previous article, you should also be able to retrieve the corresponding domain name quite easily, if any is available.
Last but not least, the download folder is now hidden, so it gives you a minimal form of protection and it makes very easy to relocate the download folder elsewhere without having to go after each and every link in external documents. For additional protection you could even obfuscate this procedure using oracle wrap utility.
I'll not make it longer than necessary, you can download here the source code of this wrapper and make the necessary adjustments for your environment.
One more thing, do not forget to:
GRANT EXECUTE ON download_it TO APEX_PUBLIC_USERWithout a public synonym the procedure needs to be invoked specifying the schema name.
/
As you can see above, in my case it becomes:
http://www.yocoya.com/pls/apex/yocoya.download_it?p_name=filenameIf you don't want to use the schema prefix, then you can create a public synonym and grant the execute privilege on it.As you see it's absolutely trivial, but you are free to make things more complicated ;-)
For instance, the first thing that comes to my mind is that you could use an oracle sequence to keep track of the total number of downloads and store that number in the click id which i left NULL.
By the way, by clicking on the link, you are using the DOWNLOAD_IT procedure in question.
See more articles about Oracle Application Express or download tools and utilities.
Subscribe to:
Posts (Atom)