Oracle PLSQL Language- P25

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

60
lượt xem 5
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'oracle plsql language- p25', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle PLSQL Language- P25

PROCEDURE calc_profits (company_id_in IN NUMBER, profit_type_in IN VARCHAR2 := 'NET'); If I try to use this procedure in Oracle Forms, I must include a value for both parameters, even if I use the default: calc_profits (:company.company_id, 'NET'); Suppose I now create the following local procedure (either in the form itself or in a PL/SQL library): PROCEDURE of_calc_profits (company_id_in IN NUMBER, profit_type_in IN VARCHAR2 := 'NET') IS BEGIN calc_profits (company_id_in, profit_type_in); END; I can now call this procedure using the default value of NET, since the Oracle Forms version of the procedure always passes a value to the stored procedure version: of_calc_profits (:company.company_id); (In Version 7.1 of the Oracle Server, by the way, you will be permitted to use default values in remote procedure calls.) This situation with Oracle Server Version 7 reinforces one of my suggestions regarding default parameter values: always specify a value for each of the module's parameters, even if a value is the same as the default. You will find yourself less vulnerable to problems down the line. Previous: 22.6 Construct Oracle PL/SQL Next: 23. Managing Code Abstract Data Types Programming, 2nd Edition in the Database (ADTs) 22.6 Construct Abstract Data Book Index 23. Managing Code in the Types (ADTs) Database The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 22.7 Tips for Chapter 23 Next: 23.2 Transaction Parameter Design Integrity and Execute Authority 23. Managing Code in the Database Contents: Executing Stored Code Transaction Integrity and Execute Authority Module Validation and Dependency Management Remote Procedure Calls Managing Stored Objects with SQL*Plus Using SQL to Examine Stored Objects Encrypting Stored Code You will either embed PL/SQL programs in your client-side application, as happens in the Oracle Developer/2000 tools, or you will store your code in the Oracle database. The term stored procedure commonly refers to code that is stored in the database. Along with procedures, you can also store functions and packages, which can contain variables, cursors, record structures, etc. Whenever you hear the term stored procedures, you should think "stored objects." There are a number of benefits to storing an object in the database: q The database manages dependencies between your stored objects. For example, if a stored function relies on a certain table and that table's structure is changed, the status of that function is automatically set to INVALID and recompilation takes place, again automatically, when someone tries to execute that function. q Any session with EXECUTE authority can execute the stored object. Whether you are in an Oracle Forms application, a database trigger, or a batch process, that stored object is accessible as long as the database is accessible. The compiled code for the object is made available from the shared memory of the database instance. q You can execute a module stored in another (remote) database. This is known as a remote procedure call, or RPC. The ability to execute code on a different database means that you do not have to distribute application code to multiple databases in order to support distributed applications. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
q Stored modules offer transaction-level security, which goes well beyond the table- and column-level data and referential integrity offered by the database. With a stored module you can make sure that transactions involving multiple tables and different steps are always performed properly. This was actually one of the original motivations for the development of the PL/SQL language. q Execution of a stored object shifts CPU load to the server. Rather than run your code locally in a form or report, the stored object executes on the database server. Assuming that the server is sized to handle the load, this shift could improve overall application performance. This feature could be less of a benefit, of course, if the server is already heavily burdened. Stored objects play an important role in today's client-server applications. The information in this chapter will help you make the best possible use of stored objects. 23.1 Executing Stored Code To execute a stored program unit, you must first store this program in the database. I'll explain this CREATE OR REPLACE step later in this chapter. For now, let's take a look at what happens when you run a stored PL/SQL program. 23.1.1 Executing Procedures If you are working in SQL*Plus, you can execute programs directly from the command line. Use the EXECUTE command to run procedures as follows: SQL> execute calc_totals Notice that you do not have to provide a semicolon. In fact, if you are going to use the EXECUTE command, the entire PL/SQL procedure call must fit on one physical line. As soon as you press the Enter key, the program executes. You can also save a few microseconds (but accumulated over a whole year...) by providing only the minimum number of characters for the EXECUTE command: SQL> exec calc_totals When you are executing a procedure within a PL/SQL block, you do not use the EXECUTE syntax. You simply call the procedure, passing all necessary parameters within parentheses, and then terminate the line of code with a semicolon. Here is the syntax for calling the calc_totals procedure within a PL/SQL block: BEGIN calc_totals; END; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
You can also execute PL/SQL procedures in various "front-end" tools, such as Oracle Forms, PowerBuilder, and many 3GL languages like C and COBOL. The syntax for calling procedures (and functions) will vary from tool to tool. Within the Oracle Developer/2000 environment, you can call stored programs from within a "native," albeit outdated, version of PL/SQL (see Appendix B, Calling Stored Procedures from PL/SQL Version 1.1, for more information). From a language like C, you will actually use a precompiler called Pro*C (from Oracle) and embed calls to PL/SQL programs within a SQL execution block like this: EXEC SQL EXECUTE BEGIN calc_totals; END; END-EXEC; 23.1.2 Executing Functions The situation with functions is a little bit different. A function returns a value, so you can't just execute it directly. If you try to run a function as if it is a procedure, you will get the following error: SQL> exec total_sales (1997) begin total_sales (1997); end; * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00221: 'TOTAL_SALES' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored If you want to run a function simply to view its return value, you can execute it within a call to DBMS_OUTPUT.PUT_LINE as follows: SQL> exec DBMS_OUTPUT.PUT_LINE (total_sales (1997)) Of course, this only works if the datatype of the return value of the function matches one of the datatypes supported by DBMS_OUTPUT: number, string and date. For anything else, you will need to execute the function within an anonymous block and then convert the value as necessary for display. In the following example, I declare a PL/SQL table of strings to retrieve the value from a function that returns all the foreign key information for a table. I then use a FOR loop to display that information: /* Employs PL/SQL 2.3 syntax for PL/SQL tables. */ DECLARE TYPE strtabtype IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
strtab strtabtype; BEGIN strtab := primary_keys.for_table (`emp'); IF strtab.COUNT > 0 THEN FOR tabrow IN strtab.FIRST .. strtab.LAST LOOP DBMS_OUTPUT.PUT_LINE (strtab.column_name); END LOOP; END IF; END; / If I have saved this block in a file called myscript.sql, I can then execute this program and verify my function's behavior as follows: SQL> @myscript NOTE: You might wonder why I placed my FOR loop inside an IF statement. Surely, if there were no rows, the strtab.FIRST method would return NULL and the loop would not execute. Yes and no. The FIRST and LAST methods do return NULL if the PL/SQL table is empty, but oddly enough, if your numeric FOR loop range evaluates to: FOR tabrow IN NULL .. NULL then the PL/SQL runtime engine does enter the loop and try to process the body contents immediately. Then it raises an ORA-06502 VALUE_ERROR exception. So you really want to put some conditional logic around those FOR loops if you think the range endpoints might evaluate to NULL.1 Now that you've seen how to execute programs "from the outside," let's move inside and take a look at the architecture Oracle employs to deliver that code to you for execution. 23.1.3 Memory-Based Architecture of PL/SQL Code Whenever you start up an Oracle instance, a chunk (usually a rather large chunk) of memory is set aside for the System Global Area or SGA. Various Oracle processes manage this area of memory to do any of the following and more: q Retrieve data from the database and deliver it to user processes (a PL/SQL procedure executing an explicit cursor, an Oracle Forms application querying records through a base table block, an Internet HTML page utilizing the Oracle WebServer interface, and so on). q Change data and then manage the transaction processing (commits and rollbacks) based on those changes. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
q Provide access to PL/SQL programs for execution in the server itself. q Transfer information between Oracle sessions using database pipes and database alerts. When you use a stored program element (by executing a procedure or function or by making reference to a nonexecutable part of a package like a constant or cursor), it must be accessible from the SGA. So when you reference a program element, its compiled version is loaded into the SGA's shared pool, if it is not already present. Then any program data associated with that element is instantiated in your Program Global Area (PGA). At that point, the code can be executed or otherwise used in your session. The next time a session with execute authority on that program element references it, it will already have been loaded and will therefore be available for execution. When the SGA shared pool area fills up, code in the pool will be aged out (that is, room will be made for newly requested programs) on a least-recently-used basis. Programs which are used often will therefore most likely be available immediately upon request. You can also pin programs into shared memory if you want to make certain that they will not be aged out. See Chapter 25, Tuning PL/SQL Applications, for more details on pinning. Note that even if your compiled code is aged out of the SGA, any persistent, package-based data will remain available in your PGA. In other words, code may be swapped out of the SGA, but program data remains and persists for the duration of your session (or until you explicitly request a reinitialization of your schema). Figure 23.1 illustrates a number of the points covered in this section. Figure 23.1: The System Global Area and Program Global Area Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
23.1.4 Key Concepts for Program Execution Here are some basic principles to remember about program execution: q Compiled PL/SQL code is shared among all users with EXECUTE authority on that code. Usually you will set up your environment so that one account or schema owns all the code for an application. You then grant EXECUTE authority to other users on that code. When any of those schemas runs this code, they are working with the same compiled code. Again, the data instantiated and manipulated within those programs is specific to each connection. q When you reference any individual element in a package, the entire package is loaded into shared memory. This can be both an advantage and a disadvantage. If you group together (in a package) functionality which is usually executed in "close proximity," then you are more likely to find the program you need in shared memory. This will speed up the completion of your program. On the other hand, suppose that you build some very large packages which contain programs covering a disparate range of functionality. My application needs just one function out of the 100 procedures, functions, and cursors defined. Too bad! My SGA and my PGA must absorb the full punishment of the package (both the room needed for the shared, compiled code and all the package data instantiated in my PGA). q Program data instantiated within a program is stored in PGA for each user, rather than in the SGA. This means that each user has his own copy of program data. If a procedure declares a PL/SQL table and stuffs it full of 1000 strings, every user who executes that procedure has his own copy of that table and its 1000 strings. The nice thing about this characteristic is that gives you a fair amount of flexibility in finding the delicate balance between those ever- Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
competing resources: CPU and memory. NOTE: The architecture in the multi-threaded server (MTS) is a bit different, but the principle is the same: different users have their own copies of their program data. q Stored code runs under the authority of that code's owner, not that of the schema which is executing the code. Suppose that you have stored all the code for your order entry application in the SYSTEM account (a very bad idea, as this example will illustrate). You then grant EXECUTE authority on that code to SCOTT, the traditional Oracle demonstration account, which has very little power outside of doing whatever it wants with the emp and dept tables. Now suppose further that the delall procedure of the ordent package allows you to delete all orders from the application table. The intention is that only administrators will have the ability to do that. In fact, the only account that can delete from the orders table at all is SYSTEM. Once you have granted EXECUTE authority on ordent to SCOTT, however, all heck can very decidedly break loose. For when a user logged in as SCOTT executes the ordent.delall procedure, that user is, for that moment, acting as though she were connected to SYSTEM. All rows are removed from the orders table! You must, consequently, very careful about (a) the authority of the schema owning your shared application code, and (b) to whom you grant EXECUTE authority on that code. q Even though stored code runs under the authority of that code's owner, the USER function still returns the name of the schema currently connected to Oracle. The USER function does not, in other words, return the name of the schema which owns the code in which the USER function was called. Previous: 22.7 Tips for Oracle PL/SQL Next: 23.2 Transaction Parameter Design Programming, 2nd Edition Integrity and Execute Authority 22.7 Tips for Parameter Book Index 23.2 Transaction Integrity Design and Execute Authority The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 23.1 Executing Chapter 23 Next: 23.3 Module Stored Code Managing Code in the Validation and Dependency Database Management 23.2 Transaction Integrity and Execute Authority The RDBMS and SQL languages give you the capability to tightly control access to and changes in any particular table. With the GRANT command you can, for example, make sure that only certain roles and users have the ability to perform an UPDATE on a given table. This GRANT statement cannot, on the other hand, make sure that the UPDATEs performed by a user or application are done correctly. In a typical banking transaction, you might need to transfer funds from account A to account B. The balance of account B must be incremented, and that of account A decremented. Table access is necessary, but not sufficient, to guarantee that both of these steps are always performed by all programmers who write code to perform a transfer. Without stored objects, the best you can do is require extensive testing and code review to make sure that all transactions are properly constructed. With stored objects, on the other hand, you can guarantee that a funds transfer either completes successfully or is completely rolled back, regardless of who executes the process. 23.2.1 Execute Authority on Stored Objects The secret to achieving this level of transaction integrity is the concept of execute authority (also known as run authority). Instead of granting to a role or user the authority to update a table, you grant privileges to that role/user only to execute a procedure. This procedure controls and provides access to the underlying data structures (see Figure 23.2). The procedure is owned by a separate Oracle RDBMS account, which in turn is granted the actual update privileges on those tables needed to perform the transaction. The procedure therefore becomes the gatekeeper for the transfer transaction. The only way a program (whether an Oracle Forms application or a Pro*C executable) can execute the transfer is through the procedure, so the transaction integrity can be guaranteed. Figure 23.2: Transaction integrity with a PL/SQL code layer Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
In order for a stored procedure or package to compile (which occurs at the time of creation or replacement), the owner of that program must explicitly be granted all the necessary privileges to any objects referenced by the program. These privileges may not be granted simply to a role. If, for example, procedure disp_customer issues a SELECT statement against the customer table, then the owner of disp_customer must be granted a minimum of SELECT privileges on that table with an explicit command: GRANT privilege ON customer TO procedure_owner; Requiring direct grants to individual Oracle users sometimes causes difficulty in environments where grants are controlled carefully and efficiently through the use of roles. After all, the whole idea of the role is to allow DBAs to move away from the onerous task of directly granting privileges to a myriad of individual users. Yet every one of those users must execute the stored procedures underlying an application. What's a DBA to do? In some Oracle shops, a single account (user), which I'll call STOROBJ, is created in the production environment. This user owns all stored objects and has update privileges on all tables, as is appropriate. Other people who use the applications might have SELECT privileges on a number of tables, and perhaps even update privileges on certain tables that are maintained through Oracle Forms applications. But all complex transactions are bundled into stored procedures and stored functions, and users are granted EXECUTE authority only to those stored programs. If an Oracle Forms screen needs to perform a funds transfer, it calls the stored procedure and displays confirmation information on the screen. The logic and authority would, however, reside in the database and be controlled tightly by the STOROBJ account. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
To grant execute authority on a stored object, you issue the following command (in SQL*Plus, SQL*DBA, or another product that supports the issuing of DDL calls): SQL> GRANT EXECUTE ON object_name TO user_or_role; NOTE: You can only grant EXECUTE authority to an entire package, not to individual programs within a package. Suppose that you build a single package to encapsulate all SQL-based access to a table; this package contains procedures which perform inserts, updates, and deletes, as well as cursors and functions to query data. There is no way to grant "read-only" EXECUTE authority. A schema can either run all programs in a package or none. As a result, you might want to build a single package encapsulating all SQL against a table or view, but then build additional packages on top of that "base" package which act as filters for certain classes of activity. 23.2.2 Creating Synonyms for Stored Objects You should create public synonyms for each of the stored objects so that individual developers do not have to reference the STOROBJ account name in order to access the programs. Suppose, for example, that we have a funds transfer procedure, as follows: PROCEDURE xfer_funds (from_account_in IN NUMBER, to_account_in IN NUMBER, transfer_amount_in IN NUMBER, transfer_status_out OUT NUMBER); Without a synonym, you would have to execute this procedure in this way: storobj.xfer_funds (:old_acct, :new_acct, :xfer_amt, : xfer_stat); You should always avoid hardcoding the name of the owner of an object, be it a procedure or a table. What if you need to change the account name? What if you decide to move the procedure to an entirely different server and then need to execute this procedure as an RPC? Whenever you are working with stored objects, you should create synonyms, as follows: SQL> CREATE PUBLIC SYNONYM xfer_funds FOR storobj. xfer_funds; Now I can call the transfer procedure without making reference to its owner: xfer_funds (:old_acct, :new_acct, :xfer_amt, :xfer_stat); While it is possible to create a synonym for a standalone procedure, it is not possible to create a synonym for a packaged procedure or function. You can, however, create a synonym for the package Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
itself. In effect, synonyms can be used to avoid having to provide the schema name of an object; you cannot use it to avoid specifying the package name in which a program is defined. Previous: 23.1 Executing Oracle PL/SQL Next: 23.3 Module Stored Code Programming, 2nd Edition Validation and Dependency Management 23.1 Executing Stored Code Book Index 23.3 Module Validation and Dependency Management The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 23.2 Transaction Chapter 23 Next: 23.4 Remote Integrity and Execute Managing Code in the Procedure Calls Authority Database 23.3 Module Validation and Dependency Management Whenever you create or replace a stored object in the database, the PL/SQL engine compiles the code. If the compile succeeds, then the following information is stored in the database: q The source code for the module, as you wrote it. q The tree structure of the module, a hierarchical representation of the original source code, with a list of local and external references. The tree structure is used only by the compilation (and automatic recompilation) process. q The pcode for the module, which is a list of instructions to the PL/SQL execution engine. The PL/SQL execution engine uses the pcode to execute the module when it is called by a host program. When a module compiles, its status is set to VALID. This status is maintained in the SYS.OBJ$ table. Upon compilation, the PL/SQL engine also has resolved all references to other database objects such as tables and other stored programs. Each of these references constitutes a dependency for that module. In other words, the validity of the module is dependent upon the validity of all objects on which it depends. All dependency information is stored in the SYS.DEPENDENCY$ table. NOTE: The tree structure, pcode, and dependency information is maintained only for named modules. Anonymous blocks and database triggers are compiled only when (and each time that) they are executed. The generated pcode for these objects is stored directly in the shared pool of the database instance for as long as they are used, and until they are erased from the System Global Area, using a least-recently-used algorithm. Starting with Oracle Server Release 7.3, triggers are compiled and their pcode stored in the database. 23.3.1 Interdependencies of Stored Objects A stored object must be VALID in order for its pcode to be loaded into the shared pool and executed by the host program. As noted above, if the compile succeeds at create/replace time, then the status is Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
set to VALID. This status may, however, depend on other objects. Consider the following function: FUNCTION full_name (employee_id_in IN NUMBER) RETURN VARCHAR2 IS first_and_last VARCHAR2(100); BEGIN SELECT first_name || ' ' || last_name INTO first_and_last FROM employee WHERE employee_id = employee_id_in; RETURN first_and_last; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; Suppose that on Monday I save this function to the database. It compiles successfully and its status is set to VALID. The PL/SQL compiler also adds a record in the DEPENDENCY$ table to indicate that full_name is dependent on the employee table. Then on Tuesday, the DBA team adds another column to the employee table. The Oracle Server automatically checks the dependencies for the employee table and sets the status of all dependent objects to INVALID. This is a recursive process. Once full_name is set to INVALID, then any modules calling full_name are also set to INVALID.[1] [1] An important exception to this "chain reaction" occurs with packaged modules. If, for example, full_name was defined within a package called, say, "employee", then even if the status of the full_name module is set to INVALID, no modules that call full_name will be tagged invalid, unless the specification of full_name changed (which it does not in this case). See the package examples and documentation on the disk for more information about this extra protection provided by packages. The next time a user runs the full_name function, the database notices that the status is INVALID. It then calls the PL/SQL engine to compile the function. If the compile succeeds, then the pcode is loaded to shared memory and the function runs. Automatic Versus Manual Compilation It is easy to see that the database does a lot of work for you by maintaining the stored object dependencies and automatically recompiling objects as needed. You may not want to always depend on such recompilation, however. There are two potential drawbacks to automatic recompilation: q The recompilation takes place at run time, when the user has requested an action. This means that the user waits while PL/SQL does its thing. The pause should not be very noticeable for small objects. Large packages can, however, take more than several seconds to compile. Do Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
you want your users to wait while this happens? q The recompilation can fail. What if the reason that the full_name function is INVALID is that the employee table was dropped? The database cannot recover from such an error, as far as full_name is concerned. A much more sensible approach to take with stored object recompilation is to manually recompile all INVALID objects before the user tries to execute those objects. This way the compilation time is moved off-line and if any objects fail to compile, you can analyze and resolve the problem. To do this manual recompilation you need to coordinate closely with the DBA group (if it is separate from the application development team). You can determine which modules are INVALID by examining the contents of the USER_OBJECTS or ALL_OBJECTS views. You could even generate the commands necessary to recompile all INVALID PL/SQL objects with the following query: SELECT 'ALTER ' || object_type || ' ' || object_name || ' COMPILE;' FROM user_objects WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') AND status = 'INVALID'; Output from this query might look like: ALTER PACKAGE PSGLOBAL COMPILE; ALTER FUNCTION FULL_NAME COMPILE; You could also force recompilation of a module by using the ALTER_COMPILE procedure in the DBMS_DDL package (see Appendix A, What's on the Companion Disk?, for more details), as follows: DBMS_DDL.ALTER_COMPILE ('package', 'SCOTT', 'FULL_NAME'); It is easy, when you use stored objects, to take for granted automatic management of these objects by the database. You might scarcely notice the status of your objects and the recompilations that take place behind the scenes. That's cool. That's the whole idea. As you move your applications into production, however, you would be well served to remember how the database works with your stored objects. That way, when something goes wrong (very slow execution time under certain circumstances, for example), you have some idea of how to fix it. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 23.2 Transaction Oracle PL/SQL Next: 23.4 Remote Integrity and Execute Programming, 2nd Edition Procedure Calls Authority 23.2 Transaction Integrity Book Index 23.4 Remote Procedure Calls and Execute Authority The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 23.3 Module Chapter 23 Next: 23.5 Managing Validation and Dependency Managing Code in the Stored Objects with Management Database SQL*Plus 23.4 Remote Procedure Calls One of the most important enhancements contained in PL/SQL Version 2.0 and all subsequent releases is the ability to execute remote procedure calls or RPCs. A remote procedure (module) call occurs when you execute a program from within one database that is stored in another database. To execute a remote module, you simply attach a database link after the module name, as follows: emp_name := full_name@new_york (:emp.employee_id); The full specification for a remote module call is: schema_name.[package_name.]module_name@database_link_name where [package_name.] is the optional package name, necessary if the module is contained in a package. Just as you should hide the owner of a module with a synonym, you should do the same thing for remote module calls. Programmers should never have to know where a module is located nor who owns it. You should never want them to know because if that information is placed in a program, that program is much more vulnerable to breakage. The very nature of a remote module call indicates that the code can be moved to a different remote database. Never let a programmer hardcode such information in a program. When you call a remote module and it raises an exception, that exception is passed back as raised to the calling program -- for all but user-defined exceptions. If your remote module raises a NO_DATA_FOUND exception, your exception handler traps that error. If, on the other hand, your module uses RAISE_APPLICATION_ERROR to raise a custom exception (error numbers between - 20000 and -20999), then that specific exception is lost in the translation back to the calling program. Instead, error ORA-06510 is always returned to the local program. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 23.3 Module Oracle PL/SQL Next: 23.5 Managing Validation and Dependency Programming, 2nd Edition Stored Objects with Management SQL*Plus 23.3 Module Validation and Book Index 23.5 Managing Stored Dependency Management Objects with SQL*Plus The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 23.4 Remote Chapter 23 Next: 23.6 Using SQL to Procedure Calls Managing Code in the Examine Stored Objects Database 23.5 Managing Stored Objects with SQL*Plus You can use SQL*Plus to manage stored objects like database triggers, procedures, functions, and packages. When Oracle7 was first released, SQL*Plus was the only way to create stored procedures. With the advent of CDE and Oracle Procedure Builder, however, you now have a number of choices for creating and updating your stored objects. Still, many of you will rely on SQL*Plus, especially if your DBA activities are performed on a UNIX (or other non-Windows) server. 23.5.1 Creating Stored Objects The syntax to create stored objects is as follows: CREATE [OR REPLACE] PROCEDURE proc_name (param_list) AS ... ; CREATE [OR REPLACE] FUNCTION func_name (param_list) RETURN datatype AS ... ; CREATE [OR REPLACE] PACKAGE pkg_name AS ... ; CREATE [OR REPLACE] PACKAGE BODY pkg_name AS ... ; CREATE [OR REPLACE] DATABASE TRIGGER trig_name AS ... ; Everything from the object name onwards has the same syntax shown in the appropriate chapter on that object. The CREATE OR REPLACE syntax in front of the normal syntax indicates to SQL*Plus that you want to create the object in the database. This is a DDL (data definition language) statement in SQL, just like a CREATE TABLE statement. You must create a package specification separately from the package body. In fact, you must create the specification first, or else you receive the following error: PLS-00304: cannot compile body of 'pkg_name' without its specification When an object is created, it is stored in a data dictionary table. The source for procedures, packages, functions, and package bodies is kept in the SOURCE$ table (owned by SYS). Views into the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
SOURCE$ table are: ALL_SOURCE Source for all objects to which you have access, and therefore EXECUTE privilege on that object USER_SOURCE Source for all objects you have created The statements executed by a trigger are stored in the ACTION column of the TRIGGER$ table (owned by SYS). You can view all triggers you created by accessing USER_TRIGGERS, or all triggers to which you have access by viewing the contents of ALL_TRIGGERS. Even though you store your modules in the database, you do not maintain your source code for these modules directly in their data dictionary tables. Instead, you must always keep the source code in text files. If you need to make a change to an object, make the changes in the source code and then replace the object in the database. In order to distinguish regular SQL*Plus scripts from scripts that maintain stored objects, I employ a different file extension for them, as shown in the following table: File Type Extension General SQL*Plus script .sql Testing script .tst Stored procedure .sp Stored function .sf Stored package body spb Stored package specification .sps 23.5.2 Tips for Storing Code in Files Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.