ORA-00998: must name this expression with a column alias

I was attempting to quickly create a view containing some expressions when i got the following error message:

ORA-00998: must name this expression with a column alias

This error is typical of a situation where you are attempting to create a view without specifying the column names after the view name, but you are relying on the column name or the column alias specified in the query, as in this example:

CREATE VIEW test_v AS
SELECT owner || '.' || table_name
FROM all_tables
WHERE owner = USER;

ORA-00998: must name this expression with a column alias

The error is easily fixed as follows:

CREATE OR REPLACE VIEW test_v AS
SELECT owner || '.' || table_name AS qualified_table_name
FROM all_tables
WHERE owner = USER;

or in this other equivalent way:

CREATE OR REPLACE VIEW test_v (qualified_table_name)
AS
SELECT owner || '.' || table_name
FROM all_tables
WHERE owner = USER;

If the view is based mostly on expressions the first approach could be faster or less error prone because you can control the association between the expression and the column name more directly (or so it looks to me...).


See message translations for ORA-00998 and search additional resources.

---

ORA-00998: fornire un nome a questa espressione con uno pseudonimo di colonna
ORA-00998: debe proporcionar un nombre a esta expresión con un alias de columna
ORA-00998: s'ha d'anomenar aquesta expressió amb un àlies de columna
ORA-00998: il faut nommer cette expression avec un libellé de colonne
ORA-00998: Dieser Ausdruck braucht einen Spalten-Alias
ORA-00998: πρέπει να ονομάσετε αυτή την έκφραση με ψευδώνυμο στήλης
ORA-00998: dette udtryk skal navngives med et kolonnealias
ORA-00998: uttrycket måste ges ett kolumnalias
ORA-00998: dette uttrykket må navngis med et kolonnealias
ORA-00998: tälle lausekkeelle on annettava sarakealias
ORA-00998: a kifejezéshez meg kell adni egy oszlop átnevezést
ORA-00998: această expresie trebuie numită cu un alias de coloană
ORA-00998: Deze uitdrukking moet een kolomalias hebben.
ORA-00998: esta expressão deve ser nomeada com um apelido de coluna
ORA-00998: tem que dar um nome a esta expressão com um pseudónimo de coluna
ORA-00998: Вы должны присвоить имя этому выражению, задав псевдоним столбца
ORA-00998: tento výraz je třeba pojmenovat alternativním jménem sloupce
ORA-00998: tento výraz musí mať stĺpcový alias
ORA-00998: wyrażenie to musi być nazwane z użyciem aliasu kolumny
ORA-00998: bu ifade bir sütun diğer adı ile adlandırılmalıdır
ORA-00998: must name this expression with a column alias

ORA-12729: invalid character class in regular expression

I was creating a regular expression using the Regular Expression Workbench for Oracle for checking if an URL was a well formed Oracle Apex URL, when i hit this error:

ORA-12729: invalid character class in regular expression

The error was easily explained: instead of writing [:digit:], i wrote [:digits:].

Here is the complete list of valid character classes:

[:alnum:] All alphanumeric characters
[:alpha:] All alphabetic characters
[:blank:] All blank space characters.
[:cntrl:] All control characters (nonprinting)
[:digit:] All numeric digits
[:graph:] All [:punct:], [:upper:], [:lower:], and [:digit:] characters.
[:lower:] All lowercase alphabetic characters
[:print:] All printable characters
[:punct:] All punctuation characters
[:space:] All space characters (nonprinting)
[:upper:] All uppercase alphabetic characters
[:xdigit:] All valid hexadecimal characters

You can find the Apex URL pattern in the library of available formats, by checking the format radio button.

See message translations for ORA-12729 and search additional resources.

---

ORA-12729: classe caratteri non valida nell'espressione regolare
ORA-12729: clase de carácter no válida en expresión normal
ORA-12729: classe de caràcter no vàlida a l'expressió regular
ORA-12729: classe de caractères non valide dans l'expression régulière
ORA-12729: Ungültige Zeichenklasse in regulärem Ausdruck
ORA-12729: μη αποδεκτή κλάση χαρακτήρων σε τυπική έκφραση
ORA-12729: ugyldig tegnklasse i almindeligt udtryk
ORA-12729: ogiltig teckenklass i reguljärt uttryck
ORA-12729: ugyldig tegnklasse i det regulære uttrykket
ORA-12729: virheellinen merkkiluokka vakiolausekkeessa
ORA-12729: érvénytelen karakterosztály a reguláris kifejezésben
ORA-12729: clasă de caractere nevalidă în expresia logică
ORA-12729: ongeldige tekenklasse in reguliere uitdrukking
ORA-12729: classe de caractere inválida na expressão comum
ORA-12729: classe de caracteres inválida na expressão regular
ORA-12729: недопустимый класс символа в регулярном выражении
ORA-12729: neplatná třída znaků v regulérním výrazu
ORA-12729: neplatná znaková trieda v regulárnom výraze
ORA-12729: niepoprawna klasa znaku w wyrażeniu regularnym
ORA-12729: düzenli ifadede geçersiz karakter sınıfı
ORA-12729: invalid character class in regular expression

ORA-19025: EXTRACTVALUE returns value of only one node

This error occurs when you try to extract scalar values from an XML fragment containing multiple nodes.
In order to simulate error, try executing the following query in a schema where you have at least two tables:

SELECT extractvalue(
     xmltype.createXML(
      dbms_xmlgen.getxml('select * from user_tables'))
     ,'/ROWSET/ROW/TABLE_NAME')
FROM dual;

ORA-19025: EXTRACTVALUE returns value of only one node

Indeed if you add a small XPath condition to the query above, you can get a valid result:

SELECT extractvalue(
     xmltype.createXML(
      dbms_xmlgen.getxml('select * from user_tables'))
     ,'/ROWSET/ROW[1]/TABLE_NAME')
FROM dual;

This query will extract the table name of the first TABLE_NAME node.

If you are trying to extract all table names from this XML file, then you must rewrite the query as i showed in a previous topic.

See message translations for ORA-19025 and search additional resources.

---

ORA-19025: EXTRACTVALUE restituisce il valore di un solo nodo
ORA-19025: EXTRACTVALUE devuelve un valor de un solo nodo
ORA-19025: EXTRACTVALUE retorna el valor de només un node
ORA-19025: EXTRACTVALUE renvoie la valeur d'un seul noeud
ORA-19025: EXTRACTVALUE gibt Wert von nur einem Knoten zurück
ORA-19025: Η τιμή EXTRACTVALUE επιστρέφει τιμή με μόνο ένα κόμβο
ORA-19025: EXTRACTVALUE returnerer kun værdi for én node
ORA-19025: EXTRACTVALUE returnerar värdet för bara en nod
ORA-19025: EXTRACTVALUE returnerer en verdi for bare én node
ORA-19025: EXTRACTVALUE palauttaa vain yhden solmun arvon
ORA-19025: Az EXTRACTVALUE csak egy csomópont értékét adja vissza.
ORA-19025: EXTRACTVALUE returnează valoarea pentru un singur nod
ORA-19025: EXTRACTVALUE retourneert waarde van slechts één node.
ORA-19025: EXTRACTVALUE retorna valor de apenas um nó
ORA-19025: EXTRACTVALUE devolve o valor de um único nó
ORA-19025: EXTRACTVALUE возвращает значение только одного узла
ORA-19025: EXTRACTVALUE vrací pouze hodnotu jednoho uzlu
ORA-19025: EXTRACTVALUE vracia hodnotu len jedného uzla
ORA-19025: EXTRACTVALUE zwraca wartość tylko jednego węzła
ORA-19025: EXTRACTVALUE sadece tek bir düğümün değerini döndürür
ORA-19025: EXTRACTVALUE returns value of only one node

Comparing the result of two queries using set operators

As you certainly know, set operators (intersect, union, union all, minus) allow to perform powerful tasks in an Oracle database.

As i am greatly deeply involved in the development of applications based on Oracle Application Express, you'll forgive me if i exploit this opportunity to use this generic technique to solve a very practical problem:
to compare two versions of the same components of the same application.

Note that the SQL statement i am going to use can be easily adapted to any other similar problem, like comparing two price lists of two different stores or checking the objects belonging to two different schemas.

So, back to my apex application comparison problem, this rather pesky job can be greatly simplified using Oracle database's built-in features since the introduction of the public views for reading the content of Apex's repository.

Suppose that i have a large application containing dozen of pages
I can quickly isolate the pages present in the first application but not in the second with the following query:

select page_id, page_name from apex_application_pages
where application_id = 21670
minus
select page_id, page_name from apex_application_pages
where application_id = 459;

Likewise i can easily drill down the page content and quickly identify the new or missing page items (depending on whether the newer version comes first or last):

select page_id, page_name, item_name, displays_as from apex_application_page_items
where application_id = 21670
minus
select page_id, page_name, item_name, displays_as from apex_application_page_items
where application_id = 459;

Note, as i said before that the order in which you process the two queries is important, so if application 21670 is older than 459, so, presumably containing fewer items, the result set may be empty. This means that if you want to show the newest additions, you should swap the application_ID values:

select page_id, page_name, item_name, displays_as from apex_application_page_items
where application_id = 459
minus
select page_id, page_name, item_name, displays_as from apex_application_page_items
where application_id = 21670;

It's also important to decide which columns must be taken into account in the comparison, if case sensitivity is important or any other details that can make a difference for you, for instance, in the following query i want to check if the page item type is the same or if the source is the same, regardless of the letter case:

select page_id, page_name, item_name, display_as, lower(item_source) as source
from apex_application_page_items
where application_id = 21670
minus
select page_id, page_name, item_name, display_as, lower(item_source) as source
from apex_application_page_items
where application_id = 459;

So, as you see, you can do this kind of comparisons very easily and on arbitrary number of columns, applying the desired "noise filtering" technique.

Converting XML leaf nodes into relational data

If you, like me, tend to forget how to build certain non-trivial SQL statements involving XML functions, you won't regret if i post here a sort of memorandum on how to extract data from the leaf nodes of an XML document.

SELECT extractvalue(value(d),'/TABLE_NAME') as TABLE_NAME
FROM dual e,
   table(
    xmlsequence(
     extract(
      xmltype.createXML(
       dbms_xmlgen.getxml('select * from user_tables')),
      '/ROWSET/ROW/TABLE_NAME')
     )
   ) d;

The query above extracts the text node under the TABLE_NAME element in an XML file containing the canonical representation of a table, as it would exported from Apex invoking the export table utility.

In order to simulate the output of Apex, i used packaged function DBMS_XMLGEN.GETXML.

The key components of the query are highlighted in bold typeface.
Function extract returns a set of nodes that is, in other words, an XML fragment.
An XPath expression identifies the desired nodes (/ROW/ROWSET/TABLE_NAME).
Function xmlsequence takes the node-set and converts it into a varray of XMLtype.
Once i have joined the source table with the nested array of nodes, i can finally apply function extractvalue that converts nodes into scalar values, that is simple oracle types.

Note also that i had to explicitly convert the document from CLOB type into XMLType.

Updated January 12, 2008.
i forgot to explain why i put DUAL table in my example: here DUAL represents the outer table that will be probably present in the real life. For instance, here is a similar statement i've created today for extracting translations from an XML file (in XLIFF format), that is stored in a staging table, that i called XML_IMPORTS:

select extractvalue(value(d),'/trans-unit/source') as source,
      extractvalue(value(d),'/trans-unit/target') as target
from xml_imports x,
    table(xmlsequence(extract(x.xmlfile, '/xliff/file/body/trans-unit'))) d
where x.id = 31

And now for something completely different

I know this is off topic, but with the title this blog bears, i cannot just ignore the event:
find out who are the winners of the 2007 IGNobel prizes!

Let me pick among these valuable researches the gay bomb, the only weapon that can truly achieve the famous slogan: make love not war.
Great, it looks like we can now make both at the same time!

Cheers,
Flavio

Apex, XE and HTTP 403 forbidden with stored procedures

This is the scenario:

you have Oracle XE installed along with Apex version 2.1 or above and you are trying to execute a stored procedure directly from the URL (exploiting the HTTP binding performed automatically by the embedded PL/SQL gateway of XE, similar to mod_plsql) like:

http://127.0.0.1/apex/schema.test

or

http://127.0.0.1/apex/test

if using a public synonym.

When you submit your request, the browser comes up with a similar screen:

Forbidden
The requested operation is not allowed

When i first hit this error message i contacted John Scott who quickly mentioned the name of the function i had to look at:

wwv_flow_epg_include_mod_local

With this function name i searched the web and i stumbled immediately on Dietmar Aust's blog entry, where he clearly explains how to fix the problem and puts a link to the Oracle documentation dealing with the subject.

Thanks Dietmar!

PS: please note that if you misspell the procedure name or if you forget to grant execute privilege to ANONYMOUS (see in the comments section) or to PUBLIC or you are trying to call the procedure name without the schema prefix and without a public synonym, you won't get HTTP 403 error (forbidden) but HTTP 404 (page not found), so if you get HTTP 403 it means that the procedure was actually found but did not pass the wwv_flow_epg_include_mod_local filter.

ORA-29289: directory access denied

You attempted to perform an operation like that contained in the following script:

declare
file_handle            utl_file.file_type;
begin
file_handle  := utl_file.FOpen('TEST_DIR', 'test.txt', 'w');
utl_file.put_line(file_handle, 'test');
utl_file.FFlush(file_handle);
utl_file.FClose(file_handle);
end;
/

ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at line 4

Note that we opened the file for writing, but the same applies when the file is opened for reading.

The error message simply means that you are not authorized to write (or read) in the target directory that is you lack the WRITE privilege (or the READ privilege) on the directory object being used.
The good news is that directory TEST_DIR is actually existing, otherwise you'd get ORA-29280.

You can fix the problem by asking the DBA to issue:

GRANT READ ON DIRECTORY TEST_DIR TO username
-- or
GRANT WRITE ON DIRECTORY TEST_DIR TO username

This error message means also that you do not hold the CREATE ANY DIRECTORY privilege, because if you held it, you could read and/or write to any directories that you created or even to those created by other users.

Without CREATE ANY DIRECTORY privilege, another user must create the directories for you (typically the DBA) and explicitly grant read and/or write privileges before you can successfully open a file located there.

You can easily determine if you are allowed to create directory objects yourself:

select privilege from user_sys_privs
/

PRIVILEGE
---------
CREATE PROCEDURE
CREATE JOB
CREATE TYPE
CREATE SYNONYM
CREATE CLUSTER
CREATE TABLE
CREATE ANY DIRECTORY
CREATE VIEW
CREATE SESSION
CREATE TRIGGER
CREATE SEQUENCE
CREATE MATERIALIZED VIEW

So, with this system privilege, it's possible to execute the following script without the need of asking the DBA to grant the write permission on TEST_DIR:

create directory test_dir as 'C:\Temp'
/* replace C:\Temp with any suitable path for your platform or environment */
/
declare
file_handle            utl_file.file_type;
begin
file_handle  := utl_file.FOpen('TEST_DIR', 'test.txt', 'w');
utl_file.put_line(file_handle, 'test');
utl_file.FFlush(file_handle);
utl_file.FClose(file_handle);
end;
/

Without the CREATE ANY DIRECTORY privilege, the first statement would fail with ORA-01031: insufficient privileges.

Note also that directory names specified in UTL_FILE procedures are case sensitive.
See the posting on ORA-29280 for further examples on this subject.

Last but not least, once you pick the directory object method for specifying file system paths for read/write operations, you no longer need to worry about the setting of parameter UTL_FILE_DIR.

See message translations for ORA-29289 and search additional resources.

---

ORA-29289: accesso alla directory negato
ORA-29289: acceso al directorio denegado
ORA-29289: s'ha denegat l'accés al directori
ORA-29289: accès refusé au répertoire
ORA-29289: Verzeichniszugriff abgelehnt
ORA-29289: δεν επιτρέπεται η πρόσβαση στον κατάλογο
ORA-29289: katalogadgang nægtet
ORA-29289: åtkomst till katalogen nekad
ORA-29289: ingen tilgang til katalog
ORA-29289: ei hakemiston käyttöoikeutta
ORA-29289: a könyvtárhoz való hozzáférés megtagadva
ORA-29289: accesarea directorului a fost refuzată
ORA-29289: Toegang tot directory is geweigerd.
ORA-29289: acesso ao diretório negado
ORA-29289: acesso ao directório recusado
ORA-29289: отказ в доступе к каталогу
ORA-29289: přístup k adresáři byl zamítnut
ORA-29289: prístup do adresára odmietnutý
ORA-29289: odmowa dostępu do katalogu
ORA-29289: dizine erişim reddedildi
ORA-29289: directory access denied

dbms_utility.get_parameter_value

Don't take me wrong, i know that this topic is no-brainer, but you must understand that the function of this blog is like that of a swiss-knife: i want a unique place where i know i can quickly find what i need, wherever i am.

In this case i am referring to one of the most useful API functions of Oracle, a function that allows you to peek at the value of an initialization parameter even if you are not the DBA.
In fact if you do not hold DBA rights or select rights on dynamic view V$PARAMETER, you won't be able to query that view and here is where dbms_utility.get_parameter_value comes in handy.

Although the link is pointing to the 10R2 documentation set, i'm fairly sure (correct me if i am wrong) that this function underwent no significant changes since version 8i and the PL/SQL sample code included in the Example section is ready-to-use.

For instance, this morning i wanted to check on a remote db where i have no special rights if parameter UTL_FILE_DIR was set or not:

DECLARE
 parnam VARCHAR2(256);
 intval BINARY_INTEGER;
 strval VARCHAR2(256);
 partyp BINARY_INTEGER;
BEGIN
 partyp := dbms_utility.get_parameter_value('utl_file_dir',
                                           intval, strval);
 dbms_output.put('parameter value is: ');

 IF partyp = 1 THEN
  dbms_output.put_line(strval);
 ELSE
  dbms_output.put_line(intval);
 END IF;

 IF partyp = 1 THEN
  dbms_output.put('parameter value length is: ');
  dbms_output.put_line(intval);
 END IF;

 dbms_output.put('parameter type is: ');

 IF partyp = 1 THEN
  dbms_output.put_line('string');
 ELSE
  dbms_output.put_line('integer or boolean');
 END IF;
END;

The name of the parameter can be specified in either lowercase or uppercase.
Should the parameter name be wrong, the following error will be raised:

ORA-20000: get_parameter_value: invalid or unsupported parameter ...

Thanks for your patience, i'll be back shortly with some more intriguing Oracle situations :-)

Updated October 2
As Jacques noted, the parameter name must be supplied in lowercase for Oracle versions prior to 10G.