Посмотреть текст процедуры oracle
The DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information.
This chapter contains the following topics:
Rules and Limits
Cursor Variables
You can use cursor variables in your application. A cursor variable is a reference to a cursor that is defined and opened on the Oracle Database version 8 server. See the Oracle Database PL/SQL Language Reference for complete information about cursor types.
The advantages of cursor variables are
Encapsulation: queries are centralized, placed in the stored procedure that opens the cursor variable. The logic is hidden from the user.
Ease of maintenance: if you need to change the cursor, you only need to make the change in one place: the stored procedure. There is no need to change each application.
Convenient security: the user of the application is the username used when the application connects to the server. The user must have execute permission on the stored procedure that opens the cursor. But the user does not need to have read permission on the tables used in the query. This capability can be used to limit access to the columns and rows in the table.
Security Model
The dbmsotpt.sql script must be run as user SYS . This creates the public synonym DBMS_OUTPUT , and EXECUTE permission on this package is granted to public .
Using DBMS_OUTPUT
This section contains topics which relate to using the DBMS_OUTPUT package.
GET_LINES Procedure
This procedure retrieves an array of lines from the buffer.
Returns an array of lines of buffered information. The maximum length of each line in the array is 32767 bytes. It is recommended that you use the VARRAY overload version in a 3GL host program to execute the procedure from a PL/SQL anonymous block.
Number of lines you want to retrieve from the buffer.
After retrieving the specified number of lines, the procedure returns the number of lines actually retrieved. If this number is less than the number of lines requested, then there are no more lines in the buffer.
You can choose to retrieve from the buffer a single line or an array of lines. Call the GET_LINE procedure to retrieve a single line of buffered information. To reduce the number of calls to the server, call the GET_LINES procedure to retrieve an array of lines from the buffer.
You can choose to automatically display this information if you are using SQL*Plus by using the special SET SERVEROUTPUT ON command.
After calling GET_LINE or GET_LINES , any lines not retrieved before the next call to PUT , PUT_LINE , or NEW_LINE are discarded to avoid confusing them with the next message.
Stored Procedures
You can store PL/SQL procedures in the database, and call these stored procedures from Oracle applications. Storing a procedure in the database offers many advantages. Only one copy of the procedure needs to be maintained; it is in the database, and it can be accessed by many different applications. This considerably reduces maintenance requirements for large applications. A stored procedure is not recompiled each time it is called.
You can store procedures in the database by using Oracle tools such as SQL*Plus. You create the source for the procedure using your text editor, and execute the source using SQL*Plus (for example, with the @ operator). When you input the source, use the CREATE PROCEDURE command. (You can also use CREATE OR REPLACE PROCEDURE, to replace an already stored procedure of the same name.)
See the Oracle Database Reference for complete information about the CREATE PROCEDURE command.
Closing a Cursor Variable
Use the Module Language CLOSE command to close a cursor variable. For example, to close the emp_cursor cursor variable that was OPENed in the examples, use the statement
Note that the cursor variable is a parameter, and so you must precede it with a colon.
You can reuse ALLOCATEd cursor variables. You can OPEN, FETCH, and CLOSE as many times as needed for your application. However, if you disconnect from the server, then reconnect, you must reallocate cursor variables.
Allocating a Cursor Variable
You must allocate the cursor variable by using the Module Language command ALLOCATE. For example, to allocate the SQL_CURSOR curs that is the formal parameter in the example, you write the statement:
You use the ALLOCATE command only for cursor variables. You do not need to use it for standard cursors.
CHARARR Table Type
This package type is to be used with the GET_LINES Procedure to obtain text submitted through the PUT Procedure and PUT_LINE Procedure.
Operational Notes
If you do not call GET_LINE , or if you do not display the messages on your screen in SQL*Plus, the buffered messages are ignored.
SQL*Plus calls GET_LINES after issuing a SQL statement or anonymous PL/SQL calls.
Typing SET SERVEROUTPUT ON in SQL*Plus has the effect of invoking
with no limit on the output.
You should generally avoid having application code invoke either the DISABLE Procedure or ENABLE Procedure because this could subvert the attempt of an external tool like SQL*Plus to control whether or not to display output.
Messages sent using DBMS_OUTPUT are not actually sent until the sending subprogram or trigger completes. There is no mechanism to flush output during the execution of a procedure.
The WITH INTERFACE Clause
The stored procedure format in the previous section can be used for stored procedures that are to be called from applications written using Oracle tools. For example, a SQL*Plus script can call the GET_GPA procedure in "Procedures" just as it is written.
You can code a WITH INTERFACE clause, or you can let SQL*Module generate a default WITH INTERFACE clause for stored procedures that have been stored without this clause.
This clause, when added to a procedure declaration in the package specification, lets you add parameters that are essential to perform an RPC to a PL/SQL procedure, through a calling interface procedure in the output file. In addition, the WITH INTERFACE clause uses SQL datatypes, not the PL/SQL datatypes that are used in the stored procedure definition. The additional features of the WITH INTERFACE clause are
use of SQL datatypes
optional indicator parameters
use of the SQLSTATE and SQLCODE status parameters
Note: The procedures names that you code in WITH INTERFACE clauses must be unique within the entire application. If you let SQL*Module generate default WITH INTERFACE, then overloaded procedure names are resolved using an algorithm described in "MAPPING".
Arrays are not allowed in WITH INTERFACE clauses.
GET_LINE Procedure
This procedure retrieves a single line of buffered information.
Returns a single line of buffered information, excluding a final newline character. You should declare the actual for this parameter as VARCHAR2 (32767) to avoid the risk of " ORA-06502 : PL/SQL: numeric or value error: character string buffer too small".
If the call completes successfully, then the status returns as 0. If there are no more lines in the buffer, then the status is 1.
You can choose to retrieve from the buffer a single line or an array of lines. Call the GET_LINE procedure to retrieve a single line of buffered information. To reduce the number of calls to the server, call the GET_LINES procedure to retrieve an array of lines from the buffer.
You can choose to automatically display this information if you are using SQL*Plus by using the special SET SERVEROUTPUT ON command.
After calling GET_LINE or GET_LINES , any lines not retrieved before the next call to PUT , PUT_LINE , or NEW_LINE are discarded to avoid confusing them with the next message.
NEW_LINE Procedure
This procedure puts an end-of-line marker. The GET_LINE Procedure and the GET_LINES Procedure return "lines" as delimited by "newlines". Every call to the PUT_LINE Procedure or NEW_LINE Procedure generates a line that is returned by GET_LINE ( S ).
Exceptions
DBMS_OUTPUT subprograms raise the application error ORA-20000 , and the output procedures can return the following errors:
Line length overflow
Cursor Variable Parameters
You define a cursor variable parameter in your module by using the type SQL_CURSOR. For example:
In this example, the parameter curs has the type SQL_CURSOR.
PL/SQL
This section contains a brief overview of PL/SQL, Oracle's procedural language extension to SQL. PL/SQL is a modern block-structured language that enables you to
declare constants and variables
control execution flow, using IF . THEN . ELSE, EXIT, GOTO, and other procedural constructs
create loops, using WHILE . LOOP and FOR . LOOP
assign constant or variable expressions to a variable
issue SQL Data Manipulation Language and Transaction Control statements
define exceptions, handle them using WHEN EXCEPTION_NAME THEN . and raise them using RAISE EXCEPTION_NAME
See the Oracle Database PL/SQL Language Reference for complete information about the PL/SQL language.
Early and Late Binding
When you generate RPCs (remote procedure calls) using SQL*Module, you have a choice of early binding or late binding. Your choice of early or late binding is controlled by the BINDING option.
When you choose early binding, SQL*Module generates a call to the procedure stored in the database, and also uses a time stamp that is associated with the call. The time stamp records the date and time (to the nearest second) that the stored procedure was last compiled. The time stamp is created by the Oracle database. If a host application calls the stored procedure through the interface procedure, and the time stamp recorded with the interface procedure is earlier than the time stamp on the stored procedure recorded in the database, an error is returned to the host application in the SQLCODE or SQLSTATE status parameter. The SQLCODE error is 4062 "time stamp of name has been changed".
The late binding option, on the other hand, does not use a time stamp. If your application calls a stored procedure that has been recompiled since SQL*Module generated the interface procedure, no error is returned to the application.
With late binding, SQL*Module generates the call to the stored procedure using an anonymous PL/SQL block. The following example shows a specification for a stored procedure that is part of a package in the SCOTT schema:
If you generate an RPC interface procedures output file for the package using the command
SQL*Module generates a call in the output file, as follows:
In other words, the call to the stored procedure get_sal_comm is performed using an anonymous PL/SQL block. This is the way stored procedures are called from an Oracle precompiler or Oracle Call Interface application.
The advantages of late binding are
changes in the stored procedure(s) are transparent to the user
gives behavior similar to interactive SQL (for example, SQL*PLus)
The disadvantages of late binding are
There might be additional performance overhead at runtime, due to the necessity of compiling the PL/SQL anonymous block.
It is difficult to detect runtime PL/SQL compilation errors in the host application. For example, if the anonymous block that calls the late-bound procedure fails at runtime, there is no convenient way for the host application to determine the cause of the error.
The lack of time-stamp capability means that changes, perhaps radical changes, in the stored procedure could be made after the host application was built, and the application would have no way of detecting this.
Use the BINDING= command line option to select early or late binding when generating RPC interface procedures. See Chapter 5, "Running SQL*Module" for a description of this and other command line options.
Examples
The following package declaration shows how you use the WITH INTERFACE clause to map PL/SQL datatypes to SQL datatypes, and add the SQLCODE or SQLSTATE status parameters. Status parameters are filled in automatically as the procedure executes. They are not directly accessible within the procedure body.
The interface procedure name specified in the WITH INTERFACE clause can be the same as the name of the procedure itself, or, as in this example, it can be different. However, the name specified in the WITH INTERFACE clause is the name that must be used when you invoke the stored procedure from your host application.
In the example, the datatypes in the WITH INTERFACE clause are SQL datatypes (INTEGER and REAL). These types are compatible with the PL/SQL datatype NUMBER.
You must include either a SQLCODE or a SQLSTATE parameter in the parameter list of the WITH INTERFACE clause. You can include both. SQLSTATE is the recommended parameter; SQLCODE is provided for compatibility with the SQL89 standard.
Parameters in the PL/SQL procedure specification cannot be constrained. Parameters in the WITH INTERFACE clause must be constrained where required.
The following package definition shows an example of the WITH INTERFACE clause:
In the example, the student_last_name parameter is a CHARACTER, which is both a PL/SQL and a SQL datatype. In the PL/SQL part of the procedure definition, the parameter must be unconstrained, following the syntax of PL/SQL. But in the WITH INTERFACE clause, you must specify the length of the parameter.
The student_last_name parameter also takes an indicator parameter, using the syntax shown. See Appendix B for the formal syntax of the WITH INTERFACE clause.
Dynamic SQL
Dynamic SQL is the capability of executing SQL commands that are stored in character string variables. The package DBMS_SQL parses data definition language (DDL) and Data Manipulation (DML) statements at runtime. DBMS_SQL has functions such as OPEN_CURSOR, PARSE, DEFINE_COLUMN, EXECUTE, FETCH_ROWS, COLUMN_VALUE, and so on. Use these functions in your program to open a cursor, parse the statement, and so on.
For more details on this package, see Oracle Database Advanced Application Developer's Guide
ENABLE Procedure
This procedure enables calls to PUT , PUT_LINE , NEW_LINE , GET_LINE , and GET_LINES . Calls to these procedures are ignored if the DBMS_OUTPUT package is not activated.
Upper limit, in bytes, the amount of buffered information. Setting buffer_size to NULL specifies that there should be no limit.
It is not necessary to call this procedure when you use the SET SERVEROUTPUT option of SQL*Plus.
If there are multiple calls to ENABLE , then buffer_size is the last of the values specified. The maximum size is 1,000,000, and the minimum is 2,000 when the user specifies buffer_size ( NOT NULL ).
NULL is expected to be the usual choice. The default is 20,000 for backwards compatibility with earlier database versions that did not support unlimited buffering.
Case of Package and Procedure Names
The Oracle Server always translates the names of database objects to uppercase when they are inserted into the database. This includes the names of packages and procedures. For example, if you are loading a package into the database in the SCOTT schema, and have a PL/SQL source file that contains the line
then Oracle inserts the name into the schema as SCHOOL_RECORDS, not the lowercase ''school_records''. The following SQL*Module command (in UNIX)
generates an error, since there is no package named ''school_records'' in the schema.
If you prefer to have your package and procedure names stored in lowercase in the database, you must quote all references to the name in the PL/SQL source file, or as you insert them into the database using SQL*Plus. So, you would code
Note also that SQL*Module preserves the case of subprogram names when creating interface procedure files.
However, if you really do want uppercase names, some operating systems (OPEN VMS is an example) require that you quote the name when you specify it on the command line. So, you would enter the command as
See your system-specific Oracle documentation, and your operating system documentation, for additional information on case conventions for command lines that are in effect for your operating system.
Restrictions on Cursor Variables
The following restrictions apply to the use of cursor variables:
1. You can only use cursor variables with the commands:
2. The DECLARE CURSOR command does not apply to cursor variables.
You cannot FETCH from a CLOSEd cursor variable.
You cannot FETCH from a non-ALLOCATEd cursor variable.
Cursor variables cannot be stored in columns in the database.
A cursor variable itself cannot be declared in a package specification. Only the type of the cursor variable can be declared in the package specification.
A cursor variable cannot be a component of a PL/SQL record.
Opening a Cursor Variable
You must open a cursor variable on the Oracle Server. You cannot use the OPEN command that you use to open a standard cursor to open a cursor variable. You open a cursor variable by calling a PL/SQL stored procedure that opens the cursor (and defines it in the same statement).
For example, consider the following PL/SQL package, stored in the database:
After you have stored this package, and you have generated the interface procedures, you can open the cursor curs by calling the OPEN1 stored procedure from your Ada driver program. You can then call module procedures that FETCH the next row from the opened cursor. For example:
In your driver program, you call this procedure to fetch each row from the result defined by the cursor. When there is no more data, the value +100 is returned in SQLCODE.
When you use SQL*Module to create the interface procedure to call the stored procedure that opens the cursor variable, you must specify BINDING=LATE. Early binding is not supported for cursor variables in this release.
Opening in a Stand-alone Stored Procedure
In the example, a cursor type was defined inside a package, and the cursor was opened in a procedure in that package. But it is not always necessary to define a cursor type inside the package that contains the procedures that open the cursor.
If you need to open a cursor inside a standalone stored procedure, you can define the cursor in a separate package, then reference that package in the standalone stored procedure that opens the cursor. Here is an example:
Return Types
When you define a reference cursor in a PL/SQL stored procedure, you must declare the type that the cursor returns. See the Oracle Database PL/SQL Language Reference for complete information on the reference cursor type and its return types.
DBMSOUTPUT_LINESARRAY Object Type
This package type is to be used with the GET_LINES Procedure to obtain text submitted through the PUT Procedure and PUT_LINE Procedure.
Accessing Stored Procedures
You can use SQL*Module to provide a bridge that enables your host application to access procedures stored in the database. A host application written in Ada cannot call a stored database subprogram directly. But you can use SQL*Module to construct an interface procedure ("stub'') that calls the stored database subprogram. shows, in schematic form, how this process works.
Figure 3-1 Accessing a Stored Procedure
In this example, there is a procedure stored in the database called enroll. The PL/SQL source code that created the procedure is shown in the right-hand box. The WITH INTERFACE clause in the procedure is described in the section "The WITH INTERFACE Clause". The procedure has two database parameters: class_no and student_id. The SQLCODE error return parameter is added in the interfacing clause.
Overview
The package is typically used for debugging, or for displaying messages and reports to SQL*DBA or SQL*Plus (such as are produced by applying the SQL command DESCRIBE to procedures).
The PUT Procedure and PUT_LINE Procedure in this package enable you to place information in a buffer that can be read by another trigger, procedure, or package. In a separate PL/SQL procedure or anonymous block, you can display the buffered information by calling the GET_LINE Procedure and GET_LINES Procedure.
If the package is disabled, all calls to subprograms are ignored. In this way, you can design your application so that subprograms are available only when a client is able to process the information.
Examples
Example 1: Using a Trigger to Produce Output
You can use a trigger to print out some output from the debugging process. For example, you could code the trigger to invoke:
If you have enabled the DBMS_OUTPUT package, then the text produced by this PUT_LINE would be buffered, and you could, after executing the statement (presumably some INSERT , DELETE , or UPDATE that caused the trigger to fire), retrieve the line of information. For example:
You could then optionally display the buffer on the screen. You repeat calls to GET_LINE until status comes back as nonzero. For better performance, you should use calls to GET_LINES Procedure which can return an array of lines.
Example 2: Debugging Stored Procedures and Triggers
The DBMS_OUTPUT package is commonly used to debug stored procedures and triggers. This package can also be used to enable you to retrieve information about an object and format this output, as shown in "Example 3: Retrieving Information About an Object".
Assume the EMP table contains the following rows:
Assume the user executes the following statements in SQL*Plus:
The user would then see the following information displayed in the output pane:
Example 3: Retrieving Information About an Object
In this example, the user has used the EXPLAIN PLAN command to retrieve information about the execution plan for a statement and has stored it in PLAN_TABLE . The user has also assigned a statement ID to this statement. The example EXPLAIN_OUT procedure retrieves the information from this table and formats the output in a nested manner that more closely depicts the order of steps undergone in processing the SQL statement.
PUT_LINE Procedure
This procedure places a line in the buffer.
The PUT_LINE procedure that takes a NUMBER is obsolete and, while currently supported, is included in this release for legacy reasons only.
Мы с вами уже многое знаем о процедурах и функциях, вот теперь давайте поговорим о том, где находятся и хранятся откомпилированные процедуры и функции. После того, как команда CREATE OR REPLACE создает процедуру или функцию, она сразу сохраняется в БД, в скомпилированной форме, которая называется p-кодом (p-code). В p-коде содержатся все обработанные ссылки подпрограммы, а исходный текст преобразован, в вид удобный для чтения системой поддержки PL/SQL. При вызове хранимой процедуры p-код считывается с диска и выполняется. Собственно сам P-код аналогичен объектному коду генерируемому компиляторами языков программирования высокого уровня. В P-коде содержатся обработанные ссылки на объекты (это свойство ранней привязки переменных, о которой мы говорили с вами ранее) по этому выполнение P-кода является сравнительно не дорогой (нересурсоемкой) операцией. Да к слову напомню, что удалить код процедуры или функции из вашей БД (схемы) можно применив, оператор DROP - вот таким образом (в шаге 87 мы уже это делали):
Теперь давайте вспомним каким образом можно получить информацию о наличии процедур и их работоспособности. Самое простое это выполнить такой запрос к системному представлению USER_OBJECTS вот так:
В моем случае получилось следующее:
Я включил только три столбца представления, которые дают основную информацию, но если хотите можете использовать все столбцы. Хорошо видно, что у нас с вами все объекты имеют статус VALID, то есть исправны. А, вот как, например увидеть текст хранимой процедуры или функции, для этого используйте системное представление USER_SOURCE. Давайте к примеру выведем текст функции из прошлого шага - FACTORIAL:
Вот и содержимое самой функции! Все можно найти в системных представлениях. А, что если при создании функции или процедуры происходит ошибка компиляции? Давайте рассмотрим такой вариант. Создадим процедуру намеренно с ошибкой:
Правильно, в данном случае мы забыли поставить ";" после завершения строки K := NUM. Вот теперь давайте дадим такой запрос:
Странно, процедура вроде бы создана, ее p-код присутствует, но она не исправна! Попробуем вызвать ее:
Ответ будет таким:
Все верно - процедура с ошибками! Так вот, я морочил вам голову, только по тому, чтобы вы ясно представляли себе куда бежать, если что-то не выходит! А вот теперь повторим компиляцию:
И дадим такую строку:
Вот теперь кое, кое что стало яснее. Ошибка PLS-00103 означает наличие незавершенного оператора, команда SHOW ERRORS, считывает данные из системного представления USER_ERRORS, вот его описание:
Теперь давайте дадим вот такой запрос:
Вот и содержимое:
Вообще это дело вкуса, но лучше использовать SHOW ERRORS - так удобнее. И будет ясно видно где ошибка! Давайте удалим нашу "инвалидную" процедуру и вы сможете сами поработать над тем, что я вам излагал! Итак:
Репутация: 3
Всего: 7
Нигде не могу найти в документации: Подскажите пожалуйста, можно ли каким програмным способом увидеть сам исходный текст хранимой процедуры, триггера, функции в БД Oracle? Я имею в виду не используя программы сторонних производителей таких как PL/SQL Developer.
Leprechaun Software Developer
Репутация: 18
Всего: 534
Некоторые триггеры и процедуры могут быть wrapped, и просто так их код не посмотришь. Декомпиляторы в приципе есть, но Oracle не приветствует это, поэтому найти такие программы очень тяжело. Если тебя интересуют стандартные функции Oracle, то это напрастный труд, т.к. там в основном идет вызов нативных функций.
P.S. Если найдешь, поделись.
Disclaimer: this post contains explicit depictions of personal opinion. So, if it sounds sarcastic, don't take it seriously. If it sounds dangerous, do not try this at home or at all. And if it offends you, just don't read it.
Репутация: 1
Всего: 64
Если процедуры\функции\пакеты написаны тобой, то все это можно найти в представлении all_source, например:
Код |
SELECT * FROM all_source WHERE name = 'SOMEPROCNAME' AND type = 'PROCEDURE' |
"Для некоторых людей программирование является такой же внутренней потребностью, подобно тому, как коровы дают молоко, или писатели стремятся писать" - Николай Безруков.
Репутация: 3
Всего: 7
Вот это то, что надо! Молодец .
Репутация: нет
Всего: нет
a как можно удалить созданную процедуру?
Добавлено @ 02:56
Все, разобрался
Данный раздел предназначен для обсуждения проблем с Oracle Database, другие продукты Oracle здесь не обсуждаются. Просьба при создании темы, придерживаться следующих правил:
Если Вам понравилась атмосфера форума, заходите к нам чаще! С уважением, Zloxa, LSD.
0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей) | |
0 Пользователей: | |
« Предыдущая тема | Oracle | Следующая тема » |
[ Время генерации скрипта: 0.1565 ] [ Использовано запросов: 21 ] [ GZIP включён ]
Репутация: 3
Всего: 7
Нигде не могу найти в документации: Подскажите пожалуйста, можно ли каким програмным способом увидеть сам исходный текст хранимой процедуры, триггера, функции в БД Oracle? Я имею в виду не используя программы сторонних производителей таких как PL/SQL Developer.
Leprechaun Software Developer
Репутация: 18
Всего: 534
Некоторые триггеры и процедуры могут быть wrapped, и просто так их код не посмотришь. Декомпиляторы в приципе есть, но Oracle не приветствует это, поэтому найти такие программы очень тяжело. Если тебя интересуют стандартные функции Oracle, то это напрастный труд, т.к. там в основном идет вызов нативных функций.
P.S. Если найдешь, поделись.
Disclaimer: this post contains explicit depictions of personal opinion. So, if it sounds sarcastic, don't take it seriously. If it sounds dangerous, do not try this at home or at all. And if it offends you, just don't read it.
Репутация: 1
Всего: 64
Если процедуры\функции\пакеты написаны тобой, то все это можно найти в представлении all_source, например:
Код |
SELECT * FROM all_source WHERE name = 'SOMEPROCNAME' AND type = 'PROCEDURE' |
"Для некоторых людей программирование является такой же внутренней потребностью, подобно тому, как коровы дают молоко, или писатели стремятся писать" - Николай Безруков.
Репутация: 3
Всего: 7
Вот это то, что надо! Молодец .
Репутация: нет
Всего: нет
a как можно удалить созданную процедуру?
Добавлено @ 02:56
Все, разобрался
Данный раздел предназначен для обсуждения проблем с Oracle Database, другие продукты Oracle здесь не обсуждаются. Просьба при создании темы, придерживаться следующих правил:
Если Вам понравилась атмосфера форума, заходите к нам чаще! С уважением, Zloxa, LSD.
0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей) | |
0 Пользователей: | |
« Предыдущая тема | Oracle | Следующая тема » |
[ Время генерации скрипта: 0.1130 ] [ Использовано запросов: 21 ] [ GZIP включён ]
This chapter describes how to use SQL*Module to generate interface procedures to call stored procedures. It covers the following topics:
The examples in this chapter use the tables defined in Chapter 6, "Demonstration Programs".
Stored Packages
The examples of stored procedures shown so far in this chapter involve standalone procedures (sometimes called top-level procedures). These are useful in small applications. However, to gain the full power of stored procedures, you should use packages.
A package encapsulates procedures, as well as other PL/SQL objects. Stored packages that are used with Ada applications have two parts: a package specification and a package body. The specification is the (exposed) interface to the host application; it declares the procedures that are called by the application. A complete PL/SQL package specification can also declare functions, as well as other PL/SQL objects such as constants, variables, and exceptions. However, an Ada application using SQL*Module cannot access or reference PL/SQL objects other than subprograms. The package body contains the PL/SQL code that defines the procedures and other objects that are declared in the package specification.
Although an Ada application can only access public subprograms, a called subprogram can in turn call private subprograms, and can access public and private variables and constants in the package.
For complete information about stored packages, see the Oracle Database PL/SQL Language Reference .
DISABLE Procedure
This procedure disables calls to PUT , PUT_LINE , NEW_LINE , GET_LINE , and GET_LINES , and purges the buffer of any remaining information.
As with the ENABLE Procedure, you do not need to call this procedure if you are using the SERVEROUTPUT option of SQL*Plus.
SQL Datatypes
The SQL datatypes that you can use in the WITH INTERFACE clause are listed in , along with their compatible PL/SQL datatypes.
Rules and Limits
The maximum line size is 32767 bytes.
The default buffer size is 20000 bytes. The minimum size is 2000 bytes and the maximum is unlimited.
PUT Procedure
This procedure places a partial line in the buffer.
The PUT procedure that takes a NUMBER is obsolete and, while currently supported, is included in this release for legacy reasons only.
Buffer overflow, limit of < buf_limit >bytes.
Line length overflow, limit of 32767 bytes for each line.
You can build a line of information piece by piece by making multiple calls to PUT , or place an entire line of information into the buffer by calling PUT_LINE .
When you call PUT_LINE the item you specify is automatically followed by an end-of-line marker. If you make calls to PUT to build a line, then you must add your own end-of-line marker by calling NEW_LINE . GET_LINE and GET_LINES do not return lines that have not been terminated with a newline character.
If your lines exceed the line limit, you receive an error message.
Output that you create using PUT or PUT_LINE is buffered. The output cannot be retrieved until the PL/SQL program unit from which it was buffered returns to its caller.
For example, SQL*Plus does not display DBMS_OUTPUT messages until the PL/SQL program completes. There is no mechanism for flushing the DBMS_OUTPUT buffers within the PL/SQL program.
Data Structures
The DBMS_OUTPUT package declares 2 collection types for use with the GET_LINES Procedure.
Summary of DBMS_OUTPUT Subprograms
Disables message output
Enables message output
Retrieves one line from buffer
Retrieves an array of lines from buffer
Terminates a line created with PUT
Places a line in the buffer
Places partial line in buffer
The PUT Procedure that take a number are obsolete and, while currently supported, are included in this release for legacy reasons only.
Procedures
A PL/SQL procedure is a named PL/SQL block. Unlike an anonymous block, a procedure can
be invoked from a separate application
be compiled once, but invoked many times
be stored in compiled form in a database, independent of the shared SQL cache
A procedure contains one or more PL/SQL blocks. The following example computes the grade point average. The student ID number is passed as a parameter to the procedure, and the computed grade point average is returned by the procedure.
The procedure declaration adds a parameter list to the PL/SQL block. In this example, student_id is a parameter whose mode is IN. The mode of a parameter indicates whether the parameter passes data to a procedure (IN), returns data from a procedure (OUT), or can do both (IN OUT). The parameter gpa is an OUT parameter. It returns a value, but you cannot use it to pass a value to the procedure. Nor can you read its value inside the procedure, even after a value has been assigned to it.
Читайте также: