Download Oracle Practical 2: Formatting Output and String Manipulation and more Study notes Business Administration in PDF only on Docsity!
Page 1 of 6
FORMATTING OUTPUT
This tutorial will cover the topics recently covered in Lectures. Joining Outputs with Concatenation, Simple Arithmetic, Column Aliases, Speech Marks andQuotes, String functions.
SQLPlus Commands* (as promised). Use the following commands to edit queries that exist within the SQL Buffer. L Last, this tells Oracle to call up the last SQLcommand. I Insert. This command can be used to add linesto a SQL command that you have called up using L. A Append, used to append to the end of a line ina query D Delete, used to delete current line C/???/!!!! Change, the command shown will replace thefirst occurrence of ??? with the string !!! in the last SQL command used. Say you type a query… SELECT table_names FROM all_table; When you attempt to execute this, Oracle will tell you it is not aware of a table by this name.Forgot the S on all_tables. An asterix will be placed under where the error occurred.
To edit the query, first print it to the screen using L. Once listed, an asterix will be placedbeside the last line, indicating that you may edit that line. To append an S to the name of the table, type… A s The space is very important. Now run the query using the forward slash /. Another error on the first line. Use L again. The asterix still appears beside the last line of the query, No. 2. Wewant to edit line 1, and get rid of the unwanted S at the end of the column name. Type 1 and hit return, note that is the line you can edit. To drop the S, or change it so that it is replaced by an empty space, type C/names/name SQL*Plus will go through line 1, until it finds the string ‘names’, and then, replace it with‘name’. Note that if we opted to use…
C/s/ As in replace ‘s’ with nothing, SQL*Plus would have carried out this operation on the first ‘s’ itcame across, in the SELECT.
Use L to list the query again, and run it with /.
Page 2 of 6
Datatypes. We already noticed how some columns within tables within Oracle have different definitions to others. This could be seen when we described a table. DESC all_tables I will give a brief description here now, so that you will understand the differences that apply when trying to manipulate the data within columns. VARCHAR2(w) Variable length character values up to width w.Maximum width is 2000 characters. * CHAR(w) Fixed length character values of width w.Default width is 1. Max: 255. * NUMBER Floating point number with a precision of 38significant digits.* NUMBER(w) Integer numbers of precision w. NUMBER(w,s) Numbers with a precision w and scales s.Precision represents the maximum significant digits allowed, which may not exceed 38. Scaleis the number of decimal places recorded to the right of the point *. DATE Date values from January 1, 4712 BC toDecember 31,4712 AD. Time information is also stored. * LONG Variable length character values up to 2 Gigs.Only one long column allowed per table. * RAW and LONG RAW Equivalent to VARCHAR2 (but, maximum widthis 255) and LONG respectively, but used for storing byte-oriented or binary data such asgraphics images or digital sound. *
- Note that the above specifications refer to Oracle 7.3. Most have been increased with Oracle8, but I wouldn’t worry about it. If I can get hold of the new definitions, I will let you know. Also Oracle 8 allows User defined Datatypes. The Datatypes you will come across most, and the only ones you really need to worry about are Varchar2, Char, Number and Date. Concatenation. This is extremely straightforward. To do it we use the Bar. (Shift+). Actually we use it twice.
SELECT column1 || column FROM table ; Here is an example using the all_tables table we used last week. SELECTFROM table_name||ownerall_tables;
You can also include text within your output through the use of ‘||’ and the single quote. SELECTFROM table_name||’ is a table name’all_tables;
Note that a space is included after the first quote. This means a clearer output. Anotherexample that leads to a more meaningful output is:
SELECTFROM table_name||’ is owned by ‘||ownerall_tables;
Page 4 of 6
You can also apply more than one function to different columns within the same query. SELECTFROM RPAD(table_name, 30, ‘.’), INITCAP(owner)all_tables;
Try using LPAD in a similar example. LTRIM and RTRIM can be used in a similar fashion. Take this example; it might be a goodidea to retrieve all owner information from all_tables first, for comparison purposes…
SELECTFROM LTRIM(owner, ‘S’)all_tables;
Or… SELECTFROM RTRIM(owner, ‘T’)all_tables;
Or to make things clearer… SELECTFROM owner, RTRIM(owner, ‘T’)all_tables;
To use SUBSTR to cut out a piece of data from a column, the following notation is applied… SELECTFROM SUBSTR( table ; column , start [ , count ])
Specify the column you want to cut a string from, the character position you want to start the extract from, and if required, how many letters you want to clip out. Example… SELECTFROM owner, SUBSTR(owner, 3)all_tables;
Or… SELECTFROM owner, SUBSTR(owner, 3, 2)all_tables;
You can also combine functions and apply several at a time to one column, using the outputfrom one function as the input for another…
SELECTFROM owner, LENGTH(SUBSTR(owner, 3))all_tables;
INSTR is used to find the occurrence of a character or a set of characters within a string. The notation is…
SELECT INSTR( column,set [ , start [ , occurrence ])
FROM table ;
As in…
SELECT table_name, INSTR(table_name, ‘M’)
FROM all_tables;
This means, ‘Oracle, tell me where in the column “table_name” does the character
“M” appear’.
Page 5 of 6
Applying the optional extras…
SELECT table_name, INSTR(table_name, ‘M’, 2, 1)
FROM all_tables;
‘Oracle, tell me where in the column “table_name” does the character “M” appear,
starting from the second character, and identifying the first occurence’.
A Practical application of the above would be to parse strings within columns using a
combination of SUBSTR and INSTR.
SELECT table_name, SUBSTR(table_name, INSTR(table_name, ‘M’))
FROM all_tables;
This cuts all text from the first occurrence of M to the end of a string. Look at it
closely, what is the output of the of the INSTR function, how does it sit within the
SUBSTR function. This type of command is very useful when dealing with Character
Delimited strings.
Repeat again and again until these functions make sense.
The DUAL Table.
Again this table is part of Oracle’s own structures, and can be seen by everyone with
a logon to the database server. Describe it.
DESC dual
When described, you will see it has only one column, called DUMMY. Its reason for
existing is to allow you carry out simple queries that may not always involve actual
tables within the database. The easiest example is the query used to retrieve the
System Date.
SELECT sysdate
FROM dual;
There is no sysdate column within the description of DUAL, yet this query throws
back the date, as it is set on the database server.
All the functions we used above, covered columns within tables (which are equivalent
to variables, in that, the values do not matter and already exist within each row of the
database). The same functions can be applied to literals which you define yourself.
For example.
SELECT ‘Hello’
FROM dual;
Or…
SELECT UPPER(’Hello’)
FROM dual;
Etc…
