Mongol IT: Insert image as BLOB into DB using stored procedure.

You need first to declare your package :

CREATE OR REPLACE PACKAGE IMAGE AS
PROCEDURE load(filename VARCHAR2);
PROCEDURE get(name varchar2);
end;
/

then you specify your package
CREATE OR REPLACE PACKAGE BODY IMAGE AS

PROCEDURE load(filename VARCHAR2) AS
f_lob BFILE;
b_lob BLOB;
image_name VARCHAR2(30);
mime_type VARCHAR2(30);
dot_pos NUMBER;
BEGIN
dot_pos := INSTR(filename,’.’);
image_name := filename; –SUBSTR(filename,1,dot_pos-1);
mime_type := ‘image/’||SUBSTR( filename,dot_pos 1,length(filename) );

INSERT INTO images values(image_name,mime_type,empty_blob() )
RETURN content INTO b_lob;
f_lob := BFILENAME(‘SOURCE_DIR’,filename);
dbms_lob.fileopen(f_lob,dbms_lob.file_readonly);
dbms_lob.loadfromfile(b_lob,f_lob,dbms_lob.getlength(f_lob) );
dbms_lob.fileclose(f_lob);
COMMIT;
END;

PROCEDURE get(name varchar2) AS
vblob BLOB;
mime_type VARCHAR2(30);
BEGIN
select content,mime_type
into vblob,mime_type
from images
where image_name=name;
owa_util.mime_header(mime_type, false);
owa_util.http_header_close;
wpg_docload.download_file(vblob);
exception when others then
htp.p(sqlcode || sqlerrm);
END;

END;
/

But this code doesn’t seem to fit your need because you said that you would like to store the c:mydirmyfile.jpg from you computer (client side) into blob field in database (server side), and with this package , you need to create on server side (data base) a source directory (SQL> create directory source_dir as ‘/mydir’; –if you have an image file in /mydir/toolbar.gif) in which you will store all yours file you need to load in blob field with this kind of statement :
SQL> exec image.load(‘toolbar.gif’);

So with this exemple you can not load ‘client-side’ file in ‘server-side’ blob field, and AS ME you would like to load from client side , if someone would have a suggestion, he/she would answer to a problem which concern 2 persons now.

OAuth and the PL/SQL

OAuth by the PL
Message from dmoraschi@gmail.com:

OAuth signatures in PL/SQL

This article is intended as first of a series to explain how to interact with those services wich require oauth authentication, most famous being LinkedIn, Twitter etc.

The OAuth standard is a little complex, and explaining it is out of the scope of this article, there are some very good tutorials out there, which include:

  1. Authenticating Requests with OAuth
  2. Amazon Products API Request Signing

 

In order to test the code in this article we will use the OAuth test cases as in the wiki page: http://wiki.oauth.net/TestCases

 

Consumer Secret

Token Secret

Base String

Signature

cs

bs

egQqG5AJep5sJ7anhXju1unge2I=

cs

ts

bs

VZVjXceV7JgPq/dOTnNmEfO0Fv8=

kd94hf93k423kf44

pfkkdhi9sl3r4s00

GET&http%3A%2F%2Fphotos.example.net%2Fphotos&file%3Dvacation.jpg%26oauth_consumer_key%3Ddpf43f3p2l4k3l03%26oauth_nonce%3Dkllo9940pd9333jh%26oauth_signature_method%3DHMAC-SHA1%26oauth_timestamp%3D1191242096%26oauth_token%3Dnnch734d00sl2jdk%26oauth_version%3D1.0%26size%3Doriginal

tR3+Ty81lMeYAr/Fid0kMTYa/WM=

 

First step is to create a function that is able to calculate an oauth signature, which is -by far- the most challenging task. Most common encription type in oauth is HMAC-SHA1.

In Oracle tere is a package named DBMS_CRYPTO that does it all.

The MAC function of this package accepts the following parameters:

 

DBMS_CRYPTO.MAC (

  src IN RAW,

  typ IN PLS_INTEGER,

  key IN RAW)

RETURN RAW;

 

In our case the first parameter is the base string, the second is the constant DBMS_CRYPTO.HMAC_SH1 and the third is the consumer secret + token secret.

Before calling the function we need to convert the VARCHAR2 string we have to a RAW type. This is accomplished by another Oracle package called UTL_I18N with the function STRING_TO_RAW:

 

UTL_I18N.STRING_TO_RAW(     data          IN VARCHAR2 CHARACTER SET ANY_CS,    dst_charset   IN VARCHAR2 DEFAULT NULL) RETURN RAW;

So for example, we calculate the signature of the first test case. Since the charset must always be ‘AL32UTF8’ the base string and consumer secret will be first converted to RAW:

 

select UTL_I18N.STRING_TO_RAW('bs','AL32UTF8') base_string_raw from dual;

BASE_STRING_RAW

——————————————–

6273

 

select UTL_I18N.STRING_TO_RAW('cs','AL32UTF8') base_string_raw from dual;

BASE_STRING_RAW

———————————————

6373

 

then:

 

declare

  oauth_signature VARCHAR2(50);

begin

  select

       dbms_crypto.mac(

               UTL_I18N.STRING_TO_RAW('bs','AL32UTF8')

               , DBMS_CRYPTO.HMAC_SH1

               , UTL_I18N.STRING_TO_RAW('cs&','AL32UTF8'))

       into oauth_signature

       from dual;

  dbms_output.put_line(oauth_signature);

end;

/

 

Note the ‘&’ at the end of the consumer secret. The OAuth standard explains this (9.4.1.Generating Signature: […] “is set to the concatenated encoded values of the Consumer Secret and Token Secret, separated by a ‘&’ character (ASCII code 38), even if either secret is empty.”).

Now we have a signature like 7A042A1B90097A9E6C27B6A78578EED6E9E07B62 which needs to be base 64 ecoded.

Another Oracle package named UTL_ENCODE, has a function base64_encode to do this; then the result must be converted to a VARCHAR2 with UTL_RAW.cast_to_varchar2.

 

declare

oauth_signature VARCHAR2(100);

begin

select  UTL_RAW.cast_to_varchar2(

 UTL_ENCODE.base64_encode(

 dbms_crypto.mac(

  UTL_I18N.STRING_TO_RAW('bs','AL32UTF8')

  , DBMS_CRYPTO.HMAC_SH1

  , UTL_I18N.STRING_TO_RAW('cs&','AL32UTF8'))))

 into oauth_signature

 from dual;

dbms_output.put_line(oauth_signature);

end;

/

egQqG5AJep5sJ7anhXju1unge2I=

 

Thanks to Jimbo1 in the Google Checkout Forum for publishing how to generate the HMAC

Now let’s compute the second example.

 

declare

  oauth_signature VARCHAR2(100);

begin

  select  UTL_RAW.cast_to_varchar2(

       UTL_ENCODE.base64_encode(

       dbms_crypto.mac(

               UTL_I18N.STRING_TO_RAW('bs','AL32UTF8')

               , DBMS_CRYPTO.HMAC_SH1

               , UTL_I18N.STRING_TO_RAW('cs&ts','AL32UTF8'))))

       into oauth_signature

       from dual;

  dbms_output.put_line(oauth_signature);

end;

/

VZVjXceV7JgPq/dOTnNmEfO0Fv8=

 

If SQL*Plus prompts you for the value of ts you need to SET DEFINE OFF before running the block of code.

 

Now we can create a function oauth_signature that always returns the corresponding VARCHAR2 signature string:

 

create or replace function oauth_signature(p_base_string in varchar2, p_consumer_secret in varchar2, p_token_secret in varchar2)

return varchar2

is

v_oauth_signature VARCHAR2(100);

begin

select  UTL_RAW.cast_to_varchar2(

 UTL_ENCODE.base64_encode(

 dbms_crypto.mac(

  UTL_I18N.STRING_TO_RAW('bs','AL32UTF8')

  , DBMS_CRYPTO.HMAC_SH1

  , UTL_I18N.STRING_TO_RAW('cs&ts','AL32UTF8'))))

 into v_oauth_signature

 from dual;

return v_oauth_signature;

end;

/

 

which we’ll call from a SELECT:

select oauth_signature('bs', 'cs', null) from dual; –first example

select oauth_signature('bs', 'cs', 'ts') from dual;  –second example

select oauth_signature('GET&http%3A%2F%2Fphotos.example.net%2Fphotos&file%3Dvacation.jpg%26oauth_consumer_key%3Ddpf43f3p2l4k3

l03%26oauth_nonce%3Dkllo9940pd9333jh%26oauth_signature_method%3DHMAC-SHA1%26oauth_timestamp%3D1191242096%26oauth_token%3Dnnch734d0

0sl2jdk%26oauth_version%3D1.0%26size%3Doriginal', 'kd94hf93k423kf44', 'pfkkdhi9sl3r4s00') from dual; –third example

 

Google Docs makes it easy to create, store and share online documents, spreadsheets and presentations.

Logo for Google Docs

Enviando por correo electrónico: Hey, Scripting Guy! Let There Be Silverlight.htm

TechNet Magazine > Home > Issues > 2007 > October >  Hey, Scripting Guy!: Let There Be Silverlight

Hey, Scripting Guy! Let There Be Silverlight

The Microsoft Scripting Guys

Download the code for this article: HeyScriptingGuy2007_10.exe (156KB)

Human history has long been marked by the clash of diametrically opposed forces: the Romans versus the barbarian hordes; the Hatfields versus the McCoys; the Scripting Guys versus those who believe a column about system administration scripting should actually talk about system administration scripting rather than, say, the historical clash of diametrically opposed forces. [Or, say, meat hooks … —Ed.]
The computer world is not immune to these clashes between diametrically opposed forces. In particular, we’re reminded of the clash between developers and system administrators. As Karl Marx said upon surveying the situation, “Either this man is dead or my watch has stopped.”
Wait, sorry—that was actually Groucho Marx. Karl Marx said, “Art is always and everywhere the secret confession, and at the same time the immortal movement of its ti
me.” To tell you the truth, we have no idea what that means. Besides, Groucho Marx was quite a bit funnier than Karl Marx, even if Groucho’s quips aren’t particularly relevant to the clash between developers and system administrators.
Nevertheless, we’re pretty sure that it was Karl Marx who used to make the Scripting Guys recite the automation pledge of allegiance every morning before work: “system administrators are not developers.” And that was definitely true: system administrators were not developers. Developers were people who wrote computer programs, while system administrators were people who … well, to tell you the truth, we’re not sure anyone at Microsoft really knew what system administrators did. But whatever it was, it sure wasn’t writing computer programs.
Of course, Karl Marx also wrote the song “The Times, They Are A-Changing.” (Or at least he told us that he wrote it.)
Thanks in part to the TechNet Script Center (microsoft.com/technet/scriptcenter), system administrators gradually discovered VBScript and began writing their own utilities, utilities that, while not as complete and comprehensive as the typical computer program, still perform useful management tasks. In writing these utilities, system administrators have had to call upon skills very similar to those used by developers.
And that’s not even the half of it. The advent of Windows PowerShellâ„¢ (microsoft.com/technet/scriptcenter/hubs/msh.mspx) means that the Microsoft® .NET Framework is no longer off limits to system administrators and script writers. Windows Vista® introduced the world to gadgets (microsoft.com/technet/scriptcenter/topics/vista/gadgets-pt1.mspx), putting system administrators—or at least system administrators who write scripts—on equal footing with developers. And now—drum roll, please—there’s Microsoft Silverlight, the “cross-browser, cross-platform plug-in for delivering the next generation of .NET Framework–based media experiences and rich interactive applications for the Web.”
Sounds impressive, doesn’t it? But, you ask, what does that have to do with system administration scripters such as yourself? To be honest, we’re not entirely sure. However, Silverlight does make it very easy to create very cool user interfaces and multimedia presentations. And while the marketing material invariably refers to Silverlight as a way to create Web applications, here’s a little-known fact: Silverlight seems to work equally well in HTML Applications, or HTAs (microsoft.com/technet/scriptcenter/hubs/htas.mspx), a medium much-loved by script writers. So it just might be worth your while to give Silverlight a try.

Before We Go Any Further …
We should point out that we aren’t going to discuss Silverlight’s many capabilities and potential uses today; our primary goal is to show you enough code snippets to get you playing around with the technology. If you’d like to install Silverlight (it’s free, by the way), go to the Silverlight home page at microsoft.com/silverlight. You can also find the SDKs, sample projects, and lots of other useful stuff at silverlight.net/GetStarted.
For now, we’ll assume that you have already installed Silverlight. In addition, we’ll assume that you’ve gone to the Code Downloads section of the TechNet Magazine Web site (technetmagazine.com/code07.aspx) and downloaded the files described in Figure 1.

File Description
CreateSilverlight.js This is a JScript script (in its initial release, Silverlight only supports JScript scripting) used to specify the initial Silverlight startup settings, including the XAML file used to configure user interface and graphic objects.
SampleProject.js This is just a blank file where you can put JScript functions.
Silverlight.js This file is used to initialize the Silverlight control.
SampleProject.html This is where all the fun takes place. SampleProject.html is simply an HTML file that includes code for reading in the three .js files. It also includes code for instantiating the Silverlight control.
SampleProject.xaml What’s this for? To find out, you’ll have to go back to the main text of this column.
   
You don’t necessarily need SampleProject.js, at least not to follow along with this month’s column. Silverlight allows you to attach scripts to objects and events in your project; these scripts can either be hardcoded into your HTML file or (as in this case) read in from an outside “include” file like this one. Also, we’re going to turn SampleProject.html into an HTA: simply rename it SampleProject.hta. That was easy, right?
Make sure you save all these files into one folder. And then get ready to have some fun.

The XAML File
As you might have guessed, all the excitement in this month’s Hey, Scripting Guy! revolves around the XAML file. (Incidentally, XAML is pronounced “zamel,” rhyming with camel. Or mammal. Or even enamel.) XAML is short for Extensible Application Markup Language. Among other things, XAML provides a way to create user interfaces using an XML-like language. Does this make it easy for someone—particularly a system administrator—to create user interfaces? You’re about to find out.
To begin this quest, start up Notepad and open the file SampleProject.xaml. If you see any existing code, replace it with the following:

Copy Code

<Canvas xmlns="http://schemas.microsoft.com/ client/2007" xmlns:x="http://schemas.microsoft.com/ winfx/2006/xaml"> </Canvas>

Note. SampleProject.hta is already preconfigured to load in information from SampleProject.xaml. To use a different XAML file, you need to edit the file CreateSilverlight.js.
After pa
sting in the code, save the file and then double-click SampleProject.hta. Guess what you’ll see? That’s right: a big, dumb, gray box.
But wait, don’t go! That’s exactly what you should see.
There’s not much to see simply because we haven’t done anything yet: all we’ve done so far is add a Canvas to our XAML file. But that’s an important step. After all, if you want to try your hand at oil painting the first thing you need to do is find a canvas to paint on. The same thing is true in Silverlight: before we can start adding elements to our project we first need a canvas to “paint” on. And now we have one.
Incidentally, why did we get a gray box that is 300 pixels wide by 300 pixels high? That’s easy: those are the specifications found, by default, in the file CreateSilverlight.js. If you don’t like those default values, just open CreateSilverlight.js and tweak the settings as needed. Would you prefer a white canvas 800 pixels wide by 300 pixels high? (Which, for the examples we’re about to use, is something you would prefer.) Then simply make sure you have these three lines in CreateSilverlight.js:

Copy Code

width:'800', // Width of rectangular region of // control in pixels. height:'300', // Height of rectangular region // of control in pixels. background:'white', // Background color of // control.

Note. Good point: in the original .js file the background is set to #D6D6D6. Now we’ve set it to white. As it turns out, in a XAML file you can use RGB values to represent colors, or you can use color names to represent colors. (See msdn2.microsoft.com/bb188314.aspx for a complete list of color values and color names.) As much as the Scripting Guys like to refer to colors by their RGB values (“My house? It’s an FFFFFACD with FFCD5C5C trim”), we decided to use color names here.
Bonus tip: you aren’t limited to using solid colors as a canvas background. Paste the code from Figure 2 into your XAML file and see what you get.

Copy Code

<Canvas xmlns="http://schemas.microsoft.com/client/2007" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Width="800" Height="300"> <Canvas.Background> <LinearGradientBrush> <GradientStop Color="Blue" Offset="0.0" /> <GradientStop Color="Black" Offset="1.0" /> </LinearGradientBrush> </Canvas.Background> </Canvas>

Did you try it? Pretty cool, huh?
Of course, a blank canvas by itself isn’t all that interesting. Because of that, let’s go ahead and add an object to that canvas. Although Silverlight allows you to add all sorts of different objects (including ellipses, rectangles, lines, and polygons), we’ll add a good old-fashioned text block to our canvas. How? By inserting the following tags between the <Canvas> and </Canvas> tags in our XAML file:

Copy Code

<TextBlock Name="Test" FontSize="40" FontFamily="Georgia" FontWeight="Bold" Canvas.Top="20" Canvas.Left="20" Text="The TechNet Script Center"> </TextBlock>

As you can see, this XAML stuff really isn’t all that hard. We start out by using the <TextBlock> tag, making sure that we include the various TextBlock parameters (such as Name and FontSize) inside the tag. We then indicate that we’re through with our TextBlock by using the </TextBlock> tag. The cool thing about this? This is pretty much how you add any object to a XAML file. Want to add a rectangle 300 pixels wide by 100 pixels high? Okey doke:

Copy Code

<Rectangle Height="300" Width="100" Fill="Blue"> </Rectangle>

By the way, when adding a shape (such as a rectangle) to your canvas, make sure you specify a Fill color and/or a Stroke color. If you don’t, Silverlight will add a transparent rectangle to the canvas. The rectangle will actually be there, but unless you have some sort of Superman-like x-ray vision you won’t be able to see it.
Speaking of Fill colors and Stroke colors, that’s the way you color some Silverlight objects. For other objects, you need to use a brush, such as the LinearGradientBrush we showed you (but didn’t discuss) in our bonus tip. If you prefer to paint objects a solid color, you can use the aptly named SolidColorBrush. For example, take a look at the XAML tagging in Figure 3 that “paints” our text (technically, that paints the TextBlock’s Foreground property) red.

Copy Code

<TextBlock Name="Test" FontSize="40" FontFamily="Georgia" FontWeight="Bold" Canvas.Top="20" Canvas.Left="20" Text="The TechNet Script Center"> <TextBlock.Foreground> <SolidColorBrush Name="test_brush" Color="red"/> </TextBlock.Foreground> </TextBlock>

For an even cooler effect, try the ImageBrush property, which fills text with a picture:

Copy Code

<TextBlock.Foreground> <ImageBrush Name="test_brush" ImageSource="Sunset.jpg"/> </TextBlock.Foreground>

And here’s yet another tip: if you try the ImageBrush property, you will most likely want to increase the value of FontSize because the bigger the text, the better the effect.
Give that a try and see what happens. You will be suitably impressed.

But Wait: You Ain’t Seen Nothin’ Yet
We have to level with you: up until now we’ve been keeping the good stuff to ourselves. It’s cool that you can display text in Silverlight; it’s even cooler that you can draw rectangles, ellipses, and polygons. But all that pales in comparison to the animations that can be added to a Silverlight project.
Again, about all we can do in this article is introduce you to some of the ways you can create Silverlight animation; we simply don’t have room to go beyond that. (Don’t blame us; we keep suggesting that TechNet Magazine become Hey, Scripting Guy! Magazine, but apparently our e-mails haven’t been getting through.)
Without further ado, here’s a cool effect that causes our text to slowly (but surely) fade in. Add the code in Figure 4 between the <TextBlock> and </TextBlock> tags in your XAML file.

Copy Code

<TextBlock.Triggers> <EventTrigger RoutedEvent= "TextBlock.Loaded"> <BeginStoryboard> <Storyboard> <DoubleAnimation Storyboard.TargetName="Test" Storyboard.TargetProperty="Opacity" From="0.0" To="1.0" Duration="0:0:5" /> </Storyboard> </BeginStoryboard> </EventTrigger> </TextBlock.Triggers>

Admittedly, it looks like there’s an awful lot going on here; fortunately, much of this is just boilerplate tagging. To begin with, you can’t just add an animation to a XAML file; you need to indicate when that animation is supposed to play. That requires us to specify a set of Triggers for the TextBlock. In this case, we’re going to create a trigger that will start the animation any time the TextBlock is loaded:

Copy Code

<EventTrigger RoutedEvent="TextBlock.Loaded">

In Silverlight, animations must take place on a storyboard (a term borrowed from the movie industry). That’s why we have the <BeginStoryboard> and <Storyboard> tags.
In order to make our text fade in, we need to modify the Opacity property. We will gradually increase the value from 0.0 (transparent) to 1.0 (solid color). Because Opacity happens to be a Double property (a property based on a double-precision numeric value), we use a DoubleAnimation animation. And just how do we use a DoubleAnimation animation? Why, by indicating the object we want to animate (in this case Test, the name we gave the TextBlock); specifying the target property (Opacity); and then specifying both the animation range (from 0.0 opacity to 1.0 opacity) and the duration for the entire animation (5 seconds). In other words:

Copy Code

<DoubleAnimation Storyboard.TargetName="Test" Storyboard.TargetProperty="Opacity" From="0.0" To="1.0" Duration="0:0:5" />

Note. Make sure you use the syntax 0:0:5 (hours, minutes, seconds) to indicate 5 seconds. Using the value 5 by itself will not generate an error, but it will cause the animation to take 5 days to complete.
Did you try that? Now try this: within the DoubleAnimation tag, set the AutoReverse property to True:

Copy Code

AutoReverse="True"

Your animation will fade in and then fade back out.
Set the RepeatBehavior property to Forever and your text will continue to fade in and fade out, well, forever:

Copy Code

RepeatBehavior="Forever"

Is forever a bit long? Then set RepeatBehavior to, say, 3x, to make the animation play 3 times and then quit.

Copy Code

RepeatBehavior="3x"

That’s all pretty cool, if we do say so ourselves. However, what if you don’t like text that fades in and then fades out; what if you like text that gradually changes color? Well, then you should use a ColorAnimation animation:

Copy Code

<ColorAnimation Storyboard.TargetName="test_brush" Storyboard.TargetProperty="Color" From="Red" To="Blue" Duration="0:0:5" />

Note. Just make sure you start out with red text if you try this one. Remember, we already showed you how to paint your text red.
That was fun, wasn’t it? And yet, you seem a little disappointed. Oh, so that’s why: the ColorAnimation was nice, but you were really hoping to be able to make things fly around the screen. Hey, don’t mope; what kind of software would Silverlight be if it didn’t let you fly things around on screen? Try this:

Copy Code

<DoubleAnimation Storyboard.TargetName="Test" Storyboard.TargetProperty="(Canvas.Left)" To="150" Duration="0:0:5" />

As you can see, we’re causing our TextBlock to slowly move from its beginning position to a spot 150 pixels from the left edge of the Canvas (Canvas.Left). If we wanted to move the TextBlock up and down, we just use the Canvas.Top property:

DIVDIVDIVDIVDIV