Posts

Posted on

Oracle CLOB and concatenate

Ran into an PL/SQL error when dealing with a clob variable. The following PL/SQL will cause an error as c_info is more than 32k. This should not be a problem as c_info is a clob. However, because the concatenate (||) is in there, the program is treating it as a varchar.

DECLARE
c_info CLOB;
BEGIN
FOR i IN 1 .. 32000
LOOP
c_info := c_info ||to_char(i);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DECLARE
V_ATTACH_ERROR_CHAR_COUNT NUMBER;
BEGIN
V_ATTACH_ERROR_CHAR_COUNT := DBMS_LOB.getlength (c_info);
DBMS_OUTPUT.PUT_LINE (
‘Variable Length:’
|| V_ATTACH_ERROR_CHAR_COUNT);
END;
END;

The fix for this is to change this
c_info := c_info || to_char(i);
To this:
c_info := c_info || to_clob(to_char(i));

If there are additional variables needed in the concatenate string, then each section needs to be within a to_clob to keep everything as a clob.

Special thanks to https://stackoverflow.com/questions/49906725/using-clob-gives-ora-06502-pl-sql-numeric-or-value-error/49906909#49906909 for the solution!

Posted on

Remove timestamp from file name

This script took a bit of work…

I receive files that are in the format of:
file_MMDDYYYYHHMIS.csv

Example: file_09022022102012345.csv

OK, so I’m not quite sure what the last digits are…all I knew is that I had to strip 9 digits out of the filename in order to just get file_date.csv

Here’s the code I used:

for file in *.csv
do
# For future reference, look for 9 numbers followed by a
# .csv and then replace that with .csv
# sed requires the {}s to be escaped, along with the .
new=$(echo $file | sed -e ‘s/[0-9]{9}.csv$/.csv/’)
# echo OLD$file
# echo NEW$new
mv $file $new
done

Posted on

Vroom Car Review

I’ve been looking at buying a new car and, in Mid-November, I found the perfect car on www.vroom.com! It fit all of the items I was looking for: make, model, miles, price, and it even already had a hitch on it!

Perfect. I registered myself on the site and was able to get the car reserved. The salesman asked me to send in some documents, which I promptly did to keep the process moving. I was hoping to have the car within 2 weeks, which was right where they say is the longest you should have to wait for a vehicle to be delivered. After sending the documents in via their online chat system, I received the message “we are good to go brother!”. Yipee! Per their FAQ, I should receive the actual contracts within 3 days.

Then the problems began. Day 2.5, I sent a message asking for the status. Crickets. Day 3 I both emailed and send a chat message asking for my salesperson to call me. Nothing. So, I called and sat on hold for nearly 40 minutes to see what the status is. I finally got a person who stated I still needed to send in one more document. They said they’d send me a link to allow me to upload the document to them within 24 hours. More than 24 hours went by and no link.

The status on their “Hub” still says that all documents have been turned in and that they are working on the contracts. Time for me to call and just cancel the whole deal. After sitting on hold for a while (now that I’ve determined which phone number on their site actually works), I explained the situation and they canceled my order. I was told I’d get an email right away summarizing our conversation and then another email within 72 hours saying what I need to do to get my deposit back. I received the first email…it asks for me to confirm my address and when I’d be available for them to pick the car up from me. Say what?

I responded to that email making sure they knew I did not have the car. Still no response from them.

After a couple of weeks, especially with the Thanksgiving holiday mixed in, I gave up with trying to get a hold of Vroom to get my deposit back.

On 12/13, I had a weird phone call. My assumption is that someone else purchased the Subaru I was looking at. I received a call from a shipping company asking to verify my address and to make sure I’d be home later today. The address they had me verify was a few states over from where I’m at and I definitely did not purchase the car.

Long story short, I’d never recommend www.vroom.com to anyone. If anyone from Vroom reads this, you have my contact information…

The good news – I did find a dealer that was nearby who had an even better car and gave me for for my trade-in than what Vroom was offering!

Posted on

Oracle install issue – Resolved!

I was trying to install the Oracle Client on a computer. I downloaded it to “C:\Oracle Download”. Don’t do that. You’ll get a “Error: Unable to access jarfile C:\Oracle” error when you try to install. Renaming the download directory to “C:\Oracle_Download” fixed the issue.

Posted on

Oracle seqno column using Oracle Sequence

Sometimes the value in an Oracle Column is based on the value of an Oracle Sequence. This is a quick and easy way to make a unique key. The column value is usually automatically determined using a table trigger.

However, it can sometimes get out of sync. Here’s the code to fix it:

DECLARE
  last_used  NUMBER;
  curr_seq   NUMBER;
BEGIN
  SELECT MAX(pk_val) INTO last_used FROM your_table;

  LOOP
    SELECT your_seq.NEXTVAL INTO curr_seq FROM dual;
    IF curr_seq >= last_used THEN EXIT;
    END IF;
  END LOOP;
END;

Credit goes to: https://stackoverflow.com/questions/6099108/best-way-to-reset-an-oracle-sequence-to-the-next-value-in-an-existing-column

Posted on

Still here

I realized I haven’t updated in quite some time. I’m still here and have just been very busy.

Take care and stay healthy!

Posted on

Find Unix File by Date or Time

I needed to find all files in my Unix directory that were created within a certain timeframe.

Here’s how to find all that were created on a set date:

find -type f -name “*” -newermt “2019-07-01” ! -newermt “2019-07-02” -ls

Here’s how to find within a time frame:

find -type f -name “*” -newermt “2019-07-01 00:00:00” ! -newermt “2019-07-01 08:00:00” -ls

It finds all files “newer than” the first value, but then excludes any files that are “newer than” the second value.

Posted on

Find UNIX files in the past day that contains a keyword

Trying to find a file that was modified in the past day that contains a certain keyword…

Here’s the trick:

find -type f -name “<filename search string>” -mtime -1 -exec grep -l “<keyword>” {} \;

Where filename search string is any help you can give it to filter down the file type, such as *.txt or s* and keyword is the phrase you are searching for.

mtime -1 could be modified to be any time frame, such as the past week (mtime -7).

Posted on

CSS Buttons

I was looking for a GIF image of a + and a X with a box around it.  After some searching, I found an easier way to customize one…use CSS.

Here’s the class code:


.red_button {
background-color: red;
border: none;
color: white;
padding: 2px 2px;
text-align: center;
text-decoration: none;
display: inline-block;
margin: 2px 2px;
cursor: pointer;
font-size: 10px;
width: 20px;
height: 20px;
}

.green_button {
background-color: green;
border: none;
border-radius: 10px;
color: white;
padding: 2px 2px;
text-align: center;
text-decoration: none;
display: inline-block;
margin: 2px 2px;
cursor: pointer;
font-size: 10px;
width: 20px;
height: 20px;
}

The red button is a square and the green one is a circle.