How to reference a shell variable named by another shell variable

Have you ever had the need to get the value of a shell variable, but the name was all, or partly, determined by another shell variable. That happened to me this week and once you know how to do it it is pretty simple, but it is hard to figure it out.

I’ll try to clarify. I have a shell variable that has been set with a password. To find that variable I have another variable. The second variable is the User Name and is called USER. The first is the password and is called USERPW. (Note: I’m not saying this is the best way to handle passwords, it is what I had to work with.)

In a given situation I’ll need the password for a user that is provided by the variable USER. I thought I could get this in a variety of ways:

$${USER}pw}
${${USER}PW}

etc…

To end the story and give you what you came looking for. Here is the solution (in a test script):

->cat deref.ksh
#!/bin/bash
export USER=MYUSER
export USERPW=
. /somewhere/pwds.sh # this script sets environment variables with passwords
echo echo deference
eval USERPW=\$${USER}PW
echo ${USER}
echo ${USERPW}
sqlplus -s ${USER}/${USERPW} << eof
show user
eof

Here is the output that shows it worked. The last line is the output from the Oracle database.

->./deref.ksh
echo deference
MYUSER 
mysupersecret
USER is "NDTC_DBA"

Oracle Shrink Space, don’t do it just once

I’ve always noticed shrinking a table more than once can benefit you, this was the first I’ve seen it makes things worse, and then better, read on here…

Unique constraints, NULL, and where SQL got it wrong (imho)

Most databases allow NULL values in a unique constraint. I like this because sometimes you really do need to do that. The problem is that they don’t consider NULL as a unique value in that constraint. That means that you can have multiple rows with the same “values” (I know, NULL is not really a value), but in some uses it is.

I have a table with four columns that need to be maintained as unique, but where some of the columns could be NULL indicating a total lack of value for that column. Due to the proprietary nature I can’t give the exact table details.

The columns are parent_id, metric_id, object_id, item_id

parent_id and metric_id are NOT NULL, they must have a value. object_id and item_id could be NULL indicating a “default” value – in other words if you want to lookup something and you can’t find a match on all four columns, you can search for a partial match where object_id and/or item_id are NULL. Objects and items have foreign keys to their parent tables so they have to have valid values, I can’t just put an empty value in there.

This means I can have

parent_id    metric_id       object_id    item_id
1            10              2123         39392
1            11              NULL         NULL
1            11              NULL         NULL


and that is not valid because I now have two “duplicate” values. I’m trying to think of a reason you’d ever want to allow that type of duplicate.

From what I’ve read SQL Server would not allow this, so kudos to SQL Server.

Feel free to add your thoughts or ideas.

 

MySQL 5.6 GET DIAGNOSTICS – Getting the MySQL warning, error, or exception that just occurred

The GET DIAGNOSTICS statement allows you to get an error message in MySQL like you would in Oracle PL/SQL with SQLCODE and SQLERRM … I needed a way to trap warnings and errors in a MySQL procedure … (click here to read more)

MySQL interval math with subqueries

Do you need to run a query with a date/time or timestamp comparison using interval math but have the value for the time period in another table? Do you wan to do interval math with sub-queries? You’d might be surprised, because in MySQL it really is as simple as it sounds. Click here to see…

ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY

If you have excessive growth of the SYSAUX tablespace or have encountered “ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY” then check this out. Read more here…

Regarding the Washington Redskins…

What if there were the:

* New England Crackers

* Atlanta Negros

?