Reputation
15,403
Top tag
Next privilege 20,000 Rep.
Access 'trusted user' tools
Badges
1 28 42
Newest
 Nice Answer
Impact
~1.0m people reached

Jan
28
answered In out parameter : how to execute
Jan
21
comment Retrieving the top row of data
If you are going to use the rownum approach in Oracle, you'd need to have the order by nested a level below the rownum predicate. As written, your query gets 5 arbitrary rows and then sorts them. There is no guarantee that it gets the top 5 rows.
Jan
19
awarded  Yearling
Jan
15
comment Restricting info to SYS users - Oracle
@tesla747 - No. You would either need to use Database Vault or you would need to have the application encrypt the data using a key that is not available to the database or to the DBA.
Jan
12
comment Procedure stuck at a bulk collect
Are you comparing the time required to fetch the first row from the query against the time required to fetch the last row in the stored procedure? Depending on data volumes and query plans, it is entirely possible that Oracle can start returning data almost instantly but that it takes quite a long time to process every row that the query will return. I can't imagine why you would declare this as an autonomous_transaction.
Jan
6
answered How can I resolve this ORA-01652 error when I already added a new file to the TEMP tablespace?
Jan
5
comment Oracle sort order changed
Does your query have an order by clause? The natural-sort tag seems to imply that you believe that there is some "natural" way in which data is sorted in the absence of an order by.
Jan
5
comment Simple trigger to update two columns in one table
You haven't told us what database engine you're using (sql is a language which is very, very different from SQL Server the database engine from Microsoft). Are you using SQL Server? MySQL? Oracle? Why would due_date be a varchar rather than a datetime? If your end goal is that both columns have the same data, why do you have two separate columns? What if an update statement changes both columns? Which value wins?
Jan
5
answered Oracle RAC: Can two transactions share an SCN?
Dec
18
comment fastest way to generate permutations of a table with n columns and put in a new table added one more question to same how to remove duplicate rows
Define "huge". Are we talking a million permutations, a billion permutations, or hundreds of billions of permutations?
Dec
16
comment Generate incrementing IDs without sequences
Taking a step back, if you're building a multi-tenant application, why do you care whether user 1 sees documents 1, 2, 3, and 12 while user 2 sees documents 4-11? Why must each user see a value starting from 1?If it is a synthetic key, why does it matter? You can always compute a gap-less set of values at query time given something else to order by (the id presumably) if you want to let the user see their most recent 50 documents.
Dec
12
comment How to select data out of an Oracle collection/array?
What do you mean by "a procedure not stored"? A PL/SQL procedure is, by definition, a stored procedure unless you're talking about an Oracle Forms application where there is a PL/SQL VM on the client machine. Do you maybe mean that you're using an anonymous block? If so, an anonymous block has not interface so you generally can't return data from an anonymous block.
Dec
8
comment How do I iterate over 1000 records in a particular order
Why do you need to migrate data "in the order it was created"? Since rows in a heap table are inherently unordered, that sounds suspicious. Any chance that you are hoping to cause queries that don't have an order by to return data in the "right" order?
Dec
7
comment Fill Database With Recursive Factorial Function
You indicate that you realize that the function is getting called 4 times (with parameters of 3, 2, 1, and 0). You know that the function prints a line every time it is called. So why are you surprised that 4 lines of output are written rather than 1?
Dec
7
comment Chance of duplicate on single select max into statement
@Ascendant - Nesting has nothing to do with it. As I said, max()+1 does not guarantee uniqueness. If you have multiple users, you will get duplicates. Do the experiment that I outlined in my answer-- open two session, do an insert in session 1, don't commit, go to session 2, do the same insert. If you're using max()+1, session 2 will get the same value that session 1 got.
Dec
7
comment Chance of duplicate on single select max into statement
@Ascendant I answered #1 already. Max()+1 guarantees duplicates in multiuser systems.
Dec
7
answered Chance of duplicate on single select max into statement
Dec
4
comment How to identify how many instances of Oracle are installed on Linux environment
And that didn't work for some reason? As I said, if you're assuming that someone in the past did installs in a non-standard method, didn't document anything, and the instance isn't running any longer, then they've done an excellent job of hiding it. You can search for things that look like parameter files and spfiles or data files but that likely requires human intervention to determine if they are "real". That's true regardless of OS.
Dec
4
comment How to identify how many instances of Oracle are installed on Linux environment
There is no guaranteed method. That's something that should be documented. You can look for indicators like entries in /etc/oratab but that is no guarantee if someone has really fouled up a server. Just as in Windows, it is possible to not have a service though that wouldn't generally be sensible.
Nov
25
comment If a table is partitioned by a single column should that column be included in index definitions?
OK. So you're creating local indexes. Is school_year reasonably selective within a partition? Or will all rows in the partition have the same value? If you are doing a range partition by month, for example, and your values are distributed throughout the month, that's different than if all rows in the partition will have the same school_year value.