Tom Kyte

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

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!

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?

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;

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?

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

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.

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 ?

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>

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.

<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>

Hello, a few questions: 1. All of the scripts I have written years ago use both address and hash_value to join v$session and v$sqlarea. What I don't quite understand is why it's not sufficient to just join on hash_value (or sql_id). Why was/is address also needed? Doc ID 43716.1 seems to indicate both should be included when doing the join. SQL_ADDRESS RAW(4|8) Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed. ----> Joins to <<View:V$SQL>> . ADDRESS / HASH_VALUE SQL_HASH_VALUE NUMBER Used with SQL_ADDRESS to identify the SQL statement that is currently being executed. ----> Joins to <<View:V$SQL>> . ADDRESS / HASH_VALUE 2. If I wanted to instead use v$sqlstats, it seems the best way to join v$session to that view is to use sql_id? Note my use-case for doing these joins in the above two questions is to get the full SQL text. 3. Why was Bug 27760729 (for SQL_FULLTEXT in v$sqlstats not actually showing the full SQL text) closed out as not a bug?!?

Hi, I have a sequence skip number problem. I have this sequence. The cache is 20 and noorder. <code> create sequence seq_serial_no minvalue 1 maxvalue 999 start with 2 increment by 1 cycle </code> The current situation that has arisen is: I have two servers A and B. They connect to the same database instance. Sometimes the sequence is incremented, but sometimes it jumps, and every time it jumps, it jumps to the last_number. Here is two examples. The first example: server value -- comment B 201 2025/04/23 9:14 A 202 2025/04/23 9:22 A 221 2025/04/23 10:35 --skip B 222 2025/04/23 11:08 A 241 2025/04/23 13:22 --skip B 242 2025/04/23 15:13 A 261 2025/04/23 17:41 --skip The second examples: server value -- comment A 541 2025/05/08 14:36 B 542 2025/05/08 15:12 A 561 2025/05/09 9:28 -- skip B 562 2025/05/09 10:18 A 563 2025/05/09 10:46 A 581 2025/05/12 9:17 -- skip B 582 2025/05/12 9:23 A 583 2025/05/12 10:30 B 601 2025/05/12 14:53 -- skip A 602 2025/05/12 15:19 The skipping of numbers looks like it has something to do with the cache being cleared. May I ask if my guess is correct? If it is right, then what would clear the cache for SGA?

Hope you're doing well. I'm reaching out to get your inputs on statistics gathering in our Oracle Autonomous Database (version 19c), which we use to support our integration workloads. We have a few very large tables (approximately 70 million records each) that serve as history tables for archiving and auditing purposes. On a daily basis, we dump data from staging tables into these history tables. Since these tables are not frequently queried, we have historically not gathered statistics on them regularly. However, we've recently observed some performance degradation when querying these tables. To address this, we are considering to run GATHER_SCHEMA_STATS nightly on entire schema. We are assuming this could help improve performance. But, we are not very familiar with the DBMS_SCHEMA_STATS APIs. Based on the documentation we are planning to run the following <code> DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'XXDB' , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE , method_opt => 'FOR ALL COLUMNS SIZE AUTO' , cascade => TRUE); </code> Could you please let us know if this is the right approach to gather schema stats? By any chance if yo have any automation scripts created for this purpose to refer? Thanks in advance.

I want to upload multiple XLSX files, each containing multiple sheets. The data from each sheet should be inserted into a table with the same name as the sheet (the tables already exist) using Oracle APEX. Is this possible?

Hi Tom, What I need to do is, to monitor a table for changes. For this a trigger needs to be setup. But my doubt is, if we create a trigger after insert or update of my table column on my table for each row, and the trigger does some time consuming task like DBMS_LOCK.SLEEP (60*1), will this block subsequent inserts? I mean, will the next insert be able to run only after the sleep? In short, Is, after insert trigger is blocking or not? Will the cost of trigger amount to the cost of insert statement? I know the before statement will. But it is really confusing for after insert. Thanks in advance, Jimson

I attended a Hotsos session by Toon Koppelaars yesterday morning on Semantic Query Optimization. Among other interesting topics, Toon lamented the lack of any DBMS implementing SQL "assertions". By which he meant a database-enforced constraint than encompassed more than a single column or a single tuple (record). The example he gave was "a manager cannot manage more than 2 departments." One should be able to have a DDL statement "CREATE ASSERTION max_manager_departments AS CHECK... " containing some appropriate SQL statement. But of course no DBMS, including Oracle, allows such. It seemed to me that these were the sorts of constraints that are usually implemented by the database designer in the form of triggers or materialized views with constraints. (Admittedly, as-implemented, most trigger-based constaints fail to account for Oracle's locking mechanisms, but that's an implementation issue). Here's an example of yours: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7144179386439 As a practical matter, are there any assertions that cannot be implemented via triggers or constrained materialized views? Or are there, ahem, "rules of thumb" or guidelines as to when one approach is better than another? It would seem to me that a discussion of "what we can implement (and this way is best)" and "what we can't implement" would be helpful. Thanks!

Hi Tom, Oracle puts a TM lock on a table even if no rows are affected by a DML statement: TX_A>create table t (x int); Table created. -- just to show there are no locks on the table: TX_A>alter table t move; Table altered. -- in another session (marked as TX_B) we now issue a statement which affects no rows: TX_B>delete from t; 0 rows deleted. -- now there's a TM lock: TX_A>alter table t move; alter table t move * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified TX_B>select type, lmode, 2 decode(lmode,2,'row-share',3,'row-exclusive'), 3 decode(type,'TM',(select object_name from dba_objects where object_id=id1)) name 4 from v$lock 5 where sid = (select sid from v$mystat where rownum=1); TYPE LMODE DECODE(LMODE,2,'ROW-SHARE',3,'ROW-EXCLU NAME ------ ---------- --------------------------------------- -------------------- TM 3 row-exclusive T TX_B>commit; Commit complete. TX_A>alter table t move; Table altered. It seems to me a bit counter-intuitive (which doesn't mean that it's "bad", of course) to retain an unnecessary lock (even a "weak" row-exclusive one) on a not-modified table ... so there's probably a reason that I can't see. Do you happen to know a strong reason for this behaviour - or shall I classify it as "just a quirk" ? -- For sure it's something to remember while coding - ie think about a code such as this: delete from t where ... if (sql%rowcount > 0) then log deletions in logging table commit; end if; This would leave the tm lock on the table preventing DDL possibly forever - the commit has to be moved after the if block, most definitely. Thanks Alberto

Hi Tom, I am trying to view explain plan for a select statement in Livesql built on 19c oracle version. I am able to view the explan result using select * from table(dbms_xplan.display_cursor); but i see Error: cannot fetch last explain plan from PLAN_TABLE while using select * from table(dbms_xplan.display); Could you please help me understand . explain plan for select * from customers where id = 1; select * from TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT Error: cannot fetch last explain plan from PLAN_TABLE select * from customers where id = 1; select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT SQL_ID 92qqjqaghv843, child number 1 ------------------------------------- SELECT O.OBJECT_NAME, O.OBJECT_TYPE, O.STATUS, E.TEXT, E.LINE, E.POSITION FROM SYS.DBA_OBJECTS O, SYS.DBA_ERRORS E WHERE O.OBJECT_NAME

El erro al crear un Interactive Grid en la version Oracle APEX 24.2.0 en versiones anteriores funcionaba sin problemas ORA-01400: cannot insert NULL into ("APEX_240200"."WWV_FLOW_IG_REPORTS"."ID")