Oracle Single Row Character Functions Returning Character Values

Exam Topic: Using Single-Row Functions to Customize Output - Describe various types of functions available in SQL Single row functions return a result for every row in a result. They can be used in any part of a SQL query, such as the SELECT lists or any part that can use a conditional statement. Oracle 11g has … Continue reading Oracle Single Row Character Functions Returning Character Values

Dignity

What is dignity? Is it a right or a perspective? It is both. The word "dignity" is overloaded and is the name of two unrelated concepts. The perspective is more about opinion of worthiness. The right is the foundation of what today is called Human Rights. "...Recognition of the inherent dignity and of the equal … Continue reading Dignity

Oracle’s SRVCTL: Enable an Instance. Make an Instance Preferred. Check a Service’s Status.

How to use the Oracle Server Control (srvctl) to enable instances, and see configurations and statuses. Enabling an Instance # srvctl enable instance -d [unique database name] -i "[instance1],[instance2]" Enables instances. If repeated, get messages, PRCC-1011: [db] was already enabled on [server name 1], [server name 2] PRCR-1002: Resource [resource name] is already enabled See … Continue reading Oracle’s SRVCTL: Enable an Instance. Make an Instance Preferred. Check a Service’s Status.

Basic Algebraic Functions

The following is a mini-workout of the basic Algebraic functions found in Oracle 11g. It includes ABS, CEILING, FLOOR, MOD, REMAINDER, NANVL, POWER, ROUND, SIGN, TRUNC and BUCKET_WIDTH. More functions can be found here. ABS (Absolute Value) This function returns the number of positions a number is from zero. select abs(-10) from dual; ABS(-10) ---------- 10 … Continue reading Basic Algebraic Functions

Oracle srvctl Basics – Listener and Database Commands

When a database is getting started, generally the listener control service (lsnrctl) is started and the database is started by logging into the database and executing the startup command. If you're in a RAC configuration, you should be using srvctl; this command knows your configuration and works on the entire RAC environment instead of just … Continue reading Oracle srvctl Basics – Listener and Database Commands

Convert Decimal to Other Number Systems

Just for fun, I made a block of code that will convert decimal numbers to any other number system. Maybe I'll go the other direction next. It's basically a loop. It takes the remainder of the original number divided by the number system (MOD) and adds that to a string. The string is made by … Continue reading Convert Decimal to Other Number Systems

110-Year-Old Computer that Plays Music: the Marenghi 81-Key Organ

To quote Wikipedia, "A computer is a general-purpose device that can be programmed to carry out a set of arithmetic or logical operations automatically. Since a sequence of operations can be readily changed, the computer can solve more than one kind of problem." Given the early devices seen over the centuries, I think this is … Continue reading 110-Year-Old Computer that Plays Music: the Marenghi 81-Key Organ

Generate Random Values

One of the best builtin packages in Oracle for anyone who needs to create random data is DBMS_RANDOM. Not only can you use it to create random values, but you can also use it to make random selections. For instance, if you have a list of values that you want to assign randomly, you can weight … Continue reading Generate Random Values

Nested Tables as Collection Data Type and a Table Column

Sometimes, a table is designed with one column that can hold an array. An array in database design is a table. So, in Oracle, you can create a type as a table and then use this type as a column in another table. What we end up with is a nested table as a table column … Continue reading Nested Tables as Collection Data Type and a Table Column

Oracle Recycle Bin: BIN$ and RB$$ files

Ever query all_source and come up with a strange object name that starts with BIN$ or RB$$? That is the recyclebin, holding on to tables and indexes you have deleted in the past until they are either released or aged out. If you are trying to make space by dropping objects, be sure to purge … Continue reading Oracle Recycle Bin: BIN$ and RB$$ files