Examples Where Dyanmic Sql Is Used in Oracke

I recently wrote a couple of blog posts on the SQL CASE expression with examples in MySQL. As I study and learn more about Oracle SQL, I wanted to apply a CASE expression to the RPAD() character function for dynamic string padding. Continue reading to see an example query…

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

---

The SQL CASE expression is so powerful and I am using it more and more in query expressions where I need that dynamic option.

Practice Data

The fictitious data for this post is stored in a FRIENDS table with a FIRST_NAME column having these values:

SELECT FIRST_NAME FROM FRIENDS;

Query results for all FIRST_NAME column values

We can see each FIRST_NAME and accompanying length of the name with this query using the LENGTH() function:

SELECT FIRST_NAME, LENGTH (FIRST_NAME)
FROM FRIENDS;

First name and accompanying length.

Create a bind variable

To determine the longest FIRST_NAME column length value, I'll use the LENGTH() character function on the FIRST_NAME column and wrap the entire expression in a MAX() function call :

SELECT MAX ( LENGTH (FIRST_NAME) ) AS longest_length_name
FROM FRIENDS;

Longest length name value…

Next, I create a bind variable named max_fname_len to store this value for easier use and handling in any subsequent queries:

VAR max_fname_len NUMBER;
BEGIN
SELECT MAX ( LENGTH (FIRST_NAME) ) INTO :max_fname_len
FROM FRIENDS;
END;
/
print max_fname_len;

Bind Variable Max Name Length.

---

Consider making a small donation as I continue to provide valuable content here on my blog. Every contribution counts and is very appreciated. Thank you so much!

---

Dynamic RPAD() using CASE

For this requirement, I am using the RPAD() function to add n number of characters to each 'first_name' value depending on if the value is less than the max 'first_name' column length value of 7. RPAD() accepts 3 parameters (2 mandatory and 1 optional):

• target_string – The string to be padded.
• length – The length of the string after it has been padded with the set pad string.
• pad string – The string that will be padded to the target_string (in this case, right-padded). If not specified, uses a single space.

By using Searched CASE in the 2nd parameter, the length value is dynamic for each row:

SELECT
RPAD(first_name,
CASE WHEN LENGTH (first_name) < :max_fname_len THEN :max_fname_len - LENGTH (first_name) + LENGTH (first_name) ELSE :max_fname_len END , '*' ) AS padded_fname
FROM friends;

First Name with Dynamic RPAD using CASE Expression.

Notice in the query results, the rows with FIRST_NAME column values 'Charlie' and 'Jupyter' have not been right-padded since their respective lengths are 7. However, all other FIRST_NAME column values have been right-padded with a number of asterisk characters (*) up to the max_fname_len value of 7.

The row with FIRST_NAME column value of 'Humpty' is right-padded with just one asterisk character since the length of that string is 6. Yet, the name 'Jim' is right-padded with 4 asterisk characters to make up the length total of 7 because its length is only 3.

---

Feel free to visit the below posts if you are interested in other similar content:

• Import CSV file with Oracle SQL Developer
• SUBSTR() Character Function — Oracle SQL Example
• How to find the first and last day of a month with Oracle SQL Date Functions

---

I understand this example query likely has no real-world use and is purely arbitrary. But, I hope through this blog post, you have an idea of how you can apply CASE expressions for dynamic values when you need them in your queries. Thank you for reading.

Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

To receive email notifications (Never Spam) from this blog ("Digital Owl's Prose") for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the 'Click To Subscribe!' button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl's Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the "Best Of" page for a collection of my best blog posts.

---

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Examples Where Dyanmic Sql Is Used in Oracke

Source: https://joshuaotwell.com/dynamic-rpad-function-using-a-case-expression-oracle-sql-example/

Share this post