Questions

I'm an 'accidental' DBA that's also been tasked with PL/SQL development work.Ours is a small environment; I'm the only DBA plus another developer who is completely new to PL/SQL.I'm working (development environment) on a basic package and after getting some co...

We have an old query that was running fine (though it could use improvements in general) but after having a VPD policy put in place, it started to run long and not complete. The difference between an 1 - 1.5 hours to not completing after running for well over ...

Hi,I am getting a table dump using an SQLplus client. The code is mentioned below. The problem is, the value of any varchar field of the dump table is saved in the dump file with padded space.means, if f1 is of varchar2(6) type field in a table. It has a value...

If I trace an execution of this statement<code>alter table TAB1 enable foreign_key_cons_on_tab2</code>then a recursive query similar to the following will attempt to find rows in TAB1 with no matching row in TAB2. If it fetches zero rows, then the constraint i...

How to frame the select query which will divide the result set into required chunks by using CREATE_CHUNKS_BY_SQL by_rowid => true.For example:i have a table contains 100 records and my select query fetches after filtration 50 records, i want to create 5 chun...

Tom:What is really the difference and when you would use each of oracle datatypes:1. Index-by-tables2. Nested Tables3. VarraysMy understanding is that index by tables are for data of same type that is stored in memory.For nested tables you can store the var...

Hi Tom,I have recently upgraded a couple of databases (12.1 -> 19.17) and remote oracle client (19.3 - 19.14 ) on application servers connecting upgraded databases.There exist only one case where 'set feedback off' is not working as expected when I commit from...

Hi, Is it possible to convert a collection into json_object?I have a collection named code1 of type codelist of table of Varchar2(2).code1 = codelist('AB','CD', 'EF'); I want the above in JSOn format as below:Is it possible? Please help{"codelist" : ['AB','CD'...

HiI had Oracle <b>12c Enterprise on Windows 64 </b>and MariaDB 10.2 on Ubuntu . I have been using ODBC transparent gateway to send the data from Oracle to MariaDB. I am in the process of transitioning to <b>Oracle Enterprise 19c on Solari</b>s and MariaDB 10...

<code>create table ticket1(ticketid number, tcktname varchar2(10), status varchar2(10) );INSERT INTO ticket1 VALUES (101,'bug','open');INSERT INTO ticket1 VALUES (102,'bug','close');INSERT INTO ticket1 VALUES (103,'...

I need to CREATE some test data where I want to CREATE N number of timestamps between 2 times for every 15 minute interval including the start time and less than the end time.N would be defined as dbms_random between a low_number and a high_number.I want to r...

Hi Tom!I am trying to set up some unified audit policies but whatever I set up I don't get the actions written to audit trail.The only audits that is written is the ones that is per default installed and enabled so I can see that all my commands for creating a...

Hello, Thanks to this forum a lot of my answers were answered. I noticed that most of the queries in an application that am working on is using the MATERIALIZE hint in the subqueries. Our queries heavily use CTEs and relies on multiple joins. Our query perform...

ORA-04063: package body "SYS.DBMS_NETWORK_ACL_ADMIN" has errors, how do I reinstall this package?...

Dear Tom,how can I create bind variable in Oracle Live SQL,<code><variable variable_name data_type;>example <variable dusko number;> </code>just doesn't work, i get "Ussuported command" message...Thanks and best regards...

Hi.We use to prepare extract file from oracle using extract script. There are few tables whose name gets change every month.Need to handle these table names dynamically.<code>SELECT TICKET_ID ,SOURCE_ID ,SERV_ID ,to_char(COLLECTED_DATE,'YY...

Hi,I have a table that holds about 2 million records. No column in the table can be used to divide rows equally. Initially when the data is loaded into the table, the bucket_number column is null. After data loaded, I would like to calculate and equally divide...

I have a materialised view in my own schema which based on a table in another schema. However I want to modify the matrealise view to add a where clause which will never becomes true e.g. Where 1=2Can i alter an existing materialised view to add this where cl...

I have a Docker Container with Oracle Database Entrepise Edition (followed the basic instructions on container-regitry.oracle.com)And i'm trying to create a NodeJs application to connect to the Oracle Database inside the container (tried the "how to basics" on...

We currently use triggers on a subset of tables in our transactional database (on-premises) to capture row / column level changes and insert them into a separate table. With the availability of DBMS_CLOUD package for our product version we are exploring the op...