Reputation
13,340
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
1 23 37
Newest
 Enlightened
Impact
~689k people reached

10h
comment Online database for test
If you want to work with a specific IDE, that would generally imply that you work with databases somewhat frequently. If you work with databases frequently, why wouldn't you connect to one of those databases? If you don't work with databases frequently, it seems odd to be trying to learn an IDE rather than trying to learn about that particular database. You can always install a database locally-- most database vendors make that relatively easy.
1d
comment Snooping-safe Vote tables
@mustaccio - Good catch. I mistakenly thought this was tagged for Oracle.
1d
comment Snooping-safe Vote tables
Does "admin" in this context mean "DBA"? Or "application administrator"? It's possible to prevent DBAs from reading certain bits of data but it will involve installing and configuring Database Vault (in addition to licensing it). Application administrators are much easier to deal with, you can just create an appropriate VPD policy.
2d
comment How to download Oracle Database 11g release 2 for Mac OS
I don't believe Oracle 11.2 was ever built for the Mac. If you want to run Oracle, I believe you'd need to run it inside a virtual machine that would itself be running, presumably, either linux or Windows.
Jul
1
comment Formatting a DATE type in an Oracle database to only accept MMDD
0 isn't a valid year. You could have 1 BC or 1 AD. There is a decent chance that either would confuse whoever has to support this application in the future.
Jun
30
comment Whitespace Trimming on Before Trigger in PL/SQL
You want :new.order_id. The leading colon is necessary. You also want a row-level trigger rather than a statement level trigger so you'd want for each row before your begin. Finally, referencing old as old new as new doesn't accomplish anything. I'd suggest removing it.
Jun
24
comment PLSQL - getting real-time updates
The insert would need to be done by a function defined as an autonomous transaction. Otherwise, it wouldn't be visible outside the scope of your transaction. Of course, that means that the changes wouldn't be rolled back if the parent transaction rolls back.
Jun
22
comment Is index performance on a TIMESTAMP(6) column impacted when converting it to a number
Oracle doesn't have a datetime data type so I assume you're talking about a column defined as a date. How are you using the index? If you want to filter data based on a predicate defined on my_date, you wouldn't want to have any function calls around my_date. Or you'd want to have a function-based index on the full expression.
Jun
22
comment Will an update in my trigger trigger itself? Oracle PLSQL
Why is the table denormalized to store the root_id for all the child rows? Why wouldn't the children just point at the parents and then query the root_id from the ultimate parent? How is a root_id different than a parent_id? Is that just a node that doesn't have a parent_id? If so, the root_id itself would seem redundant-- root nodes would be any node where the parent_id IS NULL.
Jun
22
comment Will an update in my trigger trigger itself? Oracle PLSQL
First, it would generate a mutating table exception. If you worked around the mutating table exception, it would presumably generate an infinite loop. If the data in one row is dependent upon the keys in a different row, you have a normalization problem that you should be solving.
Jun
19
comment List full text indexes in Oracle database
What do you define as a full text index in Oracle? Are you talking about listing the Oracle Text indexes?
Jun
17
comment Table size unaffected after deleting 400k rows
@user1 - That's hard to answer-- you, presumably, know your system and you, presumably, have tested the change in your lower environment. You're in a much better position to know if you need to do anything else. You know, for example, what sort of tablespace the table is in. You know whether your applications depend on the rowid being stable which wouldn't be the case during the shrink operation. You know whether the physical reorganization is going to impact application performance and whether that is an acceptable tradeoff.
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
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
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.