17 Jan 2013 @ 11:26 PM 

In this post, we’ll talk about fetching collections of data into structures, using CURSORS and FETCH.

To fetch a collection of data row by row, consider the following block of code –

declare

cursor cursor1 is
select tabname, defname from REFTABLE order by tabname,refno;
tableName REFTABLE.tabname%TYPE;
expresion REFTABLE.defname%TYPE;

begin

open cursor1;
loop

fetch cursor1 into tableName,expresion;
EXIT WHEN cursor1%NOTFOUND OR cursor1%NOTFOUND IS NULL;

dbms_output.put_line(cursor1%ROWCOUNT || ‘. ‘ || tableName);
— INSERT FURTHER PROCESSING HERE

end loop;
close cursor1;

end;

In case you are dealing with huge collections and you only want to process the first 100, substitute the following line with the ‘EXIT WHEN’ in the code above –

EXIT WHEN cursor1%NOTFOUND OR cursor1%NOTFOUND IS NULL OR cursor1%ROWCOUNT>100;

CURSOR ATTRIBUTES (such as NOTFOUND) can be used to control the fetching behaviour.

The above code block is a useful way of fetching collections of data row-by-row. In the next post we’ll talk about BULK COLLECT, which fetches entire collections much faster.

Posted By: Kevin
Last Edit: 17 Jan 2013 @ 11:26 PM

EmailPermalinkComments (0)
Tags
Tags:
Categories: basics, Snippets, SQL
 09 Jul 2012 @ 10:53 PM 

Improper handling of NULL values is a common cause of application failure. This is true for applications built on both Oracle and Sybase.

We’ll talk about how to avoid such problems when building where clauses in your SQLs.

Firstly, a NULL value is a value that is not known. A NULL value cannot be compared correctly with other values- even with another NULL value.

When building your where clauses, use the IS NULL and the IS NOT NULL statements. The following example can be used to compare columns between 2 tables –

select T1.COL1, T2.COL1 from TABLE1 T1, TABLE2 T2 where
(T1.COL1<>T2.COL1) or (T1.COL1 is null and T2.COL1 is not null) or
(T1.COL1 is not null and T2.COL1 is null)

Another solution is to use the DECODE statement. To perform the above operation, consider –

select T1.COL1, T2.COL1 from TABLE1 T1, TABLE2 T2 where
(decode(T1.COL1,T2.COL1,1,0)=0)

Depending on the needs of your application, you may need to handle NULL values in different ways. NVL can be used in select statements to replace returned NULLs with a user defined expression.

Posted By: Kevin
Last Edit: 09 Jul 2012 @ 10:57 PM

EmailPermalinkComments (0)
Tags
Tags: ,
Categories: basics, Snippets
 12 Jan 2012 @ 8:00 PM 

Hello all,

Today a fun -really, you can use this in pranks and all- stuff.

Imagine you have a script, that will need deactivation. You do not want someone to find the deactivator easily (if you want to “lock” a script for example).

There are may ways to do this, one easy one is to create a script with name ” ” (space) and to execute it in your main script.

To create your space script :

cat >\
echo ” You should not start this script”
exit 0

and then, you add the following line somewhere, for example after the functions.

. ./\

The script will exit with the message at that place, and it is very difficult for the user to find the cause.

Another option can be to  encrypt the script using crypt (you can install it if you do not know where it is) so that the user cannot use grep on all files to find the blocking one. You simply decrypt the file before running it (this can be done by your “space” script.

Oh, yeah, and a quick important note, on a related but different issue.

If you want to deactivate the following function :

function removeall {
rm -Rf .
}

DO NOT try to comment it out like this  :

#function removeall {
rm -Rf .
}

 Because this will simply remove everything every time we try to load your library !
Do it like this :

function removeall {
return 0
rm -Rf .
}

This is all for today, tomorrow we will talk about the misuse of the tr function.

Thank you for reading, and see you tomorrow !

Posted By: Dimi
Last Edit: 12 Jan 2012 @ 08:54 PM

EmailPermalinkComments (1)
Tags
Tags: , ,
Categories: Bash, basics, Snippets
 05 Jan 2012 @ 8:00 PM 

Hello all,

The last post before a long week-end (I will not be able to post tomorrow, and on Monday and Tuesday. I’ll see you on wednesday.

Today, the topic is “The difference between find -exec and find | xargs”

The situation

You have to look into a specific directory and subdirectories for all the files named “*log” because you are quite sure one of them contains the word “error”.

You have two ways to do this :

1 – using -exec

This is a parameter to the find commands that allows you to do an extra command on the found files. The syntax is as follow :

-exec <command> {} \;

<command> replaces your command

{} will be replaced by the name of the found file.

\; terminates the command

i.e. you type :

find . -name \*log -exec grep -i error {} \;

and it will return all the lines containing error, regardless of the case.

2 – using xargs.

xargs is a command that allows the piped data to be passed as parameter to another command. The syntax is as follow :

| xargs <command>

xargs simply puts at the end of the command the piped data. i.e. you type :

find . -name \*log | xargs grep -i

3 – Using a while loop.

Yes, you can do like that, but it is not the topic of this discussion.

 

The difference

What is the main difference between the two?

-exec is going to take each file, and execute the command with it. Using this, you will get a list of all the lines, but not the name of the file, as grep assumes you know which file he talks about, as you have passed the name as parameter !

A sub process will be spawn for each file to be checked.

xargs, on the other end, is going to pass the complete list of files to grep. The name of the files will be shown in the result list.

The separator for the parameters will be the space, and this is OK as long as there is no space in the names of the files (but who puts spaces in the names of the files ? Ah, ok, users…).

In fact, the fail proof  way to deal with this specific request is to make the while loop.

 

The conclusion

Both ways can be useful, depending of the request and the situation. It is important that you understand the different ways those tools work, so that you can choose which one is the best for your usage.

Thank you for reading, and see you next wednesday ! (told you, long week-end for me 🙂 )

 

Posted By: Dimi
Last Edit: 02 Jan 2012 @ 05:17 PM

EmailPermalinkComments (0)
Tags
Tags: , , , ,
Categories: Bash, basics, Snippets
 22 Dec 2011 @ 8:00 PM 

As you know, Unix has different ways of chaining commands.

The most well-known way to chain commands in Unix is the pipe (|).

Using a pipe, the output (stdout) of the first application is sent as input (stdin) of the second command, and it is done like this :

echo “hello world !” | sed -e ‘s/h/H/’

(which is, by the way, a difficult way to do something simple)

If this is something new for you, you should write it down immediately : it is bound to come in any interview you will do if you have “unix”,”linux” or something equivalent in your resume…

File Descriptors Reminder (Skip it if you understand 2>&1)

A File descriptors are the way Unix is communicating with you. The main ones (the default ones) are 1 for stdout and 2 for stderr.

If you do the following :

find / -type f

You will get a lot of error messages (if you are not root. If you are root, you should change to your own user immediately, and respectfully tell your System Administrator that you have done something bad. [And, by the way, you should always talk respectfully to your System Administrator])

The normal messages will be sent to 1>, the errors will be sent to 2>, which means that you see both on screen, because both are redirected to your screen by default. If you do this :

find / -type f >/tmp/find.out 2>/tmp/find.err

then the errors will be sent to find.err, and the stdout will be sent to find.out.

Now, how would you send both outputs to the same file ? One way is this :

find / -type f >>/tmp/find.all 2>>/tmp/find.all

But it’s long, and we are lazy. The other way to do is this :

find / -type f >/tmp/find.all 2>&1

which means “Send stdout to find.all and stderr to stdout”. &1 represent stdout. The following would work as well :

find / -type f 2>/tmp/find.all >&2

OK, let’s go back to pipe now.

Pipes and file descriptors

The pipe only passes stdout (1>) which means that whatever you have in the stderr (2>) is not redirected.

Let’s imagine you do a find, then you pipe it to grep “toto”, case insensitive because your find is not able to do it :

find . -type f | grep -i toto

If you do not have the rights on certain files or directories, some error messages will pour, and you think you can get rid of them by doing :

find . -type f | grep -i toto 2>/dev/null

Well, no luck. Error messages are still there, as the stderr is not passed through the pipe… The command that gets rid of the error messages generated by find is :

find . -type f 2>/dev/null | grep -i toto

Pipes and return code

The return code of a pipe is always the last command to be on the line of pipe, none of the other will return something to the next line (it is returned to the next piped command, if you want to check it, but is is pretty useless).

This was another one of the basics, I hope you enjoyed it !

 

Posted By: Dimi
Last Edit: 21 Dec 2011 @ 09:33 AM

EmailPermalinkComments (0)
Tags
 20 Dec 2011 @ 8:00 PM 

Ok, a quick one today.

If you have never used sed, it is time to do so.

sed stands for Stream EDitor. It is a way to make quick changes in a file or a stream (between pipes). It is extremely powerful, and very fast.

Its main function is to search for strings, then apply changes to it. A small example :

sed -e ‘s/hell/heaven/g’ file >file2

This would parse the file, and changes all the occurrences of “hell” by “heaven“. Try to do that without sed (even awk will make it quite difficult).

On the above example, the ‘-e‘ is not useful. You can forget it, but I prefer to put it all the time, because it becomes mandatory if you want to chain sed commands, i.e. :

sed -e ‘s/hell/heaven/g’ -e ‘1d’ file >file2

This would replace the “hell” with “heaven” in the file, then delete the first line of the file.

Some version of sed allow for in-place changes, meaning that you do not have to use a second file to write the output. However, this is not available in all versions and systems, so I would discourage the use if you are moving from one server to another.

The real power of sed are the REGEX, or REGular EXpressions.

Small example : Imagine you want to comment out all the scripts called “Boom.sh” that are called inside the other script “Badaboom.sh”, a script 123000 lines long… How do you do that ? vi ? vim ? notepad ?

sed -e ‘/Boom.sh/s/^/# /’ Badaboom.sh >Badaboom2.sh

Done !

The first part of the command ‘/Boom.sh/’ searches for all the lines that contains the string between the slashes.

The second part of the command ‘s/^/# /’ replaces, on the found line, the beginning of the line (^) with a hash-space string (# ).

REGEXs can become very useful, and if you are working with Unix, it is time for you to know the basics of this super tool sed.

 http://www.grymoire.com/Unix/Sed.html

Thank you for reading,

Dimitri

Posted By: Dimi
Last Edit: 21 Dec 2011 @ 09:34 AM

EmailPermalinkComments (0)
Tags

 Last 50 Posts
Change Theme...
  • Users » 66
  • Posts/Pages » 25
  • Comments » 4
Change Theme...
  • VoidVoid « Default
  • LifeLife
  • EarthEarth
  • WindWind
  • WaterWater
  • FireFire
  • LightLight