Release 8.0
A54642_01
 Library |
 Product |
 Contents |
 Index |
| Oracle8
Server Application Developer's Guide Release 8.0 A54642_01 |
|
You can include user-written PL/SQL functions in SQL expressions. (You must be using PL/SQL release 2.1 or greater.) By using PL/SQL functions in SQL statements, you can do the following:
PL/SQL functions must be created as top-level functions or declared within a package specification before they can be named within a SQL statement. Stored PL/SQL functions are used in the same manner as built-in Oracle functions (such as SUBSTR or ABS).
PL/SQL functions can be placed wherever an Oracle function can be placed within a SQL statement; that is, wherever expressions can occur in SQL. For example, they can be called from the following:
You cannot call stored PL/SQL functions from a CHECK constraint clause of a CREATE or ALTER TABLE command or use them to specify a default value for a column. These situations require an unchanging definition.
Note: Unlike functions, which are called as part of an expression, procedures are called as statements. Therefore, PL/SQL procedures are not directly callable from SQL statements. However, functions called from a PL/SQL statement or referenced in a SQL expression can call a PL/SQL procedure.
Use the following syntax to reference a PL/SQL function from SQL:
[[schema.]package.]function_name[@dblink][(param_1...param_n)]
For example, to reference a function that you have created that is called MY_FUNC, in the MY_FUNCS_PKG package, in the SCOTT schema, and that takes two numeric parameters, you could call it as:
SELECT scott.my_funcs_pkg.my_func(10,20) from dual
If only one of the optional schema or package names is given, the first identifier can be either a schema name or a package name. For example, to determine whether PAYROLL in the reference PAYROLL.TAX_RATE is a schema or package name, Oracle proceeds as follows:
You can also refer to a stored top-level function using any synonym that you have defined for it.
In SQL statements, the names of database columns take precedence over the names of functions with no parameters. For example, if schema SCOTT creates the following two objects:
CREATE TABLE emp(new_sal NUMBER ...); CREATE FUNCTION new_sal RETURN NUMBER IS ...;
Then in the following two statements, the reference to NEW_SAL refers to the column EMP.NEW_SAL:
SELECT new_sal FROM emp; SELECT emp.new_sal FROM emp;
To access the function NEW_SAL, you would enter the following:
SELECT scott.new_sal FROM emp;
For example, to call the TAX_RATE PL/SQL function from schema SCOTT, execute it against the SS_NO and SAL columns in TAX_TABLE, and place the results in the variable INCOME_TAX, specify the following:
SELECT scott.tax_rate (ss_no, sal)
INTO income_tax
FROM tax_table
WHERE ss_no = tax_id;
These sample calls to PL/SQL functions are allowed in SQL expressions:
circle_area(radius) payroll.tax_rate(empno) scott.payroll.tax_rate(dependents, empno)@ny
To pass any number of arguments to a function, supply the arguments within the parentheses. You must use positional notation; named notation is not currently supported. For functions that do not accept arguments, omit the parentheses.
The argument's datatypes and the function's return type are limited to those types that are supported by SQL. For example, you cannot call a PL/SQL function that returns a PL/SQL BINARY_INTEGER from a SQL statement.
The stored function gross_pay initializes two of its formal parameters to default values using the DEFAULT clause, as follows:
CREATE FUNCTION gross_pay
(emp_id IN NUMBER,
st_hrs IN NUMBER DEFAULT 40,
ot_hrs IN NUMBER DEFAULT 0) RETURN NUMBER AS
...
When calling gross_pay from a procedural statement, you can always accept the default value of st_hrs. That is because you can use named notation, which lets you skip parameters, as in:
IF gross_pay(eenum,ot_hrs => otime) > pay_limit THEN ...
However, when calling gross_pay from a SQL expression, you cannot accept the default value of st_hrs unless you accept the default value of ot_hrs. That is because you cannot use named notation.
To be callable from SQL expressions, a user-defined PL/SQL function must meet the following basic requirements:
For example, the following stored function meets the basic requirements:
CREATE FUNCTION gross_pay
(emp_id IN NUMBER,
st_hrs IN NUMBER DEFAULT 40,
ot_hrs IN NUMBER DEFAULT 0) RETURN NUMBER AS
st_rate NUMBER;
ot_rate NUMBER;
BEGIN
SELECT srate, orate INTO st_rate, ot_rate FROM payroll
WHERE acctno = emp_id;
RETURN st_hrs * st_rate + ot_hrs * ot_rate;
END gross_pay;
To execute a SQL statement that calls a stored function, the Oracle Server must know the purity level of the function, that is, the extent to which the function is free of side effects. In this context, side effects are references to database tables or packaged variables.
Side effects can prevent the parallelization of a query, yield order-dependent (and therefore indeterminate) results, or require that package state be maintained across user sessions (which is not allowed). Therefore, the following rules apply to stored functions called from SQL expressions:
For standalone functions, Oracle can enforce these rules by checking the function body. However, the body of a packaged function is hidden; only its specification is visible. So, for packaged functions, you must use the pragma (compiler directive) RESTRICT_REFERENCES to enforce the rules.
The pragma tells the PL/SQL compiler to deny the packaged function read/write access to database tables, packaged variables, or both. If you try to compile a function body that violates the pragma, you get a compilation error.
To call a packaged function from SQL expressions, you must assert its purity level by coding the pragma RESTRICT_REFERENCES in the package specification (not in the package body). The pragma must follow the function declaration but need not follow it immediately. Only one pragma can reference a given function declaration.
To code the pragma RESTRICT_REFERENCES, you use the syntax
PRAGMA RESTRICT_REFERENCES (
function_name, WNDS [, WNPS] [, RNDS] [, RNPS]);
where:
You can pass the arguments in any order, but you must pass the argument WNDS. No argument implies another; for example, RNPS does not imply WNPS.
In the example below, the function compound neither reads nor writes database or package state, so you can assert the maximum purity level. Always assert the highest purity level that a function allows. That way, the PL/SQL compiler will never reject the function unnecessarily.
CREATE PACKAGE finance AS -- package specification
...
FUNCTION compound
(years IN NUMBER,
amount IN NUMBER,
rate IN NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (compound, WNDS, WNPS, RNDS, RNPS);
END finance;
CREATE PACKAGE BODY finance AS --package body
...
FUNCTION compound
(years IN NUMBER,
amount IN NUMBER,
rate IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN amount * POWER((rate / 100) + 1, years);
END compound;
-- no pragma in package body
END finance;
Later, you might call compound from a PL/SQL block, as follows:
BEGIN
...
SELECT finance.compound(yrs,amt,rte) -- function call INTO
interest FROM accounts WHERE acctno = acct_id;
Packages can have an initialization part, which is hidden in the package body. Typically, the initialization part holds statements that initialize public variables.
In the following example, the SELECT statement initializes the public variable prime_rate:
CREATE PACKAGE loans AS prime_rate REAL; -- public packaged variable ... END loans; CREATE PACKAGE BODY loans AS ... BEGIN -- initialization part SELECT prime INTO prime_rate FROM rates; END loans;
The initialization code is run only once-the first time the package is referenced. If the code reads or writes database state or package state other than its own, it can cause side effects. Moreover, a stored function that references the package (and thereby runs the initialization code) can cause side effects indirectly. So, to call the function from SQL expressions, you must use the pragma RESTRICT_REFERENCES to assert or imply the purity level of the initialization code.
To assert the purity level of the initialization code, you use a variant of the pragma RESTRICT_REFERENCES, in which the function name is replaced by a package name. You code the pragma in the package specification, where it is visible to other users. That way, anyone referencing the package can see the restrictions and conform to them.
To code the variant pragma RESTRICT_REFERENCES, you use the syntax
PRAGMA RESTRICT_REFERENCES (
package_name, WNDS [, WNPS] [, RNDS] [, RNPS]);
where the arguments WNDS, WNPS, RNDS, and RNPS have the usual meaning.
In the example below, the initialization code reads database state and writes package state. However, you can assert WNPS because the code is writing the state of its own package, which is permitted. So, you assert WNDS, WNPS, RNPS-the highest purity level the function allows. (If the public variable prime_rate were in another package, you could not assert WNPS.)
CREATE PACKAGE loans AS
PRAGMA RESTRICT_REFERENCES (loans, WNDS, WNPS, RNPS);
prime_rate REAL;
...
END loans;
CREATE PACKAGE BODY loans AS
...
BEGIN
SELECT prime INTO prime_rate FROM rates;
END loans;
You can place the pragma anywhere in the package specification, but placing it at the top (where it stands out) is a good idea.
To imply the purity level of the initialization code, your package must have a RESTRICT_REFERENCES pragma for one of the functions it declares. From the pragma, Oracle can infer the purity level of the initialization code (because the code cannot break any rule enforced by a pragma). In the next example, the pragma for the function discount implies that the purity level of the initialization code is at least WNDS:
CREATE PACKAGE loans AS
...
FUNCTION discount (...) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (discount, WNDS);
END loans;
...
To draw an inference, Oracle can combine the assertions of all RESTRICT_REFERENCES pragmas. For example, the following pragmas (combined) imply that the purity level of the initialization code is at least WNDS, RNDS:
CREATE PACKAGE loans AS
...
FUNCTION discount (...) RETURN NUMBER;
FUNCTION credit_ok (...) RETURN CHAR;
PRAGMA RESTRICT_REFERENCES (discount, WNDS);
PRAGMA RESTRICT_REFERENCES (credit_ok, RNDS);
END loans;
...
To call a packaged function from SQL expressions, you must assert its purity level using the pragma RESTRICT_REFERENCES. However, if the package has an initialization part, the PL/SQL compiler might not let you assert the highest purity level the function allows. As a result, you might be unable to call the function remotely, in parallel, or from certain SQL clauses.
This happens when a packaged function is purer than the package initialization
code. Remember, the first time a package is referenced,
its initialization code is run. If that reference is a function call, any
additional side effects caused by the initialization code occur during
the call. So, in effect, the initialization code lowers the purity level
of the function.
To avoid this problem, move the package initialization code into a subprogram. That way, your application can run the code explicitly (rather than implicitly during package instantiation) without affecting your packaged functions.
A similar problem arises when a packaged function is purer than a subprogram it calls. This lowers the purity level of the function. Therefore, the RESTRICT_REFERENCES pragma for the function must specify the lower purity level. Otherwise, the PL/SQL compiler will reject the function. In the following example, the compiler rejects the function because its pragma asserts RNDS but the function calls a procedure that reads database state:
CREATE PACKAGE finance AS
...
FUNCTION compound (years IN NUMBER,
amount IN NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (compound, WNDS, WNPS, RNDS, RNPS);
END finance;
CREATE PACKAGE BODY finance AS
...
FUNCTION compound (years IN NUMBER,
amount IN NUMBER) RETURN NUMBER IS
rate NUMBER;
PROCEDURE calc_loan_rate (loan_rate OUT NUMBER) IS
prime_rate REAL;
BEGIN
SELECT p_rate INTO prime_rate FROM rates;
...
END;
BEGIN
calc_loan_rate(rate);
RETURN amount * POWER((rate / 100) + 1, years);
END compound;
END finance;
PL/SQL lets you overload packaged (but not standalone) functions. That is, you can use the same name for different functions if their formal parameters differ in number, order, or datatype family.
However, a RESTRICT_REFERENCES pragma can apply to only one function declaration. So, a pragma that references the name of overloaded functions always applies to the nearest foregoing function declaration.
In the following example, the pragma applies to the second declaration of valid:
CREATE PACKAGE tests AS
FUNCTION valid (x NUMBER) RETURN CHAR;
FUNCTION valid (x DATE) RETURN CHAR;
PRAGMA RESTRICT_REFERENCES (valid, WNDS);
...
PL/SQL packages normally consume user global area (UGA) memory corresponding to the number of package variables and cursors in the package. This limits scalability because the memory increases linearly with the number of users. The solution is to allow some packages to be marked as SERIALLY_REUSABLE (using pragma syntax).
For serially reusable packages, the package global memory is not kept in the UGA per user, but instead it is kept in a small pool and reused for different users. This means that the global memory for such a package is only used within a unit of work. At the end of that unit of work, the memory can therefore be released to the pool to be reused by another user (after running the initialization code for all the global variables).
The unit of work for serially reusable packages is implicitly a CALL to the server, for example, an OCI call to the server, or a PL/SQL client-to-server RPC call or server-to-server RPC call.
The state of a nonreusable package (one not marked SERIALLY_REUSABLE) persists for the lifetime of a session. A package's state includes global variables, cursors, and so on.
The state of a serially reusable package persists only for the lifetime of a CALL to the server. On a subsequent call to the server, if a reference is made to the serially reusable package, Oracle creates a new instantiation (described below) of the serially reusable package and initializes all the global variables to NULL or to the default values provided. Any changes made to the serially reusable package state in the previous CALLs to the server are not visible.
Note: Creating a new instantiation of a serially reusable package on a CALL to the server does not necessarily imply that Oracle allocates memory or configures the instantiation object. Oracle simply looks for an available instantiation work area (which is allocated and configured) for this package in a least-recently used (LRU) pool in SGA. At the end of the CALL to the server this work area is returned back to the LRU pool. The reason for keeping the pool in the SGA is that the work area can be reused across users who have requests for the same package.
Since the state of a non-reusable package persists for the lifetime of the session, this locks up UGA memory for the whole session. In applications such as Oracle Office a log-on session can typically exist for days together. Applications often need to use certain packages only for certain localized periods in the session and would ideally like to de-instantiate the package state in the middle of the session once they are done using the package.
With SERIALLY_REUSABLE packages the application developers have a way of modelling their applications to manage their memory better for scalability. Package state that they care about only for the duration of a CALL to the server should be captured in SERIALLY_REUSABLE packages.
A package can be marked serially reusable by a pragma. The syntax of the pragma is:
PRAGMA SERIALLY_REUSABLE;
A package specification can be marked serially reusable whether or not it has a corresponding package body. If the package has a body, the body must have the serially reusable pragma if its corresponding specification has the pragma; and it cannot have the serially reusable pragma unless the specification also has the pragma.
A package that is marked SERIALLY_REUSABLE has the following properties:
Note: If the application programmer makes a mistake and depends on a package variable that is set in a previous unit of work, the application program can fail. PL/SQL cannot check for such cases.
This example has a serially reusable package specification (there is no body). It demonstrates how package variables behave across CALL boundaries.
connect scott/tiger; create or replace package SR_PKG is pragma SERIALLY_REUSABLE; n number := 5; -- default initialization end SR_PKG; /
Suppose your Enterprise Manager (or SQL*Plus) application issues the following:
connect scott/tiger # first CALL to server begin SR_PKG.n := 10; end; / # second CALL to server begin dbms_output.put_line(SR_PKG.n); end; /
The above program will print:
5
Note: If the package had not had the pragma SERIALLY_REUSABLE, the program would have printed '10'.
This example has both a package specification and body which are serially reusable. Like Example 1, this example demonstrates how the package variables behave across CALL boundaries.
SQL> connect scott/tiger;
Connected.
SQL>
SQL> drop package SR_PKG;
Statement processed.
SQL>
SQL> create or replace package SR_PKG is
2>
3> pragma SERIALLY_REUSABLE;
4>
5> type str_table_type is table of varchar2(200) index by
binary_integer;
6>
7> num number := 10;
8> str varchar2(200) := 'default-init-str';
9> str_tab str_table_type;
10>
11> procedure print_pkg;
12> procedure init_and_print_pkg(n number, v varchar2);
13>
14> end SR_PKG;
15> /
Statement processed.
SQL>
SQL>
SQL> create or replace package body SR_PKG is
2>
3> -- the body is required to have the pragma since the
4> -- specification of this package has the pragma
5> pragma SERIALLY_REUSABLE;
6>
7> procedure print_pkg is
8> begin
9> dbms_output.put_line('num: ' || SR_PKG.num);
10> dbms_output.put_line('str: ' || SR_PKG.str);
11>
12> dbms_output.put_line('number of table elems: ' ||
SR_PKG.str_tab.count);
13> for i in 1..SR_PKG.str_tab.count loop
14> dbms_output.put_line(SR_PKG.str_tab(i));
15> end loop;
16> end;
17>
18> procedure init_and_print_pkg(n number, v varchar2) is
19> begin
20>
21> -- init the package globals
22> SR_PKG.num := n;
23> SR_PKG.str := v;
24> for i in 1..n loop
25> SR_PKG.str_tab(i) := v || ' ' || i;
26> end loop;
27>
28> -- now print the package
29> print_pkg;
30> end;
31>
32> end SR_PKG;
33> /
Statement processed.
SQL> show errors;
No errors for PACKAGE BODY SR_PKG
SQL>
SQL> set serveroutput on;
Server Output ON
SQL>
SQL> Rem SR package access in a CALL
SQL> begin
2>
3> -- initialize and print the package
4> dbms_output.put_line('Initing and printing pkg state..');
5> SR_PKG.init_and_print_pkg(4, 'abracadabra');
6>
7> -- print it in the same call to the server.
8> -- we should see the initialized values.
9> dbms_output.put_line('Printing package state in the same
CALL...');
10> SR_PKG.print_pkg;
11>
12> end;
13> /
Statement processed.
Initing and printing pkg state..
num: 4
str: abracadabra
number of table elems: 4
abracadabra 1
abracadabra 2
abracadabra 3
abracadabra 4
Printing package state in the same CALL...
num: 4
str: abracadabra
number of table elems: 4
abracadabra 1
abracadabra 2
abracadabra 3
abracadabra 4
SQL>
SQL> Rem SR package access in subsequent CALL
SQL> begin
2>
3> -- print the package in the next call to the server.
4> -- We should that the package state is reset to the initial
(default) values.
5> dbms_output.put_line('Printing package state in the next
CALL...');
6> SR_PKG.print_pkg;
7>
8> end;
9> /
Statement processed.
Printing package state in the next CALL...
num: 10
str: default-init-str
number of table elems: 0
SQL>
This example demonstrates that any open cursors in serially reusable packages get closed automatically at the end of a WORK boundary (which is a CALL), and that in a new CALL these cursors need to be opened again.
Rem For serially reusable pkg: At the end work boundaries
Rem (which is currently the OCI call boundary) all open
Rem cursors will be closed.
Rem
Rem Since the cursor is closed - every time we fetch we
Rem will start at the first row again.
SQL> connect scott/tiger;
Connected.
SQL>
SQL> drop package SR_PKG;
Statement processed.
SQL> drop table people;
Statement processed.
SQL>
SQL>
SQL> create table people (name varchar2(20));
Statement processed.
SQL>
SQL> insert into people values ('ET');
1 row processed.
SQL> insert into people values ('RAMBO');
1 row processed.
SQL>
SQL> create or replace package SR_PKG is
2>
3> pragma SERIALLY_REUSABLE;
4> cursor c is select name from people;
5>
6> end SR_PKG;
7> /
Statement processed.
SQL> show errors;
No errors for PACKAGE SR_PKG
SQL>
SQL> set serveroutput on;
Server Output ON
SQL>
SQL> create or replace procedure fetch_from_cursor is
2> name varchar2(200);
3> begin
4>
5> if (SR_PKG.c%ISOPEN) then
6> dbms_output.put_line('cursor is already open.');
7> else
8> dbms_output.put_line('cursor is closed; opening now.');
9> open SR_PKG.c;
10> end if;
11>
12> -- fetching from cursor.
13> fetch SR_PKG.c into name;
14> dbms_output.put_line('fetched: ' || name);
15>
16> fetch SR_PKG.c into name;
17> dbms_output.put_line('fetched: ' || name);
18>
19> -- Oops forgot to close the cursor (SR_PKG.c).
20> -- But, since it is a Serially Reusable pkg's cursor,
21> -- it will be closed at the end of this CALL to the server.
22>
23> end;
24> /
Statement processed.
SQL> show errors;
No errors for PROCEDURE FETCH_FROM_CURSOR
SQL>
SQL> set serveroutput on;
Server Output ON
SQL>
SQL> execute fetch_from_cursor;
Statement processed.
cursor is closed; opening now.
fetched: ET
fetched: RAMBO
SQL>
SQL> execute fetch_from_cursor;
Statement processed.
cursor is closed; opening now.
fetched: ET
fetched: RAMBO
SQL>
SQL> execute fetch_from_cursor;
Statement processed.
cursor is closed; opening now.
fetched: ET
fetched: RAMBO
SQL>
To call a PL/SQL function from SQL, you must either own or have EXECUTE privileges on the function. To select from a view defined with a PL/SQL function, you are required to have SELECT privileges on the view. No separate EXECUTE privileges are needed to select from the view.
Several packaged procedures are provided with the Oracle Server, either to extend the functionality of the database or to give PL/SQL access to some SQL features. You may take advantage of the functionality provided by these packages when creating your application, or you may simply want to use these packages for ideas in creating your own stored procedures.
This section lists each of the supplied packages and indicates where they are described in more detail. These packages run as the invoking user rather than the package owner. The packaged procedures are callable through public synonyms of the same name.
Oracle supplies the following packaged procedures to give PL/SQL access to some features of SQL:
Table 10-3 describes each of these packages. The footnotes at the end of Table 10-3 explain any restrictions on the use of each procedure. You should consult the package specifications for the most up-to-date information on these packages.
Note 1: not allowed in triggers
Note 2: not allowed in procedures called from SQL*Forms
Note 3: not allowed in read-only transactions
Note 4: not allowed in remote (coordinated) sessions
Note 5: not allowed in recursive sessions
Note 6: not allowed in stored procedures
For more details on each SQL command equivalent, see the Oracle8 Server SQL Reference.
The COMMIT, ROLLBACK, ROLLBACK ... TO SAVEPOINT, and SAVEPOINT procedures are directly supported by PL/SQL; they are included in the DBMS_TRANSACTION package for completeness.
Several packages are supplied with Oracle to extend the functionality of the database (DBMS_* and UTL_* packages). The cross-reference column in Table 10-4 tells you where to look for more information on each of these packages.
| Package Name | Description | Cross-reference |
|---|---|---|
|
DBMS_ALERT |
Supports asynchronous notification of database events. |
|
|
DBMS_DESCRIBE |
Lets you describe the arguments of a stored procedure. |
|
|
DBMS_JOB |
Lets you schedule administrative procedures that you want performed at periodic intervals. |
Oracle8 Server Administrator's Guide |
|
DBMS_LOCK |
Lets you use the Oracle Lock Management services for your applications. |
|
|
DBMS_OUTPUT |
Lets you output messages from triggers, procedures, and packages. |
|
|
DBMS_PIPE |
Allows sessions in the same instance to communicate with each other. |
|
|
DBMS_SHARED_POOL |
Lets you keep objects in shared memory, so that they will not be aged out with the normal LRU mechanism. |
Oracle8 Server Tuning |
|
DBMS_APPLICATION_ |
Lets you register an application name with the database for auditing or performance tracking purposes. |
Oracle8 Server Tuning |
|
DBMS_SYSTEM |
Provides system-level utilities, such as letting you enable SQL trace for a session. |
Oracle8 Server Tuning |
|
DBMS_SPACE |
Provides segment space information not available through standard views. |
Oracle8 Server Administrator's Guide |
|
DBMS_SQL |
Lets you write stored procedures and anonymous PL/SQL blocks using dynamic SQL; lets you parse any DML or DDL statement. |
|
|
DBMS_ROWID |
Lets you get information about ROWIDs, including the data block number, the object number, and other components. |
|
|
DBMS_LOB |
Lets you manipulate large objects using PL/SQL programs running on the Oracle Server. |
"The following chart compares the two interfaces in terms of LOB access. The" on page 6-50 |
|
DBMS_AQ |
Lets you add a message (of a predefined object type) onto a queue or dequeue a message. |
|
|
DBMS_AQADM |
Lets you perform administrative functions on a queue or queue table for messages of a predefined object type. |
|
|
DBMS_DISTRIBUTED_ |
Lets you maintain the Trusted Servers List, which is used in conjunction with the list at the Central Authority to determine if a privileged database link from a particular server can be accepted. |
Oracle8 Server Distributed Systems |
|
DMBS_HS |
Lets you administer Heterogeneous Services by registering or dropping distributed external procedures, remote libraries, and non-Oracle systems. Also lets you create or drop some initialization variables for non-Oracle systems. |
Oracle8 Server Distributed Systems |
|
DMBS_HS_EXTPROC |
Lets you use Heterogeneous Services to establish security for distributed external procedures. |
Oracle8 Server Distributed Systems |
|
DMBS_HS_ |
Lets you use Heterogeneous Services to send pass-through SQL statements to non-Oracle systems. |
Oracle8 Server Distributed Systems |
|
DBMS_REFRESH |
Lets you create groups of snapshots that can be refreshed together to a transactionally consistent point in time. Use of this feature requires the distributed option. |
Oracle8 Server Replication |
|
DBMS_SNAPSHOT |
Lets you refresh one or more snapshots that are not part of the same refresh group, purge snapshot log. Use of this feature requires the distributed option. |
Oracle8 Server Replication |
|
DBMS_DEFER, DMBS_DEFER_SYS, |
Lets you build and administer deferred remote procedure calls. Use of this feature requires the replication option. |
Oracle8 Server Replication |
|
DBMS_REPCAT |
Lets you use Oracle's symmetric replication facility. Use of this feature requires the replication option. |
Oracle8 Server Replication |
|
DBMS_REPCAT_AUTH, |
Lets you create users with the privileges needed by the symmetric replication facility. Use of this feature requires the replication option. |
Oracle8 Server Replication |
|
UTL_HTTP |
Lets you make HTTP callouts from PL/SQL and SQL to access data on the Internet or to call Oracle Web Server Cartridges. |
You can use the DBMS_DESCRIBE package to get information about a stored procedure or function.
This package provides the same functionality as the Oracle Call Interface OCIDescribeAny() call. The procedure DESCRIBE_PROCEDURE in this package accepts the name of a stored procedure, and a description of the procedure and each of its parameters. For more information on the OCIDescribeAny() call, see the Programmer's Guide to the Oracle Call Interface, Volume I: OCI Concepts.
To create the DBMS_DESCRIBE package, submit the DBMSDESC.SQL and PRVTDESC.PLB scripts when connected as the user SYS. These scripts are run automatically by the CATPROC.SQL script. See "Privileges Required to Execute a Procedure" on page 10-37 for information on the necessary privileges for users who will be executing this package.
This package is available to PUBLIC and performs its own security checking based on the schema object being described.
The DBMS_DESCRIBE package declares two PL/SQL table types, which are used to hold data returned by DESCRIBE_PROCEDURE in its OUT parameters. The types are
TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(30)
INDEX BY BINARY_INTEGER;
TYPE NUMBER_TABLE IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
DBMS_DESCRIBE can raise application errors in the range -20000 to -20004. The errors are
-20000: ORU 10035: cannot describe a package ('X') only a
procedure within a package
-20001: ORU-10032: procedure 'X' within package 'Y' does not
exist
-20002: ORU-10033 object 'X' is remote, cannot describe; expanded
name 'Y'
-20003: ORU-10036: object 'X' is invalid and cannot be described
-20004: syntax error attempting to parse 'X'
The parameters for DESCRIBE_PROCEDURE are shown in Table 10-5. The syntax is:
PROCEDURE DESCRIBE_PROCEDURE(
object_name IN VARCHAR2,
reserved1 IN VARCHAR2,
reserved2 IN VARCHAR2,
overload OUT NUMBER_TABLE,
position OUT NUMBER_TABLE,
level OUT NUMBER_TABLE,
argument_name OUT VARCHAR2_TABLE,
datatype OUT NUMBER_TABLE,
default_value OUT NUMBER_TABLE,
in_out OUT NUMBER_TABLE,
length OUT NUMBER_TABLE,
precision OUT NUMBER_TABLE,
scale OUT NUMBER_TABLE,
radix OUT NUMBER_TABLE
spare OUT NUMBER_TABLE);
All values from DESCRIBE_PROCEDURE are returned in its OUT parameters. The datatypes for these are PL/SQL tables, to accommodate a variable number of parameters.
One use of the DESCRIBE_PROCEDURE procedure would be as an external service interface.
For example, consider a client that provides an OBJECT _NAME of SCOTT.ACCOUNT_UPDATE where ACCOUNT_UPDATE is an overloaded function with specification:
table account (account_no number, person_id number,
balance number(7,2))
table person (person_id number(4), person_nm varchar2(10))
function ACCOUNT_UPDATE (account_no number,
person person%rowtype,
amounts dbms_describe.number_table,
trans_date date)
return accounts.balance%type;
function ACCOUNT_UPDATE (account_no number,
person person%rowtype,
amounts dbms_describe.number_table,
trans_no number)
return accounts.balance%type;
The describe of this procedure might look similar to the output shown below.
overload position argument level datatype length prec scale rad
-----------------------------------------------------------------
1 0 0 2 22 7 2 10
1 1 ACCOUNT 0 2 0 0 0 0
1 2 PERSON 0 250 0 0 0 0
1 1 PERSON_ID 1 2 22 4 0 10
1 2 PERSON_NM 1 1 10 0 0 0
1 3 AMOUNTS 0 251 0 0 0 0
1 1 1 2 22 0 0 0
1 4 TRANS_DATE 0 12 0 0 0 0
2 0 0 2 22 7 2 10
2 1 ACCOUNT_NO 0 2 22 0 0 0
2 2 PERSON 0 2 22 4 0 10
2 3 AMOUNTS 0 251 22 4 0 10
2 1 1 2 0 0 0 0
2 4 TRANS_NO 0 2 0 0 0 0
The following PL/SQL procedure has as its parameters all of the PL/SQL datatypes:
CREATE OR REPLACE PROCEDURE p1 (
pvc2 IN VARCHAR2,
pvc OUT VARCHAR,
pstr IN OUT STRING,
plong IN LONG,
prowid IN ROWID,
pchara IN CHARACTER,
pchar IN CHAR,
praw IN RAW,
plraw IN LONG RAW,
pbinint IN BINARY_INTEGER,
pplsint IN PLS_INTEGER,
pbool IN BOOLEAN,
pnat IN NATURAL,
ppos IN POSITIVE,
pposn IN POSITIVEN,
pnatn IN NATURALN,
pnum IN NUMBER,
pintgr IN INTEGER,
pint IN INT,
psmall IN SMALLINT,
pdec IN DECIMAL,
preal IN REAL,
pfloat IN FLOAT,
pnumer IN NUMERIC,
pdp IN DOUBLE PRECISION,
pdate IN DATE,
pmls IN MLSLABEL) AS
BEGIN
NULL;
END;
If you describe this procedure using the package below:
CREATE OR REPLACE PACKAGE describe_it AS
PROCEDURE desc_proc (name VARCHAR2);
END describe_it;
CREATE OR REPLACE PACKAGE BODY describe_it AS
PROCEDURE prt_value(val VARCHAR2, isize INTEGER) IS
n INTEGER;
BEGIN
n := isize - LENGTHB(val);
IF n < 0 THEN
n := 0;
END IF;
DBMS_OUTPUT.PUT(val);
FOR i in 1..n LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
END prt_value;
PROCEDURE desc_proc (name VARCHAR2) IS
overload DBMS_DESCRIBE.NUMBER_TABLE;
position DBMS_DESCRIBE.NUMBER_TABLE;
c_level DBMS_DESCRIBE.NUMBER_TABLE;
arg_name DBMS_DESCRIBE.VARCHAR2_TABLE;
dty DBMS_DESCRIBE.NUMBER_TABLE;
def_val DBMS_DESCRIBE.NUMBER_TABLE;
p_mode DBMS_DESCRIBE.NUMBER_TABLE;
length DBMS_DESCRIBE.NUMBER_TABLE;
precision DBMS_DESCRIBE.NUMBER_TABLE;
scale DBMS_DESCRIBE.NUMBER_TABLE;
radix DBMS_DESCRIBE.NUMBER_TABLE;
spare DBMS_DESCRIBE.NUMBER_TABLE;
idx INTEGER := 0;
BEGIN
DBMS_DESCRIBE.DESCRIBE_PROCEDURE(
name,
null,
null,
overload,
position,
c_level,
arg_name,
dty,
def_val,
p_mode,
length,
precision,
scale,
radix,
spare);
DBMS_OUTPUT.PUT_LINE('Position Name DTY Mode');
LOOP
idx := idx + 1;
prt_value(TO_CHAR(position(idx)), 12);
prt_value(arg_name(idx), 12);
prt_value(TO_CHAR(dty(idx)), 5);
prt_value(TO_CHAR(p_mode(idx)), 5);
DBMS_OUTPUT.NEW_LINE;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.NEW_LINE;
END desc_proc;
END describe_it;
then the results, as shown below, list all the numeric codes for the PL/SQL datatypes:
Position Name Datatype_Code Mode 1 PVC2 1 0 2 PVC 1 1 3 PSTR 1 2 4 PLONG 8 0 5 PROWID 11 0 6 PCHARA 96 0 7 PCHAR 96 0 8 PRAW 23 0 9 PLRAW 24 0 10 PBININT 3 0 11 PPLSINT 3 0 12 PBOOL 252 0 13 PNAT 3 0 14 PPOS 3 0 15 PPOSN 3 0 16 PNATN 3 0 17 PNUM 2 0 18 PINTGR 2 0 19 PINT 2 0 20 PSMALL 2 0 21 PDEC 2 0 22 PREAL 2 0 23 PFLOAT 2 0 24 PNUMER 2 0 25 PDP 2 0 26 PDATE 12 0 27 PMLS 106 0
The following data dictionary views provide information about procedures and packages:
The OBJECT_SIZE views show the sizes of the PL/SQL objects. For a complete description of these data dictionary views, see your Oracle8 Server Reference Manual.
The following statements are used in Examples 1 through 3:
CREATE PROCEDURE fire_emp(emp_id NUMBER) AS
BEGIN
DELETE FROM em WHERE empno = emp_id;
END;
/
CREATE PROCEDURE hire_emp (name VARCHAR2, job VARCHAR2,
mgr NUMBER, hiredate DATE, sal NUMBER,
comm NUMBER, deptno NUMBER)
IS
BEGIN
INSERT INTO emp VALUES (emp_sequence.NEXTVAL, name,
job, mgr, hiredate, sal, comm, deptno);
END;
/
The first CREATE PROCEDURE statement has an error in the DELETE statement. (The 'p' is absent from 'emp'.)
The following query returns all the errors for the objects in the associated schema:
SELECT name, type, line, position, text
FROM user_errors;
The following results are returned:
NAME TYPE LIN POS TEXT
-------- ---- --- --- -------------------------------------
FIRE_EMP PROC 3 15 PL/SQL-00201: identifier 'EM' must be
declared
FIRE_EMP PROC 3 3 PL/SQL: SQL Statement ignored
The following query returns the source code for the HIRE_EMP procedure created in the example statement at the beginning of this section:
SELECT line, text FROM user_source
WHERE name = 'HIRE_EMP';
The following results are returned:
LINE TEXT
------ -----------------------------------------------------
1 PROCEDURE hire_emp (name VARCHAR2, job VARCHAR2,
2 mgr NUMBER, hiredate DATE, sal NUMBER,
3 comm NUMBER, deptno NUMBER)
4 IS
5 BEGIN
6 INSERT INTO emp VALUES (emp_seq.NEXTVAL, name,
7 job, mgr, hiredate, sal, comm, deptno);
8 END;
The following query returns information about the amount of space in the SYSTEM tablespace that is required to store the HIRE_EMP procedure:
SELECT name, source_size + parsed_size + code_size +
error_size "TOTAL SIZE"
FROM user_object_size
WHERE name = 'HIRE_EMP';
The following results are returned:
NAME TOTAL SIZE ------------------------------ ---------- HIRE_EMP 3897
The functions in this package let you get the information that you need about ROWIDs. You can find out the data block number, the object number, and other components of the ROWID without having to write code to interpret the base-64 character external ROWID.
The specification for the DBMS_ROWID package is in the file dbmsutil.sql. This package is loaded when you create a database, and run catproc.sql.
Some of the functions in this package take a single parameter: a ROWID. This can be a character or a binary ROWID, either restricted or extended, as required. For each function described in this section, both the parameter types and the return type are described.
You can call the DBMS_ROWID functions and procedures from PL/SQL code, and you can also use the functions in SQL statements.
Note: ROWID_INFO is a procedure. It can only be used in PL/SQL code.
You can use functions from the DBMS_ROWID package just like any built-in SQL function. That is, you can use them wherever an expression can be used. In this example, the ROWID_BLOCK_NUMBER function is used to return just the block number of a single row in the EMP table:
SELECT dbms_rowid.rowid_block_number(rowid) FROM emp WHERE ename = 'KING';
This example returns the ROWID for a row in the EMP table, extracts the data object number from the ROWID, using the ROWID_OBJECT function in the DBMS_ROWID package, then displays the object number:
DECLARE
object_no INTEGER;
row_id ROWID;
...
BEGIN
SELECT ROWID INTO row_id FROM emp
WHERE empno = 7499;
object_no := dbms_rowid.rowid_object(row_id);
dbms_output.put_line('The obj. # is '|| object_no);
...
Table 10-6 lists the functions and procedures in the DBMS_ROWID package.
The DBMS_ROWID package functions and procedures can raise the ROWID_INVALID exception. The exception is defined in the DBMS_ROWID package as:
PRAGMA EXCEPTION_INIT(ROWID_INVALID, -1410);
The ROWID_CREATE function lets you create a ROWID, given the component parts as parameters. This function is mostly useful for testing ROWID operations, since only the Oracle Server can create a valid ROWID that points to data in a database.
FUNCTION DBMS_ROWID.ROWID_CREATE(
rowid_type IN NUMBER,
object_number IN NUMBER,
relative_fno IN NUMBER,
block_number IN NUMBER,
row_number IN NUMBER)
RETURN ROWID;
Set the ROWID_TYPE parameter to 0 for a restricted ROWID, and to 1 to create an extended ROWID.
If you specify ROWID-TYPE as 0, the required OBJECT_NUMBER parameter is ignored, and ROWID_CREATE returns a restricted ROWID.
Create a dummy extended ROWID:
my_rowid := DBMS_ROWID.ROWID_CREATE(1, 9999, 12, 1000, 13);
Find out what the ROWID_OBJECT function returns:
obj_number := DBMS_ROWID.ROWID_OBJECT(my_rowid);
The variable OBJ_NUMBER now contains 9999.
This procedure returns information about a ROWID, including its type (restricted or extended), and the components of the ROWID. This is a procedure, and cannot be used in a SQL statement.
DBMS_ROWID.ROWID_INFO(
rowid_in IN ROWID,
rowid_type OUT NUMBER,
object_number OUT NUMBER,
relative_fno OUT NUMBER,
block_number OUT NUMBER,
row_number OUT NUMBER);
The IN parameter ROWID_IN determines if the ROWID is a restricted (0) or extended (1) ROWID.
The OUT parameters return the information about the ROWID, as indicated by their names.
For information about the ROWID_TYPE parameter, see the ROWID_TYPE function on page 10-77.
To read back the values for the ROWID that you created in the ROWID_CREATE example:
DBMS_ROWID.ROWID_INFO(my_rowid, rid_type, obj_num,
file_num, block_num, row_num);
DBMS_OUTPUT.PUT_LINE('The type is ' || rid_type);
DBMS_OUTPUT.PUT_LINE('Data object number is ' || obj_num);
-- and so on...
This function returns 0 if the ROWID is a restricted ROWID, and 1 if it is extended.
FUNCTION DBMS_ROWID.ROWID_TYPE(rowid_val IN ROWID) RETURN NUMBER;
IF DBMS_ROWID.ROWID_TYPE(my_rowid) = 1 THEN my_obj_num := DBMS_ROWID.ROWID_OBJECT(my_rowid);
This function returns the data object number for an extended ROWID. The function returns zero if the input ROWID is a restricted ROWID.
DBMS_ROWID.ROWID_OBJECT(rowid_val IN ROWID) RETURN NUMBER;
SELECT dbms_rowid.rowid_object(ROWID) FROM emp WHERE empno = 7499;
This function returns the relative file number of the ROWID specified as the IN parameter. (The file number is relative to the tablespace.)
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid_val IN ROWID) RETURN NUMBER;
The example PL/SQL code fragment returns the relative file number:
DECLARE
file_number INTEGER;
rowid_val ROWID;
BEGIN
SELECT ROWID INTO rowid_val
FROM dept
WHERE loc = 'Boston';
file_number :=
dbms_rowid.rowid_relative_fno(rowid_val);
...
This function returns the database block number for the input ROWID.
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid_val IN ROWID) RETURN NUMBER;
The example SQL statement selects the block number from a ROWID and inserts it into another table:
INSERT INTO T2 (SELECT dbms_rowid.rowid_block_number(ROWID) FROM some_table WHERE key_value = 42);
This function extracts the row number from the ROWID IN parameter.
DBMS_ROWID.ROWID_ROW_NUMBER(rowid_val IN ROWID) RETURN NUMBER;
Select a row number:
SELECT dbms_rowid.rowid_row_number(ROWID) FROM emp WHERE ename = 'ALLEN';
This function extracts the absolute file number from a ROWID, where the file number is absolute for a row in a given schema and table. The schema name and the name of the schema object (such as a table name) are provided as IN parameters for this function.
DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(
rowid_val IN ROWID,
schema_name IN VARCHAR2,
object_name IN VARCHAR2)
RETURN NUMBER;
DECLARE
rel_fno INTEGER;
rowid_val CHAR(18);
object_name VARCHAR2(20) := 'EMP';
BEGIN
SELECT ROWID INTO rowid_val
FROM emp
WHERE empno = 9999;
rel_fno := dbms_rowid.rowid_to_absolute_fno(
rowid_val, 'SCOTT', object_name);
This function translates a restricted ROWID that addresses a row in a schema and table that you specify to the extended ROWID format.
DBMS_ROWID.ROWID_TO_EXTENDED(
restr_rowid IN ROWID,
schema_name IN VARCHAR2,
object_name IN VARCHAR2)
RETURN ROWID;
Assume that there is a table called RIDS in the schema SCOTT, and that the table contains a column ROWID_COL that holds ROWIDs (restricted), and a column TABLE_COL that point to other tables in the SCOTT schema. You can convert the ROWIDs to extended format with the statement:
UPDATE SCOTT.RIDS
SET rowid_col =
dbms_rowid.rowid_to_extended(rowid_col,
'SCOTT", TABLE_COL);
ROWID_TO_EXTENDED returns the ROWID in the extended character format. If the input ROWID is NULL, the function returns NULL. If a zero-valued ROWID is supplied (00000000.0000.0000), a zero-valued restricted ROWID is returned.
If the schema and object names are provided as IN parameters, this function verifies SELECT authority on the table named, and converts the restricted ROWID provided to an extended ROWID, using the data object number of the table. That ROWID_TO_EXTENDED returns a value, however, does not guarantee that the converted ROWID actually references a valid row in the table, either at the time that the function is called, or when the extended ROWID is actually used.
If the schema and object name are not provided (are passed as NULL), then this function attempts to fetch the page specified by the restricted ROWID provided. It treats the file number stored in this ROWID as the absolute file number. This can cause problems if the file has been dropped, and its number has been reused prior to the migration. If the fetched page belongs to a valid table, the data object number of this table is used in converting to an extended ROWID value. This is very inefficient, and Oracle recommends doing this only as a last resort, when the target table is not known. The user must still know the correct table name at the time of using the converted value.
If an extended ROWID value is supplied, the data object number in the input extended ROWID is verified against the data object number computed from the table name parameter. If the two numbers do not match, the INVALID_ROWID exception is raised. If they do match, the input ROWID is returned.
See the ROWID_VERIFY function on page 10-81 for a method to determine if a given ROWID can be converted to the extended format.
This function converts an extended ROWID into restricted ROWID format.
DBMS_ROWID.ROWID_TO_RESTRICTED(ext_rowid IN ROWID)
RETURN ROWID;
INSERT INTO RID_T2@V7db1
SELECT dbms_rowid.rowid_to_restricted(ROWID)
FROM scott.emp@O8db1
WHERE ename = 'SMITH';
This function returns 0 if the input restricted ROWID can be converted to extended format, given the input schema name and table name, and it returns 1 if the conversion is not possible. Note that you can use this function in a BOOLEAN context in a SQL statement, as shown in the example.
DBMS_ROWID.ROWID_VERIFY(
restr_rowid IN ROWID,
schema_name IN VARCHAR2,
object_name IN VARCHAR2)
RETURN ROWID;
Considering the schema in the example for the ROWID_TO_EXTENDED function on page 10-80, you can use the following statement to find bad ROWIDs prior to conversion:
SELECT ROWID, rowid_col FROM SCOTT.RIDS WHERE dbms_rowid.rowid_verify(rowid_col, NULL, NULL);
The stored package UTL_HTTP makes HTTP (hyper-text transfer protocol) callouts from PL/SQL and SQL. You can use it to access data on the internet, or to call Oracle Web Server Cartridges. The package contains two similar entrypoints, each of which takes a string URL (universal resource locator), contacts that site, and returns the data (typically HTML - hyper-text markup language) obtained from that site.
This is the specification of packaged function UTL_HTTP.REQUEST:
function request (url in varchar2) return varchar2;
UTL_HTTP.REQUEST returns up to the first 2000 bytes of the data retrieved from the given URL. For example:
SVRMGR> select utl_http.request('http://www.oracle.com/') from dual;
UTL_HTTP.REQUEST('HTTP://WWW.ORACLE.COM/')
-------------------------------------------------------------------
<html>
<head><title>Oracle Corporation Home Page</title>
<!--changed Jan. 16, 19
1 row selected.
This is the specification of packaged function UTL_HTTP.REQUEST_PIECES, which uses type UTL_HTTP.HTML_PIECES:
type html_pieces is table of varchar2(2000) index by binary_integer;
function request_pieces (url in varchar2,
max_pieces natural default 32767)
return html_pieces;
UTL_HTTP.REQUEST_PIECES returns a PLSQL-table of 2000-byte pieces of the data retrieved from the given URL. The optional second argument places a bound on the number of pieces retrieved. For example, the following block retrieves up to 100 pieces of data (each 2000 bytes, except perhaps the last) from the URL. It prints the number of pieces retrieved and the total length, in bytes, of the data retrieved.
set serveroutput on
/
declare
x utl_http.html_pieces;
begin
x := utl_http.request_pieces('http://www.oracle.com/', 100);
dbms_output.put_line(x.count || ' pieces were retrieved.');
dbms_output.put_line('with total length ');
if x.count < 1
then dbms_output.put_line('0');
else dbms_output.put_line
((2000 * (x.count - 1)) + length(x(x.count)));
end if;
end;
/
Here is the output:
Statement processed. 4 pieces were retrieved. with total length 7687
Below is the specification for package UTL_HTTP. It describes the exceptions that can be raised by functions REQUEST and REQUEST_PIECES:
create or replace package utl_http is
-- Package UTL_HTTP contains functions REQUEST and REQUEST_PIECES for
-- making HTTP callouts from PLSQL programs.
-- Function REQUEST takes a URL as its argument. Its return-type is a
-- string of length 2000 or less, which contains up to the first 2000
bytes
-- of the html result returned from the HTTP request to the argument URL.
function request (url in varchar2) return varchar2;
pragma restrict_references (request, wnds, rnds, wnps, rnps);
-- Function REQUEST_PIECES also takes a URL as its argument. Its
-- return-type is a PLSQL-table of type UTL_HTTP.HTML_PIECES. Each
-- element of that PLSQL-table is a string of length 2000. The
-- final element may be shorter than 2000 characters.
type html_pieces is table of varchar2(2000) index by binary_integer;
function request_pieces (url in varchar2,
max_pieces natural default 32767)
return html_pieces;
pragma restrict_references (request_pieces, wnds, rnds, wnps, rnps);
-- The elements of the PLSQL-table returned by REQUEST_PIECES are
-- successive pieces of the data obtained from the HTTP request to that
-- URL. Here is a typical URL:
-- http://www.oracle.com
-- So a call to REQUEST_PIECES could look like the example below. Note the
-- use of the plsql-table method COUNT to discover the number of pieces
-- returned, which may be zero or more:
--
-- declare pieces utl_http.html_pieces;
-- begin
-- pieces := utl_http.request_pieces('http://www.oracle.com/');
-- for i in 1 .. pieces.count loop
-- .... -- process each piece
-- end loop;
-- end;
--
-- The second argument to REQUEST_PIECES, "MAX_PIECES", is optional. It
is
-- the maximum number of pieces (each 2000 characters in length, except
for
-- the last, which may be shorter), that REQUEST_PIECES should return. If
-- provided, that argument should be a positive integer.
-- Exceptional conditions:
-- If initialization of the http-callout subsystem fails (for
-- environmental reasons, for example, lack of available memory)
-- then exception UTL_HTTP.INIT_FAILED is raised:
init_failed exception;
-- When the HTTP call fails (e.g., because of failure of the HTTP daemon;
-- or because of the argument to REQUEST or REQUEST_PIECES cannot be
-- interpreted as a URL because it is NULL or has non-HTTP syntax) then
-- exception UTL_HTTP.REQUEST_FAILED is raised.
request_failed exception;
-- Note that the above two exceptions, unless explicitly caught by an
-- exception handler, will be reported by this generic message:
-- ORA-06510: PL/SQL: unhandled user-defined exception
-- which reports them as "user-defined" exceptions, although
-- they are defined in this system package.
-- If any other exception is raised during the processing of the http
-- request (for example, an out-of-memory error), then function REQUEST
-- or REQUEST_PIECES reraises that exception.
-- When no response is received from a request to the given URL
-- (for example, because no site corresponding to that URL is contacted)
-- then a formatted html error message may be returned. For example:
--
-- <HTML>
-- <HEAD>
-- <TITLE>Error Message</TITLE>
-- </HEAD>
-- <BODY>
-- <H1>Fatal Error 500</H1>
-- Can't Access Document: http://home.nothing.comm.
-- <P>
-- <B>Reason:</B> Can't locate remote host: home.nothing.comm.
-- <P>
--
-- <P><HR>
-- <ADDRESS><A HREF="http://www.w3.org">
-- CERN-HTTPD3.0A</A></ADDRESS>
-- </BODY>
-- </HTML>
--
-- You should not expect for UTL_HTTP.REQUEST or UTL_HTTP.REQUEST_PIECES
-- to succeed in contacting a URL unless you can contact that URL by using
-- a browser on the same machine (and with the same privileges,
environment
-- variables, etc.) If REQUEST or REQUEST_PIECES fails (i.e., if it
raises
-- an exception, or returns a HTML-formatted error message, yet you
believe
-- that the URL argument is correct), please try contacting that same URL
-- with a browser, to verify network availability from your machine.
end utl_http;
|
 Copyright © 1997 Oracle Corporation. All Rights Reserved. |
|