Home » Other » Suggestions & Feedback » PL/SQL Developer settings FAQ in CLient tools forum
PL/SQL Developer settings FAQ in CLient tools forum [message #618910] Wed, 16 July 2014 08:15 Go to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
The posts here are to prepare a sticky for the Client tools forum keeping in mind the FAQs about SQL*Plus commands, PL/SQL Developer and Toad settings etc. I will keep adding more, and request the experts to keep ading more or modify to make it better.

Please let me know your suggestions.


Regards,
Lalit
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #618911 is a reply to message #618910] Wed, 16 July 2014 08:18 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
1. How to view the output from DBMS_OUTPUT.PUT_LINE.

While working in a SQL Window, you would see tabs on top, SQL, Output and Statistics. All the output from the DBMS_OUTPUT.PUT_LINE procedure is displayed in the Output tab.

If you are wondering why you do not see an output? That's because you did not select the "Enabled" checkbox.

Just select the checkbox and re-execute your code.

/forum/fa/12030/0/
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #618920 is a reply to message #618919] Wed, 16 July 2014 08:44 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Moderators,

Note : I am still working more on PL/SQL Developer FAQs. I cannot attach/insert more than 1 picture in 1 post, hence, posted all the required snapshots. Will reconcile everything once more content is ready.

Thinking how to keep all at one place when I can't merge the different topics having more than 1 picture?

Yet to work for SQL*Plus.
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #618926 is a reply to message #618920] Wed, 16 July 2014 10:04 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
How to merge them together as different points under a single header. I mean above posts are about mainly three things, checking output, object browser and using beautifier. So, these are 3 points for PLSQL Developer. I know it is possible just like we have other stickies, but how to do all this merge and consolidation?
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #618931 is a reply to message #618926] Wed, 16 July 2014 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Don't care, when this will be completed we will merge them.

Re: PL/SQL Developer settings FAQ in CLient tools forum [message #618934 is a reply to message #618931] Wed, 16 July 2014 10:55 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Alright then.

I will post all required things in this thread, similarly for sqlplus I will create a thread to keep the contents different and avoid confusion. Then I will upload a word/ppt doc for reference to show which snapshot belongs to what point respectively. I am currently maintaining a microsoft word doc for my own reference.

Any more suggestions are welcome. I need suggestions mainly about the frequently asked questions about these development tools. So that I can create posts based on them.

Currently I have these in my mind :

1. Checking output.
2. Object browser.
3. Formatting and indentation.
4. Plan table and errors related to it.
5. Product installation, registration and its location in OS.
6. Checking client tool version.
7. Common myths
8. When to avoid using tool and preferring SQL*Plus over 3rd party tools.
9. Advantages and flexibility to use GUI based tools.
10. Checking execution plan and customizing the execution plan window.
11. How a command window differs from SQL*Plus.
12. Avoid depending on the keywords and reserved words in a 3rd party tool.
13. How to view and edit plsql objects.
14. How to compile the plsql objects and look at error stack.
15. Very important : How to use help index.
16. How to synchronize the tool with Subversion repository.
17. Setting up the tool for first time, like port configuration, TNS entries.
...and many more

Any more ideas?

Having said that, it will take ample time to prepare them, however, we can contribute and prepare it incrementally.



Regards,
Lalit
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #618951 is a reply to message #618934] Wed, 16 July 2014 14:15 Go to previous messageGo to next message
Littlefoot
Messages: 21805
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
  • If #15 is very important, why wouldn't you move it to #1?
  • In the initial message, you said that this is supposed to cover frequently asked questions. How many questions do you expect to be FAQ? List of 17 topics and many more looks as if you are about to write a comprehensive tutorial, which is - in my opinion - not what FAQ is supposed to be.

    Don't mind me, go on, continue writing. I'm supporting your initiative by all means.
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #618962 is a reply to message #618951] Thu, 17 July 2014 00:28 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thanks, you are right, I need to stick to FAQ. But that's the difficult part. Will try, hopefully outcome should be useful.
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619174 is a reply to message #618962] Fri, 18 July 2014 10:53 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I spent quite some time on client tools forum to jot down the FAQs. As LF suggested, most of my points are not actually FAQ, so I am left with just couple of posts for PLSQL Developer.

Just an ETA, by next week I would be done with this topic, and will move on to SQL*Plus which has much more than 3rd party tools.

Once again, suggestions are more than welcome. Thanks to the folks who have positively responded to the initiative.
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619243 is a reply to message #619174] Mon, 21 July 2014 00:10 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
4. PLAN_TABLE

Most of the GUIs are helpful to a certain extent and create the PLAN_TABLE in a local schema if it doesn't exist there without noticing that there is a synonym to a shared PLAN_TABLE. Developers using GUIs often run into trouble and complain about "PLAN TABLE doesn't exist". What does it mean? It could be following reasons:

a. PLAN_TABLE doesn't exist in your schema. So you need to create it first in your schema.

SQL> @?/rdbms/admin/utlxplan.sql

Table created.


This tool doesn't have the capability to do certain things for you through preferences and settings, so we need to do them manually.

Now we have to create a plan_table synonym, either private or public.

b. Another possible reason is that, the plan table generated by the tool in the current schema is an older version of itself, hence it creates an older version of the plan_table. So first thing is to drop this old version of plan table and create it as mentioned above.

c. If you do not have CREATE privileges, it is certainly NOT the tool's issue. You have to create the plan table in a different schema and create a synonym, either public or private as mentioned above.
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619244 is a reply to message #619243] Mon, 21 July 2014 00:13 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
5. How to check the PLSQL Developer Version and other details :

a. Go to Help --> About

/forum/fa/12044/0/

  • Attachment: About.jpg
    (Size: 30.34KB, Downloaded 10073 times)
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619245 is a reply to message #619244] Mon, 21 July 2014 00:14 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
b. It will open a pop-up window.

/forum/fa/12045/0/
  • Attachment: Version.jpg
    (Size: 55.07KB, Downloaded 10045 times)
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619304 is a reply to message #619245] Mon, 21 July 2014 05:55 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
6. NLS Options

One of the most common issues is with the improper settings of NLS parameters. Questions like
"But my date format is not correct" OR "I do not see the time part"

Such questions are all related to the NLS Options. All you need to do is, set the NLS settings properly per your environment needs.

We have 3 options for the DATE, TIME and NUMBER formats.
a. User defined
b. Windows format
c. Oracle format

For all the three formats, only ALTER SESSION statement and session level format mask using SQL can override. Else, the output will be displayed per the NLS settings of the client tool.

Let's see,

User complaints that he gets output of "SELECT TRUNC(SYSDATE) FROM DUAL" as DD-MM-YYYY,

/forum/fa/12047/0/

However desired format is MM/DD/YYYY.
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619305 is a reply to message #619304] Mon, 21 July 2014 05:56 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
It is due to the following settings of DATE.

/forum/fa/12048/0/
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619306 is a reply to message #619305] Mon, 21 July 2014 06:00 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
User then changes it to Oracle format which is as defined by the Oracle environment (NLS_DATE_FORMAT). OR user defined as MM/DD/YYYY and gets the output displayed as required.

/forum/fa/12049/0/

Play around with the NLS options for more understanding.

Read the following links for further clarity,
http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/
http://www.oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals.php
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619307 is a reply to message #619306] Mon, 21 July 2014 06:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
7. Help and User Guide

/forum/fa/12050/0/

On clicking the User Guide, it will open a PDF file containing the help contents and user guide. It has lot of contents which covers the different aspects of the tool.
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619308 is a reply to message #619307] Mon, 21 July 2014 06:10 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
8. PL/SQL Developer online-forum

For any question regarding the product or any difficulties, you could also post your question in the online forum here --> http://www.allroundautomations.com/plsqldev.html
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619314 is a reply to message #619308] Mon, 21 July 2014 06:26 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@Moderators,

Can someone merge the above topics to see how it looks with those 8 points. I am currently not getting any more FAQ for this tool. I will continue checking out the client tools forum for any other frequent questions.

Any suggestions are welcome, please let me know if someone has more points which are FAQ. I will prepare those points too.
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619318 is a reply to message #619314] Mon, 21 July 2014 07:45 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Just one point before merging, please add citation to the links posted in http://www.orafaq.com/forum/mv/msg/193608/619306/#msg_619306

1. A good explanation by Ed Stevens about common myths about NLS DATE format, settings and precendence order.
2. Nice demonstration about DATE, TIMESTAMP and INTERVALS by Tim Hall.

Sorry I fotgot to mention ot above.
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619508 is a reply to message #619318] Wed, 23 July 2014 02:01 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
9. EXPLAIN PLAN preferences

There are lots of questions on explain plan like,
a. I don't see ACCESS PREDICATES in the explain plan window
b. How to include FILTER PREDICATES in the explain plan
c. How to configure the EXPLAIN PLAN window

To get rid of such issues, all we need to do is, set the explain plan window preferences.

Before setting the required preferences:

/forum/fa/12055/0/
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619509 is a reply to message #619508] Wed, 23 July 2014 02:11 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
How to set the explain plan preferences:

It could be done in either of these 3 ways :

1. Go to, Tools --> Preferences --> Window Types --> Plan Window
2. Open explain plan window, on the bar you would see an icon at right end which looks like a spanner, click on it.
3. In your SQL window, the way you execute your SQL, press F5, it will open explain plan window, then follow step 2.

Following the steps will point to the Preferences window. Using the right arrow, select the required columns from "Available column" and move them to "Selected columns".

/forum/fa/12056/0/
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619510 is a reply to message #619509] Wed, 23 July 2014 02:13 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
After setting the required preferences:

Now you could see the required columns added to your explain plan.

/forum/fa/12057/0/
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619511 is a reply to message #619510] Wed, 23 July 2014 02:16 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Do I need to open different window all the time for checking explain plan for different SQLs?

No.
If you want to view the explain plan of another SQL in the same plan window and do not bother about losing the explain plan of previous query, then just copy paste the required query in the existing plan window and press F5. The new explain plan is for the SQL which you just pasted.
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619512 is a reply to message #619511] Wed, 23 July 2014 02:19 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
10. Setting up keyboard shortcuts

Go to, Tools --> Preferences --> Key configuration


/forum/fa/12058/0/
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619525 is a reply to message #619512] Wed, 23 July 2014 03:16 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
2. Customizing the object browser

/forum/fa/12059/0/
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619527 is a reply to message #619525] Wed, 23 July 2014 03:19 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
3. How to indent and format the code using PL/SQL Beautifier.

This is a very important tool which makes the code formatting and indentation quite handy. All you need to do is customize the settings the way you want your code to be formatted. The red error points to the PL/SQL Beautifier button.

/forum/fa/12060/0/

Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619549 is a reply to message #619527] Wed, 23 July 2014 06:25 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
To save your format and indentation setting, follow these steps :

a. Go to Tools --> Preferences

/forum/fa/12061/0/
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619550 is a reply to message #619549] Wed, 23 July 2014 06:26 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
b. Select PL/SQL Beautifier under User Interface

/forum/fa/12062/0/
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619551 is a reply to message #619550] Wed, 23 July 2014 06:27 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
c. Click on Edit.

/forum/fa/12063/0/
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619553 is a reply to message #619551] Wed, 23 July 2014 06:32 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
d. Make changes and then click on Save As.

/forum/fa/12064/0/
Re: PL/SQL Developer settings FAQ in CLient tools forum [message #619555 is a reply to message #619553] Wed, 23 July 2014 06:34 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Code before and after using PL/SQL Beautifier :

/forum/fa/12065/0/
Previous Topic: Consider "FEEDBACK" as 15th point in posting guidelines?
Next Topic: Posts made before registered date? How?
Goto Forum:
  


Current Time: Tue Mar 19 03:30:44 CDT 2024