UTF_FILE and Directories

November 9, 2011 By Leave a Comment

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 information required.

SELECT * FROM ALL_DIRECTORIES;

The query above will display all the directories accessible to the current user. They are folders in the server where you can write your output files. For some reason, I forgot about this twice already and had to scour the web to find it. The problem is, I also forgot the keywords. So, I’m writing it now.

To register new directories, you can follow the example below.

Example
create or replace directory mydir as 'c:\mydirectory';

To use, run the code below.

declare
f utl_file.file_type;
begin
f := utl_file.fopen('MYDIR', 'myfilename.txt', 'w');
utl_file.put_line(f, 'just another line text 1');
utl_file.put_line(f, 'just another line text 2');
utl_file.fclose(f);
end;

Incoming search terms:

  • utf_file
  • utl_file all_directories
Filed Under: Oracle, PL/SQL Tagged With: , , , , ,

Oracle Power Function

November 7, 2011 By Leave a Comment

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.
[Read more...]

Incoming search terms:

  • oracle power
  • oracle power function
  • oracle power function negative
  • oracle power functions
  • oracle sql display power
  • power function in sql oracle
  • power function pl/sql integers
  • power oracle binary_double ask tom
  • powerc oracle
Filed Under: Blog, Oracle, PL/SQL Tagged With: , ,

Rowcount on Oracle Dynamic SQLs

August 25, 2011 By Leave a Comment

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

equivalent command for @@rowcount in Sybase/SQL Server.

However in my friend’s case, the DELETE statement is part of the dynamic SQL. Obviously, we cannot use the approach

above as it is. Here’s how to capture the affected records in dynamic SQLs.


vSQL := 'DELETE FROM ' || v_SchemaName || '.' || v_TableName || ' WHERE ' || v_Conditions || ‘;’;
EXECUTE IMMEDIATE ‘BEGIN ’ || vSQL || ‘ :z := sql%rowcount; END; ’ USING OUT nRecCount ;

Basically, we have to capture the rowcount from within the dynamic sql. That means you have to pass the variable via “OUT” parameter to flow the value out of the execute immediate. You can’t just fire nRecCount := sql%rowcount after the execute immediate because with that, the last command that ran is the execute immediate. It will return 1 instead of the actual value from the dynamic sql.

Source :

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:497908700346519628

Incoming search terms:

  • oracle rowcount
  • oracle sql rowcount execute immediate
  • %rowcount
  • rowcount in oracle
  • rowcount oracle
  • oracle execute immediate rowcount
  • return rowcount sybase sql
  • return type of rowcount in oracle
  • rowcount in oracle pl sql
  • rowcount oracle sql
Filed Under: Blog, Oracle, PL/SQL

Update Oracle Sequence Last Number

May 31, 2011 By Leave a Comment

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.
[Read more...]

Incoming search terms:

  • alter sequence last number
  • update sequence oracle
  • oracle update sequence last_number
  • oracle update sequence
  • alter sequence LAST_NUMBER
  • oracle sequence change last number
  • how to update oracle sequence last_number
  • update sequence in oracle
  • update sequence
  • update oracle sequence last_number
Filed Under: Blog, Oracle, PL/SQL

Input truncated to 1 characters

April 12, 2011 By Leave a Comment

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.

To be clear, here’s the screenshot.

[Read more...]

Incoming search terms:

  • Input truncated to 1 characters
  • input truncated to 1 characters error
  • sql input truncated to characters
  • input truncated to 1 characters oracle
  • input truncated to 1
  • input truncated to one character
  • input truncated to 4 characters
  • input truncated to 10 characters in oracle
  • input truncated to 1 characters sql
  • Input truncated to 1 characters pl/sql means
Filed Under: Blog, Oracle, PL/SQL

Oracle MERGE command

March 2, 2011 By Leave a Comment

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 if not. Prior to 9i, we need separate sql statements or a PL/SQL program to do this.

[Read more...]

Incoming search terms:

  • oracle merge statement
  • merge oracle
  • oracle merge
  • oracle merge statement in loop
  • oracle merge statement tech on the web
  • oracle merge statement when not matched then print message
  • oracle merge tech on the net
  • oracle merge when matched then insert
  • oracle user if in merge statement
  • pl/sql merge log
Filed Under: Blog, Oracle, PL/SQL Tagged With: , , , , , ,

Oracle PL/SQL Continue Statement in Loops

February 10, 2011 By 1 Comment

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 the example.

SQL> DECLARE
2 x NUMBER := 0;
3 BEGIN
4 LOOP — After CONTINUE statement, control resumes here
5 DBMS_OUTPUT.PUT_LINE (‘Inside loop: x = ‘ || TO_CHAR(x));
6 x := x + 1;
7
8 IF x < 3 THEN
9 CONTINUE;
10 END IF;
11
12 DBMS_OUTPUT.PUT_LINE
13 ('Inside loop, after CONTINUE: x = ' || TO_CHAR(x));
14
15 EXIT WHEN x = 5;
16 END LOOP;
17
18 DBMS_OUTPUT.PUT_LINE (' After loop: x = ' || TO_CHAR(x));
19 END;
20 /

[Read more...]

Incoming search terms:

  • pl/sql continue
  • continue oracle
  • pl/sql loop continue
  • continue in oracle
  • continue in oracle pl sql
  • plsql for loop continue
  • pl sql for continue
  • pl sql continue
  • oracle sql loop dbms_output put_line
  • oracle pl/sql continue
Filed Under: Blog, Oracle, PL/SQL

Run SQL Script via SQL*Plus via Prompt

February 9, 2011 By Leave a Comment

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 SQL script does not have exit at the end, you’ll have to do the following.

echo exit | sqlplus user/pass@connect @scriptname

This is another trivial thing that can be easily forgotten.

Incoming search terms:

  • sqlplus does not return
  • sql script name in shell script exit from sql prompt
  • sqlplus -s << end in shell script
  • sqlplus does not call script
  • execute sql script in shell
  • sqlplus invoke shell script
  • sqlplus no exit on end of script
  • sqlplus prompt exit
  • sqlplus run sql script
  • script sql *plus
Filed Under: Blog, Linux, Oracle

Drop/Remove Column in Oracle Table

August 11, 2010 By Leave a Comment

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?

Filed Under: Blog, Oracle

Convert Rows to One Column

August 7, 2010 By Leave a Comment

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)
[Read more...]

Incoming search terms:

  • convert rows to columns in oracle
  • convert row to column in oracle
  • oracle rows to columns
  • oracle rows to one column
  • oracle select rows to column
  • oracle xmlagg
  • oracle xmlagg row to col
  • report builder convert rows to columns
  • row to col oracle
  • row to column in oracle
Filed Under: Oracle, PL/SQL Tagged With: ,
« Older Posts