Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.
To try things out, let's just start with a simple table with (more...)
A question popped up, which I thought was interesting. How can you create a
DB_LINK in Oracle without the DBA changing the
tnsnames.ora file? It’s actually quite easy, especially if the DBA sets the TNS address name the same as the instance’s service name or in older databases SID value.
- Do the following with the
It should return this when the server’s
mohawk and domain name is
If you’re reading this post, you most likely are trying to run the Oracle Database 11g or 12c
runInstaller program, and it’s failing a critical dependency check and displaying an error like the one below. If so, choose
n because if you choose
y it won’t launch the Oracle Installer.
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 500 MB. Actual 30824 MB Passed
Checking swap space: must be greater than (more...)
Installing any Oracle database is tedious, but the installing the pre-requisites can be tedious. This post tries to simplify the process by creating a single
prereq.sh file for all the pre-requisite libraries. The file should contain the following:
yum install -y binutils \
This post shows you how to add the menu option and GUI to set users and groups. It’s quite a bit easier than mastering all the command-line syntax. It makes setting up the required user and group accounts for an Oracle Enterprise or MySQL database solution much easier.
You add the utility by calling the yum (Yellowdog Updater, Modified) utility like this:
yum installed -y system-config_users
You should see the following:
In previous articles I wrote about dealing with a missing cent when you need to divide a certain amount over multiple lines. In these articles, links are at the bottom, I described a method to calculate the difference on the last row.
Then a question arose (as a comment):
What if for example i have 42 records and i wish to divide 100 by 42. I would get a rounded value of 2.38. If (more...)
What happens when you can’t get a PL/SQL Web Toolkit to work because it only prints to a web page? That’s more tedious because any
dbms_output.put_line command you embed only prints to a SQL*Plus session. The answer is quite simple, you create a test case and test it inside a SQL*Plus environment.
Here’s a sample web page that fails to run successfully …
While SQL Developer installs with a dbms_output view, some organizations close it before they distribute images or virtual machine (VM) instances. This post shows you how to re-enable the
Dbms Output view for SQL Developer.
- You need to open SQL Developer, which may look like this when the
DBMS_OUTPUT view isn’t visible.
- You need to click on the View menu option in SQL Developer and choose the Dbms Output dropdown menu (more...)
My vacation from my blog is officially over. The question that I’m answering today is: How can you pass a set of non-sequential ID values to a function and return a result set?
You need to create three object types for this example. They are:
- a list of numbers
- a record structure, declared as an object type without methods
- a list of the record structure
These are the SQL commands to create the required data (more...)
Rounding amounts may lead to rounding-issues, I have written how this may be resolved in a previous blogpost using some analytic functions.
Playing around a little bit, I also came up with a method to resolve the rounding difference with the MODEL clause.
To create an example, first let's create a table with only three records in it.
SQL> create table t
3 select rownum + 42 id
4 from dual
5 connect (more...)
One of my pet-projects is LoggerUtil, which is a utility for Logger, which is an excellent logging tool for PL/SQL.
This post is not about Logger, but some dealings with Conditional Compilation.
With Conditional Compilation you can create a single code base to handle different functionalities depending on compiler flags.
The latest addition to LoggerUtil was a method to create a custom template. For this to work, LoggerUtil depends on a certain Logger Release (where (more...)
Virtual Columns are really cool. I like them a lot. If you've never heard of them, shame on you, learn about them.
In short: a Virtual Column is not a real column, it's an expression that looks like a column... more or less.
While using the Virtual Columns, we ran into a little oddity with them.
First of all let's start with the version of the database that I tested this on. Yes, I know (more...)
Creating database using DBCA silent mode? Learn how to provision a database in a non-interactive mode.
The post Creating Database Using DBCA Silent Mode appeared first on VitalSoftTech.
The article read most often on this blog is called "Splitting a Comma Delimited String, the RegExp way".
On this blog there are two articles about this technique, one is about splitting up a single string into multiple rows and the other is about multiple strings into multiple rows.
Links to both articles are included at the bottom of this article.
It seems like there is a need for functionality like that frequently. And just (more...)
Use DBMS_PARALLEL_EXECUTE to divide one huge task into multiple small tasks that can be executed at the same time. See how many ways are there to divide.
The post Want to SPEED Up Your Database Tasks? DBMS_PARALLEL_EXECUTE to the rescue! appeared first on VitalSoftTech.
All, Its time to be at AIOUG stage once again and the event would be the OTNYathra 2015. The event has been a great success in the past and has been able to generate/receive wide recognition and appreciation. OTNYathra focuses to evangelize the Oracle technologies to a broader and passionate audience. OTNYathra is a grand tour which is conducted … Continue reading
In addition to improving read I/Os, Oracle Exadata Write back flash cache also provides the ability to cache write I/Os directly to PCI flash. Exadata storage software version 184.108.40.206.1 is the minimum version required to use write back flash cache. Grid infrastructure and database homes must run 220.127.116.11.9 or later to use with Write-back Smart […]
The post Configure Oracle Exadata Write Back Flash Cache appeared first on VitalSoftTech.
Oracle Database is one of the leading relational database management system and is used all over the world. It has various training’s, exams and certifications for its Database and other products. However due to regular update in Database, they have to redesign their trainings, certification and exams to add new features and to remove old […]
The post Oracle Database 10g Certifications to Expire in March 01, 2015 appeared first on VitalSoftTech.
Last week I reviewed “Oracle Database XE 11gR2 Jump Start Guide” (Packt Publishing, 2012) in this post. Packt guys did like the review, and today I am pleased to announce that I will be giving away two free e-copies of the book. All you need to do is just comment below the post and win [...]
About a year ago Packt Publishing approached me and offered to write a book on (one of the technologies in) Oracle 11g. After careful consideration I had to decline the offer. Which was the right decision as I have hardly had any spare time in the past 12 months. However, Packt guys have kept in [...]