Thursday, April 22, 2010

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:


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


Note: If the SERVEROUTPUT is not set right, Oracle will raise ORA-20000 error as shown below (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)


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)