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.

(Not a ) Bug in Debug Procedures in SQLDev

Today an OP in SQLDeveloper forum at OTN had complained about "disappearing" Green icon on Procedures (To read the post click here Note: Sue has pointed out that it is not a bug.I have posted an update below on how to use compiler with debug option. So if you just want to know how to turn on or off the compiler with debug option, just scroll down. If you have time to kill  , go ahead and read the entire post. ) . So, back to my original post.... 
I just happen to have Windows version SQLDeveloper (Build Version 2.1.1.63) and Linux Version (Build Version 2.1.1.64) installed. Just a heads up for those who are wondering about the "green" icons, when you compile a procedure, in SQLDeveloper, if the procedure compiles successfully, then it will display a Green icon on the left of the procedure name. If the procedure fails to compile a "red" stop icon is displayed - pretty cute isn't it? Only that it does not seem be consistent across versions (or is it across OS versions, I wonder)?
Since I usually used Linux, I was not sure what the OP was talking about. I created a few test procedures in 63 (windows version) first (Screen -01).

Windows Build version 63. Notice missing Proc toolbar (Screen -01)

Everything went off without any hitch, although it did not display the Procedure Complier bar, it was not a show stopper. I could compile repeatedly the procedure without any issue, and update the parameter for this procedure (Screen -02).

Modify the parameter (Screen -02)

Next, I started my linux version and recreated the same procedure. Bingo! The first time procedure got compiled and the green icon was displayed. I could see the Procedure 'Compilation' Toolbar. However, when I compiled again, (Screen -03)

Linux Build 64 (notice the Procedure Tool bar (Screen 03)

When I complied "with Debug option", I could see the green icon now (Screen -04)

Go for Green (Compiled with Debug) (Screen -04)

But, I cannot modify the parameter window (Screen -05). I hope SQLDeveloper team will take note of this and correct the bug.

Cann't change the parameter (Screen -05)

Update:
As Sue Harper had pointed out, it was a case of how I compiled. Default Compilation option for SQLDeveloper is to "Compile with Debug"
If you want to reset the default preferences to "compile and no Debug" go to Tools-->Database-->PLSQL Compiler and click on the optimization level and set it to 2. Note: This setting "tells" compiler to optimize the PLSQL Library units. (Screen -06)

Screen -06

After setting the preferences to zero, I reran the procedure and was able to change the input parameter ( See below) .

Parameter Window

The log ouput window is shown below. Thanks Sue.

Log output window

You might also want to read this post ( If using windows:-

How to get SQLDeveloper working on Windows 7

If you are using *nix then

How to make SQLDeveloper work on Linux

)

Oracle Learning Library

Thanks to Sue's post (click here to read her post), I learned that, Oracle has introduced a Learning Library, includes online demonstrations, including APEX tutorials and SQLDeveloper demos. Truly worth watching. Click here to go to Oracle Learning Library. It is easy to search either by Product or by Category. For example to find out how to use SQLDeveloper for Microsoft Access, you would select "Database" under the "Category" and "SQL Developer" under "Product Component" and system provides a search output as shown below. Click on hyperlink under the column "Title" and a new window is launched displaying the Demo.

How to get SQLDeveloper working on Windows 7

Oracle SQL Developer 2.1

I have a 64 Bit Windows 7 installed in my laptop. So I downloaded and installed 64 bit Java software in my machine. Next I found where the jdk was installed (usually it is in the "C:\Program files\Java\jdk<version>". If you cannot find it, go to c:\ and type dir jdk*.* /s /p - 's' will search recursively and 'p' will pause after each page of display). Go to the directory where you have unzipped the SQLDeveloper and open the SQLDEVELOPER.conf file and updated the variable SetJavaHome (This file is located at sqldeveloper\sqldeveloper\bin directory. Then click on the sqldeveloper.exe (located in the same directory), a command window is opened and lanuches SQLDeveloper in a new window. (Note:Do not use the SQLDeveloper.bat - available in the same directory - to launch SQLDeveloper. This would result in problems Procedures/Packages/Function toolbar (see below)

This is the Screen from as launched from SQLDeveloper.bat;As you can see, Both the Compiler options and Procedure Toolbar are missing(Screen -01)

This is the Screen from as launched from SQLDeveloper.exe;As you can see, Both the Compiler options and Procedure Toolbar are present(Screen -02)

Version Screen for Sqldeveloper.bat(Screen -03)

Version Screen for Sqldeveloper.exe(Screen -04)

SO Exception, TNS-12514 and other errors

My Windows 7 was giving troubles of late and was crashing (Blue-screen-of-death) almost 3/4 times daily (Need to figure out that one at some other point of time). Naturally, the Linux OS installed in the VMware, also started giving problems. For the last two days, I could not connect to Oracle from SQLDeveloper. First I got ORA-12541 (Screen -1), then, ORA-12514 error and lastly, a "SO Exception".

Screen -01

Lastly, I logged into SQLPLUS as sys and ran the following command:-
Show parameter local listener (Screen -02).

Screen -02

Bingo! I found that the port number was wrong. Instead of 25000,my tnsnames and listener was set at 3801. Modified the listener.ora and tnsnames.ora and SQLDeveloper was able to connect again (Screen -03)

Screen -03

Search and Highlight in SQLDeveloper

Search & Highlight -1

I stumbled upon by chance, on a nice cool feature in SQLDeveloper - Search and Highlight rows. Run a Query, by pressing F9 and place cursor in the result window. Now press CTRL & F together, you would a popup window. Type a search text ( I typed 'Toibas' and clicked on  "Highlight", on the top. It would enable "Persist Highlight" and "Highlight Row" (See Search & Highlight -1 above). Check both of them and you would see a result as shown below (Search and Highlight Screen 2)

Search and Highlight -2

SP2-0027 error in SQLPLUS

Screen -1

While running a script, from SQLPLUS, I came across an error: "SP2-0027: Input is too long (> 2499 characters) - line ignored" (Screen -1) - even though I had already run SET SERVEROUTPUT ON SIZE UNLIMITED.
There is a lengthy discussion on resolving this at tom kyte's website - click here to go there. However, I ran the same script under SQLDeveloper, it worked! (Screen-2). Now, that's an easy solution :-)

Screen -2

To download SQLDeveloper click here

How to set login preferences (SQLDeveloper)?

Screen-1

Although, setting login preferences for SQLDeveloper v2.1.1.64,is straight-forward, it just doesn' t seem to work.
To set login preference for goto Tools --> preferences; select database, click on browse to choose the login you have just created and you are done (see Screen -1 above).
After setting the preferences, I ran the following SQL: "exec DBMS_OUTPUT.PUT_LINE ('1234567890123456789012345678901234567890');" and ran the script. The script output window display is shown below (Screen -2). hmm, not what I expected.

Screen -2

I opened the DBMS Output window (Screen -3) (navigation View-->"DBMS Output") and reran the above window, only to end up with same results as displayed above - no luck again.

Screen -3

So, I ran the command SERVEROUTPUT manually and ran it - success!  Unfortunately, Session parameters seems to be manually run each time a you run a script and does not get set automatically. you can see the results in (Screen -4).

Screen -4

SQLDeveloper copying Connections.xml

Sharing connections across the team members in SQLDEVELOPER is a breeze.
Step1: get the connections.xml from any one of the users and copy to your local directory (In Linux it is found at .sqldeveloper/system2.1.1.64.45/o.jdeveloper.db.connection.11.1.1.2.36.55.30/ directory).

Step 2: When I started the SQLDEVELOPER (if you want to know how to add SQLDEVELOPER to your linux menu click here), and clicked on one of the connections that appears on the left, I got an error (Screen 01)

Screen -1

A little investigation showed that I had missed setting the ORACLE_HOME variable correctly. After correcting this, reclicking on the connection I got ocijdbc11.in.java not in library path (Screen -2)

Step 3:
 If I select connection type as Basic (instead of TNS), and the enter values needed, I am connected!. (Screen -3)

Screen -3

SQLDeveloper: Sharing Shortcuts across the team

SQLDeveloper has an utility to export the short-cuts. It is an excellent way to ensure that short-cuts, are kept uniform, can be shared across the team. I have listed below steps to export the preferences and import them back.
1) Navigation: Tools-->Preferences (Screen -1)

Screen -1

2) Select Shortcut Keys (Screen -2)

Screen -2

3) Click on More Actions and select Export in the drop-down list (Screen -3)

Screen -3

4) Select the directory to save the file (Screen -4)

Screen -4

5) Importing the preferences is similar; follow the steps 1 & 2 and in step 3, select "Import" instead of "Export" and select file to import. (screen -5)

Screen -5

6). An confirmation window will appear (Screen -6), click on "yes" and the Short-cut preferences will be imported.

Screen -6

Insight Feature

Screen -1

I noticed that in SQLDeveloper, the insight feature has a pretty cool feature, in that, you can select, "auto generate GROUP BY Clause" (Screen -1). What this means, is that when you change your select columns, SQLDeveloper will automatically generate the columns in the GROUP BY clause for you (Screen -2). Navigation: Tools-->Preferences. Expand Code Editor. Click on "Completion Insight" and ensure that the Check box for "Autogenerate GROUP BY Clause" is selected.

Screen-2

Bug: However, Insight adds another column, if you add another GROUPING Function. If executed as it is, it will result in ORA-00904 error (Screen - 3)

Screen -3

Drag & Drop to create query

SQLDeveloper keeps amazing me. I can easily create a query by merely selecting columns I need on the left pane and drop it in the SQL Window to create a query. Here are the steps I followed to create a query
(I have already created a connection and been able to successfully connect to SQLDeveloper, organized the logins into folders). The initial screen will look like Screen -1

Screen-1

Open a New SQLWorkSheet by clicking on the SQL ICON (Screen-2)

Screen-2

A popup window will open up (Screen -3)

Screen-3

Select the columns you need (either by pressing SHIFT and Left Mouse Click or (as I did) CTRL+ Left Mouse click) and drag them into the SQL Window (Screen -4)

Screen-4

result is displayed as shown in Screen -5.

Bug-01: There is a minor bug in this version (2.1.1.64). Let us say there is a query already existing the query window like above. You click on the left pane to select a few columns; Drag them into SQL Window and a popup window appears, where you would have to choose amongst "Insert/Delete..." and after making your selections, instead of clicking on apply, you change your mind and click on "cancel", the query still gets copied to the SQL Window (Screen-6a, Screen-6b)

Screen-6a

Screen-6b

Note: This does not happen if there is no query in the SQL Window
Bug-02: Selecting "Join" has no effect.
SQLDeveloper is an excellent product. Would like to see it even better.

SQL Developer History

SQL Developer History

History of commands is handled very well in SQLDeveloper. Pressing F8 will display the history of sql commands that has been issued till now. Place the cursor in the SQL Window, and press "Ctrl+Up arrow" or "Ctrl + down arrow", to paste commands in the history tab into the SQL Window. This works very well, except for the last row or the first row, when the up or down arrow will takes me nowhere. For either of these two situations, I either grab the SQL, from the History window, with cursor and drop it in the SQL Window; or, go down/up a row in the History window and press the "Ctrl+Up" or Ctrl+dwn" key.

SQLDeveloper & 11g Hierarchical Profiler

11g's introduces heirarchical profiler (to learn about Heirarchical profiler click here) . SQLDEVELOPER has a tab which displays profiler details
However, the first time I tried to run it from SQLDeveloper, I came across a waring/error viz; tables/directory missing ( the error was something like this: "Required tables DBMSHP_FUNCTION_INFO,DBMSHP_PARENT_CHILD_INFO,DBMSHP_RUNS missing")
So I logged in as SYS, created PLSHPROF_DIR and granted READ,WRITE access on this directory to "srini" user.
commands I used are given below:

CREATE OR REPLACE directory PLSHPROF_DIR as '/tmp/Oraprofiles';
grant READ, WRITE on directory PLSHPROF_DIR to Srini;
grant execute on DBMS_HPROF to Srini;
grant create table to Srini;
grant create sequence to Srini;
grant unlimited tablespace to Srini;
finally ran the script ran the script dbmshptab.sql
Rerunning the test procedure provided me the profiler results shown above.

Creating Report in SQLDeveloper

Sue harper's blog note shows how easy it is to create a report ( to visit the article click here ) . It wasn't until I tried it out I found out how quickly I can build reports. Since her note required me to use SCOTT user, I found out it was locked; - no problem; just clicked on connections, selected SYS user connection, scrolled down and clicked on "other users", scroll down to "SCOTT" user (Screen-1).

Screen-1

    
Right click on "SCOTT" enables me to unlock the user as well set the password. Uncheck "Account is locked" and "Password expired" (Screen-2)

Screen-2

Since I was not sure of the column names, I kept the "popup" descriptions of both Dept and Emp tables open. It is pretty straightforward to create a Chart (Screen-3)

Screen-3

Select "Chart Details" Tab, near bottom to change the Graph type to BAR, produces a report as shown below. (Screen-4).

Screen-4

The only gripe I have, is that while creating the report, TAB is used for navigating through the fields and does not work always.  Pressing Tab from Description Field, does not take you to Tool tip field. Other than that, I faced no problem.
In totality, the entire process was pretty easy and quick and for a TOAD user, I am now a happy camper at SQLDeveloper tool.