Making a web request from oracle is as easy as:
select utl_http.request('www.google.com')
from dual;
But if you're making a request from Oracle, chances are you need to do a little work and not just google something so here's a little pl/sql code...
declare
l_url varchar2(4000) := 'www.google.com';
l_httpRequest utl_http.req;
l_httpResponse utl_http.resp;
l_tempResponse clob;
l_buffer varchar2(32767);
begin
utl_http.set_detailed_excp_support (true);
--setup the http connection
l_httpRequest := UTL_HTTP.begin_request(l_url, 'GET','HTTP/1.1');
l_httpResponse := utl_http.get_response(l_httpRequest);
--write the response (in blocks) to a temporary clob
dbms_lob.createtemporary(l_tempResponse, FALSE);
BEGIN
LOOP
UTL_HTTP.read_text(l_httpResponse, l_buffer, 32767);
dbms_lob.writeappend(l_tempResponse, length(l_buffer), l_buffer);
END LOOP;
EXCEPTION
WHEN utl_http.end_of_body THEN NULL;
END;
UTL_HTTP.end_response(l_httpResponse);
DBMS_OUTPUT.PUT_LINE('Response: ' || l_tempResponse);
end;
/
It would be a simple job to add a couple of query string parameters on to the URL. But it's very likely you'll be sending data to some sort of a web service and then expecting a response.
Here's an example of a POST request.
....
l_httpRequest := UTL_HTTP.begin_request(l_serviceUrl, 'POST','HTTP/1.1');
utl_http.set_header(l_httpRequest, 'Content-Type', 'application/x-www-form-urlencoded');
--these are likely constant for the application
l_postData :=
'x_login='|| 'xxxxxx'
|| '&x_tran_key='|| 'yyyyyyy'
|| '&x_delim_data='|| 'TRUE'
utl_http.set_header(l_httpRequest, 'Content-Length', length(l_postData));
utl_http.write_text(l_httpRequest, l_postData);
--get a reference to the response
l_httpResponse := utl_http.get_response(l_httpRequest);
....
That's it. In Part 2 I'll go over some of the finer points of SSL requests and the Oracle Wallet.
A couple years ago I wrote a short article on an old blog about connecting to Active Directory from an ASP.net project. I thought the article was fairly complete until I tried to help somebody else do the same thing. I had forgotten two major parts:
- Manually sifting thru the different AD containers to find the right LDAP string.
- Ensuring the correct security context.
The security context can be an easy thing. A console or winforms .net app runs in the context of the currently logged on user. For simplicity the ASP.Net development server in Visual Studio does the same thing. So as long as your user account as access to the AD (which is the norm unless your AD admin has locked down querying), then you should be fine while you are developing.
Running under IIS is a different story. Depending on the version, the user context of the iis process could be all sorts of things - ASPNET, Network Service, Local Service, the new IIS app pool accounts, etc. During testing the easiest thing to do is enable impersonation:
<configuration>
<system.web>
<identity impersonate="true" userName="contoso\Jane" password="********" />
</system.web>
</configuration>
Optionally, you can remove the userName and password attributes and make sure that NTLM/Integrated security is still checked in the IIS configuration. This will make IIS operate similar to the ASP.Net Development Server and use the current user as the execution context.
So with the security context out of the way, the next task is to actually query AD for a user object. In my old example, I would specify the container to connect to whe creating a DirectoryEntry object. For example:
System.DirectoryServices.DirectoryEntry directoryEntry
= new System.DirectoryServices.DirectoryEntry(LDAP://CN=Users,DC=contoso,DC=com);
This worked fine for me but was a bit to limiting as an example to go by. Many domains have an additional "corp" domain context. "Users" might be named something different like "DomainUsers". There are all sorts of variables with AD. To combat this I added an extra query to "RootDSE" which is sort of the current context. With that I'm able to get the address of an actuall domain controller and from there, can make the query on a higher level. In the example below, I'm trying to get the AD User object for the currently authenticated user, but there's lots of diagnostic output that would let you find other things or narrow down the container that you're querying (for performance reasons).
<%@ Page Language="C#" %>
<%@ Import Namespace="System.DirectoryServices" %>
<%@ Import Namespace="System.Collections.Generic" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
OptLocalUser.Text = User.Identity.Name;
Dictionary<String, String> directoryPropertyDictionary;
//get the currently connected AD server info:
DirectoryEntry de = new DirectoryEntry("LDAP://RootDSE");
directoryPropertyDictionary = new Dictionary<string, string>(de.Properties.Count);
foreach (string key in de.Properties.PropertyNames)
{
String allValues = "";
foreach (object value in de.Properties[key])
allValues += value;
directoryPropertyDictionary.Add(key, allValues);
}
GridView1.DataSource = directoryPropertyDictionary;
GridView1.DataBind();
//connect to the current server
de = new DirectoryEntry("LDAP://" + de.Properties["dnsHostName"][0].ToString());
DirectorySearcher ds = new DirectorySearcher(de);
String[] nameParts = User.Identity.Name.Split('\\');
ds.Filter = "(&(objectClass=user)(sAMAccountName=" + nameParts[1] + "))";
SearchResult result = ds.FindOne();
OptUserPath.Text = result.Path;
directoryPropertyDictionary.Clear();
foreach (string key in result.Properties.PropertyNames)
{
String allValues = "";
foreach (object value in de.Properties[key])
allValues += value;
directoryPropertyDictionary.Add(key, allValues);
}
GridView2.DataSource = directoryPropertyDictionary;
GridView2.DataBind();
OptLocalUser.Text = User.Identity.Name;
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
Local User:<asp:Label ID="OptLocalUser" runat="server" /><br/>
RootDSE:
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
AD User Path:<asp:Label ID="OptUserPath" runat="server" /><br/>
<asp:GridView ID="GridView2" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
Consider the following code:
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<div>
<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
<asp:Button ID="Button2" runat="server" Text="Button" />
<asp:Button ID="Button3" runat="server" Text="Button" />
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<Triggers>
<asp:AsyncPostBackTrigger ControlID="Button2" />
</Triggers>
<ContentTemplate>
<asp:Button ID="Button1" runat="server" Text="Button" />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</ContentTemplate>
</asp:UpdatePanel>
<asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="UpdatePanel1">
<ProgressTemplate>
Please Wait
</ProgressTemplate>
</asp:UpdateProgress>
</div>
//code behind:
protected void Page_Load(object sender, EventArgs e)
{
Label1.Text = DateTime.Now.ToString();
Label2.Text = DateTime.Now.ToString();
if (Page.IsPostBack)
{
System.Threading.Thread.Sleep(2000);
}
}
In the above example, Button1 will do a partial page update for UpdatePanel1. Button3 will cause a full postback. Button two will also do a partial page update for UpdatePanel1 since it is listed as a trigger. However, Button2 will not cause UpdateProgress1 to be displayed.
There is some help for this on the buttom of an article on codeproject.com, but I needed something for multiple controls. My solution ended up being two functions:
private void RegisterExternalTriggerFixForUpdateProgress()
{
String updateProgressWTriggerFix = @"
var triggerMappings = new Array();
function TriggerMapping(controlId, updatePanelId)
{
this.TriggerControlId = controlId;
this.UpdatePanelId = updatePanelId;
}
function RegisterTriggerMapping(controlId, updatePanelId)
{
triggerMappings.push(new TriggerMapping(controlId, updatePanelId));
}
function GetTriggerMapping(control)
{
for(var i=0; i<triggerMappings.length; i++)
{
if(triggerMappings[i].TriggerControlId == control.id)
{
return triggerMappings[i];
}
}
return null;
}
var prm = Sys.WebForms.PageRequestManager.getInstance();
function CancelAsyncPostBack() {
if (prm.get_isInAsyncPostBack()) {
prm.abortPostBack();
}
}
prm.add_initializeRequest(InitializeRequest);
prm.add_endRequest(EndRequest);
var postBackElement;
function InitializeRequest(sender, args) {
if (prm.get_isInAsyncPostBack()) {
args.set_cancel(true);
}
postBackElement = args.get_postBackElement();
var triggerMapping = GetTriggerMapping(postBackElement);
if (triggerMapping != null) {
$get(triggerMapping.UpdatePanelId).style.display = 'block';
}
}
function EndRequest(sender, args) {
var triggerMapping = GetTriggerMapping(postBackElement);
if (triggerMapping != null) {
$get(triggerMapping.UpdatePanelId).style.display = 'none';
}
}";
this.Page.ClientScript.RegisterStartupScript(typeof(Page), "UpdateProgressWTriggerFix", updateProgressWTriggerFix, true);
}
protected void RegisterExternalAsyncTrigger(Control triggerControl)
{
this.Page.ClientScript.RegisterStartupScript(
this.GetType(),
triggerControl.ClientID,
String.Format(@"RegisterTriggerMapping('{0}','{1}');",
triggerControl.ClientID,
UpdateProgress1.ClientID),
true);
}
Then to fix a particular AsyncPostBackTrigger control, you just need the following call in your Page_Load:
//fix for async update from a control outside of the update panel
RegisterExternalTriggerFixForUpdateProgress();
RegisterExternalAsyncTrigger(Button2);
More than once I've needed to find all non-ascii characters in a string. In a .net regular expression this is relatively easy:
[^\x20-\x7F]
I thought that regex would port over reletively easily to Oracle - wrong.
First, I found more than one site reference the \x sequence. However I just couldn't get it to work.
I then tried a brute force version:
[^A-Za-z 0-9 \.,\?''""!@#\$%\^&\*\(\)\-_=\;:<>\/\\\|\}\{\[\]`~]
To my surprise that didn't work either. After struggling with it for a while I found the regex reference page from Oracle. It states:
The following regular expression operators are allowed within the character list, any other metacharacters included in a character list lose their special meaning (are treated as literals):
- Range operator
'-'
- POSIX character class
[:
:]
- POSIX collating sequence
[. .]
- POSIX character equivalence class
[= =]
So in other words, character sets are pretty limited in what they can have in it. If a backslash is treated as a literal and not as an escape sequence, then that explained why my brute force version didn't work - sort of. I did eventually get it to work but for some reason I had to remove the ']' within the character class. So that wasn't perfect either.
Finally I gave it one last shot. Rather than the hex range, I tried the character range for the same values - from space to tilde (look at an ascii table of values and this will make sense).
[^ -~]
Finally this seemed to work as expected (keep in mind thought that character ranges are affected by the NLS_SORT variable - anything other than binaray, might yield unexpected results). I ran the following query as a test:
select regexp_replace('This is a test '||chr(191), '[^ -~]', '$') from dual;
The only thing left was to handle non-printable chars like crlf and tabs. I new the hex code was out so I generated the first ascii character with the chr function to replace the space:
select regexp_replace('This is a test crlf'||chr(10)|| 'blah', '[^' || chr(1) ||'-~]', '$') from dual;
Update: After I wrote this article I found a section on regular-expressions.info that explains some of the oracle differences.
I needed to record the execution time in a stored proc. I thought this would be straight forward until i realized the resolution on an oracle date datatype is only to the second. Timestamp has the additional resolution (as well as timezone if you want it). Even cooler is that when you subtract two timestamps you get an interval datatype with the difference. From there, however, I wanted to pass out the value in milliseconds, but that required each component of the interval to be extracted, multiplied, and added together. Here's some sample code of the whole process.
declare
v_start timestamp := systimestamp;
v_end timestamp;
v_elapsed interval day to second(3);
v_elapsed_ms number;
i number;
begin
for i in 1..100000 loop
v_elapsed_ms := 0;
end loop;
v_end := systimestamp;
v_elapsed := v_end - v_start;
v_elapsed_ms :=
( extract(hour from v_elapsed)* 3600 +
extract(minute from v_elapsed)* 60 +
extract(second from v_elapsed) ) * 1000;
dbms_output.put_line('elapsed time:');
dbms_output.put_line(to_char(v_elapsed));
dbms_output.put_line(to_char(v_elapsed_ms));
end;
I'm currently converting a winforms app into a WPF app and discovered the key events are significantly different in WPF. Here's a few tips and things to look out for:
The Web Capacity Analysis Tool (WCAT) is simple tool for stressing a web server. It's fairly straight forward, but because it's UI is very minimal, it can be a little tedious to set up a test. Microsofts older Web Application Stress Tool (WAS) had a nice feature to record a browser session by acting as a proxy and so I did a quick google search to see if I could find something similar for WCAT. As further testimate to Fiddler's awesomeness, "thomad" has written a great WCAT Fiddler plugin that does exactly what I want.
It's always driven me nuts that the default paste method in most programs includes the formatting of the original source. Later MS Office programs finally have some options to paste "using the destination format", but that didn't help me when pasting into things like this BlogEngine.net editor. The quick fix was to paste first into notepad, but I finally had enough and googled a better solution. Sure enough, a guy named Steve Miller created a nice little tray app called pure text. You copy as you always would but substitute windows-v for ctrl-v. Exactly what I was looking for!
I needed a way to force an application to stay on top. I thought it would be as simple as "this.Activate" in the form's deactivate event. It looked like it was trying to work - when clicking out of the app, it's start bar button would blink, but it wouldn't bring the app back on top. I then proceeded to try just about everything under the sun:
- Form.Activate
- Form.BringToFront
- Form.Focus
- Form.Show
- [DllImport("User32.dll")]
public static extern Int32 SetForegroundWindow(int hWnd);
Everything seemed to do the same thing. Finally I thought that maybe I was trying to refocus the form too soon. I decided to create a timer and delay the Activate call:
//declare the timer
private System.Timers.Timer restoreFocusTimer = new System.Timers.Timer();
//setup the timer in the constructor
restoreFocusTimer.AutoReset = false;
restoreFocusTimer.SynchronizingObject = this;
restoreFocusTimer.Interval = 1000;
restoreFocusTimer.Elapsed += new System.Timers.ElapsedEventHandler(restoreFocusTimer_Elapsed);
//define the handler
void restoreFocusTimer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
this.Activate();
}
//enable the timer in the deactivate event (I also added a preprocessor directive so i could disable it when debugging.
private void MainForm_Deactivate(object sender, EventArgs e)
{
if (!this.formClosing)
{
#if !NOALWAYSONTOP
restoreFocusTimer.Enabled = true;
#endif
}
}
I had a hard time creating a couple of indexes for some oracle tables. Things I thought for sure would work in SQL Server just would not work in oracle. Consider the following query:
select
slh_label_batch_id,
trunc(min(expected_ship_date)) as Expected_Ship_Date,
trunc(min(actual_ship_date)) as First_Ship_Date,
nullif(max(nvl(trunc(actual_ship_date), to_date('1-dec-9999'))),to_date('1-dec-9999')) as Last_Ship_Date,
count(actual_ship_date) as Completed_Ship_Count,
count(slh_label_batch_id) as Expected_Ship_Count
from ship_label
group by slh_label_batch_id
having min(trunc(expected_ship_date)) between trunc( to_date('1-mar-2009','dd-mon-yyyy')) and trunc(to_date('31-mar-2009','dd-mon-yyyy'))
order by min(expected_ship_date) desc
I thought i'd speed up the above with the following index:
create index ix_ship_label_batch on ship_label(slh_label_batch_id, expected_ship_date, actual_ship_date);
To my surprise, explain plan still said it was doing a full table scan. This didn't make any sense to me - the index a "covering" index for this query, which means it shouldn't need the table at all.
After some time i descovered that oracle will exclude index rows that are all nulls. Even still, I'm not looking for nulls, right? Wrong, no nulls would make the group by return one less row. The fix was to add one not null column to the index. Unfortunately, there wasn't a single not null column on the table (ugghh). The simple fix was to use Oracle's function-based indexing feature and index the result of a non-null function. My new index was:
create index ix_ship_label_batch on epi_ship_label, label_batch_id, expected_ship_date, actual_ship_date, nvl(confirm_flag,'N'));