Newbe Dev Stack

Answer : I found this link useful. Here is the paragraph highlighting some of the pros/cons of each approach. The most commonly seen design is to imitate the many Boolean-like flags that Oracle's data dictionary views use, selecting 'Y' for true and 'N' for false. However, to interact correctly with host environments, such as JDBC, OCCI, and other programming environments, it's better to select 0 for false and 1 for true so it can work correctly with the getBoolean and setBoolean functions. Basically they advocate method number 2, for efficiency's sake, using values of 0/1 (because of interoperability with JDBC's getBoolean() etc.) with a check constraint a type of CHAR (because it uses less space than NUMBER). Their example: create table tbool (bool char check (bool in (0,1)); insert into tbool values(0); insert into tbool values(1);` Oracle itself uses Y/N for Boolean values. For completeness it should be noted th...

Answer : UPDATE: What Worked... So after reading the link from @Raj and reading @ora-600's answer I tried to validate the database with the RMAN command backup check logical validate database; . While this worked fine, it was also clear that it was not looking at everything that the ANALYZE INDEX command would. After trying many different variations, I finally discovered that this command would work: ANALYZE TABLE .. VALIDATE STRUCTURE CASCADE offline; Yep, just switching to OFFLINE appears to fix it. And that eventually led me to this bug# 5752105 on this page: http://www.eygle.com/case/10204_buglist.htm. I am not in a position to prove it right now (cannot apply any patches for the time being), but I strongly suspect that this is what I was running into. So while the question is not fully answered, I am going to mark @ora-600's very helpful answer as correct so that he can collect Paul White's very generous bounty. I think the article Raj quoted (https:...

Answer : if var is NULL then var :=5; end if; Use: IF Var IS NULL THEN var := 5; END IF; Oracle 9i+: var = COALESCE(Var, 5) Other alternatives: var = NVL(var, 5) Reference: COALESCE NVL NVL2 In PL/SQL you can't use operators such as '=' or '<>' to test for NULL because all comparisons to NULL return NULL . To compare something against NULL you need to use the special operators IS NULL or IS NOT NULL which are there for precisely this purpose. Thus, instead of writing IF var = NULL THEN... you should write IF VAR IS NULL THEN... In the case you've given you also have the option of using the NVL built-in function. NVL takes two arguments, the first being a variable and the second being a value (constant or computed). NVL looks at its first argument and, if it finds that the first argument is NULL , returns the second argument. If the first argument to NVL is not NULL , the first argument is returned. So you c...

Answer : When you subtract two variables of type TIMESTAMP , you get an INTERVAL DAY TO SECOND which includes a number of milliseconds and/or microseconds depending on the platform. If the database is running on Windows, systimestamp will generally have milliseconds. If the database is running on Unix, systimestamp will generally have microseconds. 1 select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) 2* from dual SQL> / SYSTIMESTAMP-TO_TIMESTAMP('2012-07-23','YYYY-MM-DD') --------------------------------------------------------------------------- +000000000 14:51:04.339000000 You can use the EXTRACT function to extract the individual elements of an INTERVAL DAY TO SECOND SQL> ed Wrote file afiedt.buf 1 select extract( day from diff ) days, 2 extract( hour from diff ) hours, 3 extract( minute from diff ) minutes, 4 extract( second from diff ) seconds 5 from (select systimes...