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

11g Default Schema & Scott Schema

11g comes with quite a number of demo schemas (seeded tables which can be queried to understand /Learn various SQL commands). To know more about it, go to: Download Oracle Schema
Scott User which used to come unlocked and OPEN in every version, is no longer the case. You need to manually unlock the user. To learn more about SCOTT Schema go to: http://wiki.oracle.com/page/scott%2Ftiger  

To learn about installing Sample schemas in 10g, go to :- Installing Sample Schema's
Sample Schemas reside under $ORACLE_HOME/demo/schema.  Demobld.sql resides under $ORACLE_HOME/sqlplus/demo

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

How to set login preferences (for SQLPLUS)?

I am too lazy to keep setting debugging messages or pagesize for every session in SQLPLUS. Also, I would like to set the SQLPROMPT look much like the UNIX Prompt. There are essentially two places where I can do it. Either use a file glogin.sql (oracle creates a glogin.sql file - nothing but a shell - at $ORACLE_HOME/sqlplus/admin ) or create a login.sql at the directory where I start sqlplus.
Note: In order to change glogin, super user access is needed. If you do not have super user access, create a login.sql at a local directory.

To display debugging messages on, use SET SERVEROUTPUT ON
To set the output size (below 10g) SET SERVEROUTPUT ON SIZE 10000. (for 10g & above), it is "SET SERVEROUTPUT ON SIZE UNLIMITED
To set the sqlprompt, enter SET SQLPROMPT "Srini" - the value given between the double quotes is displayed as SQL Prompt. If you want it to be generic, then type, SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE >"
The above command displays the user, database name, connected (sysdba or normal)
Screen shot of my glogin.sql (Screen -1) is displayed below:

Screen -1

After modifying the login file, the effects are shown in the Screen -2 & Screen -3.

Screen -2

Screen -3

Note: If the SERVEROUTPUT is not set right, Oracle will raise ORA-20000 error as shown below (Screen -4):

Screen 4

References: To learn more about SQLPLUS commands click here

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

ORA-01427: single-row subquery returns more than one row

I faced today, "ORA-01427:sub-query returns more than one row" error. The problem is easy to identify:This error is raised, when a sub-query returns more than one row to an Equality or in-Equality operator. The reason being there are restrictions on the legal comparison operators (Screen -1).

Screen -1

For any comparison operator listed in the table shown below, the sub-query must return single row (in other words, the sub-query, must be a single-row sub-query, otherwise it will fail)

Single-row Sub-query Operators

Symbol	Meaning
=	equal
>	greater than
>=	greater than or equal
<	less than
<=	less than or equal
<>	not equal
!=	not equal

The operators in the following table can use multiple-row sub-queries:

Multiple-row Sub-query Operators

Symbol	Meaning
IN	equal to any member in a list
NOT IN	not equal to any member in a list
ANY	returns rows that match any value on a list
ALL	returns rows that match all the values in a list

There are several approaches to resolve this, most popular one being changing the inequality operator to a multiple-row operators (say, if the operator is "=" than change it "IN", "ANY", "ALL", so that it does not matter if multiple rows are returned.).
However, I found that if you use inline views, you can retain inequality operator as shown below in (Screen -2)

Screen -2

Note: In order to get consistent result, use the sub-query based on primary key. The above method is based on unique key

How to open linux folders in the same window?

Default behavior while opening folders in Linux, meant, a new window for each folder I double-clicked on. To prevent that, and to ensure Linux opens folders in the same window, I followed the steps listed below:
Go to home folder (Screen -1)

Screen -1

Select Edit from the Menu and scroll down to click on Preferences (Screen -2)

Screen -2

Click on Behavior Tab and select "Always open in browser window" (Screen -3)

Screen -3

The explorer view is shown below (Screen -4)

Screen -4

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.

goosh.org

Goosh.org is a Open Source Command line search utility created by Stephan (to learn about him goto http://posterous.com/people/3zFYfHr9U ). Returns 4 results at a time. If you are interested to give it a look goto http://www.goosh.org/ )

conversion differences between TO_NUMBER and TO_CHAR

It was interesting to note that what works in TO_CHAR conversion does not in TO_NUMBER. For TO_CHAR, I passed 12.56 for parameter1 and '999.9' for parameter2. It returned 12.6(has obviously rounded it up. Whereas, try it for TO_NUMBER, ORACLE returned an error ORA-01722

PLSQL & SQL Guidelines

Trivadis has taken considerable time and effort to publish a PLSQL and SQL Guidelines. Very organized and well laid out. It also earned a foreward from Steven Feurerstein. To read /download goto their website: http://www.trivadis.com/fileadmin/user_upload/email4download_Pflicht/TVD_PLSQLSQLCodingGuide.pdf

Companies are Adding Jobs. Are you ready?

Interesting Post by Dice.com.

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.

How to increase Linux Display/Screen size?

While in Linux, under System Menu, Click on "Preferences", then select "Screen resolution" (Screen1). Change the resolution to the size you would to increase it to (Screen2).

Screen-1

Screen-2

Upgrading SQLDEVELOPER

Since I had 11g installed, it already came with SQLDEVELOPER. So, while Upgrading, I unzipped the rpm into a different directory. When I tried to install it as Oracle User, I got "transaction lock" error message

So, I logged into as root and reran the command; worked successfully.
Help--> about should show you a screen as shown below

How to add SQLDEVELOPER to Linux Start Menu

How to add SQLDEVELOPER to Linux -GNOME- programs /start Menu? Well, I liked to have a shortcut to SQLDEVELOPER added to Linux Start Menu, similar to Windows  Programs Menu automatically. Since SQLDEVELOPER does not do that, we need to manually add an entry to the programs folder. (By the way, this procedure can be used to add any executable or shell program to the linux menu).

I have enclosed below screenshots to help understand the procedure.See

Screen-1

Click on New Item (Screen 2)

Screen-2

Third, Click on the Browse Button (Screen 3)

Screen-3

Fourth, navigate to the directory where SQLDEVELOPER.sh is kept (Remember to click on sqldeveloper.sh and not on exe; usually it will be located at $ORACLE_HOME/sqldeveloper and select sqldeveloper.sh (Screen -4)

Screen-4

This will bring you back to Screen 3. Now Click on "No Icon" button.  (Screen-5)

Screen-5

 Navigate to $ORACLE_HOME/sqldeveloper/sqldeveloper/bin and select splash.png (Screen-6)

Screen-6

Click OK, you will the program appear in the menu. (Screen-7)

Screen-7

When you click on the program you will see a screen like the one below (Screen-8)

Screen-8

Once launched, SQLDEVELOPER should look like the screen below (Screen-9)

Screen-9

Note: Since I have 11g installed and 11g comes with SQLDEVELOPER, I need to locate the latest SQLDEVELOPER. Used locate command to determine the path (see below). The first one is latest version.

Screen-10

Update: This has been published at Oracle SQLDEVELOPER tips & tricks as well Click here to goto SQLDEVELOPER Exchange