Oracle

  • 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 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?