![]() ![]() The source code for the examples in this article can be downloaded from here. SQL> DECLARE 2 v_sql VARCHAR2(1024) 3 v_cnt PLS_INTEGER 4 BEGIN 5 v_sql := q'' 6 EXECUTE IMMEDIATE v_sql INTO v_cnt 7 DBMS_OUTPUT.PUT_LINE( 8 TO_CHAR(v_cnt) || ' tables in USER_OBJECTS.' 9 ) 10 END 11 /4 tables in USER_OBJECTS.PL/SQL procedure successfully completed. However, in the interest of demonstrating the quoting mechanism in as simple and short an example as possible, we'll stick with it. The sharp-witted amongst us will notice that this statement doesn't in fact need to be dynamic (and even if it did, it should be using bind variables). The following is a contrived example of a dynamic SQL statement that includes literal quotes in the string. The quoting mechanism doesn't make these problems go away, but it at least makes dynamic SQL a little bit easier to transfer between SQL editors and packages during development. While providing a flexible solution to many programming problems, dynamic SQL can also be difficult to build, debug and support. Many developers will be familiar with dynamic SQL. using the quoting mechanism in dynamic sql SQL> DECLARE 2 v VARCHAR2(1024) 3 BEGIN 4 v := q'' 5 DBMS_OUTPUT.PUT_LINE(v) 6 END 7 /It's a string with embedded quotes.PL/SQL procedure successfully completed. The following example demonstrates how simple it is to use this mechanism when building a string that contains single quotes. If used with any software that uses an older Oracle client it fails with ORA-01756: quoted string not properly terminated (confirmed with sqlplus, TOAD and PL/SQL Developer). Your example has 'angled' quotes rather than the correct ' variant, meaning that either that's the actual problem, or that you've transcribed it incorrectly which leads me to think you're not. Note that at the time of writing, the quoting mechanism only appears to work with 10g clients/OCI. It's almost certainly because you're using the wrong quote types, something that often happens when you cut'n'paste text from a word processor. The syntax is q'', where the "" characters can be any of the following as long as they do not already appear in the string. The mechanism is invoked with a simple "q" in PL/SQL only. This is particularly useful for building dynamic SQL statements that contain quoted literals. Unfortunately, word processors often use alternate characters for both single quotes (see the characters around AUS in your code above) and double quotes (e.g. This is a new feature of 10g that enables us to embed single-quotes in literal strings without having to resort to double, triple or sometimes quadruple quote characters. You apparently used a word processor, such as Microsoft Word, to prepare your code. This short article introduces Oracle's new quoting mechanism in PL/SQL. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |