Couldn't edit so a second comment. This is the right answer.

The greatest prevents the negative offset being longer than the string - i.e. Substr(',-9) will give the 9 rightmost characters of the string. Substr(',-12) gives NULL. Avenir Light Free Font Download.

Sep 12, 2013. Ric Van Dyke from Hotsos asked me yesterday if I knew of a PL/SQL function that inputs a SQL_TEXT and outputs a SQL_ID. I kind of remembered Tanel Poder had written something on this regard. And I also recalled seen an API under DBMS_UTILITY that would provide the SQL_ID given a SQL_TEXT.

The offset cannot be LONGER than the string. The GREATEST ensures this: GREATEST( -LENGTH('),-12) is GREATEST(-10,-12) is -10. It ensures the offset is not longer than the string, which the first answer will not do. The first answer will work so long as string is always longer than X. – Aug 10 '10 at 10:50.

Software Convert Pdf To Word Yang Bagus 21. TECHNOLOGY: PL/SQL As Published In Working with Strings By Steven Feuerstein Part 3 in a series of articles on understanding and using PL/SQL Every application needs data. That seems rather obvious, doesn’t it?

An application is almost always built on top of database tables. Those tables are full of different kinds of data. And the programs you write—whether they are in PL/SQL or another language—manipulate that data.

It is, therefore, extremely important for you to be aware of the different datatypes supported by PL/SQL and how you can work with those datatypes. Take the Challenge! Each of my PL/SQL 101 articles offers a quiz to test your knowledge of the information provided in the article. The quiz questions are shown below and also at PL/SQL Challenge (), a Website that offers online quizzes for the PL/SQL language.

You can read and answer the quiz here, and then check your answers in the next issue. If, however, you take the quiz at PL/SQL Challenge, you will be entered into a raffle to win your choice of an e-book from O’Reilly Media (). Question 1 What will be displayed after executing this block? BEGIN sys.DBMS_OUTPUT.put_line ( INSTR ('steven feuerstein', 'e', -1, 2)); END; Question 2 True or false: When assigning a literal value to a string, that value may not contain within it any single quotes. Question 3 What will be displayed after executing this block? BEGIN DBMS_OUTPUT.put_line ( 'REPLACE=' REPLACE ('steven feuerstein', 'e', NULL)); DBMS_OUTPUT.put_line ( 'TRANSLATE=' TRANSLATE ('steven feuerstein', 'e', NULL)); END; As you might expect, there is an awful lot to learn about datatypes, and not all of that knowledge can fit into a single article.

So I will start with one of the most common types of data: strings. Very few database tables and programs do not contain strings—strings such as a company name, address information, descriptive text, and so on. As a result, you quite often need to do the following: • Declare string variables and constants • Manipulate the contents of a string (remove characters, join together multiple strings, and so on) • Move string data between PL/SQL programs and database tables This article gives you the information you need to begin working with strings in your PL/SQL programs. What Is a String? A string, also referred to as character data, is a sequence of selected symbols from a particular set of characters. In other words, the symbols in a string might consist of English letters, such as ”A” or ”B.” They might also consist of Chinese characters, such as 字串. There are three kinds of strings in PL/SQL: Fixed-length strings.

The string is right-padded with spaces to the length specified in the declaration. (See ”,” to see padding in action.) Variable-length strings. A maximum length for the string is specified (and it must be no greater than 32,767), but no padding takes place. Character large objects (CLOBs). CLOBs are variable-length strings that can be up to 128 terabytes. Strings can be literals or variables. A string literal begins and ends with a single quotation mark: 'This is a string literal' If you need to embed a single quote inside a string literal, you can type in two single quotes right next to one another, as in: 'This isn't a date' You can also use the “q” character to indicate an alternative terminating character for the literal: q'[This isn't a date]' A string variable is an identifier declared with a string datatype and then assigned a value (which could be a literal or an expression).

Declaring String Variables To work with strings in your PL/SQL programs, you declare variables to hold the string values. To declare a string variable, you must select from one of the many string datatypes Oracle Database offers, including CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB. The datatypes that are prefixed with an ”N” are “national character set” datatypes, which means they are used to store Unicode character data. (Unicode is a universal encoded character set that can store information in any language using a single character set.) To declare a variable-length string, you must provide the maximum length of that string. The following code declares a variable, using the VARCHAR2 datatype, that will hold a company name, which cannot (in this declaration) have more than 100 characters: DECLARE l_company_name VARCHAR2(100); You must provide the maximum length; if you leave it out, Oracle Database raises a compile error, as shown below: SQL>DECLARE 2 l_company_name VARCHAR2; 3 BEGIN 4 l_company_name:= 'Oracle Corporation'; 5 END; 6 / l_company_name VARCHAR2; * ERROR at line 2: ORA-06550: line 2, column 21: PLS-00215: String length constraints must be in range (1. 32767) To declare a fixed-length string, use the CHAR datatype: DECLARE l_yes_or_no CHAR(1):= 'Y'; With CHAR (unlike with VARCHAR2) you do not have to specify a maximum length for a fixed-length variable. If you leave off the length constraint, Oracle Database automatically uses a maximum length of 1.

In other words, the two declarations below are identical: DECLARE l_yes_or_no1 CHAR(1):= 'Y'; l_yes_or_no2 CHAR:= 'Y'; If you declare a CHAR variable with a length greater than 1, Oracle Database automatically pads whatever value you assign to that variable with spaces to the maximum length specified. Finally, to declare a character large object, use the CLOB datatype. You do not specify a maximum length; the length is determined automatically by Oracle Database and is based on the database block size.

Here is an example: DECLARE l_lots_of_text CLOB; So, how do you determine which datatype to use in your programs? Here are some guidelines: • If your string might contain more than 32,767 characters, use the CLOB (or NCLOB) datatype. • If the value assigned to a string always has a fixed length (such as a U.S. Social Security number, which always has the same format and length, NNN-NN-NNNN), use CHAR (or NCHAR).

• Otherwise (and, therefore, most of the time), use the VARCHAR2 datatype (or NVACHAR2, when working with Unicode data). Using the CHAR datatype for anything but strings that always have a fixed number of characters can lead to unexpected and undesirable results. Consider the following block, which mixes variable and fixed-length strings: DECLARE l_variable VARCHAR2 (10):= 'Logic'; l_fixed CHAR (10):= 'Logic'; BEGIN IF l_variable = l_fixed THEN DBMS_OUTPUT.put_line ('Equal'); ELSE DBMS_OUTPUT.put_line ('Not Equal'); END IF; END; At first glance, you would expect that the word “Equal” would be displayed after execution. That is not the case. Instead, “Not Equal” is displayed, because the value of l_fixed has been padded to a length of 10 with spaces. Consider the padding demonstrated in the following block; you would expect the block to display “Not Equal”: BEGIN IF 'Logic' = 'Logic ' THEN DBMS_OUTPUT.put_line ('Equal'); ELSE DBMS_OUTPUT.put_line ('Not Equal'); END IF; END; You should, as a result, be very careful about the use of the CHAR datatype, whether as the type of a variable, database column, or parameter.

Once you have declared a variable, you can assign it a value, change its value, and perform operations on the string contained in that variable using string functions and operators. For the rest of this article, I focus on the VARCHAR2 datatype. Using Built-in Functions with Strings Once you assign a string to a variable, you most likely need to analyze the contents of that string, change its value in some way, or combine it with other strings.

Oracle Database offers a wide array of built-in functions to help you with all such requirements. Let’s take a look at the most commonly used of these functions. Concatenate multiple strings. One of the most basic and frequently needed operations on strings is to combine or concatenate them together.