Make Views Work for You. TECHNOLOGY: PL/SQLBy Steven Feuerstein Part 1. PL/SQLIf you’re reading this article, there’s a really good chance that you write PL/SQL code. Which means that you also will at least occasionally need to analyze that code, answering questions such as On which database objects does my program depend? You can, of course, always use the search feature of your editor or integrated development environment to look through multiple database objects and files to find specific chunks of text. But that won’t be enough to answer all of the above questions and many more you will encounter. Don’t despair! One of the most lovely aspects of writing PL/SQL code and compiling that code into the database is that Oracle Database offers a set of views—known collectively as the data dictionary—that enable you to use the SQL and PL/SQL languages to get answers to just about any question you have about your code. Table 1 offers a high- level overview of the data dictionary views most often used to manage PL/SQL code. USER. This view is used mostly by Oracle Database to invalidate the status of database objects when an object on which they depend changes. USER. This view is accessed by the SHOW ERRORS SQL*Plus command. You can, however, write your own queries against it as well. USER. Once populated, this view provides you with information about all the identifiers—program names, variables, and so on—in your code base. USER. Actually, this view shows you the source, parsed, and compile sizes for your code. Although it is used mainly by the compiler and runtime engine, you can use it to identify the large programs in your environment. USER. You can, for instance, use this view to see if an object is marked INVALID, find all the packages that have EMP in their names, and so on. USER. This is a very handy view, because you can run all sorts of analyses of the source code against it with SQL and, in particular, Oracle Text. USER. Use this view to discover which programs have been compiled via native compilation. USER. You can write programs against USER. User schemas generally have no privileges on these tables; Oracle Database grants only SELECT access on the views. Most data dictionary views come in three versions: The USER view: information about database objects owned by the schema to which you are connected The ALL view: information about database objects to which the currently connected schema has access The DBA view: unrestricted information about all the database objects in a database instance (non- DBA schemas usually have no authority to query DBA views) Let’s look at an example. Suppose I want to obtain a list of the objects—tables, views, packages, and so on—defined in the database. The following query returns all the objects defined in my schema. SELECT * FROM user. The most commonly used columns are OBJECT. Show the names of all tables in my schema. SELECT object. That program unit must then be recompiled (which Oracle Database will often do automatically the next time you try to use that program unit). Show all objects that have been changed today. SELECT object. I’d like to find out where this procedure is called, outside of the SALES. See the “A Better USER. A trigger is a program in a database. Triggers are normally written in PL/SQL. Retrieved from 'http://www.orafaq.com/wiki/index.php?title=Trigger&oldid. PL/SQL does not support reflection. But we can find out the name of the current program unit using the $$PLSQL. PL/SQL Before Trigger. Indicates that the trigger should not be executed when a replication agent modifies the table that is involved in the trigger. Key columns are PLSQL. An optimization level of 1 means a minimal amount of optimization. Neither of these levels should be seen in a production environment. PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in. Write a PL/SQL program for a trigger. PL/SQL program for tracking operation on a emp table. A trigger is a named PL/SQL unit that is stored in the database and. A trigger is a named program unit that is stored in the database and. In the same way, a PL/SQL program issues a query and processes the rows. Identify all programs for which compile time warnings (which provide feedback on the quality of your code) are disabled. SELECT name, plsql. Columns of this view are AUTHID: Shows whether a procedure or a function is defined as an invoker rights (CURRENT. Key columns are TRIGGER. Find all disabled triggers. FROM user. The following query, unfortunately, fails and produces an ORA- 0. FROM user. You can use it to perform impact analysis on your code, as in: How many programs will need to be changed if I change this table? Key columns in this view are NAME: Name of the object. TYPE: Type of the object. REFERENCED. Find all the objects that depend on (reference) the EMPLOYEES table. SELECT type, name. FROM user. Let’s look at an example and then at how the USER. Here’s a PL/SQL example with a query that uses the standard Oracle Database EMPLOYEES table. PROCEDURE process. Then you can call the function as needed. Assuming that I have created a package named EMPLOYEES. It contains information about the arguments (also known as parameters) of each of the stored programs in your schema. It offers, simultaneously, a wealth of nicely parsed information and a complex structure. Key columns are OBJECT. Find all programs that have an argument of type LONG. This is the datatype that was used to store large strings (more than 4,0. Oracle Database. Now the database uses large object types such as character large object (CLOB). Oracle recommends that any usages of LONG be converted to CLOB. A recommendation you will hear from many programming experts is that functions should contain only IN arguments. A function with an OUT or an IN OUT argument cannot be called inside a SQL statement, and it cannot be used in a function- based index. If you need to return multiple pieces of information, use a procedure or return a record. Listing 1 demonstrates a query that will identify all functions defined in packages that violate this best practice. Code Listing 1: Find functions that have an OUT or an IN OUT argument. SELECT ua. object. The object names and package names must match, and the overload value must be the same or both must be NULL. The overload column is not NULL if the package has two or more subprograms with the same name. It’s a Gold Mine in There. This article merely scratches the surface of the application information that can be mined from the data dictionary views in Oracle Database. PL/SQL editors such as Oracle SQL Developer provide user interfaces to many of these views, making it easier to browse their contents. A Better USER. It makes it relatively easy to get answers to questions that would otherwise require you to parse a PL/SQL program unit and then analyze the parse tree. Here’s one example: My manager has asked me to remove from our programs any variables, constants, exceptions, and the like that are declared but never used. Finding all candidates for removal by simply searching code would be both time- consuming and error- prone. With PL/Scope, it’s easy. It may also contain other rows for usages of that identifier: a reference to it or a line of code that changes its value. So a MINUS operation between these two sets of rows will leave us with all those identifiers that are declared but never referenced or used. Here’s the query for exceptions. WITH subprograms. I wrote at more length about PL/Scope in 2. Zoom In on Your Code”. Take the Challenge. Each PL/SQL 1. 01 article offers a quiz to test your knowledge of the information provided in it. The quiz appears below and also at PL/SQL Challenge, a Website that offers online quizzes on the PL/SQL language as well as SQL and Oracle Application Express. Here is your quiz for this article: Assume that all the packages in my schema contain at least one subprogram (procedure or function). Which of these queries will display the names of all the packages in my schema?
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2016
Categories |