Home » RDBMS Server » Server Administration » Regarding DBMS_SQL
| Regarding DBMS_SQL [message #374136] |
Mon, 28 May 2001 00:01  |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hai friends,
I have a doubt regarding DBMS_SQL.
As we know that oracle uses early binding in the PL/SQL.
Hence we are not able to use the DDL Statements in the pl/sql.
To overcome this problem,we are using the DBMS_SQL package.
I know that we have to use the DBMS_SQL package or Native SQL (in oracle 8i). But can anyone explain me
how this DBMS_SQL or Dynamic sql works?
TO make my question clear..
Suppose if i have a create table command in a pl/sql block,
early binding checks for all the objects and privileges and
it finds that my table doesnot exists.Hence this pl/sql
will not run.Until unless this pl/sql is run,my table will
not be created.
But we are able to achieve this thru DBMS_SQL or Native dynamic
sql.I would like to know how we are able to overcome this
situation.
Thanks in Advance.
Regards,
Giridhar Kodakalla
|
|
|
|
| Re: Regarding DBMS_SQL [message #374138 is a reply to message #374136] |
Mon, 28 May 2001 02:31  |
Sarada
Messages: 27
Registered: April 2001
|
Junior Member |
|
|
SQL
Discovering the Wonders of Native Dynamic SQL
By Steven Feuerstein
With Oracle8i, a reimplemented version of PL/SQL makes dynamic SQL much faster and easier to write—you may never go static again.
Ever since Oracle7 Release 7.1 and PL/SQL Release 2.1, PL/SQL developers have been able to use the built-in DBMS_SQL package to execute dynamic SQL and PL/SQL. This means, for example, that at runtime you can construct a query, a DELETE statement, a CREATE TABLE statement, or even a PL/SQL block as a string—and then execute it. Dynamic SQL comes in extremely handy when you are building ad hoc query systems, when you need to execute DDL (Data Definition Language) inside PL/SQL, and when you don't know in advance exactly what you need to do or what the user will want to do. Because it solves these problems, dynamic SQL is a frequent requirement for designing Web-based applications.
But as of Oracle8i Release 2 and PL/SQL Release 8.1.6, there are still some limitations associated with DBMS_SQL:
It's a very complicated package.
It has a number of restrictions (such as not recognizing new Oracle8 datatypes).
It's relatively slow.
However, with Oracle8i Release 2 comes native dynamic SQL (NDS), a dynamic SQL facility implemented directly in the PL/SQL language.
This article is the first of a three-part series on NDS. First, I introduce you to NDS by comparing it with DBMS_SQL and explaining how to use the EXECUTE IMMEDIATE statement to handle many of your dynamic needs. Next time, I'll show you how to handle multiple-row queries with NDS. In the third article, we'll explore the nifty utilities you can build using NDS.
NDS is faster and easier to use than DBMS_SQL. It makes dynamic SQL so easy to write that you will use it when you could have used static SQL. It's time to go dynamic!
DBMS_SQL Versus NDS
First let's compare the DBMS_SQL and NDS implementations of a program that displays all the employees for the specified and very dynamic WHERE clause (see Listing 1).
For a more detailed discussion of the differences in implementation between DBMS_SQL and native dynamic SQL, see the "Oracle8i Application Developer's Guide—Fundamentals," available in the Documentation section of Oracle Technology Network (http://technet.oracle.com/docs/
products/oracle8i/doc_index.htm).
As you can see in Listing 1, you can write dramatically less code using NDS. And since the code you write relies less on built-in packaged programs and more on native, standard elements of PL/SQL, that code is easier to build, read, and maintain.
Given the benefits of using NDS, why would anyone use DBMS_SQL ever again? The answer is because NDS cannot handle everything you might want it to do. The following lists show the operations that each of these dynamic SQL implementations can perform exclusively:
Capabilities exclusive to DBMS_SQL:
Supports Method 4 dynamic SQL, which means that at compile time, you don't know how many columns you will be querying and/or how many bind variables will need to be set. Method 4 is the most complex form of dynamic SQL, and NDS doesn't support it (except under certain restricted circumstances).
As of Oracle8, lets you describe the columns of your dynamic cursor, obtaining column information in an index-by table of records.
Supports SQL statements that are more than 32KB in length.
Supports the use of the RETURNING clause for an array of values; NDS, in contrast, allows the use of RETURNING for only a single statement.
Allows you to reuse your dynamic SQL cursors, which can improve performance.
Can be executed from client-side (Oracle Developer) applications.
Capabilities exclusive to NDS:
Works with all SQL datatypes, including user-defined objects and collection types (variable arrays, nested tables, and index-by tables). DBMS_SQL, in contrast, works only with Oracle7-compatible datatypes.
Allows you to fetch multiple columns of information directly into a PL/SQL record. With DBMS_SQL, you must fetch into individual variables.
What can we conclude from these two lists? The NDS implementation can handle 80 to 90 percent of the dynamic SQL requirements most developers are likely to face, but there's still a place for DBMS_SQL.
One of the nicest things about NDS is its simplicity. Unlike DBMS_SQL, which has dozens of programs and lots of rules to follow, NDS has been integrated into the PL/SQL language by adding one new statement, EXECUTE IMMEDIATE, and by enhancing the existing OPEN FOR statement. EXECUTE IMMEDIATE executes a specified SQL statement immediately, and OPEN FOR allows you to perform multiple-row dynamic queries.
EXECUTE IMMEDIATE for Most Needs
Let's take a closer look at the EXECUTE IMMEDIATE statement. Use EXECUTE IMMEDIATE to execute the specified SQL statement. You can EXECUTE IMMEDIATE for any SQL statement or PL/SQL block, except for multiple-row queries. Listing 2 shows a useful procedure that creates an index on any table and list of columns.
As you see from the exercise in Listing 2, much of the complexity of working with dynamic SQL generally and NDS in particular has nothing to do with the statements you call. It has more to do with the complications of properly concatenating chunks of text to make a valid SQL statement.
To read Steven Feuerstein's in-depth chapter on DBMS_SQL, get the book Oracle Built-in Packages, by Steven Feuerstein, Charles Dye, and John Beresniewicz (O'Reilly & Associates, 1998; ISBN: 1-56592-375-8).
You should be aware of the following nuances of the EXECUTE IMMEDIATE statement: If SQL_string ends with a semicolon, it will be treated as a PL/SQL block; otherwise, it will be treated as either DML (Data Manipulation Language—SELECT, INSERT, UPDATE, or DELETE) or DDL (Data Definition Language, such as CREATE TABLE). The string may contain placeholders for bind arguments, but you cannot use bind values to pass in the names of schema objects, such as table names or column names.
When the statement is executed, the runtime engine replaces each placeholder (an identifier with a colon in front of it, such as :salary_value) in the SQL string with its corresponding bind argument (by position). You can pass numeric, date, and string expressions. You cannot, however, pass a Boolean, because it is a PL/SQL datatype. Nor can you pass a NULL literal value. Instead, you must pass a variable of the correct type that has a value of NULL.
NDS supports all SQL datatypes available in Oracle8i. So, for example, define variables and bind arguments can be collections, LOB (large object) types, instances of an object type, and REFs (references). On the other hand, NDS does not support datatypes unique to PL/SQL, such as Booleans, index-by tables, and user-defined record types. The INTO clause may, however, contain a PL/SQL record.
Single-Row Queries
The best way to learn about all that EXECUTE IMMEDIATE can do is through examples. We've seen the tremendous ease of executing a DDL statement such as CREATE INDEX (and don't forget that when you execute a DDL statement you also perform a COMMIT). But how well does EXECUTE IMMEDIATE handle single-row queries? Let's test it with a generic function to compute the number of rows in a table (see Listing 3). As you can see, my productivity is greatly improved because now I never again have to write SELECT COUNT(*), whether in SQL*Plus or within a PL/SQL program, as in the following:
BEGIN IF tabCount ('emp', 'deptno = ' || v_dept) >
100
THEN
DBMS_OUTPUT.PUT_LINE ('Growing fast!');
END IF;
Notice in Listing 3 that I used the INTO clause of the EXECUTE IMMEDIATE statement to retrieve the value from the query.
Dynamic Updates with a Bind Variable
How about a function that lets you update the value of any numeric column in any table? (It's a function because it returns the number of rows that have been updated.) Let's take a look at Listing 4.
The code contained in Listing 4 is a very small amount, considering all the flexibility it achieves. In this case, I take advantage of a "bind variable." The string :the_value is a placeholder for a value. The USING clause specifies the comma-delimited list of values that replace each placeholder after parsing. The list may contain any combination of variables, constants, literal values, or expressions.
I could have used concatenation to blend the value into the UPDATE statement, but you should always bind if you can, for two important reasons:
You don't have to worry about datatype conversions.
Binding results in a single SQL cursor no matter what the value is, avoiding unnecessary reparsing.
Notice also that I am able to rely on the SQL%ROWCOUNT cursor attribute to determine how many rows I've updated. That's the same attribute I've been using for years with static cursors, so it is very comfortable code to write.
Dynamic PL/SQL Code Execution
Suppose that I need to run a different stored procedure at 9 a.m. each day of the week. Each program's name has this structure: DAYNAME_set_schedule. Each procedure has the same four arguments: you pass in employee_id and hour for the first meeting of the day; it returns thþ7
Before NDS became available, I was building code-generation tools (including RevealNet's PL/Generator) that relied heavily on dynamic PL/SQL block creation and execution. The result was a flexible but somewhat unwieldy tool.
With the advent of native dynamic SQL, I get the same flexibility but I don't have to work nearly as hard. By lowering the threshold for writing dynamic SQL code in PL/SQL, Oracle has succeeded in expanding the range of functionality we can build—and build with ease.
Steven Feuerstein is a leading expert on the Oracle PL/SQL language. He is the author of five books on PL/SQL: Oracle PL/SQL Programming, Advanced Oracle PL/SQL Programming with Packages, Oracle Built-in Packages, Oracle PL/SQL Programming Guide to Oracle8i Features, and Oracle PL/SQL Developer's Workbook (all published by O'Reilly & Associates). Feuerstein also builds PL/SQL developer utilities for RevealNet, Inc. (www.revealnet.com).
SQL*Plus Version Gotcha
When working in SQL*Plus 3.x, you will see a strange result if you describe a table that contains LOBs and interMedia types. For those columns, you will see the definition UNDEFINED where the type should be. For example, a DESCRIBE of the recipes table will show the following:
SQL> DESC recipes
Name (s) Null? Type
__________________________ __________ _______________
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(100)
PREP_TIME_MINUTES NUMBER NUMBER
DESCRIPTION VARCHAR2(1000)
COOKING_INSTRUCTIONS UNDEFINED
DISH_IMAGE UNDEFINED
If you receive this response, switch to a newer version of SQL*Plus and try the DESCRIBE command again.
SQL
A Dynamic Approach to Multirow Queries
By Steven Feuerstein
Use the extended OPEN FOR syntax in Oracle8i's PL/SQL to quickly create dynamically constructed SQL queries that return multirow result sets.
In the last issue of Oracle Magazine, I introduced an exciting enhancement to the Oracle PL/SQL language in Oracle8i: native dynamic SQL (NDS). NDS is an implementation of dynamic SQL (SQL and PL/SQL statements constructed and executed dynamically, at runtime) that is built directly into the PL/SQL language. Prior to NDS, dynamic SQL was available only with DBMS_SQL—a complicated, relatively slow, and Oracle7-centric package.
Part 1 of this series, "Discovering the Wonders of Native Dynamic SQL," appeared in the November/December issue of Oracle Magazine.
In this issue, we'll focus on one of the more complex areas within NDS: multirow queries (a multirow query being, of course, a SELECT statement that identifies more than one row in its result set). Processing is more complicated with such queries than with other dynamic operations, since you must either process each row individually or take advantage of collections to retrieve multiple rows at once.
Fortunately, the learning curve for such queries isn't steep, since the syntax is basically an extension of a previously existing PL/SQL statement: OPEN FOR. We'll start our exploration of multirow NDS queries by looking at the changes in the OPEN FOR syntax, and then build up to an example of a useful generalized utility that demonstrates the power of NDS.
An Evolutionary Approach
As with many of the new PL/SQL features in Oracle8i, NDS adds significant new capabilities to PL/SQL with a minimum of new syntax—and therefore, a minimal learning curve. As we saw in Part 1 of this series, the EXECUTE IMMEDIATE statement lets you execute DDL statements, inserts, updates, and deletes—as well as dynamic PL/SQL blocks—all in one line of code. For multiple-row queries, the PL/SQL designers took a look at existing constructs in PL/SQL and realized they could use cursor variables to get the job done.
Rather than introduce new syntax, the PL/SQL designers simply extended the OPEN FOR statement to allow for dynamically constructed SQL strings. In other words, you're no longer limited to using a cursor variable with hard-coded queries, such as:
TYPE name_num_rc IS REF CURSOR;
cv name_num_rc;
BEGIN
IF type_in = 'BYDEPT'
THEN
OPEN cv FOR
SELECT ename, deptno FROM emp ORDER BY deptno;
ELSIF type_in = 'BYSAL'
THEN
OPEN cv FOR
SELECT ename, sal FROM emp ORDER BY sal;
Now, you can now use the same type of structure with variable strings, like so:
TYPE generic_rc IS REF CURSOR;
cv generic_rc;
BEGIN
OPEN cv FOR
'SELECT '|| column_list ||
'FROM '|| table_list ||
'WHERE '|| where_clause ;
And that modified OPEN FOR syntax was the only PL/SQL change needed to allow for NDS support of multirow queries.
Electing to Use OPEN FOR
Let's take a closer look at OPEN FOR and multirow queries with a concrete example. With recent elections in the U.S. and other countries, let's consider the challenge faced by Jan Voter. Before deciding who gets her vote, Jan likes to find out everything she can about the candidates— particularly with respect to the campaign contributions they've received.
For each candidate, Jan decides to check out the top 15 contributors who have donated at least US$2,500. Drawing on data from a handy site for U.S. voters (the Center for Responsive Politics, at www.crp.org), Jan has built three different tables to store this information: bush_funds, gore_funds, and nader_funds. Each table has this structure:
CREATE TABLE <name>_funds (
amount NUMBER,
source VARCHAR2(200),
);
She wants to build a single program to display the contributions for a given candidate, so she needs to use dynamic SQL. Let's walk through the steps needed to build this program (the complete procedure is shown in Listing 1).
The first step in a dynamic multirow query is to define the cursor variable that will point to the result set in the System Global Area (see lines 4 through 6 in Listing 1). We declare the cursor variable as an instance of a REF CURSOR type, so we must consider the question of whether to make it strong or weak. A REF CURSOR is strong if it is strongly typed, which means the TYPE statement includes a RETURN clause defining what structure is allowable (number of items and datatypes) for data fetched through a cursor variable based on this TYPE. A strong REF CURSOR looks like this:
DECLARE
TYPE only_order_data_rc IS REF CURSOR
RETURN orders%ROWTYPE;
I generally recommend that developers use strong REF CURSORs when using cursor variables with static SQL, because doing so makes the code easier to debug and maintain.
With dynamic SQL, however, you are not allowed to use strong REF CURSORs. At compile time, the query associated with the REF CURSOR is just a string, so the PL/SQL engine cannot validate that the query's SELECT list is consistent with the record structure in the REF CURSOR's RETURN clause. If I try to strongly type a REF CURSOR with dynamic SQL, I will get this compile error:
PLS-00455: cursor 'MONEY_CV' cannot be used in
dynamic SQL OPEN statement
So, I need to take an alternate approach. I define a weak REF CURSOR and then declare a cursor variable based on that structure, as follows:
4 TYPE refCur IS REF CURSOR;
5 money_cv refCur;
Once that is done, I declare a record that I can use to retrieve individual rows from any of the three tables (all three tables have the same structure, so I can %ROWTYPE the record against any of the tables; in this case, I used bush_funds):
6 money_rec bush_funds%ROWTYPE;
Now I am ready to open my dynamic query:
8 OPEN money_cv FOR
9 'SELECT amount, source ' ||
10 'FROM ' ||candidate_in ||
'_funds ' ||
'ORDER BY amount DESC';
I must use dynamic SQL, since I am constructing the name of the table each time I run the program. But dynamic SQL doesn't require any more in the way of special code than what I've just described. I can use the familiar explicit-cursor syntax unchanged for my cursor-variable-based code, as the following lines demonstrate:
13 LOOP
14 FETCH money_cv INTO money_rec;
15 EXIT WHEN money_cv%NOTFOUND;
16
17 DBMS_OUTPUT.put_line (
18 money_rec.source || ': $' ||
19 TO_CHAR (money_rec.amount)
20 );
END LOOP;
With this program in place, Jan Voter can now request three reports, as shown here (for the full report output, see Listing 2):
SQL> exec show_me_the_money ('gore')
Ernst and Young International: $127125
Citigroup: $98750
SQL> exec show_me_the_money ('bush')
MBNA America Bank: $213400
Vinson and Elkins: $196350
...
SQL> exec show_me_the_money ('nader')
Jack H Olender and Assoc: $4000
Kayline Enterprises: $4000
...
Jan can now decide who gets her vote—and perhaps her campaign contribution.
Building Generalized Utilities
We have seen how you can write very application-specific programs by using NDS. One of the more satisfying aspects of dynamic SQL is that you can use it to build utilities that can be applied in many different applications. Now let's construct such a utility—one that also involves a multirow query—with NDS.
How many times have you written a query along these lines:
SELECT some_columns, COUNT(*)
FROM your_table
GROUP BY some_columns;
A specific application of this query might be "How many books by category have the word 'Oracle' in them?":
SELECT category, COUNT(*)
FROM books
WHERE title LIKE '%ORACLE%'
GROUP BY category
And then there's the variation involving the HAVING clause, as in:
SELECT some_columns, COUNT(*)
FROM your_table
GROUP BY some_columns
HAVING COUNT(*) > N;
In this variation, you don't want to see all the counts; you just want to see those groupings where there are more than N identical values in the specified column(s). This sort of query is often used to identify duplicate identical rows (not a situation you'd think would occur in relational tables, but it does happen).
These are very common requirements, and with NDS, you can build a single program (we' = b.tablespace_name group by a.file_name,b.file_id,b.tablespace_name,a.bytes Can you modify my script to be shown as OEM display? The query you seem to want would be: select a.file_name, a.file_id, a.tablespace_name, a.bytes allocated, nvl(b.free,0) free, a.bytes-nvl(b.free,0) used from dba_data_files a, ( select file_id, sum(bytes) free from dba_free_space group by file_id ) b where a.file_id = b.file_id (+) / Using an inline view like that, especially when one tabition for the WHERE clause, if desired
Given those requirements, here is the header for my countBy procedure:
/* file countby.sp */
PROCEDURE countBy (
tab IN VARCHAR2,
col IN VARCHAR2,
atleast IN INTEGER := NULL,
sch IN VARCHAR2 := NULL,
whr IN VARCHAR2 := NULL
You'll find more information on using Native Dynamic SQL in the Documentation section of Oracle Technology Network (http://otn.oracle.com/docs/).
The examples in Listing 3 show what kind of output the finished program should provide. They show two counts of employees by department (one without an "at least" clause).
Now, let's look at how to construct this handy program. We'll start with the executable section, then look at the main chunks of NDS-related code, and finish up with some nested procedures to format the output nicely (for a listing of the entire procedure, see Listing 4).
The first task is to construct the SQL query string. Since we know little in advance about the table and conditions for each query, the string is composed mostly of concatenations of the parameter values:
BEGIN
SQL_string :=
'SELECT ' || col || ', COUNT(*)
FROM ' || NVL (sch, USER) || '.' ||
tab ||
' WHERE ' || NVL (whr, '1 = 1') ||
'GROUP BY ' || col;
That's the core query. However, if the user has supplied a non-NULL value for the "at least" parameter, we'll need to append a HAVING clause:
IF atleast IS NOT NULL
THEN
SQL_string :=
SQL_string ||
' HAVING COUNT(*) >= ' ||
atleast;
END IF;
With all the pieces of the SQL string in place, we can now pass it on to the NDS OPEN FOR statement:
OPEN cv FOR SQL_String;
Then, for each row we fetch, we use DBMS_OUTPUT to display the information on the screen. Note that if we just queried the first row (cv%ROWCOUNT = 1), we take a brief detour to display the header:
LOOP
FETCH cv INTO v_val, v_count;
EXIT WHEN cv%NOTFOUND;
IF cv%ROWCOUNT = 1
THEN
display_header;
END IF;
DBMS_OUTPUT.PUT_LINE (
RPAD (v_val, column_length) || ' ' || v_count);
END LOOP;
And what, you may ask, are display _header and column_length? These are both local procedures that we define in the declaration section of the countBy procedure. We could have put all of the code for these procedures directly in the countBy execution section, but the result would be harder to read and maintain. Local (or, as they are sometimes called, nested) procedures allow us to keep our executable sections small and easy to follow.
As Listing 5 shows, the display _header procedure is pretty simple. We build border lines with the RPAD function and construct the header string itself much as we did the SQL string: first the core part of the header, and then the extra descriptive information necessary if an "at least" value is provided.
The column_length procedure is a bit more interesting; rather than hard-code information about column lengths in the program, we grab the needed information from the data dictionary:
FUNCTION column_length RETURN INTEGER
IS
retval INTEGER;
BEGIN
SELECT data_length INTO retval
FROM ALL_TAB_COLUMNS
WHERE OWNER = NVL (UPPER (sch), USER)
AND TABLE_NAME = UPPER (tab)
AND COLUMN_NAME = UPPER (col);
RETURN retval;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
This procedure retrieves the maximum length of the values found in the column. By extracting the data length from the data dictionary, I can present the requested data in the most readable manner possible.
Exploring Dynamic Potential
As you start building more of these generic utilities, you'll find they don't take much code or effort—you simply need to think through the steps of the SQL-string construction carefully. And the more NDS utilities you build, the more you'll realize how handy they are. Once you incorporate the advanced techniques we'll explore in the third article in this series—including using BULK COLLECT in dynamic SQL, executing dynamic PL/SQL blocks, and working with objects and collections—you'll have what you need to meet any challenge the dynamic way.
Steven Feuerstein (steven@stevenfeuerstein.com) is a leading expert on the Oracle PL/SQL language. He is the author of five books on PL/SQL—Oracle PL/SQL Programming, Advanced Oracle PL/SQL Programming with Packages, Oracle Built-in Packages, Oracle PL/SQL Programming Guide to Oracle8i Features, and Oracle PL/SQL Developer's Workbook—as well as the PL/SQL CD Bookshelf (all published by O'Reilly & Associates). Steven hosts the PL/SQL Pipeline (www.revealnet.com/plsql-pipeline) and contributes to RevealNet's Active PL/SQL Knowledge Base. He offers training and consulting through PL/Solutions (www.plsolutions.com).
HTH,
Orashark
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jul 03 17:13:20 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
