-
Hide sqlldr Oracle Password in PS Command
Our internal audit team has raised an issue on some scripts exposing database user, password and tns. You see, all you need is an access to unix shell and run ps command and you’ll be able to get the user id and password of the database. On the otherhand, there’s not a lot of people…
-
UTF_FILE and Directories
In case you are maintaining a pl/sql program developed by someone else and that program writes into a file, then basically that file is written to a folder/directory. And since the directories are saved in codes, you have to find the meaning (or location) of those codes. The code below will help you display the…
-
Oracle Power Function
There are some pl/sql functions that you rarely use and we always forget the syntax.And here’s another example — the power function. You know, that’s the n to the power of x (n^x). Again, I feel a bit ashamed for forgetting this one. Here’s the syntax.
-
Rowcount on Oracle Dynamic SQLs
A good friend asked me on how to capture the number of affected records on his delete statement. The answer would be as simple as below: DELETE FROM my_table WHERE mycolumn = ‘mysearch’; v_rec_count := SQL%ROWCOUNT; On this case, SQL%ROWCOUNT will return the number of affected records in the immediate preceding statement. It is the…
-
Update Oracle Sequence Last Number
I only realized this last week that direct update of the last number of the sequence. Or maybe I just don’t know so what I did was to create a workaround.
-
Input truncated to 1 characters
That pesky though harmless message “Input truncated to 1 characters” keep on appearing on one of my scripts and I can’t figure out what’s the problem. It turned out that the last line of my script is the slash (/). An extra new line is needed after the slash so that this message is suppressed.…
-
Oracle MERGE command
Oracle MERGE command was added in 9i but I only learned about it lately. This command is sometimes referred to as UPSERT because in one statement, you can both update and insert records into a target. Based on the matching condition, MERGE can update existing records if a match is found or insert new record…
-
Oracle PL/SQL Continue Statement in Loops
The CONTINUE statement is very handy feature when programming with loops in Oracle PL/SQL. Basically, it lets you skip to the next iteration of your loop (i.e., next record in the cursor loop). Unfortunately, it was only added in 11g Release 1 (11.1). This is stated in the Language Reference of that version. Below is…
-
Run SQL Script via SQL*Plus via Prompt
If you want to run an SQL script via sqlplus in the shell prompt, the command is as below. sqlplus user/pass@connect @scriptname Don’t forget that there should be an exit command at the end of the sql. Otherwise, the sqlplus will not exit and just stop running until you manually call exit. Now, if the…
-
Drop/Remove Column in Oracle Table
Here’s a quick post on how to drop a column/field in a table. Dropping One Column alter table table_name drop column col_name1; Dropping Multiple Columns alter table table_name drop (col_name1, col_name2); Simple, eh?
Recent Comments