Browse: Home » Blog » PL/SQL

PL/SQL

  • Hide sqlldr Oracle Password in PS Command

    February 10, 2012 Respond

    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

    November 9, 2011 Respond

    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

    November 7, 2011 Respond

    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

    August 25, 2011 Respond

    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

    May 31, 2011 Respond

    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

    April 12, 2011 Respond

    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

    March 2, 2011 Respond

    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

    February 10, 2011 1

    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…

  • Enable/Show DBMS_OUTPUT in PL/SQL

    October 15, 2010 Respond

    The long and short of it is that the command is as below. SET SERVEROUTPUT ON There’s quite a misconception between DBMS_OUTPUT.ENABLE and SERVEROUTPUT directives. The SERVEROUTPUT tells Oracle to dump the buffer(i.e, sent via DBMS_OUTPUT.PUT_LINE) while the DBMS_OUTPUT.ENABLE starts the buffer messages which you may access by GET_LINE/GET_LINES. SERVEROUTPUT directive does two things: Issue…

  • Convert Rows to One Column

    August 7, 2010 Respond

    Converting rows to column is a breeze if you are using Microsoft Access or reporting tools like Oracle Reports Builder. However, there are times that you’ll also need it for something else. I mean like displaying rows to one column in a SQL*Plus report. So how leh? (imagine that saying with a Singaporean accent)