Showing posts with label PL/SQL. Show all posts
Showing posts with label PL/SQL. Show all posts

Calling a Stored Function from JDBC

The code we should write to call a stored procedure or function via JDBC is slightly different from the one we write to perform a SQL SELECT. A CallableStatement is used instead of a Statement plus a ResultSet to set the parameter, execute the call, and extract the result.

To check it, I have firstly created a stored function in my Oracle 12 instance, on the HR schema. It takes a SQL CHAR in input, and returns the same string with 'suffix' appended:
create or replace FUNCTION foo (val CHAR)
RETURN CHAR AS
BEGIN
    RETURN val || 'suffix';
END;
I ensured it works as I expected running this query:
SELECT foo('hello') FROM DUAL;
The I have written a couple of tests to see how to get the same result in Java. Not just one, because we can use either the JDBC escape and the PL/SQL block syntax to achieve the same result, being the first
{? = call foo(?)
and the latter
begin ? := foo(?); end;
The core of both tests is in these few lines:
cs = conn.prepareCall(call);  // 1
cs.registerOutParameter(1, Types.CHAR);  // 2
cs.setString(2, "aa");
cs.execute();
String result = cs.getString(1);  // 3
1. Assuming conn is a good java.sql connection to the HR user on my Oracle database, and call is either the JDBC escape or the PL/SQL block string showed above, the prepareCall() should return a good CallableStatement.
2. The callable statement has to know the type of the output parameter is a character string. Then we set the other parameter, with the string that we want to pass as input.
3. After executing the callable statement, we get the first (and only) result as a string.

The actual code, that you could find on GitHub, class GettingStartedTest methods callFoo(), testFunctionCallJdbcEscape(), and testFunctionCallPlSqlBlock(), is a bit more verbose because I have to provide all the standard boilerplate, initializing, testing, cleaning up.

Reference: Oracle Database JDBC Developer's Guide 12c Release 2 (12.2) 2.8 Stored Procedure Calls in JDBC Programs

Go to the full post

Trigger

A trigger is a sort of procedure that is executed as result of an event generated in the database.

As an example, let's see a trigger that generates a user exception we try to change data in the jobs table in a week day other than wednesday:

create or replace trigger tr_jobs
before insert or update or delete
on jobs
begin
if to_char(sysdate, 'DY') != 'WED' then
raise_application_error (-20900, 'Today no change allowed');
end if;
end;

When this trigger is in the database, if we try to insert a new row:
insert into jobs values('XXX', 'Unknown', 0 , 0);
Update:
update jobs set max_salary = 0;
Or delete:
delete jobs where job_id = 'AD_VP';
We always get a SQL error back - if we don't run these statements on Wednesday.

Chapter 19 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about triggers.

Go to the full post

Package

The PL/SQL package helps us to organize our code in a similar way to the header file for C/C++ code.

We create a package, containing the function declarations and, when required, subtype definitions. Then we create the package body, where the functions are defined.

Let's create a package for a couple of utility functions for the employees table, as usual located in the hr oracle test schema:

create or replace package pkg_employee as

subtype fullname_t is varchar2(200);

function fullname(
in_first employees.first_name%type,
in_last employees.last_name%type
) return fullname_t;

function fullname(
in_id in employees.employee_id%type
) return fullname_t;

end;

We have a function, fullname(), with two overloads. One expects first name and last name - and just combine them together, the other requires an employee id as input parameter. Both of them return an object of the subtype, fullname_t, created in the same package.

Now we provide the implementation for the functions:

create or replace package body pkg_employee
as

function fullname(
in_first employees.first_name%type,
in_last employees.last_name%type
) return fullname_t
is
begin
return in_first || ' ' || in_last;
end;

function fullname(
in_id in employees.employee_id%type
) return fullname_t
is
retval fullname_t;
begin
select fullname(first_name, last_name)
into retval
from employees
where employee_id = in_id;

return retval;
exception
when no_data_found then
return 'Not found!';
when others then
return null;
end;

end pkg_employee;

In this case I added the pkg_employee specification to the finale end tag. It is not mandatory, but when things get complex it helps to let the code be clearer.

Now I can use the package in my code, in this way:

declare
l_name pkg_employee.fullname_t;
l_id employees.employee_id%TYPE := 100;
begin
l_name := pkg_employee.fullname(l_id);
dbms_output.put_line(l_name);
end;

Packages are thoroughly discussed in chapter 18 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.

Go to the full post

Function

A PL/SQL function is, basically, a procedure that explicitely returns a value.

Or, other way round, a procedure is a function that explicitely does not return anything.

In any case both procedure and function could return as many values as required in their parameter list, marking any parameter required to be a return value with the "OUT" tag. But only the explicit return value from function could be assigned directly to a value in the caller code as a result of invoking that indipendent code block.

Here is how to create a simple function accepting an input varchar2 parameter and returning another varchar2:

create or replace function country_name(in_id in varchar2)
return varchar2
is
l_country_name countries.country_name%type;
begin
select country_name
into l_country_name
from countries
where country_id = in_id;

return l_country_name;

exception
when no_data_found then
return 'No such country id';
when others then
return 'Unexpected error: ' || sqlerrm;
end;

In case of exception, we trap it and return just an error message to the caller.

Here is how we call the function we just created:

declare
l_country_name countries.country_name%type;
begin
l_country_name := country_name('UK');
dbms_output.put_line(l_country_name);
end;

And that's how to get rid of it:
drop function country_name;

Procedures, functions, and parameters are thoroughly discussed in chapter 17 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.

Go to the full post

Procedure

If you know how to deal with a PL/SQL anonymous block, you should easily grasp the same look and feel in the procedure definition.

The PL/SQL is a block of code that we could call from another PL/SQL block by its name. Besides, we could pass any (reasonable) number of parameters to it.

Here is how we create a procedure for the current schema:

create or replace procedure say_hello(in_name in varchar2)
is
begin
dbms_output.put_line('hello ' || in_name || '!');
end;

We "create or replace" it. So, if we are posting a change in the procedure code we won't get an error. If we prefer to avoid the risk of destroying already existing code, we can just "create" it.

This procedure is named say_hello, this is the name we should use to call it.

It accepts one single input ("in") parameter, a varchar2 named in_name.

The procedure body should not require any further explanation.

Once we have a procedure in our schema, or in an accessible one, we can call it like this:

begin
say_hello('Tom');
end;

We can get rid of our procedure, if we don't need it anymore, dropping it:
drop procedure say_hello;
Assuming our user has the rights to do that, naturally.

Procedures, functions, and parameters are thoroughly discussed in chapter 17 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.

Go to the full post

Passing parameters to cursor

A PL/SQL cursor could be thought like a sort of function that perform a query to the database and makes available the resulting recordset.

Being like a function, there should be a way of passing parameters to it.

Let's change the code we wrote in the previous post. There we have a loop on an explicit cursor that selected all the rows in the countries table.

Here we do something a bit smarter. We want to select just the countries in a specific region. And we want to be able to pass the region id to the cursor from the calling code.

Here is how we can do that:

declare
cursor lc_countries(p_region number) is -- 1.
select * from countries where region_id = p_region; -- 2.
lr_country countries%rowtype;
begin
open lc_countries(4); -- 3.

loop
fetch lc_countries into lr_country;

if lc_countries%notfound then
dbms_output.put_line('---');
exit;
end if;

dbms_output.put_line(lr_country.country_name);
end loop;

close lc_countries;
exception
when others then
dbms_output.put_line('Something went wrong ' || sqlerrm);
if lc_countries%isopen then -- 4.
close lc_countries;
end if;
end;

1. p_region is a parameter to the cursor.
2. We use the parameter passed to the cursor in the SQL statement.
3. With the open call to the cursor, we pass the requested parameter.
4. Another little change in the code about robustness: we ensure the cursor is open before closing it.

We can also specify a default value for the cursor parameter. If we rewrite the line (1) in this way:
  cursor lc_countries(p_region number := 1) is
We can accordingly rewrite line (3) to use the default value for the cursor instead of providing an explicit one:
open lc_countries();
Where the round brackets could be omitted.

More information on data retrieval in PL/SQL in chapter 15 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.

Go to the full post

Loop on an explicit cursor

To loop on an explicit cursor we take advantage of the fact that when we try to read the first element next to the end of a cursor we do not get an exception back, but we simply have the cursor property NOTFOUND set to true.

Here is an example:

declare
cursor lc_countries is select * from countries;
lr_country countries%rowtype;
begin
open lc_countries;

loop
fetch lc_countries into lr_country;

if lc_countries%notfound then -- 1.
dbms_output.put_line('End of cursor');
exit;
end if;

dbms_output.put_line(lr_country.country_name);
end loop;

close lc_countries;
exception
when others then -- 2.
dbms_output.put_line('Something went wrong ' || sqlerrm);
close lc_countries;
end;

1. if the fetch reports that no row is available, setting the cursor property NOTFOUND to true, we end the loop.
2. it's a good idea to consider that we could have some unexpected exception after we open the cursor and before we could close it in the normal execution flow. Checking for exceptions we ensure we properly close it.

More information on data retrieval in PL/SQL in chapter 15 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.

Go to the full post

Implicit cursor

The simple cursor showed in the previous post is an explicit one: we declare it with its associated SQL statement in the DECLARE section of our PL/SQL block, then we explicitely OPEN, FETCH and CLOSE it.

When we write an implicit cursor, we rely on the PL/SQL compiler to do all the under the curtain job and we just get the result in a local variable.

In this example we use an implicit cursor to fetch a row in a local record:

declare
lr_country countries%rowtype; -- 1.
begin
select *
into lr_country -- 2.
from countries
where country_id = 'BE';

dbms_output.put_line(lr_country.country_name || ' ' || lr_country.region_id);
exception
when no_data_found then -- 3.
dbms_output.put_line('no data found');
when too_many_rows then -- 4.
dbms_output.put_line('more than a row found');
end;

1. Declare the record to be used by the implicit cursor.
2. The SQL statement is used by the PL/SQL compiler to generate an implicit cursor that would fetch its first row in our local record variable.
3. If there is not such a row in the table, a exception is raised.
4. Changing the WHERE clause in the SELECT statement to a "like 'B%'" we are going to get more than one row, and so a exception will be raised.

More information on data retrieval in PL/SQL in chapter 15 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.

Go to the full post

Simple cursor

A simple PL/SQL cursor could be thought as a pointer to the result of a query.

The cursor declaration specifies how the query should be run. The we have to OPEN it, to actually perform the query; FETCH a single record from the cursor, to be able working on the data, and finally CLOSE it.

Once the cursor is open, we can check its status showed by a few attributes - in case of implicit cursor we check the attributes of SQL. Trying to read the status of a cursor not already initialized leads to a INVALID_CURSOR exception.

Here is a simple usage example on the countries table from the test HR Oracle schema:

declare
cursor lc_countries is select * from countries; -- 1.
lr_country countries%rowtype; -- 2.
begin
open lc_countries; -- 3.
dbms_output.put_line('Row fetched from cursor: '
|| lc_countries%rowcount); -- 4.

fetch lc_countries into lr_country; -- 5.

if lc_countries%found then -- 6.
dbms_output.put_line('Row fetched');
end if;

dbms_output.put_line('The first fetched country is ' || lr_country.country_name);

close lc_countries;
exception
when invalid_cursor then -- 7.
dbms_output.put_line('Cursor has not been opened yet');
end;

1. Cursor declaration.
2. Record used by the cursor.
3. First step to do is opening the cursor.
4. Attribute rowcount: it returns the number of rows already fetched from the cursor. In this case zero.
5. Then we fetch the cursor, to access a row by the record.
6. Attribute found: true if the fetch operation has been accomplished correctly.
7. The exception we could get when we try to access a cursor attribute.

More information on data retrieval in PL/SQL in chapter 15 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.

Go to the full post

Records in INSERT and UPDATE

We can semplify our PL/SQL code using records to perform INSERT and UPDATE statements.

Here is an example where we try to INSERT a new row in table country, as available in the test Oracle HR schema. If the country id is already in the table, we get an exception, so we fall back to call an UPDATE:

declare
l_country countries%rowtype; -- 1.
begin
l_country.country_id := 'VA'; -- 2.
l_country.country_name := 'Vatican';
l_country.region_id := 1;

insert into countries values l_country; -- 3.
exception
when dup_val_on_index then -- 4.
dbms_output.put_line('Country already inserted');
update countries
set row = l_country -- 5.
where country_id = l_country.country_id;
end;

1. Declaration of a record matching the countries table definition.
2. Record setup
3. INSERT for record: after VALUES no round brackets.
4. Exception when we try to insert a country id already existing.
5. In UPDATE we use the SET ROW clause.

More information on DML in PL/SQL in chapter 14 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.

Go to the full post

Binding by RETURNING

We could bind PL/SQL variables to a SQL DML statement so that it could return values extracted from the database to the PL/SQL block. To do that we use add a RETURNING clause to the SQL statement.

As an example, here is a loop that performs an UPDATE statement on each row of the EMPLOYEES table (again from the Oracle test HR schema). Two local variables are set in the RETURNING clause and then used in the proceeding of the PL/SQL code:

declare
l_salary employees.salary%type;
l_name employees.last_name%type;
begin
for rec in (select * from employees)
loop
update employees
set salary = salary * 1.05
where rec.employee_id = employee_id
returning salary, last_name into l_salary, l_name;

dbms_output.put_line(l_name || ' new salary is ' || l_salary);
end loop;
end;

More information on DML in PL/SQL in chapter 14 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.

Go to the full post

SQL%FOUND and SQL%ROWCOUNT

We could get information on the most recent execution of a SQL statement in PL/SQL checking a few implicit cursor attributed made available to us.

Here we see a couple of them, SQL%FOUND and SQL%ROWCOUNT, in action.

In a PL/SQL block we run this UPDATE statement:

update family
set surname = 'Smith Dumblee'
where surname like 'S%';

After that, we could check if we modified anything in the database:

if(sql%found) then
dbms_output.put_line('at least one row affected');
else
dbms_output.put_line('no rows affected');
end if;

If we need more precise information, we could use SQL%ROWCOUNT:

case sql%rowcount
when 0 then
dbms_output.put_line('no rows affected');
when 1 then
dbms_output.put_line('one row affected');
else
dbms_output.put_line('more than one row affected');
end case;

More information on DML in PL/SQL in chapter 14 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.

Go to the full post

Collection.exists()

The PL/SQL method collection.exists() could be called on associative array, nested table, varray to check if an element is in the current collection.

It does not throw any exception. Even if the undelying collection has not been initialized, it just returns FALSE.

If parents_t is a varray type available to the current PL/SQL, we could write this code:

declare
parents parents_t := parents_t();
begin
if parents.exists(42) = false then
dbms_output.put_line('This item is not in the collection');
end if;
end;

That prints the message, since that element is not in the collection. More interestingly, even though we don't initialize the varray but just declar it:
parents parents_t;
the result won't change.

Chapter 12 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about collections.

Go to the full post

Collection.delete()

We use the delete() method on a PL/SQL collection (associative array, nested table, varray) to delete a single element in it, a subinterval of it, or all its members.

On varray we can call delete() only without argument, to remove all the items in it. The trim() function could be used to remove a single item at the end of the collection.

If we apply delete() on an unitialized nested table or varray, we get a COLLECTION_IS_NULL exception.

As an example, we could modify the code we wrote for testing the associative array inserting a remove call before dumping the data to the output buffer.

If we want to remove all the items, we could write:
l_countries.delete();
We achieve exactely the same result specifying explicitely the range starting from the first element and ending to the last one:
l_countries.delete(l_countries.first(), l_countries.last());
If we want to delete all the items but the extreme ones we could call delete() in this way:

l_countries.delete(
l_countries.next(l_countries.first()),
l_countries.prior(l_countries.last()));

We have remove all items in the interval starting from the next to te first element and ending to the previous to the last one.

We can explicitely provide the key of the element we want to remove, when we know it. For instance, since Brazil was inserted with key 98, here is how we can remove it:
l_countries.delete(98);
Chapter 12 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about collections.

Go to the full post

Collection.count()

We could check the number of elements in a PL/SQL collection (associative array, nested table, varray) using the count() function.

In case of a collection that requires initialization (nested table and varray), if its count() method is called before it is initialized, a COLLECTION_IS_NULL exception is raised.

Here is an example that uses the example data we created in the previous post on varray.

declare
l_parents parents_t;
begin
-- dbms_output.put_line('This raise an exception ' || l_parents.count()); -- 1.

select parents
into l_parents
from family
where surname = 'Smith'; -- 2.

dbms_output.put_line('Smiths: ' || l_parents.count());
exception
when collection_is_null then
dbms_output.put_line('Collection is null');
when no_data_found then
dbms_output.put_line('No data found');
when others then -- 3.
dbms_output.put_line('Unexpected: ' || sqlerrm);
end;

1. At this point l_parents has not been initialized, if you uncomment this line you would get a COLLECTION_IS_NULL exception.
2. If no family with surname Smith is in the table, we have a NO_DATA_FOUND exception
3. No other exceptions are expected in this piece of code.

Chapter 12 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about collections.

Go to the full post

Varray

In a Oracle database, we can use define datatypes based on VARRAY, a sort of resizable vector with a fixed max size.

Let's say that we have to manage a kindergarten waiting list. We want to put in a table the kid names and the relative parents names. And we want also to be able to manage the case of parents who don't have currently any kid, but still they want to enter in the list (it's a fancy kindergarten with a very long waiting list).

Any kid could have 0, 1, or 2 parents. And this is how we create the type that describes their names:
create type parents_t is varray(2) of varchar2(100);
We could have 0 or 1 kid (twins are not supported):
create type child_t is varray(1) of varchar2 (100);
And now we use these new types for our family table:

create table family (
surname varchar2(100),
parents parents_t,
kid child_t
);

Here is the PL/SQL code to put in the list Ann and Bill Smith, proud parents of Charlie Smith:

declare
parents parents_t := parents_t();
kid child_t := child_t();
begin
parents.extend(2);
parents(1) := 'Ann';
parents(2) := 'Bill';

kid.extend();
kid(1) := 'Charlie';

insert into family (surname, parents, kid)
values('Smith', parents, kid);
end;

And here is the case of Dan Schmidt, single, no kids, but with lot of hopes in the future:

declare
parents parents_t := parents_t();
kid child_t := child_t();
begin
parents.extend(1);
parents(1) := 'Dan';

insert into family (surname, parents, kid)
values('Schmidt', parents, kid);
end;

Chapter 12 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about collections.

Go to the full post

Nested table

When programming in PL/SQL we could use SQL nested tables, that behave like (STL C++) vectors of the underlying data.

Being nested tables SQL type, we could store them in a database schema and use them in any PL/SQL block having SELECT access to that type.

Nested tables could be seen as set, an we can apply on them set operators like MULTISET EXCEPT.

As an example, let's create a nested table type of VARCHAR2, declare a few variables ot this type and use them:

declare
type nt_names_t is table of varchar2(20); // 1.
family nt_names_t := nt_names_t(); // 2.
children nt_names_t := nt_names_t();
parents nt_names_t := nt_names_t();
begin
family.extend(4); // 3.
family(1) := 'Mother'; // 4.
family(2) := 'Father';
family(3) := 'Son';
family(4) := 'Daughter';

children.extend(); // 5.
children(1) := 'Son';
children.extend();
children(2) := 'Daughter';

parents := family multiset except children; // 6.

dbms_output.put_line('Parents:');
for l_row in parents.first() .. parents.last() // 7.
loop
dbms_output.put_line(parents(l_row));
end loop;

dbms_output.put_line('Childrens:');
for l_row in children.first() .. children.last()
loop
dbms_output.put_line(children(l_row));
end loop;

dbms_output.put_line('Family:');
for l_row in family.first() .. family.last()
loop
dbms_output.put_line(family(l_row));
end loop;
end;

1. We define locally a nested table type. To create a nested table type in the current schema we would have written:
create or replace type nt_names_t is table of varchar2(20);
2. Before usage, a nested type variable has to be initialized calling the constructor (C++ style).
3. We have to reserve explicitely memory for new elements.
4. In this way we add a new element to the nested table.
5. By default extend() reserves room for one more element.
6. With MULTISET EXCEPT we get a set that is the difference from the left hand to the right hand passed nested tables.
7. An interesting property of nested tables is that we can loop on them using a for loop delimited by its first() and last() element.

Chapter 12 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about collections.

Go to the full post

Associative array

PL/SQL makes available associative arrays that we can use to store key-data pairs.

Here is an example where we create an associative array based on the pair { pls_integer, varchar2(20) } where the integer acts as a key:

declare
type aa_names_t is table of varchar2(20) index by pls_integer; -- 1.
l_countries aa_names_t; -- 2.
l_key pls_integer; -- 3.
begin
l_countries(714) := 'Belgium'; -- 4.
l_countries(2) := 'Belize';
l_countries(98) := 'Brazil';
l_countries(12) := 'Burkina Faso';

l_key := l_countries.first(); -- 5.
while(l_key is not null) -- 6.
loop
dbms_output.put_line(l_countries(l_key));
l_key := l_countries.next(l_key); -- 7.
end loop;
end;

1. Instead of an explicit varchar, we could have used as type the one of an existing table row, like this: countries.country_name%type
2. Declaration of a local variable of the just created type.
3. Variable used to loop on the associative array's keys.
4. In this way we insert a new element in the associative array.
5. Get the key of the first row.
6. Loop until there is an element available.
7. Get the next key.

Chapter 12 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is about collections.

Go to the full post

Records

The PL/SQL could create his own data types that in this language are identified by the keyword RECORD.

A record could be created in three ways: basing it on a table data definition; making it as an indipendent brand new type; and basing it on a cursor type definition.

If we want to fetch data from a table, we could create a record matching the table definition. For instance, let's say that we want to work with the COUNTRIES table in the Oracle test hr schema:

declare
l_country countries%rowtype; -- 1.
begin
select *
into l_country -- 2.
from countries
where country_id = 'AR';

dbms_output.put_line(l_country.country_name); -- 3.
exception
when no_data_found then -- 4.
dbms_output.put_line('No data found');
when others then
dbms_output.put_line('Unexpected exception');
end;

1. that's how we define a table based record.
2. we fetch the data through select in our record.
3. that's how we access fields in a record.
4. if SELECT don't find anything, an exception is raised.

No need of duplicating an entire table structure in a record, if that is not necessary. For instance here we create and use a custom record based on the COUNTRIES table, but using only a couple of fields:

declare
type my_country_rt is record ( -- 1.
country_id countries.country_id%type,
country_name countries.country_name%type
);
l_country my_country_rt; -- 2.
begin
select country_id, country_name
into l_country
from countries
where country_id = 'AR';

dbms_output.put_line(l_country.country_name);
exception
when no_data_found then
dbms_output.put_line('No data found');
when others then
dbms_output.put_line('Unexpected exception');
end;

1. That's how we define a brand new record type.
2. And this is the definition of variable of our new record type.

Given a cursor, we could create a record based on it:

declare
cursor l_countries_cur is
select * from countries
where country_id like 'A%';
l_country l_countries_cur%rowtype; -- 1.
begin
open l_countries_cur;
loop
fetch l_countries_cur into l_country;
exit when l_countries_cur%notfound;
dbms_output.put_line(l_country.country_id || ' ' || l_country.country_name);
end loop;
close l_countries_cur;
end;

1. That's the definition of a cursor based record

We have already seen that we could simplify the cursor-record relation using the cursor for loop structure. Here is the previous code rewritten using an implicit cursor based record:

begin
for l_country in (select * from countries where country_id like 'A%')
loop
dbms_output.put_line(l_country.country_id || ' ' || l_country.country_name);
end loop;
end;

Chapter 11 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein tells a lot more than this about records.

Go to the full post

Padding and trimming

A couple of very popular PL/SQL functions for strings are the ones used for padding and trimming characters. Their standard usage is for providing a simple way of formatting data to be printed in columns, so the default character is blank (' ').

But let's think to a situation where make sense using a different character for padding, for instance zero ('0'). It could happen that we have a table with spare parts descriptions, described by a seven digit code. We want to merge this table with another one, where the codes are ten digit wide, so we pad our original codes with zero. Question is: leading or trailing?

We could do both. LPAD() is for padding on the left, and RPAD on the right. At the same way we could need to remove extra characters on both side of a string. For this purpose we could use LTRIM() or RTRIM() or the ISO SQL TRIM() function, that could even trim on both sides in one single shot:

declare
l_code1 char(7) := '5467312';
l_code2 char(7) := '0745667';
l_new_code1 char(10);
l_new_code2 char(10);
begin
l_new_code1 := lpad(l_code1, 10, '0');
l_new_code2 := rpad(l_code2, 10, '0');

dbms_output.put_line(l_new_code1);
dbms_output.put_line(l_new_code2);

dbms_output.put_line(ltrim(l_new_code1, '0'));
dbms_output.put_line(rtrim(l_new_code2, '0'));

dbms_output.put_line(trim(both '0' from l_new_code2));
dbms_output.put_line(trim(leading '0' from l_new_code2));
dbms_output.put_line(trim(trailing '0' from l_new_code2));
end;

Chapter 8 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about strings datatypes and related functions.

Go to the full post