Thursday, April 14, 2016

Error When Using Distinct - ORA-01791: not a SELECTed expression

The Problem
I had a query that was working, but when I added a DISTINCT to it started throwing an error.  The error I was receiving was ORA-01791: not a SELECTed expression.  It didn't give me which field it didn't like, so I was confused.


The Solution
What I found was that once DISTINCT was in play all the fields in my ORDER BY had to be in the SELECT.  All of the ORDER BY fields had to be fields and not calculations.

Friday, March 18, 2016

How to insert an ampersand into an Oracle field


I was trying to insert a value into a varchar2 field.  The value contained an ampersand.  Something like "A&B Home Remodeling".  It kept giving me an error like &B was a variable.


What worked for me was storing it like this:
'A'||'&'||'B'


It like that much better.  I also saw suggestions to chr(38).  I did not try that, so I am not sure if it works, but I am guessing it would be very similar to what I did.  Something like:
'A'||chr(38)||'B'

Wednesday, December 16, 2015

Updating a Session Variable With JavaScript

Although JavaScript is client side code with an AJAX call you can set or update a session variable on the server when client side code fires.


In my case I couldn't do a post back, since I could not have a form on the page, so I needed to do a reload.  I still needed the values from the page, which reload does not preserve.  Because it is a reload it shouldn't preserve those values, but for my purpose I needed one of them.


What I ended up doing was using JQuery to make an AJAX call to a handler page.


Once the AJAX call returns then I call the reload.  The code looks similar to this:


$.ajax(

{

cache: false,

url: sURL,

datatype: "html"

}).done(function (data) {

if (data != "") alert(data);

//alert("Should reload");

window.location.href = window.location;

window.location.reload(true);

})

.fail(function (xhr, ajaxOptions, thrownError) {

alert("fail");

alert(thrownError);

});


sURL is the address of the handler page.  You will need to pass a parameter to the handler page to tell it that you want to call the routine in the handler that sets the session variable.  The handler code in ASP.NET with C# would look like this:


static HttpContext _context;



public void ProcessRequest(HttpContext context)




{



   string retval = "";

   _context = context;

   switch (context.Request["method"])




   {



     case "SetSession":

        Session[context.Request["SessionVarName"])] = context.Request["SessionValue"])
        break;

     default:

        context.Response.ContentType = "text/plain";

        retval = "Invalid method specified";

        break;




   }



   context.Response.Write(retval);




}


   













Tuesday, November 10, 2015

Oracle Scheduled Jobs

TO CREATE A JOB:

Begin
 Dbms_scheduler.Create_job (
    Job_name => 'RUN_JOB_TO_RUN',
    Job_type => 'PLSQL_BLOCK',
    Job_action => 'BEGIN JOB_TO_RUN(); END;',
    Start_date => To_timestamp_tz('10-NOV-2015 13:00:00','DD-MM-YYYY HH24:MI:SS'),
    Repeat_interval => 'FREQ = DAILY; BYHOUR=13',
    comments => 'Some job to run.',
    Enabled => True);
End;


TO UPDATE A JOB:

begin
Dbms_scheduler.Set_attribute
(  name => 'RUN_JOB_TO_RUN'
,   Attribute =>'REPEAT_INTERVAL'
,   value =>'FREQ=DAILY; BYHOUR=2');
End;



Thursday, September 24, 2015

Format Strings to Use a Percent Sign (%) Without the Formatter Multiplying the Value by 100

I had an application that needed to display a value with a percent sign (%) after it. The value had already been converted from decimal to integer, or in other words had already been multiplied by 100. I did not have the option to just append a % at the end of the string. All the object gave me access to was the format. I tried "#0%", which gave me what I wanted, except that it multiplied the value by 100, so 20% became 2000%.

After a little research I found that using an escape character gave me what I wanted.

ToString("#0\\%")

Wednesday, September 9, 2015

C# Equivalent of DoEvents

I have been looking for a long time for an equivalent to Visual Basic's DoEvents statement.

DoEvents allows your code to yield execution to other threads in order to share processing time.  This could be do in a loop to allow you to process an iteration of the loop and when done yield.  This is often done to attempt to keep the UI responsive.  After each iteration you would call DoEvents thus allowing the UI some processing time.

I was studying today about multi threading and asynchronous processing when I came across some code that did a loop and called Thread.Sleep(0) at the end of each iteration.  Supposedly this yields execution without causing the loop to execute longer, since the sleep is for zero milliseconds.  Since the thread goes to sleep, other threads can then take over and release control when it is done.

I think you can use DoEvents in C# by using the the Visual Basic library, but from what I have seen people saying that is not recommended.  This is why I have been looking for a native C# method of doing this.

Visual Basic
        For Each file In files
            DoFileStuff()
            Application.DoEvents()
        Next

This is useful if DoFileStuff() updates the UI. DoEvents() would then let go of control, so the UI could take a turn and refresh itself.

C#
        foreach (var file in files)
        {
            DoFileStuff();
            Thread.Sleep(0);
}
The thread goes to sleep and immediately wakes up ready to be scheduled for execution when it is its turn again.

Saturday, January 24, 2015

HttpContext.Current Null in Asynchronous Methods

I created a page that had some logic to dynamically create the page.  It was doing a lot of fetches from the database and then calculating how to display each batch of data that was retrieved.  The page became slow.

To speed it up I converted the most time intensive loop to a parallel for each.  Inside the loop it was using HttpContext.Current.  Once I made the loop Parallel HttpContext.Current was null inside things called inside the loop..

It appears that if you leave the main thread of execution it no longer has a reference to the current context.

I found that HttpContext.Current can be set as well as read.  Outside the parallel loop I captured HttpContext.Current in a local variable.  I then accessed the variable in the loop.  The other things I was calling inside the parallel for each needed HttpContext.Current, so I set HttpContext.Current to the local variable.  The other methods were able to read HttpContext.Current as long as they were on the same thread as the calling method.