tag:blogger.com,1999:blog-180370242024-03-15T11:53:34.948+01:00Annals of Oracle's Improbable ErrorsWelcome to Flavio Casetta's official Oracle database application development related blog.
No Mysql supporters were hurt in the making of this blog.Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.comBlogger426125tag:blogger.com,1999:blog-18037024.post-76455063948737707532024-03-15T11:53:00.000+01:002024-03-15T11:53:00.887+01:00Order of appearance of functions and subqueries in a WITH ... SELECTI don't know if it is the same for you but I tend to forget certain details, that's why often I need to write down some examples in this place to save time for the future.For instance one thing that I tend to forget is the order in which you can specify functions and subqueries inside the same WITH. Functions must come first, then subqueries, if any.Here is a working example, two functions, Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-64622189608893964732024-03-05T15:24:00.002+01:002024-03-05T16:06:41.715+01:00When #OWNER# is not the OWNER you expectedOracle provides developers with a ton of dictionary views, many of which containing a column called OWNER, basically most of the DBA* and ALL* views contain such column, with a few exceptions, for instance compare view ALL_INDEXES with ALL_IND_COLUMNS and try to figure out why the latter comes with a column called INDEX_OWNER while the former comes with a simple OWNER.If you are developing an Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-7749597202560396462024-02-29T11:30:00.000+01:002024-02-29T11:30:49.932+01:00APEX Master Detail side-by-side form ICON_CLASS and ICON_COLOR_CLASS explainedIn case you are wondering what to put in the ICON_CLASS and ICON_COLOR_CLASS columns of the query underlying the left side column of a Master Detail side-by-side form, here is an example.
select "FILE_ID",
null LINK_CLASS,
apex_page.get_url(p_items => 'P210_FILE_ID', p_values => "FILE_ID") LINK,
case
when x.ACTIVE = 'Y' then 'fa fa-badge-check'
else 'fa Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-6999942991959465242024-02-20T08:10:00.000+01:002024-02-20T08:10:19.359+01:00APEX feature request, support needed!I recently submitted the following feature request through the APEX Ideas and feature request application.https://apex.oracle.com/ideas/FR-3624In short, I am asking for the possibility of sub-setting the list of named (private) interactive reports basing on the value of one or more items, thus reducing the size of the list of named reports shown to the end-user as I have an application where the Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-45928303251401272102024-01-16T16:18:00.000+01:002024-01-16T16:18:15.196+01:00ORA-14148: DML and DDL operations are not directly allowed on the guard-column.Say you had a table in you production environment and you had to add a nullable column with a default value.In your test environment however, for some reason, instead of adding the column, you recreated the table, therefore the new column is present since the beginning (in my case this happened because we are in the middle of a migration to a new version of Oracle).Suppose then that you haveByte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-16715628776751148762024-01-12T17:33:00.000+01:002024-02-06T07:50:16.774+01:00Resetting system generated sequence numbers for identity columnsExported and imported a table containing an identity column and the last number conflicts with the data?No problem, just execute the following:
alter table table_name modify column_name generated as identity start with limit value;Oracle will scan the table for the maximum value and set the last number to that number + increment. Thanks to the following article of Jonathan Lewis for explaining Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-35394648150021461702023-12-11T08:10:00.146+01:002024-01-27T13:57:48.238+01:00Getting all the simple combinations of n elements with a query (and what to do next)Last week a colleague of mine came up with an entertaining problem:which invoices, out of a set of 14, when added together, result in a certain total?(image courtesy of T. Plesk)I mean, in comparison with my average tasks, this was definitely an entertaining one. Mathematically speaking the answer to the question turns out to be a practical application of simple combinations of n elements taken kByte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-54541755742140395002023-10-31T10:52:00.004+01:002024-01-27T14:41:21.495+01:00Handling XLIFF documents generated by Oracle APEXXLIFF files have been around since the first version of Oracle APEX as I remember.Even if you are not interested in XLIFF files, you may find
interesting the code below as I am showcasing the usage of many SQL/XML
functions.Recently I wanted to spare some time while translating some of these files, so I decided to brush up my Oracle XMLDB skills that have been put on hold in the last years Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-61124138139384076552023-09-12T16:16:00.000+02:002023-09-12T16:16:18.192+02:00ORA-00942: table or view does not exist on inserting rows with a user other than the table ownerAlways check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
As of Oracle 12.1 it became possible to create tables with IDENTITY columns, that is a numeric column where the value is automatically generated using a system created sequence.In Oracle 12.2, and perhaps also in 12.1, one could also define a numeric column with a default value based on Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-43984794413157098902023-07-31T17:30:00.001+02:002023-07-31T17:30:48.469+02:00In a single-byte character set database non-ASCII characters may cause JSON parse to failAlways check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
declare
js json_object_t;
begin
js := json_object_t.parse('{"name":"$DB_NAME - report GEN - MIELE – Riepilogo domande aiuto"}');
end;
Error report -
ORA-40441: JSON syntax error
ORA-06512: at "SYS.JDOM_T", line 4
ORA-06512: at "SYS.JSON_OBJECT_T", line 86
ORA-06512: at line 4
40441. Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-33968062675971650792023-07-04T12:36:00.001+02:002023-07-04T12:36:53.026+02:00Weird behavior of UPDATE when a SDO_GEOMETRY column needs to be updatedA few days ago I posted a comment in the SQL & PL/SQL forum regarding a strange situation that occurs when updating a SDO_GEOMETRY column with a value resulting from the execution of a user-defined function, i.e.:UPDATE TEST_GEOM
SET SHAPE = MY_FUNCTION(SHAPE)
WHERE ID = 1;It turns out that in the case of a SDO_GEOMETRY column MY_FUNCTION is executed 8 times for each row being Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-26095624687043787502023-05-31T15:29:00.002+02:002023-05-31T15:30:30.192+02:00Handling of non-ASCII characters in JSON objects in a non-AL32UTF8 databaseAlways check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
declare
js json_object_t;
begin
js := json_object_t.parse('
{
"name" : "whatever",
"values" : [
{"name" : "x", "type" : "number", "value" : 2023},
{"name" : "y", "type" : "string", "value" : "some non-ASCII char àùì"}
]
}
');
end;
/
Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-49302851778398561142023-05-30T14:02:00.000+02:002023-05-30T14:02:00.177+02:00Fixing the SRID in a spatial geometry columnAlways check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
CREATE INDEX test_geom_srid_spidx ON test_geom_srid (geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
Error report -
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-76887123793193150252023-03-10T14:05:00.011+01:002023-03-13T10:17:04.199+01:00Yet another curious syntax with little documentation: SELECT FROM RELATIONAL("schema"."table")Have you ever seen this syntax? SELECT * FROM RELATIONAL("SCOTT"."EMP");For tables containing only primitive data types it returns the same results as it would the normal query:SELECT * FROM "SCOTT"."EMP"; For tables containing object data types, however, it splits object's underlying attributes into separate columns:CREATE TABLE GEOM_TABLE(ID NUMBER,GEOM SDO_GEOMETRY);INSERT INTO Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-39714242959236786562023-03-06T13:29:00.004+01:002023-03-06T13:29:49.722+01:00The strange case of ORA-01841 and ADD_MONTHS Today I came across this strange behavior of the function ADD_MONTHS: if I add a number of months between 1 and 11 to the maximum allowed date, that is December 31, 9999, I get NULL as result (!). But if I add 12 or more, I get ORA-01841 error.
SELECT ADD_MONTHS(date'9999-12-31', 1) d FROM DUAL;D======(null)SELECT ADD_MONTHS(date'9999-12-31', 11) d FROM DUAL;D======(null)SELECT ADD_MONTHS(date'Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-48988548992035733492022-10-26T11:30:00.001+02:002022-10-26T11:30:56.223+02:00The strange case of the REVERSE functionAlways check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
Goal: reverse the order of the characters in a string:
select reverse('hello') s from dual;exec dbms_output.put_line(reverse('hello')); S -----ollehError starting at line : 11 in command -BEGIN dbms_output.put_line(reverse('hello')); END;Error report -ORA-06550: line 1, column 28:Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-88278401889340494792022-05-12T14:30:00.000+02:002022-05-12T14:30:08.378+02:00Excel's "The file is corrupt and cannot be opened"I wonder when Microsoft will be able to give some more clues about what Excel doesn't like in an XML file instead of this generic and useless message. It shouldn't be that difficult to write a log and show it to the user or at least display the first n errors, chances are that the remaining ones are of the same kind. Meanwhile, I had to use the binary search approach to Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-25986024567936934582021-11-14T17:34:00.000+01:002021-11-14T17:34:28.195+01:00Oracle XE21C and RHEL8 preinstall rpm wrong address In case you are wondering why you can't download oracle database 21c preinstall file for Red Hat Linux 8 (RHEL8) using the instructions given in the documentation, the reason is quite simple, it's in a different repository.Oracle Installation guide for XE21c says to download the preinstall file from here:
https://yum.oracle.com/repo/OracleLinux/OL8/latest/x86_64/getPackage/Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-73941649893685604682021-06-03T09:59:00.001+02:002021-06-03T09:59:52.455+02:00JSON_QUERY behavior in SQL vs PL/SQL (Oracle 12.2 only?)Frankly speaking I can't say if this is expected behavior for JSON_QUERY but I find it somewhat inconsistent:
set serveroutput on
begin
dbms_output.put_line(json_query('X','$.test'));
end;
/[] select json_query('X', '$.test') v from dual;V ====== (null)
So, on Oracle 12.2 in PL/SQL the string returned is not null, whilst in SQL is null.From a test I made later on Oracle 18c the Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com2tag:blogger.com,1999:blog-18037024.post-16094205234843493912020-06-25T16:22:00.000+02:002020-06-26T09:21:11.817+02:00Oracle Spatial, dynamic SQL, a view and the strange problem of a SDO_GEOMETRY bind variableThe title sound like an old italian joke and indeed I wasted 4 hours on this joke before having the epiphany.Scenario:A set of functions accepting a few parameters, in some cases one of the parameters can be a geometry object, while in other cases it's a CLOB containing a geometry in WKT format.These functions must execute dynamic SQL queries built on some of these parameters.The data suitable Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-63044746293940004902019-12-13T13:35:00.000+01:002019-12-13T13:35:18.223+01:00ORA-40478: output value too large (maximum: )Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
If you are hitting the following error while executing a query containing a JSON_TABLE function like this:
select s.code,
s.date_ins,
to_char(s.date_ins,'HH24:MI') as t,
r.report_name,
fn_tab2str(cast(multiset(select name || ':' || value
Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-33817252524258301252019-10-28T10:58:00.000+01:002020-02-13T09:07:27.145+01:00GRANT INHERIT PRIVILEGESAs per Oracle 12.2 SQL Reference:
GRANT INHERIT PRIVILEGES TO user
"Execute invoker's rights procedures or functions owned by the grantee with the privileges of the invoker when the invoker is the user on whom this privilege is granted."
If there is someone who can translate in plain english the sentence above, please contact me ASAP, I read it a dozen times and still can't make a sense out Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com2tag:blogger.com,1999:blog-18037024.post-40229910833780525042019-10-10T13:55:00.001+02:002019-10-29T17:37:30.679+01:00Pretty print JSON strings in Oracle 12.2 the easy wayAlways check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
It turns out that in Oracle 12.2 you can easily get pretty printed large JSON strings using a query like this:
select json_query(large_json_value, '$' returning clob pretty) as json_clob
from some_table;
Note however that the syntax returning clob pretty is not mentioned in the Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-84705889531005085422019-10-03T21:07:00.000+02:002019-10-03T21:07:31.226+02:00How to rename an oracle scheduler jobAlways check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
If you ever wondered if it is possible to rename a job, be advised that you can execute:
RENAME old_name TO new_name;
Apparently this is the only way to do it as there isn't an ALTER JOB command or a DBMS_SCHEDULER API for doing that and interestingly it's not mentioned in the Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com0tag:blogger.com,1999:blog-18037024.post-85256063792141989752019-07-30T15:53:00.000+02:002019-07-30T15:53:39.874+02:00JSON, the Euro symbol and a WE8MSWIN1252 character set databaseAlways check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
Trying to parse a JSON_OBJECT_T string containing the € (euro) character on a WE8MSWIN1252
12.2.0.1.0 database returns the following error:
declare
j json_object_t;
begin
j := json_object_t.parse('{"currency":"€"}');
end;
/
ORA-40441: JSON syntax error
ORA-06512: at "Byte64http://www.blogger.com/profile/15629209362377395020noreply@blogger.com3