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

21h
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.
22h
answered Formatting a DATE type in an Oracle database to only accept MMDD
1d
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
24
answered PLSQL - getting real-time updates
Jun
22
answered Is index performance on a TIMESTAMP(6) column impacted when converting it to a number
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
22
answered Will an update in my trigger trigger itself? Oracle PLSQL
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
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
2
comment Ignore special characters and uppercase or lowercase in all queries Oracle 9i
Presumably, it's clear to you what "special characters" you wish to ignore. The rest of us have no idea what that list might be. Perhaps you want to ignore any non-US7ASCII character. Perhaps you also want to ignore certain ASCII characters (such as control characters). Perhaps you want to ignore some symbols (~ and ^ for example) but not others (, and . for example)
Jun
2
comment Ignore special characters and uppercase or lowercase in all queries Oracle 9i
What, exactly, then is your definition of "special character" that you want to ignore? You almost certainly won't be able to ignore particular characters via session level settings.
Jun
2
comment Ignore special characters and uppercase or lowercase in all queries Oracle 9i
I think I understand that you want your queries to be case insensitive. I'm not sure that I understand what you mean by "ignoring special characters"? Are you trying to convert accented characters to their base characters in order to do comparisons (e.g. treating "é" as "e" for comparison purposes)? Or are you trying to do something like ignoring the copyright symbol ©?
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?