Shulou(Shulou.com)06/01 Report--

The use of PLSQL Developer software Daquan

Chapter I PLSQL Developer Features

PL/SQL Developer is an integrated development environment for the development of Oracle database storage program units. Nowadays, more and more business logic and application logic have turned to Oracle Server, so PL/SQL programming has become an important part of the whole development process. PL/SQL Developer focuses on ease of use, code quality, and productivity, taking full advantage of the main advantages of Oracle application development.

The main features of PL/SQL Developer:

PL/SQL editor, powerful-the editor has many intelligent features, such as syntax enhancement, SQL and PL/SQL help, object description, code assistant, compiler hints, PL/SQL refinement, code content, code grading, browser buttons, hyperlink navigation, macro library, and many other intelligent features to meet the most demanding users' needs. When you need some information, it will appear automatically, and you can call it up with a single click at most.

Integrated debugger (requires Oracle 7.3.4 or higher)-this debugger provides all the features you need: Step In, Step Over, Step Out, stop on exception, breakpoints, observe and set variables, observe the entire stack, and so on. Basically any program unit (including triggers and Oracle8 object types) can be debugged without any modification.

PL/SQL refiner-this refiner allows you to normalize SQL and PL/SQL code through user-defined rules. When you compile, save, and open a file, the code is automatically normalized. This feature increases the productivity of your code, improves the readability of PL/SQL code, and facilitates collaboration among large-scale work teams.

SQL window-this window allows you to enter any SQL statement and view and edit the results in a grid, supporting query patterns by example to find specific records in a result collection. In addition, there is a history cache that allows you to easily invoke previously executed SQL statements. The SQL editor provides the same powerful features as the PL/SQL editor.

Command window-use the command window of PL/SQL Developer to develop and run SQL scripts. This window has the same look and feel as SQL*Plus, plus a built-in script editor with syntax enhancements. In this way, you can develop your own scripts without having to edit / save the script / convert to SQL*Plus/ to run the script process, or leave the PL/SQL Developer integrated development environment.

Reports-PL/SQL Developer provides built-in reporting capabilities that allow you to run reports based on program data or Oracle dictionaries. PL/SQL Developer itself provides a large number of standard reports, and you can easily create custom reports. The custom report is saved in the report file and then included in the report menu. This makes it convenient to run custom reports that you often use.

You can use the Query Reporter free software tool to run your report, without the need for PL/SQL Developer, directly from the command line.

Engineering-PL/SQL Developer's built-in engineering concepts can be used to organize your work. A project includes a collection of source files, database objects, notes, and options. PL/SQL Developer allows you to work within a specific set of items, rather than within a full database or schema. In this way, if you need to compile all project items or move the project from one location or database to another location, it becomes easier to find the required project items.

To-Do entries-you can use To-Do entries in any SQL or PL/SQL source file to quickly record what needs to be done in that file. You can later access this information from the To-Do list, which can be done at the object layer or the engineering layer.

Object browser-Configurable tree browsing can display all information related to PL/SQL development. With this browser, you can get object descriptions, browse object definitions, create test scripts for debugging, enable or disable triggers or constraints, recompile illegal objects, query or edit tables, browse data, find text in object sources, drag and drop object names to editors, and so on.

In addition, the object browser can also display dependencies between objects, which you can extend recursively (such as package reference checks, browsing reference tables, chart types, and so on).

Performance optimization-with PL/SQL Profiler, you can browse the timing information (Oracle8i or higher) of each line of PL/SQL code executed to optimize your SQL and PL/SQL code performance.

Further, you can automatically get the SQL statements and PL/SQL program statistics executed. This statistical information includes CPU usage, block Imax O, record Imax O, table scan, classification, and so on.

HTML Guide-Oracle currently supports online guides in HTML format. You can integrate it into the PL/SQL Developer workspace to provide content-sensitive help in the event of editing, compilation errors, or runtime errors.

Non-PL/SQL objects-without using any SQL, you can browse, create, and modify behaviors for tables, sequences, symbols, libraries, directories, jobs, queues, users, and roles. PL/SQL Developer provides an easy-to-use form, and as long as you enter information into it, PL/SQL Developer will generate the corresponding SQL to create or transform objects.

Template list-PL/SQL Developer's template list can be used as a real-time help component to help you enforce standardization. By clicking on the appropriate template, you can insert standard SQL or PL/SQL code into the editor, or create a new program from a draft.

Query Builder-the graphical query builder simplifies the creation of new selection statements and the modification of existing statements. Simply drag and drop tables and windows, select columns for the area list, and define federated tables based on external key constraints.

Compare user objects-after making changes to table definitions, views, program units, and so on, it can be useful to pass these changes to other database users or check the differences before and after the changes. This may be another development environment, such as a test environment or a production environment. The compare user objects feature allows you to compare selected objects, visualize different points, and run or save SQL scripts that apply the necessary changes.

Export user objects-this tool can export DDL (data definition language) statements for objects selected by the user. You can easily recreate objects for other users, or you can save files as backups.

Tools-PL/SQL Developer provides several tools specifically to simplify day-to-day development. Using these tools, you can recompile all illegal objects, find text from database sources, import or export tables, generate test data, export text files, monitor dbms_alert and dbms_pipe events, browse session information, and more.

Authorization-in most development environments, you don't want all databases to have the full functionality of PL/SQL Developer. For example, you can allow full functionality of PL/SQL Developer in database development, while you can only allow data query / edit and object browsing in database testing, and you don't even want PL/SQL Developer access in database development. With the PL/SQL Developer authorization feature, you can easily define the features that are allowed by specific users or rules.

Plug-in extensions-PL/SQL Developer functionality can be extended through plug-ins. Plug-ins are available on the Add-ons page and can be downloaded for free. Plug-ins (such as version control plug-ins or plsqldoc plug-ins) can be provided by Allround Automations or other users. If you have the programming language to create DLL, you can also write your own plug-ins.

Multithreaded IDE--PL/SQL Developer is a multithreaded IDE. This way, you can continue to work while SQL queries, PL/SQL programs, debugging sessions, and so on are running. Moreover, the multithreaded IDE means that it does not abort when a programming error occurs: you can interrupt execution or save your work at any time.

Easy to install-unlike SQL*Net, there is no need for middleware or database object installation. Just click the installer button and you can start installing and using the software.

Chapter II PLSQL Developer configuration 2.1 remember passwords

This is a controversial feature because remembering passwords can cause data security problems. But if it is the development of the library, the password can even be the same as the user name, each time enter the password is really meaningless, you can consider letting PLSQL Developer remember the password.

Setting method: menu Tools-> Preferences-- > Oracle-- > Logon History-- > Store With Password

Log in again and enter the password again and you will remember it.

If the above methods do not work, use the following methods:

In the "Fixed Users" in the interface mentioned above

Add a username / password @ ORACLE_SID that can be logged in after direct selection

Such as:

Cbsdb/cbsdb@cbsdb

When logging in again, from behind the Username of Oracle Logon's login interface. At the button

Just select the user who needs to log in.

2.2 SQL statement characters are all uppercase

The core of the information system is the database. When there is something wrong with the system, the first thing to check is the SQL statement. How to quickly find the SQL statement in the vast log is a more painful thing. All uppercase SQL statements do not completely solve this problem, but it is relatively easy to find a line of all uppercase characters in the middle of a pile of code. Setting method: menu Tools-> Preferences-- > Editor-- > Keyword Case-- > Uppercase

2.3 Special Copy

SQL statements written in SQL Window usually need to be put in Java or other languages, and you need to convert them into strings and add corresponding hyphens. There is no need to repeat this. Right-click on the written SQL and use a special Copy!

Setting method: right mouse button-> Special Copy

2.4 Custom shortcut keys

There are many keys reserved in PLSQL Developer for users to customize. In general, after opening PLSQL Developer, the most commonly used is to open SQL Window and Command Window. You can define shortcut keys ALT+S and ALT+ C for these two operations.

Setting method: menu Tools-> Preferences-- > Key Configuration

Shortcut:

=

Edit/UndoCtrl+Z

Edit/RedoShift+Ctrl+Z

Edit/PL/SQL BeautifierCtrl+W (Custom)

Shift+Home Select the cursor position to the beginning of the line

Shift+End Select cursor position to the end of the line

Ctrl+Shift+Home Select the cursor position to the beginning of the first line

Ctrl+Shift+End Select the cursor position to the end of the trailing line

Object:ViewShift+Ctrl+V View (Custom)

Object:DescribeShift+Ctrl+D structure (Custom)

Object:PropertiesShift+Ctrl+P property (Custom)

Object:BrowseShift+Ctrl+B browsing (Custom)

Object:Edit DataShift+Ctrl+E Editing data (Custom)

Object:Standard QueryShift+Ctrl+S Standard query (Custom)

Edit/Find ReplaceCtrl+F

Edit/Find NextCtrl+L

Edit/Find PreviousShift+Ctrl+L

Edit/Replace NextCtrl+P

EDIT/Full ScreenCtrl+F11

Edit/Go to LineCtrl+G

Edit/Next Tab PageCtrl+H

Edit/Previous Tab PageShift+Ctrl+H

Session/ExecuteF8

Session/BreakShift+Esc

Session/CommitF10

Session/RollbackShift+F10

Debug/Toggle BreakpointCtrl+B

Debug/StartF9

Debug/RunCtrl+R

Debug/Step IntoCtrl+N

Debug/Step OverCtrl+O

Debug/Step OutCtrl+T

Tools/Explain PlanF5

Tools/Code AssistantF6

Editor: Start of DocumentCtrl+PgUpORCtrl+Home

Editor:End of DocumentCtrl+PgDnORCtrl+End

Editor:Delete LineCtrl+Y

Editor:Navigate BackAlt+Left

Editor:Navigate ForwardAlt+Right

SQL Window:Previous SQLCtrl+Up

SQL Window:Next SQLCtrl+Down

2.5 execute a single SQL statement (automatically select statements based on cursor position in SQL Window)

When using PL/SQL Developer's SQL Window, press the F8 key. By default, PL/SQL Developer executes all the SQL statements of the window, which needs to be set to the SQL statement where the mouse is located, that is, to execute the current SQL statement.

Setting method: PL/SQL Developer-> tools- > Preferences-- > Window types, and check "AutoSelect Statement". Note that each statement is followed by a semicolon.

2.6 automatic replacement

Quickly enter SQL statements, such as enter s, press the space, automatically replace it with SELECT; and then, for example, enter sf, press the space, and automatically replace it with SELECT * FROM, which is very convenient and saves a lot of time to write repeated SQL statements.

Setting method: menu Tools-> Preferences-- > Editor-- > AutoReplace. -> Edit

Some rules are defined below as a reference

S=SELECT

F=FROM

W=WHERE

O=ORDER BY

D=DELETE

Sf=SELECT * FROM

Df=DELETE FROM

Sc=SELECT COUNT (*) FROM

When using PL/SQL Developer's SQL Window, sometimes the input SQL statement is too long or too messy. I hope we can write it in a more general format, so that it will look better and easy to analyze.

How to use: select the SQL statement that needs to be formatted, and then click the PL/SQL beautifier button on the toolbar.

Click the menu tools-> window list, drag the pop-up window to the appropriate location in the lower left corner, and then click the menu window- > save layout

2.9 prevent login timeout

Tools- > Preferences-- > Oracle- > Connection Select "check connection"

2.10 do not back up sql files

Tools- > Preferences- > Files- > backup, select disabled in backup files on the page

2.11 right-click menu

Every text editing window in PL/SQL Developer (hereinafter referred to as PLD)

Such as SQL Window,Command Window and Porgram Window

Right-clicking on an object name will bring up a menu containing Operand commands, which we call the right-click menu here.

Object types can be tables, views, synonyms, stored procedures, functions, and so on.

Pop-up menus vary depending on the type of object.

Tables and views have functions such as View, Edit, Rename, Drop, Query data and Edit data.

View and Edit view and modify the structural information of the table, such as fields, primary keys, indexes and constraints, respectively.

Query data is equivalent to opening a new window and executing the select * from table.

Edit data is equivalent to opening a new window and executing the select * from table for update.

Stored procedures and functions have the function of Test, which can be debugged when selected.

Sometimes due to PLD recognition error, right-click the object will not come up with the correct menu

You can add a semicolon before the DDL or DML statement where the object is located, so that PLD can correctly determine the type of object

2.12 TNS Names

From the menu Help- > Support Info- > TNS Names, you can view the tnsnames.ora of Oracle.

2.13 Copy to Excel

Execute the Select statement in SQL Window, and after the result comes out, right-click on the following data area

By selecting Copy to Excel, you can copy the records of the data area to Excel as is.

But there are two points to note:

(1) you cannot start with = in field, otherwise Excel will be mistaken for a function

(2) the number should not exceed 17 digits, otherwise the following digits will be set to 0

But you can make Excel think that the field is text by adding 'before the number.

At the same time, for fields of Numbe type in the database, it is best to use to_char output, otherwise the display may not be normal.

2.14 keep the last opened SQL script

When re-entering PL/SQL Developer, Window List can open the document from the last exit:

(1) check the menu Tools- > Window list option

(2) Tools- > Perferences- > User Interface- > the right side of Options

Check "Autosave desktop".

(3) quit PL/SQL Developer and re-enter.

2.15 quickly find a table or other object with a known table name

In the Tools menu, check Object Browser to open the object browser

Double-click the folder where an object is located in the object browser

For example, tables are all in the Tables folder.

Then enter the table name as soon as possible and you will find the object that starts with the letters you enter.

2.16 quickly close the document window that is open in Windows List:

Hold down the shift key and left-click the document window you want to close.

2.17 remove the multi-connection mode of plsql 9.0 and above (what you have been looking for for a long time, must do)

In the multi-connection mode of plsql version 9.0 and above, during the actual development process, it is easy to connect to the wrong library and cause production accidents. You can disable this feature, as shown in the figure:

2.18 set connection indicator

The appearance changes after the setting of the following figure:

If something appears on the connection:

2.19 display of null value

Because it is difficult to distinguish between null values and spaces in the result set, you can set the color to distinguish null values.

2.20 set the maximum value of the nearest object 2.22 set the toolbar

When you type the names of database objects, the code assistant will automatically display information about them, and this preference page allows you to define the behavior of this feature.

Automatic activation

After a delay, the code helper can be called automatically (see below). You can also select the function key to activate the code assistant manually.

Delay

The number of milliseconds that the editor will wait before displaying the code helper list.

Code style

Controls how selected items will be inserted into the editor when you select them:

? Smart-the code assistant will consider the object being described to determine the style.

? Init Caps-the first character of each word (separated by an underscore) is capitalized.

? Lowercase-all characters are converted to lowercase.

? Uppercase-all characters are converted to uppercase.

If possible, use the original case.

When this option is allowed, if possible, the code assistant will determine the case of identifiers from the source stored in the Oracle dictionary. This applies to all program units and their elements (parameters, types, and so on), as well as to view columns, and goes beyond the code style preferences described above. If the original case cannot be determined, the code style will be applied. You can request to disable this feature for enforcement reasons.

Describe the user

Determines whether the objects owned by the user are listed when you type a user name followed by a period. If this option is allowed, you can also define which object types you want to include in the inner table.

The description context determines whether the code assistant should describe the context of the current user, editor, and program unit.

Minimum number of characters

Determines how many characters of words need to be typed before the context description can be automatically invoked. Note that you can always call the code assistant manually, even if the number of characters has not been typed.

Describe standard function

By default, the code helper describes standard functions such as to_char, add_months, and so on. If you are familiar with these functions, you can disable this option.

Set "automatic line wrapping for long SQL" under 2.24 PL/SQL Developer

Go to Tools-Preferences-Editor and make relevant settings, as shown in the following figure:

Click "Editor" to set it, as shown below:

This setting, in order to achieve automatic line wrapping of long code, check "wrap lines".

The long code automatically wraps lines, making it easier to display and read, as follows:

It is wrong to add the SQL above, only to demonstrate the line wrapping of a long SQL. Normal writing should be as follows:

SQL > create table cool ("1" number (4), "2" varchar2 (10), "3" varchar2 (9), "4" number (4), "5" date, "6" number (7 number 2), "7" number (7 number 2), "8" number (2))

Table created

Chapter III PLSQL Developer usage skills 3.1 New Command window

Dynamic Performance Tables not accessible

Automatic Statistics Disabled for this session

You can disable statistics in the preference menu,or obtanin select

Priviliges on the vicisession.jobsesstat and v$statname tables.

This error message will appear in different PL/SQL Developer versions. From the detailed error message above, we can tell that the reason for the error does not lie in the tool itself.

The reason for this prompt is:

Plsql dev collects user statistics when the user is running, but this prompt appears because the user you are logged in now does not have access to the view of vaccounsessionpower vSecretsesstat and v$statname, so it cannot collect the statistics of the current user, which conflicts with the Automatic Statistics configured in the plsql dev tool.

Here, record in detail the three ways to deal with this small problem.

3.2.1 the first treatment (not recommended)

Just check the "Don't show this message again" option in the Error dialog box that reported the error, and the error will not be prompted next time.

This method should be called the "ostrich approach". The problem has not been solved fundamentally.

The error message is described in great detail because the dynamic performance table does not have the right to be accessed, so we solve this problem by giving the required access rights to specific users.

Here are three specific ways that I can think of. You can continue to add.

1) if only a specific user has permission to query these three dynamic performance views, you can do the following

Note here: the view we authorize is V_$session, not V$session, because V$session is the same name, not a specific view. Otherwise, you will receive the following error.

Sys@ora10g > grant select on V$session to user_sec

Grant select on V$session to user_sec

*

ERROR at line 1:

ORA-02030: can only select from fixed tables/views

The correct authorization method is as follows:

SQL > grant select on V_$session to user_sec

SQL > grant select on V_$sesstat to user_sec

SQL > grant select on V_$statname to user_sec

2) you can use the following "simple and rude" method to deal with it.

SQL > grant SELECT ANY DICTIONARY to user_sec

3) the above two methods are user-specific processing methods. If you want all users (not limited to the above user_sec users) to be able to query these three dynamic performance views, you can do this by authorizing the query permissions to the public method, as follows. This ensures that all developers will not have the above error message again.

SQL > grant select on V_$session to public

SQL > grant select on V_$sesstat to public

SQL > grant select on V_$statname to public

3.2.3 third method (recommended)

Completely disable this function of PL/SQL Developer.

The methods are as follows:

Navigate to Tools-- > Preferences-- > Options

Find the "Automatic Statistics" option, remove the small check box in front of it, and then click "Apply" and "OK" to save exit.

3.3 View the execution plan

When using the SQL Window of PL/SQL Developer, sometimes the execution efficiency of the input SQL statement is analyzed. To analyze the structure of the following table and how to improve the efficiency of the query, you can check the execution plan provided by Oracle.

How to use it: select the SQL statement to be analyzed, and then click the Explain plan button on the toolbar (that is, execute the plan), or press F5 directly.

Click next in this and you can see the process of implementing the plan.

In addition, for plsql 11, you can also see html format, Plan Hash Value or SQL PROFILE, which is very useful:

3.4 Debug stored procedures

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

The method of calling a 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 debugged stored procedure, right-click, and select Test. In the pop-up Test Value window, you need to input a value for the parameter defined as in; finally, click the number of buttons above: Start debugger or press F9; finally, click: RUN or Ctrl+R

3.5 File location after exporting the data to the Excel table:

C:\ Users\ Administrator\ AppData\ Local\ Temp

As shown in the figure:

3.6Use pl/sql developer debug

Establish a Test WINDOW after connecting to the database

Enter the code to call SP in the window, and F9 starts debug,CTRL+N step debugging

3.7 Debug triggers

Right-click my computer-computer properties-advanced system settings-environment variables.

Find the variable name: NLS_LANG (if not, create a new one, if any, click-- Edit).

Change its variable value to: SIMPLIFIED CHINESE.ZHS16GBK

Then click-- OK, restart PLSQL and OK

3.9 related oracle official documentation

[tips] how to search full-text oracle official documents: http://blog.itpub.net/26736162/viewspace-2065550/

Http://wenku.baidu.com/view/412b6ac208a1284ac9504304.html

Use PLSQL Developer to view official documents

Today, we will teach you to use PLSQL Developer to view official documents. This is very convenient, which is equivalent to the online search function.

The first step is to download the official document locally and decompress it. I won't say much about this.

Oracle Server version

File size

Oracle 11.2 Library

408 MB

Oracle 11.1 Library

374 MB

Oracle 10.2 Library

446 MB

Oracle 10.1 Library

257 MB

Oracle 9.2 Library

209 MB

Oracle 9.0 Library

210 MB

Step 2: open plsql developer, press F1, or open the following interface:

Step 3 enter the location of the official document and click the create button

However, wheat seedlings now basically use offline chm files to search for what they need, as detailed in http://blog.itpub.net/26736162/viewspace-2065550/

Friends who need offline chm files can go to Weiyun of wheat seedlings to download it at http://blog.itpub.net/26736162/viewspace-1624453/

3.10 remove the annoying logon window when PL/SQL Developer is opened

Remove the annoying logon window when PL/SQL Developer is opened

Solution:

1. First of all, if your PL/SQL Developer configuration has been modified, back up your PLSQL configuration and your connection configuration first

two。 Delete the Preferences folder under C:\ Users\ login\ AppData\ Roaming\ PLSQL Developer

3. When you reopen it, you will find that the annoying logon window will disappear, but the connection configuration will also be cleared, so remember

Save previous connection configuration

4. Just import the configuration you backed up before.

Chapter IV the path to the PLSQL Developer configuration file

Version 11 of plsql supports configuration files to be exported and then imported, which is a good feature.

Brief description of existing features:

Main menu function all main menus can be set up toolbar buttons in PL/SQL to facilitate calling

CnPlugin / Comment / & Commnet Lines comments the currently selected code with "- -"

CnPlugin / Comment / & Uncommnet Lines removes the currently selected code commented with "- -"

CnPlugin / & ReConnect database connection interrupted by reconnection

CnPlugin / & ExPaste formats the currently selected or empty clipboard into an IN string, such as the format string AA,BB,CC is ('AA','BB',CC')

CnPlugin / & ExCreate inserts the field description of COMMENT when creating a table, such as CREATE TABLE tt (ID NUMBER-- number); statement, in addition to executing the current table-building statement, the comment on column TT.ID is' numbering 'comment statement is automatically generated

CnPlugin / Script / Load From MDB reads SQL statements that have been saved in the Access database as a list window

CnPlugin / Script / Save To MDB saves the current SQL statement to the Access database

CnPlugin / Script / Save As To MDB saved as the current SQL statement to the Access database

CnPlugin / Toggle Read Only set / unset the current SQL window to read directly.

CnPlugin / Find All... Find the specified string for the current window, list all matches, and support regular expression lookup

CnPlugin / & Preferences CnPlugin plug-in property Settings window

CnPlugin / & About' CnPlugin about window right-click menu function

Query data using alias uses the field comment character as the field name to spell out the query SQL statement (selected table name appears)

Open in new SQL Window copies the SQL statement selected in the current window to the new window (the selected string appears)

Execute in new SQL Window copies the SQL statement selected in the current window to the new window and executes the statement (the selected string appears)

Generate Word Documentation exports the contents of the current table structure to an Word document (selected table name appears)

No menu function

Shortcut key input function: such as enter s space, bring out 'select * from', support cursor positioning to log in to open or execute the specified SQL file

My common function is the Expaste function, which automatically adds single quotation marks to copied text, which is very useful.

After performing an expaste paste:

PL/SQL Developer startup Times error:

"Control 'dxDockBrowserPanel' has no parent window"

Cause: once the PL/SQL Developer 8.0.4 was opened and the interface was not loaded, the machine crashed, then restarted forcefully, and then opened PL/SQL Developer, an error was reported.

What you've tried:

1. This problem occurs in several versions, such as reinstalling PL/SQL Developer7.1.5/8.0.2/8.0.4/9.0.2.

two。 System restore is also invalid.

3. The profile to delete C:\ Users\ user name\ AppData\ Roaming\ PLSQL Developer is invalid.

Solution:

Delete from the registry

HKEY_CURRENT_USER\ Software\ Allround Automations\ PL/SQL Developer\ Docking

It could also be Docking1, Docking2, Docking3.

Just delete all this stuff and open OK.

6.2 64 bit is not supported

The solution that PLSQL Developer can not connect to Oracle 11g64 bit installed on Win7 64-bit system

Because 64-bit Oracle is installed on the native Win7X64, when you try to access it using PLSQL Developer, it is reported that the oci.dll file cannot be loaded. The original oci.dll is 64-bit, 32-bit application PLSQL Developer naturally can not be loaded.

There are currently three solutions to this problem.

6.2.1 method one, general method on the Internet

1) install Oracle 11g 64-bit

2) install 32-bit Oracle client (instantclient-basic-nt-11.2.0.2.0)

Download instantclient-basic-nt-11.2.0.2.0.zip (it must be 32-bit, do not download the wrong version, available on the Oracle website), and extract it to Product in the Oracle installation directory (the default folder is: instantclient_11_2): d:\ app\ yeohcooller\ product\ instantclient_11_2.

Copy a directory under the root directory of the database installation D:\ app\ yeohcooller\ product\ 11.2.0\ dbhome_1\ NETWORK to D:\ app\ yeohcooller\ product\ instantclient_11_2 under the Oracle client directory (actually only NETWORK\ ADMIN\ tnsnames.ora is needed)

3) install PL/SQL Developer

To install PL/SQL Developer, set OCI Library and Oracle_Home in perference- > Connection, for example, the native setting is:

Oracle Home: d:\ app\ yeohcooller\ product\ instantclient_11_2

OCI Library: d:\ app\ yeohcooller\ product\ instantclient_11_2\ oci.dll

This next step is optional.

Set environment variables (modify PATH and TNS_ADMIN environment variables)

For the NLS_LANG environment variable, it is best to set it to be the same as the database side, and first query the character set information from the database side:

SQL > select userenv ('language') nls_lang from dual

NLS_LANG

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

Right-click "my computer"-"Properties"-"Advanced"-"Environment variables"-"system Environment variables":

1 >. Select "Path"-Click "Edit" to add "D:\ app\ yeohcooller\ product\ instantclient_11_2;"

2 >. Click "New", set the variable name to "TNS_ADMIN", set the variable value to "D:\ app\ yeohcooller\ product\ instantclient_11_2;", and click "OK"

3 >. Click New, set the variable name to "NLS_LANG", set the variable value to "SIMPLIFIED CHINESE_CHINA.ZHS16GBK", and click "OK".

Finally, click OK to exit.

It should be noted here that the installation directory of oracle cannot contain spaces.

6.2.2 my own approach (1)-batch processing

I didn't have so much trouble when I took care of it myself:

1. Download instantclient-basic-nt-11.2.0.2.0 (higher version is fine, but must be 32-bit) to any directory, which cannot contain spaces.

2. Create a new batch file in the client directory, as shown in the following code. Here, load the shortcut of plsqldev into this directory, or load the batch file into the plsql developer directory, and run the batch file directly later.

@ echo off

Set path=D:\ instantclient_12_1

Set ORACLE_HOME=D:\ instantclient_12_1

Set TNS_ADMIN=C:\ app\ oracle\ product\ 12.1.0\ dbhome_1\ NETWORK\ ADMIN

Set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

Start D:\ instantclient_12_1\ plsqldev

It is sometimes necessary to set the variable of ORACLE_HOME in Windows environment.

6.2.3 my own method (2) strongly recommend downloading the pl/sql developer of wheat seedling customization

Download the PLSQL Developer_all_lhr_new.zip file in the wheat seedling cloud disk, decompress the file, set the environment variable and use it (the setting method is already provided in it), no need to install, green version.

Note: there is no need to download the instantclient file separately, the customized version of wheat seedlings already contains client tools, including sqlplus, sqlldr, exp, tnsping and other tools, which is very useful.

6.2.4 my own method (3)-download the 64-bit version

Download 64-bit PL/SQL Developer.

The cloud disk of wheat seedlings can be downloaded. Http://blog.itpub.net/26736162/viewspace-1624453/

6.3 Database roles cannot be selected when connecting

As follows, if the role cannot be selected, it may be a problem with the OCI configuration:

Is it appropriate in the configuration? Here is my configuration:

D:\ Program files\ app\ oracle\ product\ 11.2.0.1\ dbhome_1

D:\ Program files\ app\ oracle\ product\ 11.2.0.1\ dbhome_1\ bin\ oci.dll

6.4 the database connector tns cannot be selected

Although this problem does not affect login, there is always something missing:

Error login interface:

Correct interface:

Solution:

Set TNS_ADMIN to see if the system environment variable has the TNS_ADMIN variable set, and the value of the variable is to the directory name:

As follows, there is a normal error window:

The following stored procedure has no error window:

Reason: it is obvious that the parentheses after the name of the stored procedure should be in English instead of Chinese parentheses.

About Me

.

● author: wheat seedlings, only focus on the database technology, pay more attention to the application of technology

● article is updated synchronously on itpub (http://blog.itpub.net/26736162), blog Park (http://www.cnblogs.com/lhrbest) and personal Wechat official account (xiaomaimiaolhr).

● article itpub address: http://blog.itpub.net/26736162/viewspace-2134628/

● article blog park address: http://www.cnblogs.com/lhrbest/articles/6493218.html

● pdf version of this article and wheat seedling cloud disk address: http://blog.itpub.net/26736162/viewspace-1624453/

● QQ group: 230161599 WeChat group: private chat

● contact me, please add QQ friend (642808185), indicate the reason for adding

● was completed in Taixing apartment from 10:00 on 2016-11-28 to 24:00 on March-02, 2017.

The content of the ● article comes from the study notes of wheat seedlings, and some of it is sorted out from the Internet. Please forgive me if there is any infringement or improper place.

Copyright ● all rights reserved, welcome to share this article, please reserve the source for reprint

.

Pick up your phone and use Wechat client to scan the picture on the left below to follow the Wechat official account of wheat seedlings: xiaomaimiaolhr, scan the QR code on the right to join the QQ group of wheat seedlings, and learn the most practical database technology.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.