Powerful Tool for OraclePL/SQL Development

Source: Internet
Author: User
To write and debug OraclePLSQLProgram using Toad, you must master the following view applications: (1) SchemaBroswer Web browser (SchemaBrowser) can quickly access data dictionaries and browse tables, indexes, and stored procedures in databases. Toad provides quick access to the database, which is extremely convenient to use. The user interface is concise and the structure is integrated.

To write and debug Oracle PL/SQL Program using Toad, you must master the following view applications: (1) Schema Broswer Web Browser allows you to quickly access the data dictionary, browse tables, indexes, and stored procedures in the database. Toad provides quick access to the database, which is extremely convenient to use. The user interface is concise and the structure is integrated.

To write and debug Oracle PL/SQL Program using Toad, you must master the following view applications:

(1) Schema Broswer

Schema Browser allows you to quickly access data dictionaries and browse tables, indexes, and storage in databases.

Process. Toad provides quick access to the database, which is extremely convenient to use. The user interface is concise and the structure is reasonable. When you click

A separate database object. Toad immediately displays the details of this object. For example, to point a database table, all and

The indexes, constraints, stored procedures, SQL statements, data in the table, and the mutual reference relationships with other tables are

The same interface is displayed. All operations on database objects can be performed in the Schema Browser window.

The developer queries information about all objects connected to the current user in the Oracle database. In the development, various objects, Procedure, Function, and Package can be viewed here. By clicking an object, the developer can read the relevant code, right-click an object to implement re-compilation and other operations. Double-click an object to enter the corresponding development and editing view, and perform operations such as modification, debugging, and statement tracking performance analysis;

(2) SQL Editor

The main functions of the SQL Editor are to edit, run, and adjust SQL statements. TOAD advanced editing

The series window includes many features to improve the productization of SQL statements written by developers. For example, a simple code model is generated.

The content and column name of the package are automatically found before the SQL statement is compiled. The SQL editor consists of an editing window and running knot.

If the window is displayed, developers are allowed to test the test run results during the editing process. The SQL editor includes not only standard editing commands,

It also includes some enhanced functions, such as quick query of fields in the table and formatting of SQL statements. This window can be

It is very useful for large development projects to process content up to 4 GB. Convenient bookmarks make it easy for developers

Location. In the running result window, user-defined configuration is provided, and long raw columns are supported,

Data can be detached from a disk, printed, and edited.

(3) Procedure Editor

The main functions of the stored Procedure Editor are to edit, compile, test, and debug stored procedures and

Sender. TOAD provides syntax, error, and many other easy-to-use functions, such as displaying table names and columns in the pop-up window.

And Oracle functions. Unlike other PL/SQL editing tools, TOAD allows multiple data operations in one file.

Library object, which can compile an object, compile multiple objects, compile to the current cursor, and compile from the cursor. An error occurs during running.

The stored procedure stops to a problematic statement. You can use shortcuts or templates to quickly compile PL/SQL statements, or

Generate your own template as needed. Toad allows you to easily edit tasks, such as Setting bookmarks, canceling comments,

Format SQL statements.

Write two instances as follows:

  • Procedure example:

 
 
  1. CREATE OR REPLACE PROCEDURE SCOTT.calc_totalTemp (fudge_factor_in IN NUMBER)
  2. IS
  3. subtotal NUMBER := 0;
  4. PROCEDURE compute_running_total (increment_in IN PLS_INTEGER)
  5. IS
  6. BEGIN
  7. subtotal := subtotal + increment_in * fudge_factor_in;
  8. END;
  9. BEGIN
  10. FOR month_idx IN 1..12
  11. LOOP
  12. compute_running_total (month_idx);
  13. END LOOP;
  14. DBMS_OUTPUT.PUT_LINE('Fudged total for year: ' || subtotal);
  15. END;
  16. /
  • Function example

 
 
  1. CREATE OR REPLACE FUNCTION SCOTT.wordcountTemp (str IN VARCHAR2)
  2. RETURN PLS_INTEGER
  3. AS
  4. /* words PLS_INTEGER := 0; ***Commented out for intentional error*** */
  5. words PLS_INTEGER := 0;
  6. len PLS_INTEGER := NVL(LENGTH(str),0);
  7. inside_a_word BOOLEAN;
  8. BEGIN
  9. FOR i IN 1..len + 1
  10. LOOP
  11. IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len
  12. THEN
  13. IF inside_a_word
  14. THEN
  15. words := words + 1;
  16. inside_a_word := FALSE;
  17. END IF;
  18. ELSE
  19. inside_a_word := TRUE;
  20. END IF;
  21. END LOOP;
  22. RETURN words;
  23. END;
  24. /

(4) PL/SQL Debugger

Toad provides powerful and easy-to-use PL/SQL debugging functions, saving developers in the development and testing of large projects.

Valuable time for trial to Improve the Quality of application development. During the development of the stored procedure, Toad can edit, debug, and

Run the code. During running, you can enter parameters as needed and observe the changes to check whether the stored procedure is correct. In debugging

Toad can display all breakpoints, parameters, call stacks, and output parameters in a window. Use Toad, not

It is often easy to detect errors in stored procedures. developers can run PL/SQL statements step by step to identify problems. Debugging session

It can be used together with other programs.

When I started to use Toad to debug Procedure and Function, the debugging button was grayed out and failed to be debugged. After I clicked the debug button, the program ran directly and did not stop at the breakpoint, I have searched the internet for a long time, and most of them have not properly solved this problem;

The solution to Toad debugging is as follows:

First, check whether your current Toad user connection is a DBA user such as Sys and System. If yes, the Toad Debug button is available and the breakpoint can be set, however, when the program runs directly, Toad does not support direct debugging of PL/SQL programs by DBA users;

Then, change the user connection to another user, such as Scott, to reconnect Toad. When Toad is connected to this user, it is not feasible to directly debug PL/SQL, this user does not have the Debugger permission by default, and the Debug button is also grayed out in this case;

Then, execute the following statement (assume that the current Toad user connected to Oracle is Toad ):

 
 
  1. grant debug connect session to scott;
  2. grant debug any procedure to scott;

Finally, it's almost the same as the Eclipse Debug Java program, that is, setting breakpoints. Click the Execute PL/SQL with Debugger button on Toad to start debugging!

(5) Connection Color-Coding

Toad allows you to connect to multiple databases at the same time to facilitate switching and comparison between multiple databases. But this also adds

The risk of misoperation in the database. Connection Color-Coding allows you to define a new database Connection

Specify a color for the connection to serve as an eye-catching reminder.

Neutron star blog, please be sure to keep this source http://ckitpro8086.blog.51cto.com/3653012/770589

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.