752 Actions

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.

When Should You Shrink Your DatabaseSpecifying 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.

Use of temporary table variable inside a stored procedureIf 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.

wm_concat in a specific orderIt'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.

I want to pivot a table and aggregate a column into stringsCan 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).

May7

comment

I want to pivot a table and aggregate a column into stringsIf 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.

Removing locks in Oracle without being adminYou 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.

How to track DML and DDL daily eventsYou 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.