Welcome to the Blog of Mike Ross

December 9, 2010

How To Import Data From a File To A Table Using PL/SQL

If you have some columns of data in Excel (or any other way you can get either comma delimited or other standard data format), you can import this data into an Oracle table pretty easily using PL/SQL. Here's how:

Create the table you want to import the data into.

Create a .csv comma delimited file of your data. (You can use a lot of other ways to delimit your data, but this is an easy example.)

Next, go to PL/SQL and open Tools -> Text Importer. There are two tabs in the Text Importer - one to set up your data to import, and one to tell it which table and column to put the data. Do not go to the second tab until you have got your data set up on the first tab - it just erases all of it if you go back to the first tab and change anything.

Just above the first tab, named "Data from Textfile" there is a row of icons. The very first icon is sort of an "open" icon, and sure enough that is what you use to open your .csv file.

Once your data file is open, PL displays a list of the data in the top pane. You go in to the Configuration pane and set up Fieldcount (how many columns). It will then display entries such as Field1 (+0 ..",") and Field2(blah,blah). Just highlight field one and see if it picks the first field of data from your file. And so on for each field. If it doesn't pick the right field, adjust Field Start or Field End (in this case select Character and ",").

Once you have it set so that it picks the correct data for each field, you are ready for Tab 2 - Data to Oracle. This is pretty simple. Pick your schema, table name, and select each field displayed. Make sure each field is mapped to the correct table column.

Press Import.

That's it!

Posted by rossm at 2:57 PM | Comments (0)

March 24, 2010

A Little Bit of SWAMP Coding

I was asked to prepare a new form/report for SWAMP that allows a user to query labor data in a number of different ways. This was a good opportunity to look through some existing code for some clever ideas. One of the queries looks like this:

SELECT USER, wo_number, (name||', '||first_name), description,
work_date, hours,
hours * A.rate,b.employee
FROM table A, table B
WHERE A.employee = B.employee
AND work_date >= decode(beg_date,'',work_date,beg_date)
AND work_date <= decode(end_date,'',work_date,end_date)
AND wo_number = decode(wo1,'',wo_number,wo1)
and b.employee = decode(empno,'',b.employee,empno)
and b.name = decode(lname,'',b.name,lname)
and a.description like '%' || pdesc || '%';

Two clever things (wish I had thought of these, but they were stolen from Bryan Hockett) -first, on the last line notice how the all encompassing search through descriptions was done. pdesc is the value the user typed into the form. Second clever thing - "decodes" that handle the case if a value is null (i.e. user did not use that particular item). The way you read the decodes is like this (for example: decode(end_date,'',work_date,end_date)) if end_date is null then insert work_date, else insert end_date. So you either wind up with:
and work_date <= work_date (no harm done) OR
work_date <= end_date (if end_date has a value).
End result: You don't need to know whether end_date is null or not! The decode takes care of it for you. Veeerrrrry interrresting....

Posted by rossm at 3:29 PM | Comments (0)

March 26, 2008

Teamwork - Project Management Software

Michael Ellis has assigned each of us the job of putting our programming tasks into a new application called Teamwork. After bugging Michael a few times, I was able to log into the new application. It is a bit clunky to use, and there is a definite small learning curve to get around in it, but after a while I got the hang of it and was able to enter some project information into the application. As I entered by information, a lot of other programmers where doing the same thing with their projects, and Teamwork was really getting hammered. There are now about 4 pages of projects entered by the programming team.

We are hopeful that Teamwork will help to schedule and track our projects. We are also hopeful that it will enhance our ability to demonstrate how badly we need some additional programmers! Our current programming team does not have a single person who does strictly programming. For example, I estimate that my job has no more than 10% available for programming tasks. One other item I've learned is that as we add more and more software applications - ones we've written and ones that have been purchased - more and more time is necessary to maintain those applications. We all spend a certain amount of time making small modifications or fixing data entry errors, etc. to existing applications. They just don't keep on working by themselves.

Teamwork will also have the benefit of assisting management of tasks. For instance, if a new project comes along, it will become more obvious how long it will be before someone can begin working on the new project, and also, if we start work on this project right away, what other projects will have to be put off. That will be very helpful in assigning tasks and in setting programing priorities. We are all aftraid that there will be a mighty long wait to get to new tasks.

Posted by rossm at 10:53 AM | Comments (0)

October 19, 2006

A Little Fun With Radio Buttons

Tom Neal recently asked me to modify a SWAMP report. The existing report returned information about the 05-06 Fiscal Year, and he also wanted information from the current Fiscal Year. This report is pretty long, so I wanted to keep the report to no more than one Fiscal Year. Also, this is a time frame that makes sense for this report.

I got the idea of creating a "front" page where the user could select a Fiscal Year, then click on a button and run this report for that time frame. A perfect spot for using "radio" buttons. Here's how I put the radio buttons to good use, blending JavaScript and PL/SQL.

First, the radio button is a good choice here because I don't want the user to pick more than one Fiscal Year at a time. To select more than one fiscal year at a time, checkboxes would have been the right choice. Checkboxes work pretty much the same as radio buttons.

In setting up radio buttons, JavaScript is really slick. If you give all the buttons the same name, JavaScript will make an array of them. Then you can just loop through the array looking for the selected button. Here's a look at the radio buttons I made:

Here's the code that created these buttons:

<tr>
<td>Fiscal Year 06-07</td><td><input type=radio name=fy checked=true>    </td>
</tr>
<tr>
<td>Fiscal Year 05-06</td><td><input type=radio name=fy>    </td>
</tr>
<tr>
<td>Fiscal Year 04-05</td><td><input type=radio name=fy>    </td>
</tr>

Three radio buttons with the name "fy". Notice that the first button has the added property "checked=true". This automatically selects the first radio button. In this way, I avoid having to handle the possible condition of no radio buttons checked.

So, the user has selected the Fiscal Year they want and clicked the "Prepare Report" button. We must figure out which radio button was selected, then run the report for the appropriate Fiscal Year. Here's the JavaScript function that makes it happen:

function pickEd(){
var theone=0;
for (i=0;i<document.detail_form.fy.length;i++){
if (document.detail_form.fy[i].checked==true){
theone = i;
break; //exit for loop, as target acquired.
}

}
location.href = "http://[yourDADinfohere]/report_tools_equip.expend?pfy="+theone;

}

I obtained the guts of this function from here. This is the JavaScript Kit Reference page, and it is a great JavaScript resource.

What this function does is loop through the radio buttons until it finds the one that is checked, then calls a PL/SQL procedure, passing a 0,1, or 2 depending on which radio button was selected. My PL/SQL procedure then examines the value passed in and sets the date range. I did it like this (pfy is the value passed):

if(pfy = '0') then
vlodate := '01-JUL-06';
vhidate := '30-JUN-07';
elsif(pfy = '1') then
vlodate := '01-JUL-05';
vhidate := '30-JUN-06';
else
vlodate := '01-JUL-04';
vhidate := '30-JUN-05';
end if;

Here's something interesting - you can use these variable names in your procedure cursors. So a cursor could look like this:

select xxxxx from table.table
where condition
and item_date between vlodate and vhidate;

This works. Go ahead and set up your report as needed. Here's a sample of my report's output:

There you have it. Another useful JavaScript - PL/SQL combination.

Here's a comment from Summer:

I've created a
procedure that ads the javascript function to your code:

<pre>procedure getradio is
begin
htp.p('
<script language="javascript">
function getCheckedValue(radioObj) {
if(!radioObj)
return "";
var radioLength = radioObj.length;
if(radioLength == undefined)
if(radioObj.checked)
return radioObj.value;
else
return "";
for(var i = 0; i < radioLength; i++) {
if(radioObj[i].checked) {
return radioObj[i].value;
}
}
return "";
}
<script>');
end getradio;</pre>

Posted by rossm at 8:36 AM | Comments (0)

October 4, 2006

Dynamic HTML Query - Are You Ready For This?

I just finished a small modification to a SWAMP form, allowing for an additional search item (unit price). Now, you can search our receipt data by unit price - it comes in handy once in a blue moon. The modification wasn't too tough, but the code that handles these queries is really something. I thought I'd share with you.

First, let me set the stage. What we're trying to accomplish is to create a form that is initially all null values. The user is then allowed to fill in one or more of the text boxes and then click on the "query" button. We will determine which fields have been filled, create a dynamic query, execute the dynamic query, then display the results. Get ready for a rough boat ride.

First, let's take a look at the blank query form. This particular form allows us to query the receipts data. This means we will look through about 250,000 records of items that have been received. Here's the form:

As you can see, there are a lot of options for the user. For our purposes here, let's pretend that we only have the first three options, that is, Invoice No., PO No., and WO No. The code would be the same, but with added elements for the other items that we're going to ignore. So, for our example, we'll be able to search for only three items instead of 20 or so.

The first step is to display the "blank" query form. This is accomplished by a call to a javascript function upon clicking the "Query" button. The javascript function looks like this:

function queryLines(){
var Addy = "http://www.yourdomain/yourdad/schema.form_receipts.query_lines";
var newWindow = window.open(Addy,"SWAMP","scrollbars,resizable,WIDTH=780,HEIGHT=500,left=0,top=0");
newWindow.focus();
}

This function calls our PL/SQL procedure query_lines. The procedure header (shortened for three items only) looks like this:

procedure query_lines(pinvoice_no varchar2 default NULL,ppo_number varchar2 default NULL,pwo_number varchar2 default NULL)

The "default null" behind each form element allows us to make the call to this procedure as in the javascript function - we don't need to send a null value for each element - very handy. Next, we need to check to see whether this is the initial display of the form (all records have null values) or whether a query has been requested (one to three not null values). Display the blank form using normal HTML if ALL values are null, otherwise run a query. Our if statement will look like this:

if(pinvoice_no is not NULL OR ppo_number is not NULL OR pwo_number is not NULL)

If true, run a query, if false display initial page. OK, I'm going to let you figure out how to display your own page. Let's work on the dynamic query - by that I mean we're going to build a query based upon the values passed in to our procedure. If one, two, or three of our values passed in is not null, then we will run a query. Here we go!

We've created a variable vblock varchar2(10000). This string variable will actually contain all the text for a complete PL/SQL procedure. It will be built a little at a time using string manipulation. This notion will seem very odd and frail to you at first, but it does work and it has proven to be quite stable over time. The only real downfall is that debugging is very difficult.

Let's build our string. We know that the following will be necessary for every query, so we add it in first.

vblock := '
DECLARE
BEGIN

for j in (select a.rec_number,b.po_number,wo_number,part_number,building_number,quantity,um,unit_price,discount,commodity,description,
vendor_no,brand_no,brand_name,catalog_no,preq_no,requestor,a.enter_date,b.enter_user
from rec_header b,rec_detail a
where a.rec_number=b.rec_number
and a.enter_date=b.enter_date';

We have started our procedure and set up our for loop. The for loop is also our cursor, kind of a neat PL/SQL trick. But we haven't finished with our search conditions yet. Here's how we'll add them in:

if(pinvoice_no is not null)then
vblock := vblock||' and b.rec_number like UPPER('''||pinvoice_no||''')';
end if;
if(ppo_number is not null)then
vblock := vblock||' and b.po_number like UPPER('''||ppo_number||''')';
end if;
if(pwo_number is not null)then
vblock := vblock||' and a.wo_number like UPPER('''||pwo_number||''')';
end if;

This is mighty slick! For each variable that has a value, we add to the vblock string another search condition. Look at all those ''' marks - the bane of this kind of programming. What we are doing is popping in and out of PL/SQL and javascript to build a proper query string. Are you still with me? This is one of those things you might have to look at a couple of times before it makes sense to you.

Next, we'll tack on an order by clause and start our loop:

vblock := vblock||' order by a.enter_date desc) loop

If you see what we're doing here, move to the front of the class:

htp.print(''
<table>
<tr>
<td><input type=text name=pinvoice_no size=15 maxlength=20 value=''||j.rec_number||''></td>
<td><input type=text name=ppo_number size=12 maxlength=15 value=''||j.po_number||''></td>
<td><input type=text name=pwo_number size=8 maxlength=10 value=''||j.wo_number||''></td>

Correct. Not only is this a dynamic query, it will also print out it's results. That's pretty cool, I don't care who you are... Now we'll do a bit of cleaning up so our string contains a complete procedure (this is a continuation of the above code):

'');

end loop;
htp.print(''

'');
END;
';

And now, let's see if the darn thing works.

execute immediate vblock;

Bryan Hockett wrote these things for SWAMP, and it took me a good year to learn all the little intricacies involved to modify them and keep them working. They are quite robust even though they don't look it. Here's a sample of the form displayed for a query on Invoice Number B000617084:

Nice display of two records that matched our search criteria. You will notice that I've omitted some housekeeping chores from this discussion such as error checking, and checking for a display condition. I hope that dissecting this code will assist you in developing your own dynamic queries in your forms.

Posted by rossm at 9:46 AM | Comments (0)

September 28, 2006

Overdue Orders Report Enhancement

One of SWAMP's reports tells you which items on order are overdue by more than a week. The report gives you the details on what was ordered and when it was due to be delivered. The problem with the existing report was that Marsha would check on the overdue items, but when she printed out the report several days or a week later, she had no way of knowing the status of items she had previously checked up on the last time the report was printed out. She needed some way to mark the overdue items that she had already checked up on with a code that indicated what the current status of the item was. If she could do this, then she would not have to waste time re-checking items.

Javascript and DHTML to the rescue! We decided on these codes:

The goal is to turn the existing report into a dynamic form that will allow for input of the new status codes. The first thing was to modify the report to display text areas for each displayed item. Each text item contained this html code (the value assignment is the way a PL/SQL variable is referenced in a loop):

<input type=text name=yo size=6 maxlength=6 value="'||j.bambi||'" onchange="upper(this),ed(this)">

A status variable and a hidden variable were added. The hidden variable is ed. It will be used to keep track of what's going on in the form. By the way, a new table was added for tracking the status variable.

It was pretty easy to allow for entry of a new status. Each time a commit was done, the form was checked for status values that weren't null and then the status was inserted into the form. So far, so good.

The next step is to figure out ed - an array that matches up with our text box arrays. Ed will tell us which status records have been edited since the form was loaded. It looks like this:

function ed(myEle){

var statEl = myEle.sourceIndex;
document.all[statEl+4].value = ''true'';
}

First, we get the source index number of the element that called ed. Then we set the ed array value to "true" by adding 4 to the index. 4 is the number that works in this situation, you will need to experiment to see what works in your situation.

So, now I have the status value for each record, and whether it has been edited. I'm ready to figure out whether to "insert", "delete", or "update".

if(edited(i) = 'true' and wstatus(i) is not null)

This is an insert condition (the status box was edited, and it has a value). Note that this also works for the case where a value that was not initially null was changed to another value (because I delete from the table first, then insert the current values).

elsif(wstatus(i) is null and edited(i) = 'true')

This is a delete condition, a record has been modified to null.

If a record was not edited since loading, it isn't even a test condition since we don't want to do anything with that record anyway.

Here's what the new version of the Overdue Orders form/report looks like:

One final consideration is value checking - we only want to allow the values to be entered that match up with the key. In this instance I decided not to actually check the values before insert, but at the very end of the form we will just delete any records that have bogus entries like this:

delete from fic30.eta where status not in ('D','R','BO','CP','J','M');

So there you have it, a nice little interactive report that is an improvement from just printing out a list of records.

Posted by rossm at 11:28 AM | Comments (0)

November 15, 2005

LOOPing for Data - Part II

In Part I, we considered the subtle, but very useful cursor with a variable. We set up two cursors, with two corresponding loops and were able to display a list of projects for a given funding source. The following screen shot is from the SWAMP projects module, and used this structure to break out the display.

The outer loop displays the "Miscellaneous" and "Tier 1" funding source information, the inner loop displays the individual projects and their corresponding information. There are three projects under "Miscellaneous" and four projects under "Tier 1", at least for now. When new projects are added, or old projects removed from the database, then the display will change automatically.

OK, so that's all well and good, but how do you handle the HTML in each loop to make this work? And, by the way, didn't you promise to show us another use for the cursor-loop combo? Well, glad you are paying attention! Read on.....

Let's flesh out our loops a bit and put some real-world HTML in there. Remember our cursors look like this:

cursor fun_src is
select distinct fund_source
from proj_table;

cursor ind_projs(fc varchar2) is
select *
from proj_table
where fund_source = fc;

I hope you are getting more comfortable with the notion of passing in a variable to a cursor (which value actually comes from the first cursor). A happy combination.

Here's the outer loop:

for i in fun_src loop

Let's add some HTML in here. The projects form uses a new table for each fund type, then one table row for each project, like this (THANKS to Summer who showed me how to print out the HTML without having it interpreted by using lt; and gt; directives!):

< table width=95% border=1 class=graytable>
<tr><th>'||j.proj_type||'</th></tr>
<tr align=left>
<td width=22%><b>Project Name</b></td><td width=7% align=center><b>Est Cost</b></td>
<td width=7% align=center><b>Encum</b></td><td width=10% align=center><b>Proj Mgr</b></td>
<td width=12% align=center>lt;b>Start Date</b></td>
<td width=12% align=center><b>Est End Date</b></td>
<td width=7%><b>Last Status</b></td><td width=7%><b>Curr Status</b></td><td width=16%><b>Comments</b></td></tr>

The outer loop gives me a table, the Project Type (i.proj_type), and a table row containing the column headings such as Est Cost, Proj Mgr, etc.

Now we will start the inner loop:

for j in ind_projs(i.fund_source) loop

and place in it the individual project information, like this:

</tr align=left>
</td width=22%></a style="color:'||vcfont||';text-decoration: underline" href="http://www2.wou.edu:7777/pls/wou/fmm30.form_projects.get_totals?pproj_no='||i.proj_no||'">'||i.proj_name||'</td><td align=right width=7%>'||i.proj_est_cost||'</td><td width=7% align=right>'||i.encumbered||'</td><td> width=10% align=center>'||i.proj_manager||'</td>
<td width=12% align=center>'||i.proj_start_date||'</td><td width=12% align=center>'||i.proj_completion_date||'</td><td width=7% align=center><img src = "https://www2.wou.edu:443/images/swamp/'||vimagelast||'" /><td width=7% align=center><img src = "https://www2.wou.edu:443/images/swamp/'||vimage||'" /></td>
<td width=16%>'||i.comments||'</td>
</tr>

This gives you a good example of the syntax required to acquire the data values from the cursor. Also, for your specific situation you might need to add in PL/SQL if-then statements, and others, to get your output correct. But I think you can now get the idea of how to use these cursor-loop combinations.

The last thing I wanted to suggest was using a cursor-loop combination for the sole purpose of passing values into your form! That's right. Of course you can pass in any number of parameters into a procedure. Here's a good example of what I'd like to avoid:

procedure displaypage(vdisabled varchar2, vcomments varchar2, vvendor_no varchar2, vname varchar2,
vsales_rep varchar2, vvendor_account_no varchar2, vfax varchar2, vtelephone varchar2,
vmobile_phone varchar2, vemail varchar2, vprice_agreement varchar2,
vconfirm varchar2, vfob varchar2, ppreq_no varchar2, vpo_no varchar2);

That's a pretty hairy procedure call! Well, SOMETIMES, certainly not always, you can use the cursor-loop combo to simply fetch a set of values from the database, and just display them by going through the loop only once. This works for a situation where you can pass in one or two parameters, and then use those to query the rest of the data with a cursor. If the values you are passing are available from the database, give this one a try.

Well, that's it for LOOPing for Data. I hope you can see a way to use the power of the cursor-loop combination to improve your own forms!

Posted by rossm at 1:35 PM | Comments (0)

November 10, 2005

A Little String FUN

One of the biggest challenges in writing DHTML,PL/SQL,JS combination code is handling characters in strings that also have meaning to the programming language. For example, the string "Joe's Bar and Grill" will give you fits because the ' has special meaning in both PL/SQL and JS - it means the end of the string. So, everything after the ' is just so much jibberish. Of course, this problem is so widespread that there's an easy fix, like this: "Joe\'s Bar and Grill". Will work.

But consider this little problem. We want to delete a vendor from a preq, and the vendor number just happens to be, guess what "G&SM01". We build our URL string (in JS) to call the delete vendor procedure like this:
//capture the vendor number from the form
var val = document.detail_form.pvendor_no.value;
//build the URL string Addy. ppreq_no is the preq number from the form
var Addy = "http://......?ppreq_no="+preq_no+"&pvendor_no="+val;
Do you see the problem?

Right. Here's the Addy string we just built:
"http://......?ppreq_no=PRQ123456&pvendor_no=G&SM01"
ERROR: No value given for variable SM01. "=" expected.
The "&" character means a new variable is being passed. There is no way for the browser to know that it's just part of the vendor number. Well, the easy answer is, don't allow vendor numbers to contain "&"'s. OK, but a quick check of the vendor table reveals we have 43 vendors with an "&" in their vendor number. You tell John he has to change them. Me, I'm going to think up something else.

And here it is. A simple cure with some tricky code syntax problems. But, once you figure those out, it's a snap.

First off, both JS and PL/SQL have Replace functions that will seek out a character in a string and replace it with something else.

JS: var x = val.replace(/&/,"~");
//changes G&SM01 to G~SM01 - there are no ~'s in vendor numbers.
//This is the exact format required within JS.

Now I build my URL with the x string and the error goes away. The browser is happy, but nothing happens. That is because my delete procedure needs to "reverse engineer" my string back to the way it was in order to know what record to delete from my data.

PL/SQL Replace is used as follows (pv is the passed in value x):

vvend := replace(pv, '~','&');
//pv = G~SM01, vvend = G&SM01
Now all that's left is to perform my delete based upon vvend.

That's it! We used the JS replace() function to remove the offending character(s), called our PL/SQL procedure and passed the modified vendor number, then converted the vendor number back to the way it was using the PL/SQL replace() function. A much better solution than requiring our users to modify their data to remove the offending characters!

Posted by rossm at 9:03 AM | Comments (0)

November 2, 2005

LOOPing for Data - Part I

Often, when beginning a DHTML page display, we will start with a PL/SQL cursor. The cursor gathers a set of values for us, which we can then loop through and display the resulting data on our DHTML page. This entry will show you not only how to accomplish this goal, but also how to use loops for grouping your output, and even using a cursor-loop combo just for displaying form values!

Let's get started with a simple PL/SQL cursor that fetches a project name, a project number, and a project cost, ordering by the project number, such as this:

cursor get_proj is
select proj_name, proj_number, proj_cost
from project_table
order by proj_number;

We can then loop through the get_proj cursor results and display whatever is there. This is quite handy for dynamic web pages - whatever is in the database that matches our query can be displayed.

So, we might have some code like this:

for i in get_proj loop
htp.print(' [do some cool html thing here] ');
end loop;

Note: Referring out to PL/SQL variables from HTML is a bit awkward - you must use this form:
'||i.proj_name||'
This will get you the proj_name from the i record. If you want to get fancy, you can also use pl/sql functions, such as:
'||to_char(i.proj_cost,'$9,999,999')||'
This will give you output such as $1,234 ...Ah, but I digress.

This cursor-loop combo is a common tool and is quite useful, but what I wanted to show you were two little twists to this scenario that can save you some time. They go hand in hand.

First, let's assume we want to display some projects, ordered by their funding source. Under each funding source there will be anywhere from 1 to n projects (if there are 0 we don't want to display anything). We can accomplish this by using two loops. The outer loop will pick the funding source, and the inner loop will pick all projects with that funding source. When we've looped through all the projects from the first funding source, we'll increment the outer loop to the next funding source, and so on until we're done. This is a great idea but requires one very subtle little trick to work - a cursor with a variable!

That's right - a variable. Consider these two PL/SQL cursors:

cursor fun_src is
select distinct fund_source
from proj_table;

cursor ind_projs(fc varchar2) is
select *
from proj_table
where fund_source = fc;

Yes, this does work!

Our outer loop will be like this:
for i in fun_src loop

And our inner loop will follow, like this:
for j in ind_projs(i.fund_source) loop

Can you see what's happening? The inner loop calls to the ind_projs cursor with the current value of the funding source! Ok, that's enough for you to chew on for Part I of this article. Consider this carefully, and you will see just how neat of a trick this is!

Next time we'll put in a bit more code and put these two loops to work for us. We'll also consider another use for these cursor-loop combo's.

Posted by rossm at 1:08 PM | Comments (0)

October 26, 2005

Putting PDF files on the Web

While this item doesn't fit exactly into this category, it seemed like a good place to describe the process of creating a PDF file and getting it on the web.

Why would you want to know this? Well, it is certainly useful for putting just about any Word or text document onto the web. If you use the Adobe PDF file format, then others can either read or print out the document, or any part of it. Have you ever tried simulating a complex document using HTML? It is not easy! The PDF files are easy to create and display!

Tools Required: You will need to acquire Adobe Acrobat 7.0 Professional. The free Adobe reader only reads PDF's, it will not create them. I ordered an educational license from Tricia for $47.50. This is not one of those expensive Adobe products like PageMaker or Designer, so the cost is reasonable. You create the PDF with Acrobat Pro, and then anyone with the free reader can view or print it.

I am no expert at this (yet!) but here's a good example of one way I've already used this. Tom Neal had a magazine article he wanted to send out to about 14 other persons. The article is 5 pages long and is in color. How can you do this? This is no easy thing. The first thing I try is a black and white scan. No good because the background over the text is blue, the letters are darker blue, and the whole thing is an illegible mess. Scanning as a color document to a jpg file is OK, except you need to use a pretty high resolution and each page's file is close to 1 MB. There must be a better way.

Enter Acrobat Pro. The scan was still tricky, because if I scanned the page directly into a PDF, it was nearly 10 MB per page. Waaay too big! BUT, if I scan to a file and select PDF when I go to save, I get a nice looking, legible file of about 200K per page. Much better!

To make a long story short, I scanned in the 5 pages, each page in it's own file, then opened up Acrobat Pro. There under File -> Create PDF -> From Multiple Files, it was easy to put the pages into one "binder" file. The whole thing was less than 1 MB when done, which is still pretty big, but at least usable.

The second project I've tried was to put a New Project Form into SWAMP for the project managers to use. This form is pretty simple, but has the WOU logo on it, a bunch of bullets, and some text. Well, just thinking about the HTML it was going to take to mimic this form was giving me a headache. Why not a PDF? No reason at all. After you install Acrobat Pro, you have a new tool bar in Word. You open the Form, click on the Adobe tool, and save the document in Adobe PDF format. Then I copied the PDF to an image directory and put an href anchor tag in pointing to the document, and voila! You click on the link and it opens the document. Very Slick!

In summary, this is what I've learned so far. To create Adobe PDF's you need Acrobat Pro. Not the free reader, and not the fancier PageMaker type Adobe products either. Once this is installed it is a pretty straightforward process to use the PDF in some way to publish a fairly complicated form, or to put together a long article - even using color. So, give it a try!

Posted by rossm at 1:10 PM | Comments (0)

September 20, 2005

Processing Multiple Row Check Boxes - Part III

If you have followed Part I and Part II of this series, then you know that we are using our Oracle database to display a web page. This particular web page we are studying displays all work requests that are open, one per row, with a checkbox displayed at the end of each record. When a user checks one or more checkboxes and presses the "Close Selected Work Requests" button, a javascript function called closeReqs() loops through all the records and creates a marker array with the value "true" set wherever a checkbox was checked. If there are records to be closed, then this statement is executed:

document.hist.submit();

Here in Part III, we will see what this does.

In order to know what document.hist.submit() does, we must go to the form tag in our procedure named "hist". Here is the tag (with < changed to _ for display purposes):

_FORM action = "plant.plant_admin.close_req" method = "post" name=hist>

You can see that the form tag calls the plant_admin.close_req procedure. Let's see what that procedure looks like:

procedure close_req (reqno List, checker List, marker List)
is
vx varchar2(100);
cursor reqs is
select *
from plant.wrk_requests_data
where status is null;

begin
fmm30.template.header;

htp.print('

_script language=javascript>

var arr = new Array();

function goHome(){

window.navigate('''||ADMIN_URL||''');
}

_/script>

_h2>Record of Closed Work Requests_/h2>
_hr 80%>
');

for i in 1..reqno.count() loop

if(marker(i) = 'true') then

update plant.wrk_requests_dataset status = 'C'
where request_no = reqno(i);

htp.print('
_FORM action = "plant.plant_admin.close_req" method = "post" name=document_detail>
_table>
_tr>_td>This request number has been closed:_/td>_td>'||reqno(i)||'_/td>_/tr>
_/table>

_/FORM>

');
end if;
end loop;
htp.print('
_br>_br>
_input type=button class=button value=" Back to Work Request Admin " onclick="goHome()">
');
commit;
end close_req;

You can see that this procedure begins with a cursor reqs that selects all open work requests from the table. A for loop processes the marker array looking for a value of "true". When such a value is found, then the table is updated where the request_no = reqno(i). This is worth stopping and considering. The web browser does a really neat thing for you when multiple records are displayed on a form. It automatically creates arrays for each of the text box, check box, and hidden objects. All the data for one record can be found at the same index. So, marker[i] and reqno[i] can be matched up to get the request number that needs to be closed. This is very powerful stuff!

From here it's all downhill. The work requests that are closed are displayed as feedback information for the user, and finally there is a button that takes the user back to the main page. The main page will once again display the open work requests, but not the ones that we just closed.

Let's review. Our goal was to create a way for the user to see all open work requests, and then to easily close one or more of them. We displayed a list of open work requests, one to a line, with a checkbox at the end of each line. The user then selected the work requests to close by simply checking the appropriate box or boxes and clicking a button. We then processed the request, making sure that at least one box had been checked, and creating a marker array which held a value of true at the index of the record to be closed. We then looped through the marker index and closed each record at the same index of the "true" marker value.

I hope you can see a way to use this technique to make your application more efficient and more user friendly.

Posted by rossm at 1:45 PM | Comments (0)

September 8, 2005

Processing Multiple Row Check Boxes - Part II

In Part I we looked at a way to display all open work requests together with an empty check box for each one. Now, we need to decide how to figure out which of these records is ready to be closed (Remember, a work request is closed if the status column in the main table = 'C'). How on earth to do this?

The first step is to be notified by the user that it's time to close the records which have been checked. I used a button like this:

_input type=button id=closer class=button value=" Close Selected Work Requests "
onclick="closeReqs()">

It looks like this:

So, there we are, ready to close the checked work requests. You can see from the code above that the button press calls closeReqs(). This is a javascript function, and here it is:


function closeReqs(){

if( confirm(''Close the selected Work Requests?'') ){

/* Calls new_preq */
document.hist.action = ''plant.plant_admin.close_req'';
var good = false;

var x = document.hist.checker.length;

if(x== undefined){
good = true;
document.hist.marker.value = ''true'';
}
else {
for( i = 0; i < document.hist.checker.length; i++){

if( document.hist.checker[i].checked == true){
good = true;
document.hist.marker[i].value = ''true'';

}


}
}
if(good){
document.hist.submit();
}
/*This alert makes sure at least one line was selected.*/
else {
alert(''You must check at least one box.'');
document.hist.action="plant.plant_admin.close_req";
location.reload;

}
}
}

Yes, it's fairly long. Have you seen the if(confirm(...)) javascript before? It is pretty neat - it will display a dialogue box asking the user whether they want to "close the selected work requests?" If they answer yes (0r OK), then we proceed to close the work requests. If they answer no (Or CANCEL), then the function essentially does nothing. Also, you will see that the last chunk of code below the alert is just checking to make sure at least one checkbox was checked. If not, then an error message is displayed.

So, let's focus on the meat of this javascript function. First, the variable x is set to the length of the checker array (in other words, the number of records that are displayed as open). If x is undefined, then good is set to true and marker is set to true. This is the case of only one open work request being displayed, so no array was created. The "else" portion of that if statement is where the action is at, and covers the normal case of more than one record displayed, and one or more of the checkboxes checked.

In the normal case, then, we loop through the records one at a time and check whether the checkbox is checked. If the box is checked, good is set to true and marker[i] is set to true. The net result is that good is true and the marker array has values of true wherever there was a matching checkbox checked. This is what we will use next time for actually closing the proper records.

One last comment about the good variable (boolean). Good is used to determine whether or not we have one or more records to close. If good is false, then no checkboxes were actually checked. If good is true, then we have some records to close.

In summary, Part I of this series showed you how to display multiple records on a web page, with each record having a checkbox and a hidden marker value. Here in Part II we have seen how to create the "Close Selected Work Requests" button, and how the closeReqs() javascript function has modified the marker array with a value of true at the index corresponding to the record index we wish to close. In Part III we will figure out what happens when document.hist.submit(); is called, and it is here that we will actually do the work of closing the appropriate work requests. See you next time!

Posted by rossm at 9:45 AM | Comments (0)

August 31, 2005

Welcome to the DHTML, PL/SQL, and Javascript Coding Category

Welcome! This category will be used to demonstrate the blending of DHTML, PL/SQL, and Javascript for some pretty slick web applications. Here at WOU we use PL/SQL Developer, and, currently, an Oracle 9i database. It is a great platform for web development, but there are very few resources that show you how to create applications incorporating all of these technologies into one package.

And I do mean "package". All of our applications consist of one or more packages stored on our Oracle database. When combined with an Apache web server, the database has the ability to serve up web pages from these packages! If you don't think that is really cool, well, I can't help you.

I am going to assume you know the basics of writing an Oracle package using PL/SQL, that you are familiar with basic web coding in HTML and DHMTL, and that you at least know what Javascript is. If you have questions about the basics in any of these areas, give me a post, and I'll try and help you out.

The most trying aspect of this coding environment is moving back and forth between them in your package. For example, let's say we have a table "mike.test_web" (mike is the schema, or owner, test_web is the name of the table). test_web has two columns, msg, a varchar2(40), with some text in it (like "Hello, world."), and num, a number column, which is a primary key, like 1.

So, if I want to display the message in a browser window, here is what my package would look like:

Create or replace package mike.test_msg is

procedure display_msg is

vtext varchar2(40);

begin

select msg into vtext
from mike.test_web
where num = 1;

htp.print('
H2 Yes indeed, this really works!/H2>br>
table>tr>td>'||vtext||'/td>/tr>/table>
'); end display_msg;

end test_msg;

I had to leave off all the "<"s so the blog server did not interpret the HTML code, but you get the idea. Don't forget your header file for the package, and compile. Then you can call your package from a browser window.
(NOTE: Your database and web browser must be configured to accomplish this task. It needs DAD's and such which are beyond the scope of this entry.)

Here it is:

You can see the web server stuff, then the package name and procedure name in the URL. Pretty cool!

Note the htp.print(' opening "tag" and '); closing "tag". This is how you move between PL/SQL code and the web code. It is pretty easy here, but it quickly gets complicated! What can you make of this line: td>'||vtext||'/td> ?? What is going on here?
If you can see that you are calling a PL/SQL variable from within your HTML, you've got it!

You should be thinking of all the possibilities this opens up for you. You can add in any HTML or DHTML (and soon PHP!) or Javascript to make applications that are dynamic and very nice looking....

Posted by rossm at 10:36 AM | Comments (0)

Processing Multiple Row Check Boxes - Part I

You can see a screen shot of this topic here on my blog under the Physical Plant category, More SWAMP Fun.... entry. The idea is to display a list of records with a checkbox for each record. You can then process the records, determine which one(s) were checked, and perform most any action on the selected group.

Let's start with an easy case. I want to simply insert a 'C' in the status column of my work request main table to indicate that this record is closed. For Part I, let's get started by setting up the display!

The first step is to display the records onto your form. We will display all open records so that the user can select which ones need to be closed. I built an HTML table and created rows like this (I replaced the <'s with _'s so the blog server would not interpret the HTML - does anyone know how to get around this?):

for i in open_req loop
htp.print('
_tr>
_td align=center>_input type=text name=reqno value='|| i.request_no||'>_td>'||substr(i.description,1,50)||'
_td>'|| i.submit_date||'_td>Open
_td align=center>_input type=checkbox name=checker>
_input type=hidden name=marker>
_/tr>
');
end loop;

open_req is a cursor that fetches all work requests that are "open", that is, all of them that have null in the status column. You can see that each table row will display the work request number, the first 50 characters of the description, submit date, a default status of Open, and a checkbox. [marker is a hidden value whose use will be seen in Part II.]
The end result is a table with all open work requests displayed - one work request per row, with an empty checkbox at the end of each row. This is a great start. Next time, we'll figure out which records should be closed, and, finally, perform the actual table update and re-display the form with a corrected list of open work requests.

Posted by rossm at 10:29 AM | Comments (0)