AskTom - Hot Questions

Subscribe to AskTom - Hot Questions feed AskTom - Hot Questions
These are the questions on Ask Tom that have the most comments
Updated: 1 hour 23 sec ago

Automating External Table load

Fri, 05/29/2020 - 00:00
Hi There, Will like some thoughts on the following (working on 12c). We get flat files from various sources and each of these might come in with different delimiter ('|', ',','tab'). Is there a way to create an automated process to do the f...

Adding an optional json array to a json object

Wed, 05/27/2020 - 00:00
I have a parent table and child table with a 1 to 0-many relationship. I need to create a json object that includes parent data and a list of child table data. The goal is an object that includes: <b>"child":[]</b> when there is no matching data in ...

Create MVW takes 15 mins. Refresh of same MVW takes 12 hours (or mostly failes)

Mon, 05/25/2020 - 00:00
We have a materialized view, which has undergone some tuning. When creating the new MVW, it took 15 mins. The MVW is then refreshed using a shell in which many MVWs are refreshed in order. As part of the refresh, to make it faster, the indexes on ...

Partial indexing using multiple function indexes or precomputed json object

Wed, 05/20/2020 - 00:00
I have an orders table with a status flag showing whether that order has been completed. Most orders in the tables are completed: probably only 5%ish of the table are incomplete. I know I can build a partial index by using a function based index whi...

Copy 50 million records

Mon, 05/11/2020 - 00:00
Hi bro, Please you should help on this. My question was :- I want copy a table from other table with 50 million records, now what is the best method and fastest way to re-create it. 1)PLSQL-BULK COLLECTION WITH LIMIT(It is also taking 2 t...

Generating JSON data for data in given table dynamically

Wed, 05/06/2020 - 00:00
My goal is to generate the data in JSON format from one table. The table might have 10 millions records. Another challenge is I am not aware of the number of columns and column names of the table for which I need to generate the JSON. My DB serve...

Multi Tenancy - Application SYNC to PDB

Thu, 04/23/2020 - 00:00
Hi Friends, I am trying to get the idea clear regarding Application Sync in Multi-Tenancy architecture. I have an Application Container, and I started installing application - created a user (application schema) - created objects in it ...

Wildcards in Oracle Text query relaxation and escaping

Thu, 04/23/2020 - 00:00
Hi Tom, my question is probably very basic but I found some trouble while using the query relaxation feature along with the '%' wildcard. So I would like to escape all the chars in the query with {} and then as a suffix add the % to make a prefix...

Use of TIMESTAMP datatype

Thu, 04/23/2020 - 00:00
Hello, Ask TOM Team. I have three DATE columns in some tables. I have been asked to do some math and show it in milliseconds. I need to change the DATE datatype to TIMESTAMP, maybe TIMESTAMP(3). This is my query: SELECT COUNT(*) QTY_DOCS, R...

Best approach to handle customer-specific data attributes in a data model for packaged software

Sun, 04/19/2020 - 00:00
I need to create a module to allow for entering one or more quality attributes of delivered products. This is for packaged software and so I need to be able to configure this module to work for a variety of customers. Here are the general requireme...

Load and update summarized data to a table

Sun, 04/19/2020 - 00:00

Filters order in SQL query

Thu, 04/09/2020 - 00:00
Hello, Ask Tom Team. I want to know to put the correct order in the filter in a SQL query. I mean, in the WHERE condition. 1. How can we help the optimizer in order to have the best performance? I know that it tries to execute the best plan ba...

Oracle 11g select query with Json field

Thu, 04/02/2020 - 00:00
I want to fetch data from below table. Table Name: Insight <code>Id | Name | JSON_DATA ----+------------+------------------------------------------------------------- 1 | Sitansu. | {"country_codes": ["IND"], "regions": ["ASIA"]...

Uploading files in a Rest webservice using PlSql UTL_HTTP

Mon, 03/30/2020 - 00:00
Hi, I'm calling REST web services using PLSQL UTL_HTTP package, in JSON, with no problems. However, now i need to invoke a WS but attaching a file. I can do it with Postman in ?form-data? mode. In PLSQL, I've tried the same thing in the...

Pass string values to stored procedure

Fri, 03/27/2020 - 00:00
Hello, Ask Tom Team. I have several DELETE statements and I want them all within a stored procedure to simplify execution. All these statements would use different string values in the where condition. DELETE FROM user1.table4 t4 WHERE

many to many joins

Wed, 03/18/2020 - 00:00
Hi there, Firstly, Thank you to you all for an amazing service you provide here. Now onto business, I am a complete novice at understanding the ins and out of Oracle, though I have been dabbling with it for years. I am currently working on...

PLSQL with date loading errors

Sat, 03/14/2020 - 00:00
Hai, I need solution from you, I have a table with 7 lack records and when i re-create a table with CTAS, the rows upto 4k inserted and after that it is throwing a below error. The error was...ORA-01841: (full) year must be between-4713 and 999...

Ethiopian calendar support

Sat, 03/14/2020 - 00:00
In oracle databse 12c it says it suppirt ethiopian my database I have one employee table in that I have one column hiredate which date data type until i want to insert ethiopian calander date type data how it is possible?

how to sum pivot query results

Fri, 03/13/2020 - 00:00
<code>with t1 as (select 'dfu1' dfu, 1 type, '01-Jan-2020' startdt, 10 period1, 20 period2, 30 period3, null period4, 50 period5 from dual union all select 'dfu1' dfu, 4 type, '01-Jan-2020' startdt, 1 period1, 2 period2, 3 period3, 4 period4, nul...

Stored procedure to truncate multiple tables not doing anything

Fri, 03/13/2020 - 00:00
Hello, I have written a stored procedure to truncate the multiple tables. When running the procedure no error but when checking by select statement still I can see that in the table. This means the procedure is not doing anything. Any help much ap...