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: 35 min 21 sec ago

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