We have all written PL/SQL that looks like this:

l_message := 'Order ' || p_order_id || ' for customer ' || p_customer_name || ' totalling ' || p_total || ' has been created.';

It works. But it is a pain to read, a pain to edit, and it is so easy to lose a space or a pipe somewhere in the middle and end up with Order123for customer. If you have ever squinted at a line like that trying to find the missing || ' ', this post is for you.

There is a much nicer way, and it is already sitting in your database: apex_string.format. Let's take a look.

The basic idea

apex_string.format lets you write your string as a template with placeholders, then pass the values separately. The placeholder is %s, and the values get slotted in wherever a %s appears, in order.

So that messy line above becomes this:

l_message := apex_string.format(
   'Order %s for customer %s totalling %s has been created.',
   p_order_id,
   p_customer_name,
   p_total
);

You write the sentence once, cleanly, with %s everywhere a value needs to go. Then you list the values. No pipes, no scattered quotes, no hunting for the missing space. The sentence reads like a sentence.

Honestly, the first time you swap a horrible concatenated string for this, it feels really good.

Using numbered placeholders

Plain %s fills in the values in order: first %s gets the first value, second %s gets the second, and so on. But sometimes you need the same value more than once, or you want them in a different order. For that, you can use numbered placeholders instead.

The numbers start at zero. So %0 is the first value, %1 is the second, %2 is the third:

l_message := apex_string.format(
   'Hi %0, your order is ready. Thanks for shopping with us, %0!',
   p_customer_name
);

See how %0 appears twice? You only pass the name once, and it gets dropped into both spots. With plain %s you could not do that, you would have to pass the name twice.

A small thing to keep an eye on: do not mix %s and the numbered style in the same string. Pick one. Either count on order with %s, or be explicit with %0, %1, %2. Mixing them just gets confusing.

A couple of handy extras

There are two more placeholders worth knowing.

If you actually want a percent sign in your output, use %%:

apex_string.format('You have used %s%% of your quota', p_percent)

That gives you something like You have used 80% of your quota. The double %% becomes a single % and does not get treated as a placeholder.

And if you want a line break in your string, %n does that:

apex_string.format('Line one%nLine two')

Handy for building up messages that span more than one line without messy concatenation of chr(10).

Where this is genuinely useful

A few places where apex_string.format really earns its keep:

Debug messages. If you use apex_debug, the typed procedures already accept %s placeholders directly, so you might not even need apex_string.format there. But for any other logging, building the message with format keeps it readable.

Error messages. When you raise an application error, you often want to include some context, the ID that failed, the value that was wrong. format makes that tidy:

raise_application_error(
   -20001,
   apex_string.format('Customer %s not found in region %s', p_customer_id, p_region)
);

Dynamic SQL. When you are building a query string, format is far easier to read than a wall of concatenation. Just be careful here: apex_string.format is plain text substitution, so never use it to drop user input straight into a SQL statement. Use proper bind variables for values. format is great for assembling the static shape of the query, not for injecting data into it.

Anything with a repeated value. The numbered placeholders shine whenever the same value shows up two or three times in a message. One parameter, several %0s.

A note on length

apex_string.format has an optional p_max_length parameter. If you pass it, each substituted value gets trimmed to that many characters before it goes into the string. That is occasionally useful if you are formatting something with a hard length limit and you would rather truncate than overflow. Most of the time you will not need it, but it is there if you do.

Give it a go

Next time you find yourself building a string with a long chain of || and loose quotes, stop and reach for apex_string.format instead. Write the sentence as a clean template, mark the gaps with %s, and pass your values after.

Your code gets easier to read, easier to change, and you stop losing spaces in the middle of concatenated strings. Small change, but it is one of those things that just makes your PL/SQL a little bit nicer to live with.

The Cleaner Way to Build Strings in PL/SQL: APEX_STRING