Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 3 months 6 days ago
Creating hierarchy using a parent child table
hello guys,
I'm trying to create hierarchy using a parent child table for analytic view but I'm struggling.
the steps:
1. create a attribute dimension using the table --> (id,name,parent_id)
(I succeeded but idk the correct way for the parent child hierarchy).
2.create a hierarchy using the attribute dimension from previous step,
(i succeeded to create a regular hierarchy that isn't based on parent child)
idk how to do it, tried 3 chatbots and also oracle docs with no answer.
im doing this on toad 19 - oracle 19
hope someone can help me.
thanks!
Categories: DBA Blogs
plsql gateway procedure on Oracle Autonomous Database
Ask Tom:
I would like to migrate a PL/SQL Web Toolkit application from an on-prem database instance to the Oracle Autonomous Database. I see references to the PL/SQL Gateway via ORDS (I think) in documentation and administration screens but I'm struggling to connect the dots. Most documentation and articles I find focus on the ORDS APIs but not a simple HTTP to PL/SQL connection.
I have created a User, CAAT_CODE, and can create a procedure through the web-based SQL Developer. I want to execute a procedure something like
<code>
create or replace procedure gateway_test
as
begin
htp.print( '<html><body>Hello, World.</body></html>' );
end;
/
</code>
with a URL that looks like
https://g3958a9838612f9-caat.adb.us-chicago-1.oraclecloudapps.com/ords/caat_code/gateway_test
So far, all I get is the HTTP 404, not found error.
Please suggest documentation or a set of steps to configure the gateway in the Oracle Autonomous Database environment or let me know that what I'm trying to do is not supported.
Thanks for many years of Ask Tom. I have found it to be valuable in many Oracle development cases.
John Jeunnette
johnjeunnette@prairiesystemsgroup.com
Categories: DBA Blogs
Inability to Export/Import our APEX APP
Hi everyone,
We?re developing an APEX app that handles Excel files, and we?ve run into a problem when trying to export the app from one Workspace and import it into another.
<b>The main issue:
When we export the app and attempt to import it into a different Workspace, the import fails with various errors, and ultimately the app does not load.</b>
Has anyone experienced similar issues or have any advice on how to resolve this? Being able to seamlessly transfer the app across Workspaces would greatly improve our workflow.
Additional details:
We export the entire application using default export options.
During import, we select ?Import as Application? in the Import Wizard.
After some loading time, an error appears?although the exact error message varies. Examples include:
- <b>Internal Server Error - Write</b>
The server encountered an internal error or misconfiguration and was unable to complete your request.
Reference #4.73a2f17.1748513046.f1d44d4
https://errors.edgesuite.net/4.73a2f17.1748513046.f1d44d4
- <b>Execution of the statement was unsuccessful. ORA-02091: transaction rolled back</b>
<code>
begin wwv_flow_imp.import_end(p_auto_install_sup_obj => nvl(wwv_flow_application_install.get_auto_install_sup_obj, false));
commit;
end;
</code>
We encounter the same problem even when exporting and importing a single page into a blank app. Clicking ?Install Page? triggers the same ORA-02091 error as above.
Any guidance, tips, or workarounds would be highly appreciated. Thanks so much for your help!
Categories: DBA Blogs
Additonal help with JSON object - Add/Remove items from array within a JSON object
I thought I could take what Chris showed me in my original question (https://asktom.oracle.com/ords/asktom.search?tag=looking-for-help-with-json-object).
However, after 2 days, I have not made any progress.
The JSON data -
{
"recall_control":
[
{
"item_id":"item1",
"item_cd1":"CA",
"item_cd2":"AP",
"item_cd3":"CO",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL3", "VAL5"]
},
{
"item_id":"item2",
"item_cd1":"CA",
"item_cd2":"AP",
"item_cd3":"EX",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL2"]
},
{
"item_id":"item3",
"item_cd1":"CA",
"item_cd2":"TW",
"item_cd3":"CO",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL2", "VAL3"]
},
{
"item_id":"item4",
"item_cd1":"CA",
"item_cd2":"TW",
"item_cd3":"EX",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL2", "VAL4"]
}
]
}
With Chris's help - I can get the item_id values
<b><code>with jdata as (
select treat ( '{
"recall_control": [
{
"item_id":"item1",
"item_cd1":"CA",
"item_cd2":"AP",
"item_cd3":"CO",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL3", "VAL5"]
},
{
"item_id":"item2",
"item_cd1":"CA",
"item_cd2":"AP",
"item_cd3":"EX",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL2"]
},
{
"item_id":"item3",
"item_cd1":"CA",
"item_cd2":"TW",
"item_cd3":"CO",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL2", "VAL3"]
},
{
"item_id":"item4",
"item_cd1":"CA",
"item_cd2":"TW",
"item_cd3":"EX",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL2", "VAL4"]
}
]
}' as json ) j from dual
)
select json_query ( j,
'$.recall_control[*] ? (
exists ( @.recall_list[*] ? ( @ == "VAL3" ) )
).item_id' with wrapper ) items
from jdata;</code>
ITEMS
-----------------
["item1","item3"]</b>
I took what he gave me and over the last 2 days I have tried to figure out the second half of my issue. How to ADD/REMOVE items from the RECALL_LIST array based on the values obtained in the original query.
I thought I could use the JSON_VALUE method to find where ITEM_ID = "item1" and create an update query to remove VAL3 from the array or add VAL6 to it. However, the query returns now rows updated.
I tried to use Chris's search advice - <b>"You can do this by nesting search expressions, first to locate the recall_control. Then using the exists method to see search the recall_list array. e.g.:"</b>
That didn't work.
What a...
Categories: DBA Blogs
Deadlock while doing parallel operation
Hello,
I am executing a package on multiple threads. Our package is for deleting data in bulk.
Whenever I am running the package on multiple threads I am getting a deadlock.
On checking the trace file I found the delete statement which is causing deadlock. But the graph shows as
Rows waited on:
Session 38: no row
Session 70: no row
So I am not understanding the root cause of this deadlock.
There are indexes on all the foreign key columns for the table being deleted.
The deletion is based on primary key as well.
Can you please help what could be the probable issue?
Categories: DBA Blogs
Oracle.DataAccess.Client.OracleException ORA-03135: connection lost contact
I am using the following
IIS Server for App server.
ODP.Net
Oracle 10.2.0
After 30 minutes of idle time i get the error ORA-03135: connection lost contact.
The Error is same even if I use Sql*plus.
I asked the network group to increase firewall to 4 hours which they did increase it on port 1521.
I am not sure of the return ports time out values can be set.
Since it?s a web service on IIS, the w3w.exe opens an active connection and we have default settings on connection pooling, so one connection is maintained.
After 30 minutes idle on the next call, I get the error. I tried the same with Sql*plus and I do get the same error.
Is there a way or work around for the same?
Appreciate your help.
Categories: DBA Blogs
With Oracle EBR, creating DML triggers when foreign keys are present
Dear Tom,
I have a question related to Oracle EBR.
We are using Oracle 19C (Enterprise Edition) & now moving to be EBR compliant.
Currently we do have DML triggers defined on tables.
These table have foreign key constraints defined with cascade on delete option.
For instance, ORDER_LINE_TABLE has a foreign key referring to ORDER_TABLE with cascade when delete (deleting a record in ORDER_TABLE).
As per the Oracle guidelines, the recommended approach for creating triggers in general seems to be define them on editioning views (EVs), instead on real tables, for many good reasons.
But in this case, it is not possible to create the triggers on EVs due to cascade operation initiated by FK constraint is performed at real table level, thus not visible to triggers on the EV.
This limitation has been mentioned by Oren Nakdimon in below document:
https://db-oriented.com/2025/01/08/ebr-part-13-the-trouble-with-foreign-keys-with-on-delete-clause-and-related-triggers/
As a workaround for this, we have been testing on defining the DML triggers on the real tables, in this like scenarios. But we see some challenges when they are present especially while using Cross Edition (XE) Triggers during upgrades. Since XE triggers are operating on real table level, DML triggers would see those DML operations happening on real tables, hence, it is hard for us to have an isolation in upgrade period.
Even the above document says, I doubt it is a rare combination of having FKs with cascade & having DML triggers.
Do you have a better approach to define DML trigger in such scenarios, instead of the approach I mentioned above?
Thank you & Kind Regards,
Navinth
Categories: DBA Blogs
Connection lost with ORA-01335 with ODA server
Hello,
We have migration project form Oracle database 11g in Exadata server, to 19c in ODA server, when we tested the query in the new server with client tool such as Sql developper, Sqlplus and Sqlcl, the connection was lost after 1 hour of inactivity, the only difference between Exadata and ODA is in the network architecture, for the traceroute Exadata, we take switch A, but for ODA we, take firstly Firewall, after switch b to come to ODA, we increase timeout in Firewall for the port 1521 to 24h but the same issue. When we try to connect ton ODA directly, there is no timeout.
I added this ligne: SQLNET.EXPIRE_TIME = 10, in my sqlnet file, and seems work perfectly, my question is it the right solution, or there is another solution more efficnecy. If it is the only solution, I need to do others configurations for the connection doesn't use sqlnet, such as SSIS that use ODBC, and others that use JDBC.
Thanks in advance
Categories: DBA Blogs
Database DDL trigger
We use OCI GoldenGate Microservice to replicate tables from Oracle Database to BigData.
I was tasked to create a process to replicate any "ALTER TABLE ADD COLUMN" statements to let bigdata team modify schema safely.
Tested solution using database trigger in dedicated schema in test environment was rejected because
1. Triggers are evil
2. Database patching can have unpredicted consequences having database trigger. I wonder if that is true.
I used similar trigger to log my own DDL for journaling for 5 years in a database which passed through multiple patching without any issues.
Still, my experience is not statistically correct prove.
My colleague suggested using use aud$unified table, but that involves creating procedure executed by frequently running scheduled job because releases are not 100 % regular.
Are there better approach?
With regards,
Alex
Here is the setup:
prompt create table utility.ddl_log
--drop table utility.ddl_log purge;
create table utility.ddl_log (
ddl_time timestamp(6) default systimestamp not null
ddl_by varcha2(30) default sys_context('userenv', 'current_user')
, os_user varchar2(100) default sys_context('userenv','os_user')
, host varchar2(100) default sys_context('userenv','host')
, ip_address varchar2(100) default sys_context('userenv','ip_address')
, module varchar2(100) default sys_context('userenv','module')
, terminal varchar2(100) default sys_context('userenv','terminal')
, operation varchar2(100)
, owner varchar2(50)
, object_name varchar2(50)
, object_type varchar2(50)
, sqltext clob
);
create or replace trigger utility.after_gg_table_ddl
after alter on database
declare
l_2run binary_integer := 0;
l_sql_id varchar2(16);
l_sql clob;
begin
--p('ora_dict_obj_name = '||ora_dict_obj_name);
select count(*)
into l_2run
from dba_users u
where 1 = 1
and profile = 'APP_USER' -- only application schemas to exclude any other schemas
and ora_dict_obj_type = 'TABLE'
and exists (
select 1
from all_log_groups a
where a.owner = u.username
and a.table_name = ora_dict_obj_name
and log_group_type is not null
)
and ora_dict_obj_owner = u.username
;
--p('l_2run = '||to_char(l_2run)||'; sid = '|| sys_context('userenv','sid'));
if l_2run > 0 then
select sql_id
into l_sql_id
from gv$session
where sid = sys_context('userenv','sid')
and rownum = 1
;
select lower(sql_fulltext)
into l_sql
from gv$sql
where sql_id = l_sql_id
and rownum = 1; --to be on the safe side as many sessions and children can have the same sql_id
-- This part is not required
insert into utility.ddl_log (operation, owner, object_name, object_type, sqltext, prevsqltext)
values (ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, ora...
Categories: DBA Blogs
Looking for help with JSON object
Attempting to learn and make use of JSON data within a table. Stuck on the following and would really appreciate some help...
Looking for help to create a list of ITEM_IDs where a match is found in the JSON object.
Sample column data - (column: recall_control_json)
<code>{
"recall_control":
[
{
"item_id":"item1",
"item_cd1":"CA",
"item_cd2":"AP",
"item_cd3":"CO",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL3", "VAL5"]
},
{
"item_id":"item2",
"item_cd1":"CA",
"item_cd2":"AP",
"item_cd3":"EX",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL2"]
},
{
"item_id":"item3",
"item_cd1":"CA",
"item_cd2":"TW",
"item_cd3":"CO",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL2", "VAL3"]
},
{
"item_id":"item4",
"item_cd1":"CA",
"item_cd2":"TW",
"item_cd3":"EX",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL2", "VAL4"]
}
]
}</code>
Trying to write the query that would return a list of ITEM_IDs where the RECALL_LIST contains VAL3 or getting help from others in learning why it can't be done and/or how it could be done.
So far, I have been able to return all the ITEM_ID values with this query --
<code>select json_query(rt.recall_control_json, '$.recall_control.item_id' With wrapper) as recallList from recallTbl rt
where recall_id = 502;</code>
RETURNS -- ["item1","item2","item3","item4"]
But I have not been able to update the where clause so that I only get the ones I need
Categories: DBA Blogs
Setup Apex 22.1 email with MS O365 Outlook SMTP
I am trying to integrate Microsoft Office365 Outlook's SMTP with Oracle Apex 22.1(Running on Amazon RDS for Oracle 19c).
I have used the following configuration on Apex:
SMTP server name: smtp.office365.com
SMTP port: 587
Username: <my-email-id@outlook.com>
Password: <generated App password after 2 factor authentication>
use TLS: Yes
I have uploaded the correct MS root and intermediate certificates to the auto-login wallet and created required Netwrok ACLs.
When I try to send email using Apex (SQLPLUS), I do not get any error but the email never reaches the inbox instead the MAIl QUEUE in Apex gets populated.
begin
apex_util.set_security_group_id (p_security_group_id => 5140893825405459);
APEX_MAIL.SEND(p_from => 'awsrahul2024@outlook.com',
p_to => 'awsrahul2024@outlook.com',
p_subj => 'Test Mail2',
p_body => 'This is a test email');
apex_mail.push_queue();
END;
/
PL/SQL procedure successfully completed.
When I try to validate the email configuration, I get the following error.
SQL> begin
APEX_INSTANCE_ADMIN.validate_email_config;
end; 2 3
4 /
begin
*
ERROR at line 1:
ORA-29279: SMTP permanent error: 535 5.7.139 Authentication unsuccessful, basic
authentication is disabled. [BM1P287CA0002.INDP287.PROD.OUTLOOK.COM
2025-05-21T21:47:12.528Z 08DD989AFE00C8F6]
ORA-06512: at "APEX_220100.WWV_FLOW_MAIL", line 1284
ORA-06512: at "SYS.UTL_SMTP", line 57
ORA-06512: at "SYS.UTL_SMTP", line 142
ORA-06512: at "SYS.UTL_SMTP", line 446
ORA-06512: at "APEX_220100.WWV_FLOW_MAIL", line 1268
ORA-06512: at "APEX_220100.WWV_FLOW_MAIL", line 2588
ORA-06512: at "APEX_220100.WWV_FLOW_INSTANCE_ADMIN", line 1671
ORA-06512: at line 2
Kindly advise. Thank you!
Categories: DBA Blogs
unique index
from the oracle10g concepts guide
"Oracle recommends that unique indexes be created explicitly, and not through
enabling a unique constraint on a table.
Alternatively, you can define UNIQUE integrity constraints on the desired columns.
Oracle enforces UNIQUE integrity constraints by automatically defining a unique
index on the unique key. However, it is advisable that any index that exists for
query performance, including unique indexes, be created explicitly."
what's the difference between creating unique constraint while creating table
(any way unique constraint will create unique index to enforce integrity)
and creating unique index explicitly?
Categories: DBA Blogs
MYSQL Custom Function Return issue
With?row?based replication?(binlog_format = ROW), the function generates duplicate sequence values; in?mixed?mode (binlog_format = MIXED), no duplicates occur. Using AWS RDS 8.0.22
CREATE DEFINER=abc@% FUNCTION nextval(seq_name varchar(100)) RETURNS bigint
BEGIN
DECLARE cur_val bigint(20);
SELECT
sequence_cur_value INTO cur_val
FROM
sequence_data
WHERE
sequence_name = seq_name
;
IF cur_val IS NOT NULL THEN
UPDATE
sequence_data
SET
sequence_cur_value = IF (
(sequence_cur_value + sequence_increment) > sequence_max_value,
IF (
sequence_cycle = TRUE,
sequence_min_value,
NULL
),
sequence_cur_value + sequence_increment
)
WHERE
sequence_name = seq_name
;
END IF;
RETURN cur_val;
Categories: DBA Blogs
ORA-12637: RÚception du paquet impossible
Hello ,
Hello, I'm having a rather strange problem:
A single Windows user is unable to connect to a database hosted on the WINDOWSPROD server. The error is: ORA-12637: Unable to receive packet; in the alert.log:
ORA-00609: Could not attach to incoming connection
ORA-12699: Native service internal error
The same user can connect to the database hosted on the WINDOWSPROD server. Both servers are in the same domain.
Does anyone have any ideas?
Categories: DBA Blogs
Need to get match and unmatch output for all users having profile and one role
Dear Tom,
I have a question. I have a profile say myprofile in dba_users view.
I'm selecting how many users having this myprofile
select username from dba_users where profile='MYPROFILE';
User1
User2
User3
Now I want to check whether all this 3 users have Connect role or not from dba_role_privs.
Suppose User1 and User2 are granted Connect role so how to check that in single query along with unmatched one which is User3.
Thanks
Nite
Categories: DBA Blogs
resolution of functions in query
Hello,
In this query :
<code>select id, to_char(txn_date, 'yyyymmdd'), count(*)
from table1
where to_char(txn_date, 'yyyymmdd') > '20250101'
group by id, to_char(txn_date, 'yyyymmdd')
order by to_char(txn_date, 'yyyymmdd');</code>
Will the to_char(txn_date, 'yyyymmdd') be resolved four times or only once ?
If four times, then will the below query be more efficient ?
<code>with q1 as
(
select /*+ materialize */
id, to_char(txn_date, 'yyyymmdd') dt_yyyymmdd
from table
where to_char(txn_date, 'yyyymmdd') > '20250101'
)
select q1.id, q1.dt_yyyymmdd, count(*)
from q1
group by q1.id, q1.dt_yyyymmdd
order by q1.dt_yyyymmdd;</code>
Thank you for your time and expertise.
Categories: DBA Blogs
architecting new apex environment
I"m looking for guidelines on how to architect a
new APEX solution for migrating legacy
Oracle reports to APEX.
We have 67 seperate municipal government entities
in 67 seperate Oracle instances. Some are 12c some
are 19c. They are spread across 4 servers.
Table structures are identical across all 67
instances.
Do we need to install APEX in all 67 instances?
How would we configure ORDS?
Can anyone point me to any whitepapers and/or
use cases for how to architect the best
APEX framework for this ?
Categories: DBA Blogs
Best practice for rounding issue with date math.
I ran into this problem with 1% of our records converting views from SQL Server to Oracle where my testing found different results when doing a compare during UAT.
We have a system that needs to express time rounded to tenths of hours because of industry norms in aviation.
In the example below you can see I am finding the difference between two dates, but the result seems to be rounding incorrectly. Normally to test these issues is to try converting the output to a char to see if there is an unseen precision issue.
In this case, I know that Oracle is computing the RAW_DIFF value as .649999999 and is the reason it appears to round incorrectly. What I found surprising is that RAW_CHAR did not return .649999999.
<b>My question is what is the best practice for avoiding this case because I can see how an analyst writing a query would expect ROUND(<val>,1) to just work since this is a 1% kind of issue?
</b>
START_DATE 5/7/2025 10:46:00 PM
START_CHAR 2025-05-07 22:46:00
END_DATE 5/7/2025 10:07:00 PM
START_CHAR 2025-05-07 22:07:00
RAW_DIFF 0.65
RAW_CHAR .650000000000 -- actually .649999999 depending on the query tool
SINGLE_ROUND 0.6
DOUBLE_ROUND 0.7
<code>
create table ROUND_TEST
(
id NUMBER generated always as identity,
start_date DATE,
end_date DATE
);
insert into round_test
(start_date, end_date)
values
(to_date('5/7/2025 10:46:00 PM','MM/DD/YYYY HH:MI:SS PM'), to_date('5/7/2025 10:07:00 PM','MM/DD/YYYY HH:MI:SS PM'));
select t.start_date,
to_char(t.start_date, 'YYYY-MM-DD HH24:MI:SS') start_char,
t.end_date,
to_char(t.end_date, 'YYYY-MM-DD HH24:MI:SS') end_char,
(t.start_date - t.end_date) * 24 as raw_diff,
to_char((t.start_date - t.end_date) * 24,'9,999.999999999999') as raw_char,
round((t.start_date - t.end_date) * 24, 1) as single_round,
round(round((t.start_date - t.end_date) * 24, 3), 1) as double_round
from round_test t
</code>
Categories: DBA Blogs
Get dates based on Quarter of the year
Tom,
Is there a way I can find the first day and last day of a given quarter.
In a given year, there are 4 quarters.
If I select first quarter then I should be able to get
the first day and last day of the first quarter
example:
quarter = 1 and year is 2007. expected result is 01/01/2007 - 03/31/2007.
similarly quarter = 2 and year is 2007 then expected result is 04/01/2007 - 06/20/2007.
Thanks in advance.
Categories: DBA Blogs
dbms_sql.bind_variable
<code>
Hi, Tom
I wrote the following procedure, there are two PLACEHOLDERs which means two bind variables
and the two PLACEHOLERS have the same name, that is to say ,the name of PLACEHOLDERs are identical,
they are both "MYTEST".
In the procedure, I assign a value to the PLACEHOLDERs by DBMS_SQL.BIND_VARIABLE only once,
but if I use 'EXECUTE IMMEDIATE' to execute a dynamic SQL, we have to separately assign value
for the TWO PLACEHOLDERs even though their names are same.
I'd like to know if the usage of "two PLACEHOLDERs with same name, bind only once" of DBMS_SQL is correct?
I have done many experiments of this usage and I got the correct result.
Thank a lot
Best Regards
create table test(id1 number,id2 number);
insert into test values(1,2);
insert into test values(2,1);
insert into test values(2,3);
commit;
SQL> select * from test;
ID1 ID2
---------- ----------
1 2
2 1
2 3
set serveroutput on
declare
v_sql varchar2(32767);
v_cur pls_integer;
v_execute pls_integer;
v_column pls_integer;
v_count pls_integer;
V_ID pls_integer := 1;
begin
v_cur := dbms_sql.open_cursor;
v_sql := 'select count(*)
from (select *
from test
where id1=:MYTEST
or id2=:MYTEST)';
dbms_sql.parse(v_cur,v_sql,dbms_sql.native);
dbms_sql.bind_variable(v_cur,'MYTEST',V_ID);
dbms_sql.define_column(v_cur,1,v_column);
v_execute := dbms_sql.execute(v_cur);
if dbms_sql.fetch_rows(v_cur) > 0 then
dbms_sql.column_value(v_cur, 1, v_count);
end if;
dbms_sql.close_cursor(v_cur);
dbms_output.put_line('count is '||to_char(v_count));
end;
/
count is 2
PL/SQL procedure successfully completed.</code>
Categories: DBA Blogs