| Oracle Tips | More Oracle Tips |
As a programmer and instructor for many years, I have developed proficiency in Oracle, Powerbuilder, Unix (especially AIX) and HTML, among other technologies and products. One of the things I naturally do for my own sake is to capture sequences and screen shots to aid in retention of the important concepts and 'gotchas' inherent in any IDE/technology/standard.
In addition to capturing training sequences for my own benefit, when I was an instructor, I took it upon myself to write short notes and handouts for students. The handouts sought to firm up understanding of concepts covered that day, or to give more examples of topics, or to reinforce a novel concept or powerful feature we had seen that class day. A representative selection of this content is provided here, to demonstrate my written style and depth of technical understanding.
| Anonymous Blocks PL/SQL can be assembled into program units in a number of ways. One simple form of PL/SQL programming, (but finally one with some limitations) is the Anonymous Block. It's a block of instructions in PL/SQL and SQL which is not saved under a name as an object in your schema. It is also not compiled and saved in server storage, so it needs to be parsed and executed each time it is run. You can build them with various tools. However, this simple form of program can use variables, can have flow of control logic, can return query results into variables (if they are single-valued), and can prompt the user for input using the SQL*Plus '&' feature. This example shows a number of things in a short discussion. Notice the block and the way it looks in SQL*Plus:  I have run the block a number of times, which puts the whole thing into the SQL*Plus buffer. Any query or block can be re-run with the / character, which runs the whole block, which in turn prompts each time for the HomeSite 'code'. Notice the apostrophes around the variable reference, which puts the quotes around the values at runtime. Notice also the use of SELECT .. INTO to hand off query results and the use of the Oracle 'package' DBMS_OUTPUT to do printing from the block back to the SQL*Plus environment. The PUT_LINE function of that package is the standard way to send messages back to the client environment, and is an example of Oracle extensions to the SQL standard. PUT_LINE will do automatic datatype conversion as you can see, since the variable named RESULT is numeric. Notice the prevalence of the ';' character at the end of every statement. One of the things you cannot do inside an anonymous block is to generate a result set with a standard SELECT. Trying to do a SELECT which does not return a result into variables causes a compiler error; this is a significant limitation. See later use of cursors running within looping structures, and the use of variables and DBMS_OUTPUT again, for result sets which exceed one row. SELECT within a PL/SQL block If you run interactive queries through SQL*Plus, then you have full discretion over all the variations of DML. If, however, you put a SELECT command into a PL/SQL block, then you have two possible ways to make this work, a SELECT INTO statement and the use of a cursor. The first option is to do a SELECT ... INTO my_var [, my_var2, my_var3] where all the columns in the SELECT match up with a variable declared in your declaration section. (If a program is a procedure or function, then the word DECLARE is not normally accepted, incidentally). One easy way to ensure that the receiving variables have the appropriate datatype is to use the %TYPE attribute, which always uses the type of the specified table/column. This is called an 'anchored' datatype. If your SELECT ... INTO returns more than one row, you easily generate an 'exception', which causes Oracle to interrupt and try to process this error condition. One easy way around this, and the second way generally, is to use a cursor, and do a single FETCH (i.e., do not process the cursor inside a loop). This approach does permit better control over the processing, and is actually recommended. If you actually want to process more than one row in the result set, you may use any of several looping constructs, where a cursor fetch is done implicitly or explicitly within that loop. Notice also that you may create a cursor which signals its intention to update the database with the SELECT ... FOR UPDATE syntax. This is coupled with the use of UPDATE/DELETE WHERE CURRENT OF cursorname to provide a cursor which automatically knows which single row in the cursor should. Constrained and Unconstrained data types Formal parameters in Oracle stored program units may not provide all possible detail about precision and size when standard datatypes such as NUMBER and VARCHAR2 are used. Instead, if you are using parameters which related to columns in tables, take advantage of the 'Anchored' or constrained style where you append '%TYPE' to the end of the table and column name, as: MyVar EMPS.EMPNO%TYPE; One way to handle exceptions in DML operations Since exceptions cause control to exit the program, you may want to code all DML operations as functions which return a true / false value (available as a PL/SQL datatype, not a DDL datatype). In the function, create an exception handler like this: FUNCTION XYZ... RETURN BOOLEAN AS BEGIN ...processing RETURN TRUE ; EXCEPTION WHEN OTHERS THEN RETURN FALSE; Test, in the calling block, for whether the return value is true, and if so, commit, else rollback. Invoker Rights can clear up a muddy permissions morass In Oracle 8.1, the original sole authorization model for running stored programs was that of the owner, whereby the owner of the object would need to do appropriate GRANTs to appropriate users/roles. Since the great majority of Oracle installations ( according to Steven Feuerstein ) use multiple schemas, this can be a hassle. Starting with Oracle 8.1, you can use Invoker Rights in addition to Definer (owner) to have the Oracle server use your permissions when you run a procedure. Add the words AUTHID DEFINER to enforce the default model, or AUTHID CURRENT_USER to use invoker rights. Either syntax variant appears in front of the word IS in the specification. Trigger Notes - Mutating Tables error Triggers can be assigned on the statement level to query the table they are servicing, which obviates the 'mutating table' problem often found in row-level triggers. Triggers can now also do their own COMMIT and ROLLBACK, perfect for logging user activity and ensuring it is written out with a COMMIT, even though the larger transaction is rolled back. See 'Autonomous Transactions' in the documentation. How to isolate the date portion of an Oracle date datatype As you may know, despite the name, the Oracle datatype known as 'date' supports date and time storage. A normal insert statement can cite SYSDATE, as opposed to any other date represented as a string. If SYSDATE is used as the date expression, then the current time of day is also stored. To only focus on the date portion, you would use: TRUNC( date_expr ), which ignores the time part of the stored data. A much-respected source says to use TRUNC( date_expr ) on triggers to lop off the time portion of a column entry within the trigger to modify the :NEW data element. (This all assumes, of course, that time of day detail is insignificant). Storing data in this way makes it possible to use indexes effectively in search predicates. Fixing display length problem when listing View DDL Create Script In the SQL*Plus environment, there is a global setting called LONG which governs the maximum displayed length of columns in any table or view. The TEXT column in the USER_VIEWS Data Dictionary view is of type LONG, so consider commands like SET LONG 2000 to deal with this common problem. (To automatically have this setting changed, create a LOGIN.SQL file to house this and other common settings). Various variables In SQL*Plus and other 'host' environments, you can 'bind' a variable to one within a PL/SQL block. This block can be anonymous (storable in a text file, but not as an object in the database), or they can reside within packages, procedures and functions. Packages provide a way to have global type variables, in the sense that accessing that variable is enough to instantiate the package and make that a persistent value. You also have bind variables found in database triggers. The ':' (colon) character is used to prefix any bind variables from within a PL/SQL block. CREATE PUBLIC SYNONYM You may use the CREATE PUBLIC SYNONYM syntax to make a synonym globally available, but be careful that the synonym name does not already exist. Using NVL with Aggregates COUNT, SUM, AVG, STDDEV and VARIANCE provide the ability to operate on a column across multiple rows. Only COUNT has the ability to operate on entire rows, not just specific columns or column-level expressions, with its special COUNT(*) syntax. With the exception of COUNT(*), all other forms of aggregate expressions need you to pass them a column name or any expression of the appropriate datatype. For example, you can't do much with SUM(last_name), for SUM of course wants to work with numeric values. Here's an example of doing a 'what if' calculation on the fly, where we want to know what the impact of an across-the-board 12% pay raise would mean on the total payroll: SELECT SUM( salary * 1.12 ) "Projected Payroll" FROM INSTRUCTOR. What about only those instructors in Denver (DNV)? SELECT SUM( salary * 1.12) "Denver Projection" FROM INSTRUCTOR WHERE HOME_SITE_CODE='DNV'. By default, all forms except the COUNT(*) will ignore null (missing) values in the column they are operating on. Assume that the instructor table in your sample database has a lot of new rows, and due to some issue with data entry at the time, none of the salaries has been filled in for the new instructors. Over half of the rows in instructor have null in the salary column. If you type SELECT AVG( salary ) FROM INSTRUCTOR, you will only get the average of the rows which have a value in that column. What if you wanted to temporarily substitute a value to more appropriately 'pad' or adjust the salaries for the purpose of doing a better average? You can use the NVL function to provide runtime substitution. Assume that we generally believe that all the new instructors have an average of 50,000. Therefore you can type SELECT AVG(NVL(salary, 50000)) FROM INSTRUCTOR. By default, all aggregate functions use all values in that column (as of course determined by any WHERE clause in the query, which would limit rows based on criteria). Interactive Prompting can get weird While there are ways to prompt from a SQL*Plus session, using ACCEPT and the '&' functionality, this interactivity does not extend to programs such as stored procedures and stored functions. What happens is that as soon as the SQL*Plus runtime finds the '&' character, it stops at that point, gets the output, then 'freezes' that input string as part of the code that is trying to compile, with unhappy results. Can't have 'em in your PL/SQL stored programs unless you build the programs with some tool which doesn't lend significance to that character! Messaging Tips In my opinion, every Oracle developer will need a good understanding of how messaging is done from triggers, functions, procedures, etc., and how one can pick up RETURN'ed and RAISE'd messages. Note that RAISE_APPLICATION_ERROR() will return a number and text which can be picked up on the client end, but that RAISE_APPLICATION_ERROR will force an immediate return from the current program. Use of this function in a trigger will abort the DML of which the trigger is a part by its association with one or more tables effected by DML. Exception handlers, either those pitifully few which are actually mapped to a label by Oracle, or those which you can define using PRAGMA EXCEPTION_INIT(), provide granular, centralized exception handling. Exceptions which are RAISE'd either implicitly by the system or by your use of RAISE are like GOTO's, however. You cannot return from an exception handler to the execution block. Check out how to have exceptions propagate up a chain of calls, if you have a very modular style. Consider a style of programming whereby you use the IN OUT parameter passing style to unofficially RETURN one or more values from a procedure or function, and maybe have your functions always return a boolean TRUE/FALSE, for success/failure flag. Remember that if you are using SQL*Plus, you must SET SERVEROUTPUT ON to see messaging from the server. This is NOT set on by default, unless you create a LOGIN.SQL file to automate such settings. Oracle Forms ease standard CRUD on Oracle tables If you are in need of a quick and effective way to do maintenance of your data tables with a minimum of unnecessary exertion, then get used to the Oracle Form Builder utility which is often part of a standard install of Oracle. WIthin minutes, using a modern GUI and multiple wizards, you can define a data set, link directly to the form builder using that data set, go with the defaults and immediately launch the Forms runtime to use your new CRUD screen, with full fledged, powerful menu generated by default. Without any scripting, you can connect to a schema, run a retrieval, permit scrolling and editing, and support update on the server through automatic menu and keystroke operations! Using Oracle Query Builder to create correct join syntax Please note the usefulness of Query Builder in building join syntax for you. Used with SQL*Plus, you get a lot of ease of use, and a nice synergy between two of the Oracle client tools you probably have available to you. Start Query Builder, then choose the tables you need. If you are not sure which tables are related, pick one or two to start, then select one and choose to Show Related Tables. From there, you can select some or all of the related Tables, and anytime two tables are related through a FOREIGN KEY constraint, a line will be drawn between those Tables, more specifically, from the foreign key column(s) in the 'child' table to the primary key column(s) in the 'parent' table. To build the minimal join syntax, select at least one column from each of the tables you wish to join, so that the line connecting them gets dark. This will build the necessary WHERE clause to match rows in each Table by shared data values. You may also build any sort and search clauses, (i.e. ORDER BY and WHERE ), or just get what you need from Query Builder, then Show SQL, select the syntax you want to copy (highlight it), copy it into the Windows clipboard, then go to SQL*Plus and paste the syntax into the SQL buffer. Press the / character to run the query. Strange 'Input Truncated' error during SQL*PLUS login If you work with a LOGIN.SQL file as a user of SQL*Plus (and you should certainly consider its use, as it can automate a number of customized settings to your SQL*Plus environment, as well as automatically run commands during startup, such as SPOOL) Anyway, if you work with a LOGIN.SQL file, you may experience an 'input truncated at nnn characters' message when you start up. This is essentially an aesthetic problem, as it does not impede the operation of the LOGIN file, but it's a bit annoying, especially if you see it day after day. To handle this problem, within SQL*Plus, type: EDIT LOGIN then go to the end of the last line of the file, and press {Enter} to force a new blank line. Save your changes, and you are good to go. Instead-of Triggers and Updateable Views Views which represent one table and include all mandatory (not null) columns in their SELECT are updateable, if GRANT has been used to make the view's DML available. Those views which represent aggregates cannot be updated because they cannot map a view row to only one row per table; views which are based on joins or other such complex queries are updateable depending on the columns involved. The Oracle 'Instead-of' Trigger can be assigned to the DML of a view, to provide more complex, multi-table update/insert/delete. They are also used for Object views. RAISE, RAISE_APPLICATION_ERROR, and related topics In PL/SQL, you can use the RAISE instruction to make control of processing move to your exception handler, or the exception handler of the owning block, if any. Oracle provides about 2 dozen named exceptions, which can therefore be trapped by using their name in the EXCEPTION area of your program. But what about those hundreds of others which have an Oracle error number, and which might occur, and for which you want dedicated exception handlers? Use the PRAGMA EXCEPTION_INT( ) function to associate a freeform label of your choice for any known Oracle error number. The benefit is you can have more specific responses to possible outcomes. Note however that control never returns to the section of your code which raised the exception. RAISE_APPLICATION_ERROR is intended to send messages back to the client application. RAISE, when followed by an exception name, is like a GOTO, whereas RAISE_APPLICATION_ERROR is like a 'message and return' method. NOTE: The RAISE instruction all by itself, when used within an exception handler, essentially means 're-RAISE the exception which brought processing to this block of code'. Work Tables in Oracle In Microsoft and Sybase SQL Server, a developer can create a temporary uniquely named table (at least it's uniquely named internally) by using CREATE TABLE with a poundsign in front of the name, as #mytemptable, to create a temporary table. While Oracle did not originally support the creation of database tables which the server scratches and uniquely names for you, current versions of the RDBMS permit you to approach this need in two different ways, and one of them will be familiar to Sybase/Microsoft database programmers, as a very handy place to store temporary (intermediate) results of complex processing. You can use a nested table of RECORD types; this provides a memory-based equivalent to a database object, but inherent table-based features such as sorting and WHERE clauses are not supported without procedural code. Starting with Oracle 8i, the CREATE GLOBAL TEMPORARY TABLE command creates a temporary table which can be transaction specific or session specific. For transaction-specific temporary tables, data exists for the duration of the transaction while for session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data. The table definition itself is not temporary! In effect, the 'temporary' monicker refers to the transient nature of the data, although data can be kept or discarded when the transaction ends, depending one what is decided at the time the table is created. For example, appending the phrase: on commit delete rows will cause the data to persist in the global temporary table only as long as the transaction is pending. Correspondingly, on commit preserve rows will retain the data for duration of the user session. (Looks like a future 'tip' in the making!) Creating linked datasets in one Oracle Form A very common relationship in databases is the one-to-many, or parent-to-child relationship. This relationship usually takes the form of existing in multiple related tables, and relies upon foreign keys. To display and support such a relationship in an Oracle Form, do this: 1.) Using the Data and Layout Wizards, create a standard window and canvas for the topmost dataset, i.e., the 'parent' part of the relationship. You can pretty much take the defaults all the way through the Wizard, such as 'Content' Canvas. Usually, the parent row is displayed one per page, so use the Form style. (Save your design after testing it, so that you can use your possible best friend, the 'Revert' operation). 2.) In the Object Navigator, click on the Form module name itself, NOT the current Data Block. Invoke the Data Wizard again, then create the datablock for the next table down in the relationship, i.e., the 'Child'. You should come to an additional page. where you create a relationship. Choose the existing data block as the master block. Keep the new datablock on the same Canvas, when you get to the Layout Wizard, and use a Tabular style format, with multiple records per page. You should choose to show the scroll bar as well. That's it. You have synchronized retrieval, update/delete/insert capability, and more. Incidentally, if you come to a tabbed interface when running the Data Block Wizard to create the secondary block, you are illustrating its re-entrant properties, and you are also in the wrong place. User-friendly names for columns, tables You may be working with a third-party application which is supported by a lot of tables, (typically the case). These tables, for whatever reason, do not have sensible names. Neither do the columns within them. How do you deal with this? You can create synonyms for tables, but that has limited effect. You can always 'hard code' column aliases into queries, but that obviously is not a robust solution. The best way to take care of this issue is to create Views, which can provide column aliases which are built into the definition of the View. From that point, on, if the View is used, the column names (as seen with a DESCribe), would be more natural. You may, as a DBA, limit users, (which can mean developers), to only the views and not the underlying table(s), which really creates a win-win situation. Creating interactive WHERE clauses in Oracle Reports When you build your Data Model with a SQL query by hand, you can specify a where clause which uses an apparent variable name as its comparison value, as: SELECT * FROM INSTRUCTOR WHERE HOME_SITE_CODE = :HomeSiteCode The presence of the ':' character indicates the occurrence of a 'bind parameter', which is loaded at runtime. You may modify the prompting behavior by accessing the Parameter Form menu choice of the Report Editor, then, if necessary, invoking the Parameter Form Builder. Blank lines with Oracle DBMS_OUTPUT package To get a single blank line in your PL/SQL output, use DBMS_OUTPUT.PUT ( CHR(10)); The use of PUT_LINE will automatically append an additional new line command into the buffer, so that you get two lines. Unfortunately, just passing a null or an empty string to PUT_LINE does no good Using the Query/Where Window properly in Query mode When you enter the Query mode, you have several limitations, one of which is the inability to generate a lot of useful searches. You can get this extra dimension by going into query mode, then typing an ampersand character, the '&' character, then executing the query. You come to a separate typing area, where you may type the WHERE clause used to retrieve matching records. This requires some familiarity with the SQL language, which is not a bad career move anyway. Incidentally: Do not type the word WHERE when you enter the search criterion. It's implied. Doing DML in an Oracle program unit One sound way to do DML under program control is to have a function do the work of the DML, and have a program call that function. The function could, for example return boolean for success/fail. In the function execution section, do the DML then return true. Make sure you have an exception handler which as a minimum should say WHEN OTHERS THEN RETURN FALSE. The calling program or function would then test the result coming back from the function, and could decide to do the COMMIT/ROLLBACK. It is recommended to do COMMIT/ROLLBACK decisions at the calling level, rather than having the function do that work. Oracle and Unix / AIX Oracle can use the UTL_FILE package to do direct writes to an operating system file. Additionally, using SQL*Plus, and notably its SPOOL, SET ECHO OFF, SET VERIFY OFF, SET FEEDBACK OFF commands, an output file can be created by any anonymous block, using DBMS_OUTPUT.PUT_LINE(). Once those files exist, they can be processed by Unix utilities such as AWK, which receives and parses an input file into logical records based on a physical delimiter, and has a number of search predicates which can conditionally trigger processing of that text line. Logical fields can also be isolated in each record. Output of awk can be another file. Output files can also be sorted using standard SORT capabilities of the Unix / AIX operating system. Three server options on Oracle's FOREIGN KEY clause The FOREIGN KEY constraint is the most powerful declarative constraint there is, since it invokes so much automatic functionality. It is used to maintain both referential integrity and domain integrity. INSERT or UPDATE statements which try to alter or insert a value in the constrained column which does not match a primary key are rejected. Here's something you may not know about the FOREIGN KEY constraint, however: By default, the minimal syntax for this constraint implies an ON DELETE RESTRICT clause, meaning that not only must the foreign key value match a primary key in the parent table, but also no parent row can be deleted if there are child rows present. Two other possibilities are available as well. ON DELETE SET NULL will actually remove foreign key value when the parent row is deleted, effectively 'orphaning' the child row, if no NOT NULL constraint; ON DELETE CASCADE will delete all child rows as well. How to determine success, error conditions, and rows effected by queries When you use DML queries, you can use two separate areas of the Oracle environment to determine what happened after the query has run. The first area is the pair of built-in functions SQLCODE and SQLERRM; (I prefer to think of them as variables, since there are no parentheses after their names, but whatever). If SQLCODE = 0 after a DML query, then the query ran successfully. (I think 'query' is a misleading name, since we are not asking a question, but giving a command). For example, if I attempt an INSERT, UPDATE or DELETE, and I did not violate any constraints or fire off any triggers which rejected my DML, Then the value of SQLCODE will be 0. If there is an error condition, as determined by the non-zero contents of SQLCODE, check SQLERRM for detail about the nature of the problem. The other area to check is the 'implicit' cursor which is always declared, used, and scratched by Oracle for any DML. Like any Oracle cursor, it has cursor attributes. Look at SQL%ROWCOUNT after DML to see how many rows were affected. Less useful, but available are SQL%FOUND, a boolean value indicating some rows were affected, and SQL%NOTFOUND to see if no rows were affected. HTML in a Pinch I was able to create HTML output by using the UTL_FILE package of Oracle. I combined the use of the package with a cursor based on passed parameters, a really nifty feature of Oracle. The where clause of a cursor definition can be passed a parameter which is declared as part of the signature of the cursor in the DECLARE section. How to have the equivalent of global variables Oracle Packages are containers for procedures, functions, variables and cursors which are naturally related. You can create an easily addressable set of variables by simply specifying a package with variables in the declaration section. Address those variables with package_name.variable_name. (You do not need a BODY if you are only doing variables). How to have more control over object positioning in Oracle Forms If you can't seem to exactly position visual elements where you want, check the 'View' menu dropdown for the 'Snap to Grid' setting. Turn that off to have better control. See also the unit of measurement. Tips on more flexibility in the Oracle Reports Layout Painter 1. Play around with the 'Flex' and 'Confine' choices in the Layout Painter, to permit you to resize and more easily move visual elements of your design. 2. Remember that the 'margin' area, what we would normally call 'header' and 'footer' regions of a page, is an icon choice in the Layout Painter. You can see the current bolierplate text and images in the margins of the pages, with a number of ease of use features. Top Ten and similar listings in Oracle Reports One common type of listing is a 'top ten' , or 'top one hundred', or any such countable list, which has rankings. One way to do this in Oracle Reports, and to have some flexibility for easy modification, is to create a failry generic listing, in tabular style typically, and to sort the data by whichever category (column) is used in the ranking. Therefore, to get 'top ten' salaries in an employee table, or ten biggest sales from a sales table, you would sort by the salary column in the first case, and the sales amount column in the second. Within the Object Navigator, in the layout model, you will see a repeating frame for that record group. It has an arrow inside a dashed rectangle as its symbol. Access its property pallette, and assign a Group Filter, with 'FIRST' and the choice, and the number indicated below that in the property pallette. (You won't see the prompt for the number until you choice FIRST or LAST). Using the Query/Where window in Oracle Forms when you don't know column names When you enter the Query mode you may use 'placeholder' variables in any fields you want to use in a freeform search condition. Instead of typing an ampersand character, you type a colon followed by a letter, as: :P and :W and then executing the query. You come to a separate typing area, where you may type the WHERE clause used to retrieve matching records, intermixing the placeholder names for whatever the actual database column names are. This tip may be necessary if you are working with Oracle Forms which do not have the default field names/column names next to the data entry areas. Incidentally: Do not type the word WHERE when you enter the search criterion. It's implied. User defined functions in Oracle Unlike its RDBMS competitors, Oracle lets you create functions which are available orthogonally throughout SQL queries, and are callable available from triggers, procedures and other user-defined functions. The values generated from these UDF's can be also used in WHERE, ORDER BY and GROUP BY clauses. Here is a discussion and example. Assume a business rule based on column values which returns a 'go/no go' flag for discounts for customers. SQL> desc customers Name Null? Type ------------------------------- -------- ---- CUST_NO NOT NULL NUMBER(15) FIRST_NAME VARCHAR2(60) MIDDLE_NAME VARCHAR2(60) LAST_NAME VARCHAR2(60) NUM_OF_DEPENDENTS NUMBER(3) ACCOUNT_NO VARCHAR2(20) MEMBER_NO VARCHAR2(30) MEMBER_SINCE DATE Now we create a simple UDF: 1 CREATE OR REPLACE FUNCTION DISCOUNT_ELIGIBLE (P_START DATE, P_MEM_NO VARCHAR2) 2 RETURN VARCHAR2 3 IS 4 BEGIN 5 IF SYSDATE - P_START> 365 AND P_MEM_NO LIKE 'A%' THEN 6 -- Customer has been with us over a year and has appropriate member number pattern 7 RETURN 'Y' ; 8 ELSE 9 RETURN 'N' ; 10 END IF; 11* END; Here's a sample sentence; notice that this is a scalar function, like most Oracle built-ins, and that you can pass columns to the UDF: 1 SELECT * FROM CUSTOMERS 2* WHERE DISCOUNT_ELIGIBLE( MEMBER_SINCE, MEMBER_NO ) = 'Y' The user-defined function is one of the hidden treasures of Oracle, IMHO. Coexistence of Scalar and Aggregate data in a query Selects of column-level expressions cannot normally be mixed with grouplevel, 'aggregate' expressions, such as those obtained with SUM(), AVG(), COUNT(), and other group functions. There are two ways of doing this in Oracle: to do the GROUP BY approach, which requires that every column which is not an aggregate be in the GROUP BY clause, or create a user-defined function to do the aggregation, and return it row-by-row. This second approach permits such reporting features as percentage of the part to the whole. Another way to mix single-valued and aggregate data in one line in a query SQL> CREATE OR REPLACE FUNCTION HS_COUNT ( SITE_CODE CHAR ) 2 RETURN NUMBER 3 IS 4 SiteCount NUMBER(9) ; 5 -- Local variables get declared between AS and BEGIN 6 BEGIN 7 SELECT COUNT(*) INTO SiteCount 8 FROM instructor 9 WHERE home_site_code = SITE_CODE; 10 RETURN SiteCount; -- Here’s the handoff to the next higher level from function 11 END; Function created. SQL> SELECT LAST_NAME, FIRST_NAME, HS_COUNT('SEA'), HS_COUNT('DNV') 2 FROM INSTRUCTOR; LAST_NAME FIRST_NAME HS_COUNT('SEA') HS_COUNT('DNV') -------------------- -------------------- --------------- --------------- FLINTSTONE DINO 7 4 FLINTSTONE FRED 7 4 RUBBLE BARNEY 7 4 JETSON JUDY 7 4 JETSON GEORGE 7 4 . . . 23 rows selected. As we have seen, there are restrictions on how you can have scalar (‘normal’ atomic ) data and group-level, ‘aggregate’ data expressions in the same query. This is a very useful, Oracle-specific way to do a lot of things. The solution to this situation is a user-defined function. Once this is created, it can be used ‘in line’ anywhere any function can be used, and can be used in Oracle Reports, which supports custom code exits to extend standard SQL capabilities. The example used is actually silly, but it is intended to prove a point about the general usefulness of user-defined functions in mixing the two types of data. REF Cursors Cursors which use the REF CURSOR syntax and the related OPEN cursor_name FOR some_select permit you to share a cursor among programs. THis is available starting with PL/SQL Release 2.3. A cursor variable which uses this new functionality can be opened for any query. Since the cursor (or rather, the results of the Fetch) can be passed between programs, a client-side block can open and fetch from the cursor var, then pass that variable to a stored procedure or function on the RDBMS. This stored program could use syntax such as UPDATE or DELETE WHERE CURRENT OF that cursor, for reuse. Nice possibilities, once you get the full picture. Business Rules and Business Calculations: Who, What, Where A Business Rule is part of the mission of an organization, part of its competitive advantage, part of its personality. Business rules reflect the way an organization does business. Business rules change. Subject Matter Experts (SME's) who may or may not be technical, work with DBA's and developers to install business rules in the applications and databases which are used in that organization. For example, a Business Analyst might work with contract developers and project managers to meld the strategic and competitive goals of a corporation to the global market. Part of the interface would be installation of discrete business rules on coluns of a database, when possible. Such Business Rules, in the Oracle universe, can be stored and enforced in a number of places, in a number of ways. Business calculations are like business rules applied to output, and these calculations can also be done in a number of ways. The DBA and/or developers (depending on rigor of the environment) can/would install such simple rules on the table via Data Definition Language (DDL).
Note: One issue you must ALWAYS be aware of is whether you have up-to-date DDL listings. There is no record kept on the server of when/whether the DDL in a file has ever been run, so the presence of valid DDL in a file somewhere should not provide ANY comfort level automatically. Of course, you can eyeball the state of the schema vs. the statements in the DDL script, one at a time, with some interpretation, and thereby validate, but it's not always simple. The same holds true for Entity-Relationship diagrams. They can be compelling, very detailed, and logically/semantically complete, but that does not mean they are consonant with the DDL in the schema at present. After the Declarative methods, we come to programmed (procedural) methods, which, being programs, are susceptible to human error, sometimes subtle.
Summary:
DDL and DML Coexistence Certain SQL query 'verbs' are used for creating objects in your schema, such as Tables, Views, Sequences, Indexes, Procedures, and others. These fall into the category of Data Definition Language, or DDL. Contrast this with Data Manipulation Language, (DML), the normal province of developers and report writers. Whenever you run a DDL query, the changes, (if syntactically correct) are immediately applied. You cannot 'undo' DDL with the ROLLBACK command. DDL in Oracle always causes a COMMIT. Notice the following sequence of commands, both DML and DDL, and notice how the 'FEES' table continues to exist and the INSERT has been committed, even though I've done a ROLLBACK: SQL> INSERT INTO STUDENT VALUES ( 101, 'Joe', 'Schmidt', 'Alliance', NULL ); 1 row created. SQL> CREATE TABLE FEES ( FEE_CODE CHAR(2) PRIMARY KEY, FEE NUMBER(9,2) NOT NULL ); Table created. SQL> ROLLBACK; Rollback complete. SQL> DESC FEES Name Null? Type ----------------------------------------- -------- --------------------- FEE_CODE NOT NULL CHAR(2) FEE NOT NULL NUMBER(9,2) SQL> SELECT COUNT(*) FROM STUDENT WHERE STUDENT_SID = 101; COUNT(*) --------------- 1 I guess one principle I'd like to suggest is that you do not mix your DDL and DML. Providing permanent, sensible names for columns and tables This is a discussion of how to make sensible alternative names for what can be cryptic table and column names. You may know of the (limited) usefulness of column aliases, which is part of the syntax of the SELECT clause. The column alias is indicated by a string which follows the column name, or expression which refers to one or more columns. This is limited because you are only getting the effect for the duration of that query, but that may be sufficient. (Remember how you can store queries of any kind in a 'start' file, an ASCII file which can be loaded and run either ad hoc during your SQL*Plus session, or 'fed' to a session from a Unix/AIX/Linux/DOS command line using the '@' sign). If (and only if) you use SQL*Plus, the COLumn command can be used to supply custom column headings, and provide some output formatting, when you do SQL*Plus reports. Again, however, the benefit of the column heading/alias is limited to that session, possibly that particular report. Another item to mention in this discussion is the Oracle 'synonym' feature, which lets you create permanent nicknames for existing tables. Such definitions are stored in your schema, and are part of the Data Dictionary, but they only apply on the table level. Finally, for 'permanent' aliases, give strong consideration to creating views. A view is good for other reasons, but it can certainly be used to provide a set of synonyms for both columns and tables. For example, this view will create aliases on both levels, and also illustrate how an apparent 'column' in a view can be composed of pieces: CREATE VIEW TEACHERS ( name, salary, hired ) AS SELECT Substr( first_name, 1, 1) || '. ' || last_name, salary,hire_date) FROM instructor; This approach relies upon the optional 'column list' syntax of creating a view, which provides the column alias component. Of course, the view name is the table alias. Oracle Data Type Notes The CHARacter (CHAR) datatype, unlike its counterparts in other RDBMS products, was not always fixed length. In earlier releases, it meant the same as VARCHAR, and its preferred equivalent, VARCHAR2. You should not use VARCHAR for new development; its use is deprecated. The Cursor FOR Loop Oracle's PL/SQL lets you loop through cursors a number of ways. Perhaps the most standard approach, which Sybase/Microsoft folks would recognize, is to use structured loops with WHILE and EXIT clauses, where you are responsible for a formal OPEN, CLOSE, FETCH, with their respective syntax requirements. With Oracle, however, the one to consider is the elegant cursor FOR loop, which eliminates the need to do a formal OPEN, FETCH or CLOSE, and automatically loads an entire row at a time into the variable named after keyword FOR, as: FOR my_cursor_rec IN my_cursor LOOP -- process individual columns in the SELECT of the cursor END LOOP; In the example above, the variable my_cursor_rec represents all columns in the SELECT which is defined for the cursor (not shown in this example, but DECLARE'ed like other cursors), with the datatype anchored implicity to the ROWTYPE of the cursor. Using dot notation, you can isolate any column in the cursor variable; in fact, the only addressable data elements are the pieces of this PL.SQL 'record'. Maybe an even more 'implicit' and simpler version of the cursor FOR loop is shown in cases where the declaration of a cursor is skipped, and the SELECT is done entirely within the FOR loop, as: FOR my_cursor_rec IN (SELECT .... ) LOOP -- process as with other example, using 'dot notation' END LOOP; DDL in the context of Transactions The standard behavior of Oracle as regards transactions is faithful to the ANSI standard, namely that every DML (Insert/Update/Delete) action occurs within the context of a transaction. In Oracle, any of those statements will cause a 'flag' to be set on your connection to the Oracle Server, whereby any DML you issue is in a tentative state until you issue a COMMIT statement. This releases locks and writes changes out of a server-resident cache to permanent storage. ROLLBACK permits a multi-level 'Undo' behavior. A transaction is that which occurs between COMMIT/ROLLBACK statements, whenever DML is issued, so group logically related DML into one transaction and COMMIT/ROLLBACK as soon as possible. The ANSI-compliant way that Oracle handles transactions has additional capabilities. If ANY DDL is issued within a transaction, an immediate, automatic COMMIT is issued by the Oracle client-side libraries. Similarly, if you quit out of SQL*Plus, an automatic COMMIT is issed for you! Starting with PL/SQL 8.1, programmers will be pleased to hear of the Oracle solution to a common Oracle problem: One recent addition to the PRAGMA instruction set lets you define an 'autonomous' transaction, for use in triggers, any procedure which needs to write log / trace information, or any process which needs to create new objects in the schema. Using DECODE for counting conditionally The DECODE functio is the 'raw material' for a cross-tabulation (two-dimensional) report.Notice how, in this listing of a course_training_site table,the 1 and 0 are generated, and how they can be taken to be a YES/NO type of 'vote' for whether a particular course/site combination is in a particular site. SELECT site_code, course_code, decode(site_code,'DNV',1,0) "Denver", decode(site_code, 'PTL', 1, 0 ) "Portland", decode(site_code, 'SEA', 1, 0 ) "Seattle", SIT COURSE Denver Portland Seattle DC 93 rows selected. If I add up all the 1's (and ignore the zeroes), then I can get a count display which runs horizontally, rather than vertically. SQL> 1 select substr(course_code,1,1), S Denver Portland Seattle 2 rows selected. I do have to 'manually' build the buckets which go across, but the use of SUM and GROUP BY give me some value in the other dimension. select decode(substr(course_code,1,1), CURRICULUM Denver Portland Seattle 2 rows selected. Passing multiple parameters to an Oracle Procedure or FunctionDespite the apparent limitation of only one returnable result from functions, and the apparent inability to return anything from procedures, you can get the best of both worlds by using the IN OUT parameter passing style. Consider this simple example of a procedure: create or replace procedure sp_test (p1 number, p2 IN OUT number, p3 OUT number ) as begin //* I want to be able to pass multiple values to a program AND have those values changed and pass back out the other end. A function is limited to returning only one value at a time. This style of programming permits multiple 'return' values, but there is no formal 'RETURN' statement */ p3 := p1 + p2 + 100; p2 := p2 + 5; end; Usage: DECLARE The value of p2, if changed during the execution of the stored procedure, is 'changed in place', in the sense that we are 'passing by reference', or in other words, with syntax, we are sharing a place in memory between this procedure and any program or block which calls it. If you do not change p2 inside the procedure, it passes through unchanged, but is addressable in the calling script. The value of p1 is simply passed in (the default), so no specific additional syntax is needed, but the word IN could have been placed in the signature. The value of p3, unlike p2, can only be assigned a value which matters within the program. For this type of program to work, I need to have variables which match with every OUT or IN OUT parameters, or else I will never be able to 'see' what's being passed out. Now notice how this minor change to the program causes a problem, with a very understandable error message: The first query is satisfied by just going into the entire table, and getting the MAX(SALARY), then using that as the comparison value in the outer query, as: SELECT * The second solution needs an extra syntax 'guide', which uses the TABLE ALIAS. We will see this used in Equijoins for convenience and as a required part of SelfJoins. The correlated subquery also requires this syntax feature. Notice the use of the alias FAMILY in the WHERE clause and the subquery. This is all that is needed to do the correlation of the inner and outer queries: SQL> select last_name, department, salary LAST_NAME DEPARTMENT SALARY 4 rows selected. CREATE TABLE error_list ( e_id CHAR(3) primary key ); create or replace procedure sp_migrate as begin Procedure created. create or replace function f_load ( p_id instructor.instructor_sid%TYPE, p_name instructor.last_name%TYPE ) 2 RETURN BOOLEAN Function created. SQL> desc target_table I_ID NOT NULL NUMBER(3) SQL> truncate table target_table Table truncated. SQL> -- That's because I had already run this set of programs. SQL> exec sp_migrate; Successfully processed 12 . . . ETC. Successfully processed 620 PL/SQL procedure successfully completed. The branching logic should be evident from the sp_migrate example, as well as the 'well behaved' nature of the function relative to the larger process. The trick as I see it is to understand that an exception will exit the current program, so therefore do things which might generate exceptions inside of a program called from another program. User defined functions |
|||||||||||||||||||||||||||||||||||||||||||||
