PL/SQL Developer Usage Tips

1, PL/SQL developer remember login password

When using PL/SQL developer, you want PL/SQL developer to remember the username and password to log in to Oracle for your convenience;

Setup method: PL/SQL Developer 7.1.2->tools->preferences->oracle->logon, "Store History" is checked by default, tick "store With password "can, re-login in the input password once remember.

2. Execute a single SQL statement

When using PL/SQL developer, press the F8 key, PL/SQL developer is the default to execute all of this window, and need to be set to the SQL statement that the mouse, that is, the execution of the current SQL statement;

Setup method: PL/SQL Developer 7.1.2-->tools->preferences-->window types, check "AutoSelect Statement".

3. Formatting SQL statements

When using PL/SQL developer, sometimes the SQL statement entered is too long or too messy, I hope to use a more general format words, so look good, or analysis;

To use: Select the SQL statement that you want to format, and then click the PL/SQL Beautifier button on the toolbar.

4. View Execution Plan

When using the SQL window of PL/developer, sometimes the efficiency of input SQL statement execution, analysis of the following table structure, how to improve the efficiency of the query, you can view the execution plan provided by Oracle;

To use: Select the SQL statement you want to analyze, and then click the Explain Plan button on the toolbar (that is, execution plan), or press F5 directly.

5. Debugging Stored Procedures

When using PL/SQL developer to operate Oracle, some stored procedures are sometimes called, or stored procedures are debugged;

Method of calling the stored procedure: first, select procedures in the browser to the left of PL/SQL developer to find the stored procedure that needs to be called, then select the stored procedure for debugging, right-click, select Test, and in the pop-up Test scrīpt window, For a parameter defined as an in type, you need to enter a value for the parameter's value; Last click the number of bars button above: Start debugger or press F9; Last click: RUN or Ctrl+r

· Oracle Learning Manual: Beginner Common Error Episodes

No one would deny that Oracle is one of the most influential database products in the world, but the good stuff seems to be not so easy to use (at first glance), or even ruthless-always giving layman a ruthless error number. Here is my personal summary, all useful, hoping to give beginners a little revelation.

About "good things always seem to be not so easy to use (at first)" A joke: in the IBM DB2 512, 513 training before the campus download to the installation program, but let us a few students to study a half-day, also do not know which files are installed files, unexpectedly no installation success. Finally, I agree: It seems that this training is really necessary! Afterwards, only to know-we downloaded 4linux!

[Take 8.1.6 as an example]:

1, Ora-12541:tns: No listener

Cause: There is no boot listener or the listener is corrupted. If it is the former, use the command net start Oracleorahome81tnslistener (the name may differ), and if it is the latter, use the listener configuration of the Net8 Configuration Assistant Tool Wizard Add a listener (basically do not write any information, all the way OK. You may need to remove all the listeners before adding them!)

2. Ora-12500:tns: The listener cannot start the private server process

Or

ORA-12560:TNS: Protocol Adapter error

Cause: Oracle's database service is not started. You can use the command net start oracleserviceoradb (oradb as the database name). If it's still not resolved, keep looking down.

3. If the database service fails to start, it is most likely that its registry key value is corrupted, and the best practice is to do the following two steps:

1) Oradim-delete-sid oradb Delete database service item

2) Oradim-new-sid oradb New database service entry

Note: If there is an error in this process, restart the computer!

4, Ora-12154:tns: Can resolve the service name

Cause: The Network Service name for Oracle is not configured correctly. Use the local network service name configuration in the Net8 Configuration Assistant Tool Wizard to configure TNS. If it's still not resolved, keep looking down.

5. Ora-1034:tns:oracle Not Available

Cause: The Oracle database service started correctly, but the database is not open!

Use the command:

1) SVRMGRL start Service Manager

2) Connect internal login as internal

3) Startup Open database

6, Ora-12560:tns: Protocol adapter error (stubborn)

Cause: Unknown.

Solution: Must kill skill--open "Windows Task Manager", kill ORACLE.exe and ORADIM.exe process, write your own ora_startup.bat, execute it!

Ps:

1. My ora_startup.bat:

net start Oracleorahome81tnslistener

net start oracleserviceoradb

SVRMGRL generally do not use, but sometimes it is necessary, the concrete steps to see the 5th step.

2. My ora_shutdown.bat:

net stop Oracleorahome81tnslistener

net stop oracleserviceoradb

3. For Oracle related service name, see the service name beginning with Oracle in the services of the administrative tools.

· Oracle 10g Green Client plus PL/SQL developer-build a convenient Oracle client usage Environment

I've been doing this for the whole summer. Oracle-based data processing program. But there has been no convenient way to find a suitable manual access to the lab's database.

The coolest thing I do is to write a program to use JDBC to connect to the database to get the table I want ...

Later found in the laboratory there are many machines are installed in the Oracle client, but MS is the lab to build the database with the original installation CD-ROM installed, I have a physical fear of such a bloated client, and then still use the raw method of their own development.

Until one day, see Oracle's official website on the top of the 10g green version of the simple client ...

Http://www.oracle.com/technology/tech/oci/instantclient/index.html

Oracle's official website download software is to account, this application is a, I do not have online space to store related software, even if there is no official online reliable and durable, so only to a link.

Http://www.allroundautomations.nl/plsqldev.html

PL/SQL Developer is a software that develops database-related engineering, MS is free, doesn't know much, but sees many people developing Oracle-related databases in use, and I'm generally used only as a client interface for interacting with database servers ... Most of the time is mainly open a window to knock SQL statements, (Overkill ... ）

Below is the installation process, because it is free of installation, so you have to configure some environment variables and files, more trouble ...

First, the downloaded Oracle client's compression package is extracted to a path, the local machine is extracted to E:\OracleClient, so, in fact, the path of the Oracle client is E:\OracleClient\instantclient_10_2

Then go to the Instantclient_10_2 folder, create a new network folder, and create a new admin folder under the network folder. In the Admin folder, create a new name for the Tnsnames.ora file, which is used to configure the login information to connect to the remote database (the client software obtains the information of the connected database from the file under this relative path), as follows:

DatabaseName =

(DESCRIPTION =

(Address_list =

(address = (PROTOCOL = TCP) (HOST = IP ADDRESS) (PORT = 1521))

)

(Connect_data =

(service_name = Database NAME)

)

)

For example, the corresponding file contents of my linked Lab database are:

TCM =

(DESCRIPTION =

(Address_list =

(ADDRESS = (PROTOCOL = TCP) (HOST = *.*.*.*) (PORT = 1521))

)

(Connect_data =

(service_name = TCM)

)

)

IP I will not write it out, I use the database named Tcm,so ...

Then install the PL/SQL Developer, the process is fool-style. After you enter PL/SQL (you will be asked to log in to the database when you enter, but now PL/SQL cannot find your Oracle client now, so login is not successful, but you can enter the software), select Tools->preferences, at Oracle Home In the OCI Library two column, the path of the Oracle client is populated with the paths of the OCI files (the Oci.dll file is directly under the Instantclient_10_2 folder), so what I'm filling in the two options is "E:\OracleClient\ Instantclient_10_2 "and" E:\OracleClient \instantclient_10_2\oci.dll ". Log in again to be able to log in successfully. Restart PL/SQL if not yet.

The green version of the client may have problems with Chinese support, primarily because the specified character set on the server side and the default character set of the client are not the same, as long as the server-side character set is found and the client's character set settings are consistent with the server-side. There are several ways to modify the client character set settings, you can modify the registry, or you can use the environment variable method to resolve it. However, I introduce the client is the green version, just unzip to a certain path, so the method of the registry is not very applicable here, so I wrote a startup script, before starting PL/SQL, a temporary environment variable Nls_lang, and assign value to the variable, and then start the software. (I've done experiments, it's not working here by establishing a system environment variable, for specific reasons I can't say clearly ><)

The contents of my script Plsql.bat are as follows:

Set Nls_lang=simplified Chinese_china. Zhs16gbk

CD "C:\Program files\plsql Developer"

PLSQLDev.exe

The path where the second line enters is the path where PL/SQL developer is installed.

My reality here is that the server-side charset is set to simplified Chinese_china. ZHS16GBK, so I'll assign this value to Nls_lang. The server database can be accessed normally by running the Plsql.bat script.

PL/SQL Developer Usage Tips