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: 2 hours 21 min ago

How to extract substrings enclosed within double quotes from a string

Sun, 02/12/2017 - 00:00
I have a requirement to extract column names (there could be multiple instances) from a SQL string. The column names will always be enclosed within double quotes. e.g. "le code" = 802 AND "principal amount" > 1200 OR "branch id" = 'ABC' <b>Req...

Shuffle values randomly in columns

Tue, 02/07/2017 - 00:00
Note - this is more of an academic question as I have a resolution, I am just keen to see whether my alternative approach is possible. I have a HR table with a list of names. For demo purposes I needed to shuffle these around to make them a bit mo...

Insert Into table_name Select for table with CLOB column is very slow.

Wed, 02/01/2017 - 00:00
Hi Sir, I have a activity for making non partition table to partition table, the table has clob column i was using below approach to fulfill the activity. rename existing table to _old create new partition blank table insert into new blank pa...

SQL Query Help Required

Tue, 01/31/2017 - 00:00
I'm new to Oracle and have got a requirement from my client as follows. Eventhough I have got the PL/SQL solution for this requirement, I'm interested in understanding whether a SQL based solution is possible rather than a PL/SQL approach. If yes, p...

Concat all columns

Tue, 01/31/2017 - 00:00
Hello Tom. I want to concat all columns of a row into one string. <code>select * from table</code> should bring out one colum per row, including all fieldvalues as one string. The use auf || didn't work, because I want it for different table...

Finding if record is overlap or not

Sun, 01/29/2017 - 00:00
Hi Tom, Hi Tom, Could you please help me with the SQL in finding if the record is a overlap or not. Here is the test case.. seq_val is calculated first and would like to check overlaps based on that order.. ID Start_dt end_dt Seq_val 1 01/0...

Memory usage in heavily partitioned database

Fri, 01/27/2017 - 00:00
Hi From the depths of internet I have found sql-statement which I slighly modified. Purpose was to find out what objects exists in the buffer cache and many blocks they were using. </> with t1 as ( select o.owner, o.object_name ...

Performance and commits

Fri, 01/27/2017 - 00:00
Hi team, I have a simple question. Let's say i have 20,000 txn for one session and i have multiple sessions say 10. So is it good to mention commit frequency as 300-400 in code for better performance. Please let me know as what i am follo...

Fetch Ref cursor to another ref cursor

Thu, 01/19/2017 - 00:00
Hi, First of all Thank you Gurus! for your time and effort to solve user issues. I have a requirement where I have to consume first row of the big query result set and fetch the rest of the result into a refcursor(for some reason I can't change...

Edition based redefinition- Data handelling

Tue, 01/17/2017 - 00:00
Hi, I am using EBR to upgrade from one application version to another with zero downtime. Definitely, this includes migration DB scripts to be run to migrate from one version to another. The requirement is that the migration scripts have some DMLs...

Locking issues on Table A with FKs on table X while a procedure inserts records in table B with FKs on table X too

Mon, 01/16/2017 - 00:00
Hello, we use oracle 11.2.0.3 and following topic i would like to discuss here: Setup like this: Table A (partitioned) with an article column and FK defined to masterdata (article) keytable X Table B also with article column and FK define...

Procedure accepting more than 32K text as a parameter and inserting to CLOB coumn

Sat, 01/14/2017 - 00:00
Hi Team, We have one requirement where: 1) we have to write one proceudre which will insert into a clob column meant for email body. This procedure should accept text more than 32K. 2) the application or routine will execute this procedure by p...

Use of Wrap utility for 12K LOC packages

Thu, 01/12/2017 - 00:00
I've create a SP to encrypt the source code of existing packages, my problem is that legacy packages contain more than 12K LoC (lines of code), with small packages I have no issue, but with the heavy ones I got : <i>06502. 00000 - "PL/SQL: nu...

Where clause with multiple arguments which can be null or populated

Wed, 01/11/2017 - 00:00
I have a search feature in my application where a user can type filter on 3 different columns(can be more), each of these filters can be used or null. What is the best way to select a ref cursor for the data? I can think of 2 different ways. 1st wa...

Library cache lock for wrong passwords

Tue, 01/10/2017 - 00:00
Hi, I want to know what exactly happens in the library cache which in turn puts the users into lock/delay logins when a user tries to login multiple time with wrong password. If you could explain me regarding this it would be a great help. T...

filtered hierarchical query

Tue, 12/13/2016 - 00:00
I have some hierarchical data (potentially ~80 million rows, if that's important): <code> drop table test; create table test (id number,pid number,t varchar2(20)); insert into test values (1,NULL,'Animalia'); insert into test values (2,1,'Mamm...

Partitioning Strategy

Fri, 12/09/2016 - 00:00
Hi Tom, This is my first time that I'm posting a question to you. I have been huge fan of your answer and humor sometimes :) Keep it up !! Q) I have a schema that contains around 50 tables, 5 tables contain around 1.5 billion rows and rest 45 ...

Index creation taking more time

Wed, 12/07/2016 - 00:00
Hello Tom, The index creation is taking more time. Previously the index creation used to take around 1 min and now it has increased to 10 - 20 min. The indexes are dropped and recreated on a daily basis during our night load. Due to this, our ni...

Truncate Load gather stats

Thu, 12/01/2016 - 00:00
Hi, I have a DW activity. <b>Steps include</b>. Drop index on Table A Truncate Table A. Load table A with millions of records. Create indexes back on table A. Drop index on Table B Truncate Table B. Load table B with millions of records....

Insert All with sequence - feature or featurette?

Tue, 11/29/2016 - 00:00
I posted this problem elsewhere and we had a bit of a discussion. However I would like to know whether what we see is a feature (i.e. it can be relied upon to be somewhat consistent in future releases) or whether it is a "featurette" - i.e. unintende...

Pages