Reputation
13,615
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
1 23 38
Newest
 Caucus
Impact
~717k people reached

Jun
16
answered Table size unaffected after deleting 400k rows
Jun
12
comment How to populate another column with a sequence value generated for an identity column?
The referencing old as old new as new line is pointless. And there is no need to have the inserting check in the if statement in a before insert trigger. More of a concern, though, assuming the unique_id is being assigned in a trigger, which seems like the only way your insert would work, is that Oracle is free to execute your triggers in any order but this code will fail if the trigger that assigns unique_id hasn't fired yet. Almost certainly, you'd want to add this logic to the existing trigger rather than creating a new trigger. Or set up a trigger order explicitly.
Jun
12
answered Error with my trigger. What should I do?
Jun
1
comment how to prevent an oracle block corruption from duplicating a database with a few nologging tables
I've voted to move this to the DBA stack. It would be helpful as well if you could clarify how you are duplicating the database. Providing a version number is also very helpful.
May
26
comment Is there a way to calculate yesterday / today of last month / year in SQL?
Which database are you using? Date functions are pretty different across database engines. You'll also need to define your terms a bit better. What is "today of last month" if today is March 31, 2015? Is it Feb 28, 2015? So "today of last month" is the same for March 28-31 in a non leap year?
May
24
comment When Should You Shrink Your Database
Specifying the database you're using, whether these staging tables are going to fill up again in the future, and exactly what you mean by "shrink" (i.e. the command you'd want to run) would be helpful.
May
18
comment Use of temporary table variable inside a stored procedure
I don't understand what you mean by "@ symbol works". An Oracle nonquoted identifier cannot begin with or contain an @ sign (well a database link name can contain but not begin with an @ though I can't imagine why you'd want to do so) docs.oracle.com/cd/B19306_01/server.102/b14200/…
May
18
comment Use of temporary table variable inside a stored procedure
If you are really using Oracle rather than SQL Server, you wouldn't prefix variable names with @, you wouldn't have a declare there, there is no such thing as a table variable, and you can't have a procedure that just does a SELECT without either selecting data into a scalar variable (or variables) or into a collection or opening a cursor that can be used or returned to the caller. Since you're not manipulating data, it would also not make sense to create a procedure rather than a function. Possibly, you want a function that returns a sys_refcursor but that's a wild guess.
May
14
answered Oracle paging query performance
May
11
comment How oracle syncs data across nodes in a cluster
You've posted a good answer if the original poster was using MySQL. Everything in the question, though, appears to indicate that the original poster is using the Oracle database.
May
7
comment wm_concat in a specific order
It's not clear whether you're saying that that query has an ORDER BY clause or not. If the inner query is ordered, you're probably out of luck. wm_concat is an undocumented function so you don't have a lot of options if it's not working the way you want.
May
7
comment I want to pivot a table and aggregate a column into strings
In the question I linked to, there is a link to a number of different string aggregation techniques. Pick one.
May
7
comment I want to pivot a table and aggregate a column into strings
Can you define "not working"? What output are you getting for the sample data you provided (I assume in your sample data, patient b's rank 2 row should be a 0 not a 1-- otherwise I'm not sure why the desired string would be 10 rather than 11).
May
7
comment I want to pivot a table and aggregate a column into strings
If the number of columns that you wanted isn't known at compile time, you can't use a pivot at least not in a static SQL statement. You could use dynamic SQL to construct the SQL statement at runtime but that would require running a query first to get the max(rank) and then constructing a SQL statement with that number of output columns. Returning a single string would be easier.
May
7
comment I want to pivot a table and aggregate a column into strings
select * from v$version will tell you the version. Are you guaranteed that the rank will never exceed 4?
May
7
comment I want to pivot a table and aggregate a column into strings
What version of Oracle? The pivot operator is relatively new. If you need to support older versions of Oracle, you'll need to use the old max(case trick.
May
6
comment Removing locks in Oracle without being admin
You can't get there from here. A lock will only be released when the transaction holding it terminates (either successfully committing or rolling back). The only way to force another session to immediately terminate its transaction is to kill it. You don't have privileges to kill the session so you'd need to escalate the issue to someone who does. Are you sure that you have a problem, though? I can't see the image you posted so it's not clear if the sessions are actually blocking others rather than simply holding a lock.
Apr
30
awarded  Enlightened
Apr
30
awarded  Nice Answer
Apr
25
comment How to track DML and DDL daily events
You realize that all_tab_modifications is not a real-time accurate count of the number of modifications to a table since the last time statistics were gathered, right? The counts are approximate (though generally pretty accurate) and the data is written periodically in batch. It would seem very odd to want to get information like whether the last change was an insert or an update from a view that may not reflect a change to the table that was made half an hour ago.