Tips and wisdom from 15 years of DBA experience

Thursday, October 22, 2009

How to set Oracle Apex Checkbox default value

As often is the case when I blog about something, I spent waaaaay too much time trying to figure out how to do something that is seemingly very simple. In this case my trivial requirement was to create a single checkbox, that when checked, puts "Yes" in the database, and when unchecked, puts "No" in the database. Here is how to do it:

- Create your Checkbox item.
- Make sure that the following are set in the "List of Values" section of the checkbox item:

Named LOV: - Select Named LOV -
Display Extra Values: No
Dynamic Translation: - Not Translated -
Number Of Columns: 1
Display Null: No
Null Display Value:
Null Return Value:
List of values definition: STATIC:;Yes

Save these changes, if you made any and then add a computation to the page as follows:

Click the "Create" icon.
Choose "Item on This Page"
click next...

Compute Item: (The checkbox you want to modify)
Sequence: (choose the default)
Computation Point: After Submit
Computation Type: Static Assignment
click next...

Computation: (Value you want to return) for example: No
click next...

Condition Type: Value of Item in Expression 1 is NULL
Expression 1: (name of checkbox you want to modify. Example: P1_DATA_FG
click create...

Viola! Give it a shot, and it should work for you. I learned all this stuff from here:

Craig Glendenning

Tuesday, October 13, 2009

ORA-22921 and streams

The most likely issue is that you are trying to stream between two different character sets. In my case, I ran into this when running dbms_streams_adm.maintain_schemas() with a source db on WE8MSWIN1252 and a destination on AL32UTF8. I had run csscan on the source schema and there were no issues or truncations returned, but the problem still haunted me. I read on an Oracle forum that there may be a bug associated with this, but when I tried to search on metalink, it just hung, thanks to the "New and Improved" metalink. Grrr. I will take a running ford focus over a non-running cadillac any day of the week.

Saturday, October 3, 2009

Viewing SYSDBA and SYSOPER operations through Grid Control (OEM)

Auditors need to see that I track operations performed as SYSOPER or SYSDBA. Here is how I do it. First, in the database, log in as SYSDBA and issue:

SQL> alter system set audit_trail=xml scope=spfile
SQL> alter system set audit_sys_operations=true
SQL> shutdown immediate;
SQL> startup;

Ok, now be sure to create an OS-level space monitor to do something with those .xml files that will be rapidly clouding your $ORACLE_BASE/admin/(db_name)/adump directory. I will explain why you need to use XML in a moment, instead of audit_trail=DB. Now, go into Grid Control and click on the home page for the instance you just restarted. You must be sure to log into the database as SYSDBA and be sure to choose "Save as Preferred Credential" for this instance . Next, go to the reports tab, and go to Reports -> Security -> Database Targets -> SYS User Operations. On the next page, click the flashlight and choose the instance you just restarted and click continue.

If you receive an error like "Failed to render element. Error: This report only applies to database with version 10.2 and up." it means that you did not properly log into the database instance as SYSDBA and set the preferred credentials. Try going back to the instance home page, logging out, then logging back in being sure to click "Save as Preferred Credential". I have seen cases where immediately after checking the checkbox, the page refreshes, and un-checks it. This can be easily overlooked while you are going to click the "Login" button. Now retry the steps above on the Reports tab. If you experience a problem, please post a comment on this page so someone can help you!

If all went well, you should now have a listing of your audit trail. I like to choose "Show All xxx" and then click the "Time" heading twice to sort by most recent activity. Viola! Now you know what is going on in your systems. The next step is to put alerts on the things that you care about, like "Page me when someone logs into my instance as SYSDBA or SYSOPER."

Ok, so why do you need to set audit_trail=XML? I believe it is a requirement by OEM. I don't recall the exact message but setting audit_trail=DB did not display any records and gave a message indicating that you must set audit_trail to DB or XML. Since I was already set to DB, I decided to try XML, and it worked. CG version Will show audit records with audit_trail=DB? I dunno.