Feed aggregator

What should a PL/SQL Developer should learn in 2019

AskTom - Hot Questions - Wed, 11/28/2018 - 00:00
Hi Tom, I worked for around 14 years as Oracle developer, over SQL, PL/SQL, Forms Reports and APEX. Now since 2018 we are observing major changes like people are using Rest API rather direct backend coding. So what should a traditional Oracle ...

How can I show result sets like this?

AskTom - Hot Questions - Wed, 11/28/2018 - 00:00
Hi Tom, I have two tables. One is test and another is test1. Each table has two columns, record_id and c1 and both are varchar2(10). Test content is below <code>record_id c1 ----------------- 1 null 2 3 3 + 4 ...

Joining date and timestamp partitioned tables but partition pruning seems to not to work

AskTom - Hot Questions - Wed, 11/28/2018 - 00:00
Hi We have date and timestamp partitioned tables. While joining them partition pruning seems to not to work. Is there any way to do this? <code> create table T_A( ORDERED DATE not null, C1 number(20) not null ) partition ...

oracle 11g place limits on memory to session/user

AskTom - Hot Questions - Tue, 11/27/2018 - 00:00
Hello ! Here is my question : Is it possible to allow a minmum amount of memory to a specific session or a user on Oracle 11g? If it is, could anyone, please, explain it to me ? :) My fundamental need is as follow : I have two applications th...

Difference between named sequence and system auto-generated

AskTom - Hot Questions - Mon, 11/12/2018 - 00:00
Hello, Guys. A new db (12c) will have lots of tables with sequence used as PK. What is the difference between named sequence and system auto-generated in Oracle 12c? What would be the best approach?

Oracle Sequence and EXPDP/Historical Data

AskTom - Hot Questions - Fri, 11/09/2018 - 00:00
Hello, Ask TOM Team. We are designing a new database and there are lots of tables with identity column. The sequence used by each table is a named sequence (we are not using the system-generated sequence). The default values of these tables is lik...

Oracle Sequence vs GUID

AskTom - Hot Questions - Wed, 11/07/2018 - 00:00
We're developing a new system in for the company with .NET and Oracle Database 12cR2 using RAC. It basically does the following: receives invoices in XML format, then validates it according to business rules. A requirement is to give to the clien...

Hierarchy Validation - Incoming hierarchy vs Expected hierarchy

AskTom - Hot Questions - Wed, 11/07/2018 - 00:00
Hi Experts, I have a requirement to validate the hierarchical structure of flat files which are loaded into the database against an expected structure that is known. I have created some sample data to demonstrate what I would like to do: <code>...

Manual dataguard

AskTom - Hot Questions - Wed, 10/31/2018 - 00:00
hi there we are using oracle database 11g (11.2.0.1.0 Standard Edition one) for our production db i have gone through the docs to setup automatic log apply which is available only for Enterprise Edition.. i saw the notes from the doc saying ...

ORDER BY and CONSISTENT GETS divided by ten

AskTom - Hot Questions - Wed, 10/17/2018 - 00:00
Hello TOM, When I use an ORDER BY, I see that the consistent gets are divided by ten in my tests, can you explain why? Here are my tests. First I create a table. <code> SQL> create table test_obj01 as select OWNER, OBJECT_NAM...

Is there way to convert user defined type to JSON?

AskTom - Hot Questions - Fri, 10/12/2018 - 00:00
Is there way to convert user defined types/collections to JSON (the same way as it's possible to convert them to XML via XMLTYPE)?

Tablespace sizing for datawarehouse

AskTom - Hot Questions - Sun, 10/07/2018 - 00:00
Hello Team, my question is a bit similar to https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:228413960506 but I will need further clarification. I am loading data on monthly partitioned tables from flat files using sqlloader ev...

Is the Oracle regular expression supporting this character extraction?

AskTom - Hot Questions - Fri, 10/05/2018 - 00:00
Hi Oracle SQL experts, I am using Oracle regular expression to deal with some characters. My db is 12c. I have a string every line like this input like this 1234adhefd#123 345bheufs15# ... output will be from the first alpha lette...

SQL Query to Convert Ten Rows with One Column to Five Rows With One Column

AskTom - Hot Questions - Thu, 10/04/2018 - 00:00
i have a table with column name as value. There are 10 rows in the table. The desired output of this to be displayed as two columns first 5 rows as one column A and rows 6 to 10 as column B , next to each other as 5 rows of data like this <code>A B...

Query on dba_hist_active_sess_history query taking too long

AskTom - Hot Questions - Wed, 10/03/2018 - 00:00
Hi, I'm using the below query to fetch details from dba_hist_active_sess_history which matches a specific wait event occurring at a specific hour of the day within the last 90 days: select USER_ID, PROGRAM, MACHINE from dba_hist_active_sess_his...

Migrating Oracle 10g on Solaris Sparc to Linux RHEL 5 VM

AskTom - Hot Questions - Wed, 09/19/2018 - 00:00
Hi, if i will rate my oracle expertise i would give it 3/10. i just started learning oracle, solaris and linux 2months ago and was given this task to migrate. yes our oracle version is quite old and might not be supported anymore. Both platforms ...

ERROR WITH IMPORT OPERATION IN DATABASE 11G RELEASE 1 AND DEVELOPER 10G

AskTom - Hot Questions - Tue, 09/18/2018 - 00:00
Hi Tom, I installed Oracle Database 11G in the folder (E:\DB11)and successfully imported a user to it. But when you install the developer 10G IN FOLDER(E:\DEV10). and TRY TO DO a user import into the database ORACLE 11G, i am receiving the follo...

Parse string then flatten into columns

AskTom - Hot Questions - Thu, 09/13/2018 - 00:00
Hi, LiveSQL link not accepted by the form: https://livesql.oracle.com/apex/livesql/s/g88hb5van1r4ctc65yp4lq9gb I have this situation (see link): <b>ID String</b> Id1 Thing1: Sub1, <br>Thing2: Sub7 ,Sub8 , Sub9 <br>Thing3: Sub12 Id1 Thing...

Database link queries

AskTom - Hot Questions - Tue, 09/11/2018 - 00:00
create or replace procedure P_POP_ILC (P_POL_NO VARCHAR2 DEFAULT NULL) is cursor c1 is select * from rsds_locn_exposure@dmn_rsk_150 where RLE_ULM_NO = NVL(P_POL_NO , RLE_ULM_NO); begin DELETE FROM IDS_LOCN_CLM@dmn_rsk_150; comm...

Pages