SQLDeveloper - Connection parameters

Here is how I am able to connect to the database using SQLDeveloper, without using TNSNAMES option.
Note:  All the notes have been written for *nix (Linux/UNIX) OS versions
Leave the connection Type as "Basic"

 
 Determine the Hostname, port and global database name as mentioned below:

• open a terminal 
• type lsnrctl (the prompt will change to LSNRCTL>)
• type show pid
• This will display host (which you will enter in hostname) and the port values.
• Next determine service name by running the following SQL:
• SELECT VALUE AS "Service Name" FROM v$parameter WHERE name = 'service_names'
• Enter value  into Service Name
• Click on Test and ensure that you are able to connect. 
• Finally click on save to save it in the connection list.

How to reconfigure Oracle Enterprise Manager on Linux

I was unable to connect to Enterprise Manager Console for the last one week. I had tried out everything that I could search for from the web, but nothing seemed to work. I was able to start the dbconsole, connect, but it would give me a message stating "Enterprise Manager could not to connect to the database". Finally, in sheer desperation,I thought of dropping and recreating the EM. I chanced upon a well written article, by MacLochlainns, (See References below) on recreating the EM in windows. Although, I am running on Linux, I was sure, the commands would be same or similar. It turned to be the same!. In fact, I did not face any of the problems mentioned in his blog (Point # 7). Users, SYSMAN and MGMT_VIEW, the PUBLIC Synonyms setemviewusercontext & mgmt_target_blackouts and the role mgmt_user, were all automatically dropped,.
Other than that, rest of it, I just followed his instructions and I have the EM running. Thanks a lot MacLochlainns
The commands I used, are listed below:
Start the Oracle Listener and Database

emca -deconfig dbcontrol db -repos drop

Once the above command finishes, run the following one:
The emca command required me to enter Oracle SID, Listener port; passwords were keyed-in without using double or single quotes (again there was a difference between my reconfiguration and  MacLochlainns web article). In other words, key-in just the passwords, encasing them within double quotes is not needed. 
Finally, I accepted default values for Email notifications, Outgoing Mail Server for notifications, and I was done.

emca -config dbcontrol db -repos create

Important: Backup emkey.ora, since the database is kept in encrypted mode. If the key gets corrupted then, all the data will be lost.
once it is completed, test run it by logging onto the Enterprise Manager console.

References :-

How to reconfigure Oracle Enterprise Manager on Windows | MacLochlainns Weblog
Oracle's page on Enterprise Manager Configuration Assistant (EMCA)

Errorlogging Clause in 10.1 and above

I was working on the Errorlogging clause in the Insert statement which can be used to capture errors that may occur while inserting, and I noticed that Errorlogging clause does not automatically create an error logging table, if it is not specified. I was trying this using SQLDeveloper, so was not sure whether it was a bug in the SD or not. To test it, I ran it using SQL*Plus.

Here is the Insert statement with Error logging clause

Log Errors Syntax (Screen -01)

Since it appeared that Error log table can be optional, I decided to omit and run the Insert statement. However, Oracle returned an Error (Screen -02)

Not mentioning table is Not an option (Screen -02)

So, decided to follow the textbook approach and created the errorlog table (Screen -03)

Created Error Log Table (Screen -03)

As you can see from the screen below, the procedure got executed successfully (Screen -04)

Procedure to generate Error (Screen -04)

The data got inserted into the Err$_Employees Table (Screen -05). So, I think I might have hit into a Bug, however, I am still researching about it.

Data Inserted Into Error Log Table (Screen -05)

References:  

Click here to view Insert Statement
Click here to view DBMS_ERRLOG Statement

Click here to view well written example @orafaq.com
Bug: Could not find any bug other that this old one in Metalink # Bug 5255455

Date display in Oracle/SQLDeveloper

    A post in OTN forum (forum question was, How can I tell SQL Developer to always display datetime values in full?) asked how SQLDeveloper will display "Hours & seconds" in a "DATE" datatype, when the data entered is Date with no time component. Will it error out or will it display '00:00:00"? To this, I was certain, SQLDeveloper would gracefully display the timestamp at midnight. However, to prove it, I have to key-in data in date column and display them.
Before we get to that, let us first understand how Oracle stores DATE datatype.
Oracle stores date in 7 bytes (Ref: Oracle Advanced Application Developer's Guide. to access click here) Quote: Oracle Database stores dates in its own internal format. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second. End Quote
So let us get back to SQLDeveloper and analyze how it displays date datatype
I would use the seeded table EMPLOYEES which has "hire_date" column defined as DATE datatype for demonstration purposes.

Emp table Columns and Datatypes (Screen -01)

Let us take an internal dump of the date and display the data (Screen -02)

Data Display (Screen -02)

As you can see in the above screen, SQLDeveloper displays all Hire_date column data with the time component. How does it do it, when the data keyed in does not contain time component? Well, if the data does not contain time details, then Oracle stores it as 12:00:00 AM.
A brief note on the Datadump column:-
Let us take line # 6 and analyze. 120 represents Century, 109 represents Year (both are displayed as 100+Century/100+Year), then comes the month, and date. Next you would see 1,1,1 which represents the time portion (or in other words the time here is 12:00:00 AM). Time is stored as (HH+1),(MI+1),(SS+1) and displayed as 12+(HH-1) and so on. For example Line # 3 diplays 23-FEB-2010 06:34:12 AM and is stored as CC=(100+20), YY=(100+10), MM=2, DD=23, Hours=7 (6+1), Minutes=35(34+1), Seconds=13(12+1).


Why Century and Year are stored in excess 100 notation? so that we can represent BC numbers. If you look at Line # 4, you would notice that for Century and Year column the value is less that 100. That is because, the data represents BC.90-100=-10, the century inserted and the negative value will tell Oracle that it is a BC and not AD.Another point is that, we know that BC 1010 is greater than BC 1011 (line # 5). So it works  perfectly well in binary sorting too.

So, to conclude, there is absolutely no issues in displaying date by SQLDeveloper