June 3, 2010
New Technology Study - Flash + XML + PL/SQL
Today I am inspired to begin a study of some new (to me) technology relating to web programming. Currently I develop web applications using PL/SQL, javascript, and HTML. This is a nice combination with some huge benefits in performance because PL/SQL is compiled directly in the database. But, nothing stands still in the IT world, so I'm looking to expand my horizons. I know that a popular web technology is Flash, and the "Adobe Flash CS4 Professional" software is available on our Terminal Servers. So, Flash seems like something that might be useful, plus we already have the software, so it can be learned for free. My impression is that web pages (mostly forms for me, no fancy animations) can be created more easily using Flash. This is one thing I intend to find out.
Also, it seems that whenever I turn around, someone is working with XML in one form or another. Any new application has an option for XML output. My current thinking (without actually knowing anything about it...) is to use XML as a layer for transmitting data from a Flash web page (form) to the database. Don't even know if this is possible, but it seems like it should be workable.
OK, so here I go - first step, make a flash web page!
Well, so maybe the first step is to make a super-simple Flash - html example:
Don't think this will work here on the blog server, but it's just a light blue background with a purple bouncing ball that goes through it once. Progress!
Posted by rossm at 8:42 AM | Comments (0)
January 30, 2008
Hello From Home
Well, an experiment is underway - I'm trying out working from home one day per week, on Wednesdays. Dale Goodell is manning the office at the Physical Plant. Hopefully, this will give me some time to focus on three goals:
Get my Blogging up to date
Put the finishing touches on Astra implementation
Upgrade our production database to Oracle 10gR2
The plan is to give this a try for two months. The biggest advantage so far - I can see it raining out of my window!
I'll keep you posted.....
Posted by rossm at 8:55 AM | Comments (0)
November 7, 2006
Statspack Rides Again
Dale and I had some fun working with Statspack this week. Statspack is an Oracle tool that helps you tune your database. What you do is take two snapshots of the database about 15 minutes apart. Then you use Statspack to create a (very long) report about all facets of the database. What we did was take the Statspack report from AERO and put it into "Statspack Analyzer", a free tool developed by Oracle guru Don Burleson and Texas Memory Systems (a company that makes "solid state disks" - interesting).
Well, this is a preliminary blog only as we just did manage to get the Statspack Analyzer report before we ran out of time. So, now we will investigate the recommendations made by the Analyzer and see what, if anything, we can do to tune our database. One thing that caught my eye was a recommendation for using "bulk collect" or "forall" data fetches in our applications. Huh?! Well, it's just a way to tell the database to return an entire group of records to you all at once, rather than fetching the records one at a time. We do a lot of this kind of record fetching in our web applications.
I think this is a really interesting area, so we'll see if there is anything we can do to "speed things up" on the database a bit.
Posted by rossm at 10:29 AM | Comments (0)
August 31, 2006
Oracle Analytics
Dale and I had a shortened time today, but we did manage to review a nice introduction on Oracle Analytics. Analytics allow you to use plain SQL (as opposed to PL/SQL) to gather data on row sets. There are about 20 or so Analytics functions, and they are similar to existing SQL functions such as sum and count except they can return multiple rows.
Today we managed to determine what Analytics is and also got some further research materials lined up. We feel that Analytics will help us to replace a lot of PL/SQL code with these advanced SQL functions for increased efficiency. The next step is to identify a real world problem and see if we can solve it using Analytic functions.
Posted by rossm at 11:40 AM | Comments (0)
July 13, 2006
Oracle XE and Other Stuff
Today Dale and I took a look at Oracle XE (Express Edition). This is the free edition of Oracle 10g and it has an http interface. It reminds me a bit of WebDB, not a good thing. In other words, the management functions and general interface are pretty limited. I tried to import a .dmp file and it couldn't import it.
Remaining questions are: Can you connect to this db with PL/SQL Developer? Can you connect using SQL Developer (formerly Raptor)? This db doesn't come with an Apache web server, but uses some form of http server - needs further investigation.
If we can get a more user friendly interface and also figure out some import strategies so we can get some data to work with, then we'll be able to give a better evaluation of this product.
In addition to exploring XE, we once again opened up OEM and worked on Aero's tablespace datafile allocations - making them more realistic compared to actual disk usage. I created the tablespaces with either 400 MB or 600 MB of disk space allocated. A few needed more, most needed far less, so we reclaimed a bunch of that disk space. We now have Aero's data storage pretty well allocated.
Posted by rossm at 1:47 PM | Comments (0)
June 20, 2006
I'm Back and Working on Oracle Enterprise Manager
Last Thursday Dale and I continued with our staff improvement project of Oracle database tuning, and learning other DBA type tasks. We are working with Oracle Enterprise Manager (OEM) - standalone console. In working through the OEM we are finding out some very interesting things about the Aero database. For example, Aero has been set up with a number of different tablespaces, each of which is managed by the database. Wilb had very few tablespaces, which resulted in nearly everyone's objects and data being kept in one huge tablespace. If one user of that tablespace had problems, then you could not take it offline to fix them without affecting everyone else. The design of Aero is much better in this respect, but Dale and I found that many of the tablespaces were WAY over-allocated with disk space. It was common for a tablespace to have been allocated 400 MB or 600 MB of disk space and only be using less than 10 MB. So, Dale and I used OEM to reduce the data file space allocation for those tablespaces to a more reasonable amount. We also found a couple tablespaces which had used up a major amount of their allocated space so we increased the space allocation for those. There are a bunch more tablespaces to modify, so we'll do that next Thursday.
Posted by rossm at 10:56 AM | Comments (0)
May 11, 2006
Oracle Tuning - Table Partitioning
This week Dale and I continued our pursuit of the elusive Oracle tuning knowledge. We wanted to test the effect of partitioning on a large table (about 1 million records). The table we selected is very interesting not only because of it's size, but we discovered the data tends to be clustered around certain points, such as crn's (course numbers). Therefore, figuring out the best way to partition this table was quite a challenge. In many tables you will have a primary key, or a column such as "Order Number" that keeps growing over time. These kinds of data make for pretty easy partitioning. Our table, however, has nearly 172,000 records that relate to crn's in the range of 20801 to 30000 - many records around not too many differing values.
Read on to see how we partitioned this table, and what we learned.
After considering the nature of the data contained in this table, we decided against partitioning by year and/or term. This data was mostly gathered from one term per year, so the partition with that term held about 90% of the year's data, while the other 10% was spread around the rest of the year. Not very useful as this would leave us with one huge partition and one to four tiny partitions. We finally settled upon using the course number (crn) column, and created eight partitions. Here's the code we used to create our table.
First step is to create the table:
create table raw_data_p
(QUEST_NUM NUMBER,
QUEST_VALUE NUMBER,
YEAR NUMBER,
CRN NUMBER,
SURVEY_NUMBER NUMBER,
TERM NUMBER)
partition by range(crn)
( partition part_1 values less than (20100),
partition part_2 values less than (20400),
partition part_3 values less than (20800),
partition part_4 values less than (30000),
partition part_5 values less than (30300),
partition part_6 values less than (30700),
partition part_7 values less than (31000),
partition part_8 values less than (200000));
Second step is to load up the data into the partitions:
insert into raw_data_p
(select * from raw_data);
We then created one index on the crn column, and set timing on. By the way, the Command window in PL/SQL Developer did allow us to turn on timing (set timing [on|off]).
So, now we have a partitioned table - raw_data_p to compare with the production table raw_data, and we also have the ability to time various queries against these tables. Let's try it!
First of all we tried something simple:
select count(*) from [table];
Normal: .016 seconds Partitioned: .656 seconds
(It takes time to traverse the partitions for this count.)
select * from [table] where crn = 39708;
Normal: 2.000 seconds Partitioned: 1.938 seconds
select quest_num, quest_value from [table] where crn in (36748,99815,22882,4,61792) and quest_num=5 and quest_value=3;
Normal: 4.796 seconds Partitioned: 4.172 seconds
select quest_num, quest_value from [table] where crn in (310108,1075815,20745882,1075824,2095861) and quest_num=5 and quest_value=3;
Normal: .078 seconds Partitioned: .063 seconds
What does it all mean? Well, although we did notice some gain, we weren't particularly impressed by the times. It actually takes longer to do a simple count of the records. We speculate that part of the reason that the gains were minimal is that the data in each record is small (6 number columns). Oracle is pretty fast just using the index, whether searching through 130,000 records or 1,000,000.
Next week Dale and I plan on pursuing the mechanics of tuning tables and SQL statements.
Posted by rossm at 11:54 AM | Comments (0)
April 27, 2006
Analyzing Oracle Objects and Creating Explain Plans
Today Dale and I got our hands dirty and started using some tuning tools on real database objects. Two tools we used are analyze and explain plans. The analyze tool is used to tell Oracle to collect statistics on a particular object (or you can do an entire schema all at once). Once the statistics are gathered, you can then request an explain plan to see how the optimizer intends to carry out the SQL request and what the cost of the operation will be. Read on to see a nice example.
The first thing I learned was that you must use a SQL Plus session, not PL/SQL Developer. The directives just won't work from PL/SQL Developer.
We decided to look at a fairly large table from SWAMP called LID (short for line item detail). We actually used lid_bak, a backup table, just in case... The first step is to analyze the table, like this:
analyze table fic30.lid_bak compute statistics;
Once this completes, we will set autotrace to display only the explain plan (and not actually run the SQL statement), like this:
set autotrace traceonly explain
Next, go ahead and write up your SQL statement. I used a really simple one:
select * from fic30.lid_bak where preq_no = 'PRQ135670';
And this is the output I received:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1654 Card=3 Bytes=43
2)
1 0 TABLE ACCESS (FULL) OF 'LID_BAK' (Cost=1654 Card=3 Bytes=4
32)
What the heck! It's wanting to perform a full table scan to look up the info on only one preq_no? Why? Well, looking at this backup table, I see there are no indexes on it.
I then created an index of the lid_bak table on the preq_no column.
Now let's try that SQL statement again (same exact statement):
select * from fic30.lid_bak where preq_no = 'PRQ135670';
And now look at the output:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=432)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'LID_BAK' (Cost=2 Card=3
Bytes=432)
2 1 INDEX (RANGE SCAN) OF 'BAKIND' (NON-UNIQUE) (Cost=1 Card
=3)
Ahhh, much better! Well, we accidentally learned something here - the value of a good index! Look at the Cost of using the index = 1 , compared to the cost without an index, or a full table scan = 1654. Very interesting.
Next, we turned our attention to a table Dale has been working with sir.raw_data. We ran the statistics on that one and then tried a couple SQL statements against it. One thing we noticed is that this table has about 6 indexes on it, and the optimizer only seems to be using one of them. This is true even if we request columns in our SQL statement that exactly match the columns the index is based on. This means these extra indexes are just excess baggage. Also this table has more than 1,000,000 rows so is a good candidate for partitioning. Next week we're going to fiddle around with this table and see if we can use some timing statistics to show improvement in queries against this table by using some of our new tuning techniques, such as removing unused indexes and partitioning.
Posted by rossm at 11:36 AM | Comments (0)
April 20, 2006
Oracle Database Tuning
Today Dale and I finished up the Oracle DB Tuning course offered by VTC. VTC is something I read about on Joe's blog and it has some useful material for us to use. This particular course/area is pretty dense, and there was no way to obtain the scripts used in the course, so Dale and I started taking screen shots of the most important scripts for later use.
We covered three important areas today. First, was dbms_stats. This is a built-in Oracle package that creates statistics. This package can be used for such things as exporting statistics from a production database to a test database which will give pretty good results on tuning even if all the data isn't in the test instance. This points out the main reason for collecting statistics in the first place - the Cost Based Optimizer (CBO) uses them to create the explain plan (that is, to decide how a particular query will be processed). The more statistics, the better.
A second way of creating statistics is by using the Analyze command. This is a way of controlling the collection of statistics from the command line, and is generally pretty straight forward to use.
The final thing we studied today was Oracle statspack. This is a way to collect statistics about the actual performance of a running database. The idea is to take a snapshot of the database, then take another one some time later (15 minutes, say), then use statspack to compare the two snapshots. This one gets pretty involved. I've run it on Aero a few times, but digesting the results is quite a challenge. In typical Oracle fashion there is more information there than you can sift through in a reasonable amount of time.
Next, we plan to implement these different items, and Oracle Enterprise Manager as well, to see if we can identify poorly tuned SQL queries or other bottlenecks on a database.
Posted by rossm at 3:02 PM | Comments (0)
March 23, 2006
RMAN Is "Handled"
As you know, Dale and I have been expanding our knowledge of Oracle recovery and restore, particularly using Oracle's Rman - recovery manager. We have studied the Oracle manuals and gotten a good handle on the concepts involved and the things needed to make restores available on Aero. The roadblock we've hit here is this: first, we can't play with rman on our production database. Second, we talked with Troy and he has already got rman going on a test instance of 10g. Furthermore, the current goal is to move Aero to another server and upgrade to 10g this Summer. So, it seems unlikely that we will be able to get anything satisfactory in place much before the database is moved and upgraded. Therefore, we've concluded that pursuing rman on Aero any further isn't a good use of our time. We've gathered some valuable knowledge and we're going to take on something new. We both agree that learning more about Oracle is the direction we want to take. Presently we're considering two things: performance tuning or Analytics. We'll keep you advised of what we decide to do. Either of these options will be useful whatever version of Oracle we have running.
Posted by rossm at 11:45 AM | Comments (0)
March 17, 2006
RMAN Recovery Project
Dale and I have been learning the ins and outs of Oracle's rman recovery tool. Our current backup and restore process is good, and in the event of a major calamity we could restore our entire database in a reasonable amount of time. However, we do not have the ability to easily recover erroneously dropped objects, truncated tables, and such. It can be done, and it has been done, but we were hoping to put Rman into action to make the process easy and quick. So, our goal is to create a process whereby dropped or lost database objects can be easily restored.
We have taken the first big step in reaching our goal by chopping Aero into many, smaller tablespaces. What this does is allow us to restore objects in a single tablespace, which can be done by taking only that tablespace offline. The rest of the database will still be up and running, available to all other users. Wilb had essentially one huge tablespace (WEB) and all users shared this one tablespace, so taking it offline wasn't practical as it would effectively shut down the entire database. A tablespace is the smallest unit that can be manipulated when performing a restore.
An important piece of information: Oracle calls this process we are talking about RMAN TSPITR (Recovery Manager Tablespace Point In Time Recovery).
We were able to pretty easily connect up to Wilb in Rman. We were even able to take a test tablespace offline and completely restore it. Unfortunately, this is where "easy" went out the window. There is a HUGE difference between a complete restore of a tablespace (easy) and TSPITR (very difficult). The difference is that a complete restore can be done from the original database and the TSPITR has to be done from a CLONE (or AUX) database. In other words, you must create a completely separate database in which to do the TSPITR recovery, then export the object from this database back to the "real" database.
Another hard won piece of knowledge: The CLONE database must be started with a tablespace backup and a control file backup that PREDATES the time when the target object was lost. Think about this for a minute because it makes no sense right off. To perform a recovery, you have to be able to go back in time, start up the CLONE at this earlier time, then you can roll the CLONE forward in time until just before the object was dropped (using the archive logs). Once this makes sense to you, then you will be pretty much caught up with Dale and me. This is the piece (well, one of several as it turns out) that we are missing right now - you must have Rman running and performing Rman backups of the tablespaces and the control file BEFORE you can perform TSPITR. So, the first step is to divide up your database into nice, compact sized tablespaces. The second step is to get Rman up and running so that you have an Rman tablespace backup and a backup of the control file (the control file backup essentially contains a "snapshot" of the database state at a certain point in time). Notice that these backups don't have to be real regular - the only difference is how many archive logs you have to use, the key is that the backup PREDATES the losing of an object. For Aero we have months worth of archive logs, so any kind of reasonable rate for backup will work.
So far, so good. Now for the bad news. For reasons presently unknown, Rman on Wilb can not be configured. Issuing standard configuration comands to do such critical items as start auto backup of the controlfile throw errors, cause the database to go crazy, and in one case, to hang the entire database. The other bit of bad news is that, so far, we don't have the knowledge to run Rman on Aero in the clustered environment - can't even connect. Well, you know, the heart of computing science is in the solution of seemingly insoluble problems. That's where we are right now. We've done it before and we'll do it again!
So far, we've collected a solid base of knowledge of Rman. We at least know what it is we need. Now, we just have to get there. Also, we need to make our solution workable in 10g when we upgrade our production database. We're anxious to get to the point where we can provide this service to our fellow developers.
Posted by rossm at 8:35 AM | Comments (0)
February 23, 2006
Some More Wilbur
After getting a slow start today, Dale and I once again delved into the 129 schemas on Wilb that need/needed to be migrated to Aero. With the notable exception of Admissions, which Summer is in the process of rewriting, Dale and I agreed that it was as ready as it could get. We updated our Excel spreadsheet that tracks all the schemas, and also Dale made his first Wiki page (WilbUrls).
We created WilbUrls as a handy reference for the schemas that are still being hit on Wilb, even though the applications have been migrated to Aero. That way, the help desk can easily direct lost users to the correct URL they need to get to the Aero version of their app.
Next, Dale and I decided we'd like to learn to be Oracle DBA's, make a bunch of money, and retire to the Bahamas. Well, maybe we'll just expand our knowledge of Oracle DBA stuff. There is a lot we'd like to learn including backup and recovery (RMAN), upgrading to 10g, using Oracle Enterprise Manager, upgrading our SQL skills to utilitze the new grid computing elements, and many more.
Posted by rossm at 1:08 PM | Comments (0)
February 16, 2006
Nailing Down Wilbur
Dale and I continued our assault on the loose ends of database migration from Wilb to Aero today. We have been collecting data on which packages are still being used for about two weeks. For all schemas except three, we're as ready as we're going to get for shutting down Wilb. We have posted notices that appear in each schema directing users to the Aero URL, but without doing so automatically.
Three schemas are in the process of major rewrites, so there's no choice but to leave them on Wilb until the rewrites are completed. Summer is working on Admissions, Ron is working on a web calendar application, and Camilla is working on a library reference app.
Dale and I both felt pretty good about the status of Wilb now. By the way, if you are a member of UCS staff, there is an excel spreadsheet on i:\\db_migration_aero\newschemas.xls that contains information on all the schemas.
When we got done with our Wilbur chores, Dale showed me the way to use LDAP authentication. I've got a campus-wide app that needs to be converted over to use LDAP, so will begin work on that soon.
Posted by rossm at 1:42 PM | Comments (0)
February 9, 2006
Attacking WILB
For the past three weeks Dale and I have been readying WILB for the great computer boneyard. Our idea was to go through ALL of the WILB schemas and place email calls in key procedures to track which schemas are still being used. We dropped the redirect procedures which had been in place, and added in a procedure that gives a user the correct, new URL, but does not redirect them there. In other words, all links to WILB are more or less dead ends. We felt like this was a good "half-way" measure before WILB goes away permanently.
Everything has now been migrated to AERO with the exeption of the Admissions schema which was so bad that Summer is just rewriting the whole thing. Thank you, Summer! Dale and I wrestled with the current application, and got most of it's errors cleaned up, but it had gotten pretty ugly.
For now, Dale and I will monitor the procedures that are still being accessed on WILB. Hopefully, those will diminish over the next few weeks, and we'll be able to gracefully say goodby to our old production database WILB.
Posted by rossm at 1:09 PM | Comments (0)
January 19, 2006
WILB Migration - The End Is In Sight!
Dale and I spent a couple more hours working on migrating the last of the apps on Wilb over to Aero. We are placing a call to the wou_util.web.redirect procedure in all Wilb procedures which show access. The schemas we worked on today were pchp, comments (won't be migrated), and publicbp (Federal work study).
Today we also wrote a small procedure that does not redirect users to the new page, it simply gives them the URL, but they must take some action to get the app. This is a kind of "half-way" step, so that users are encouraged to get the Aero URL in place. Hopefully, this will reduce the number of users who will hit the Wilb apps, who knows.
We also decided to speak with Joan G. and Ron S. to get the helpdesk/webmaster a list of applications and the correct URL's in case some users contact them.
Two major schemas are left on Wilb now - Admissions and Public_relations (calendar events). Summer is in the process of rewriting the admissions application. Moving it would just be too much work, and it really needs a good rewrite. It was written using WebDB. Ron S. is working on the public relations calendar.
Next week, we are going to replace more of the re-directs with notices. Also, we will go through all the Wilb schemas again and make sure we haven't overlooked any. If time allows, we'll put together a comprehensive list of schemas/apps and their correct URL's.
Posted by rossm at 2:06 PM | Comments (0)
January 13, 2006
Chasing Admissions, Looking at Longs
This week Dale and I worked again on settling down the admissions application so it was at least presentable. Although we were able to get most of the images transferred to the secure folder, there are a couple WebDB references still out there somewhere that are causing minor trouble. Travis installed the new SSL Certificate and that major error is now history. Troy helped us out by creating an image folder on Wilbur.
One of the greatest frustrations with the legacy WebDB applications is that several of the tables contain long data types. I have discovered a relatively simple way to at least view a piece of the long data. Read on to see how...
Long data types were available up to about Oracle 8i when they got replaced by LOB's (CLOB's and BLOB's). Long data types had a lot of disadvantages and were quite difficult to work with. Nothing has happened to make that any easier except for the to_lob() function available in Oracle 9i and on.
To view long data, you first create a table that contains the new CLOB (BLOBs are binary LOBs) datatype:
create table lobo (itemid number, itemdesc clob);
What we will do is insert into lobo the long from a table and use the to_lob() function, like this:
insert into lobo
select app_template_id, to_lob(the_value)
from webdb.wwv_usr_app_template_details$
[the_value is the column name of the long datatype]
Now, from a command line we'll select * from lobo and get a result like this:
ITEMID ITEMDESC
---------- --------
12019 <HTML>
<HEAD>
<TITLE>#
</HEAD>
<BODY BG
This is as far as I got. You can see that you're only getting a peek at the long data, not the entire thing. But, hey, you can see something!
Posted by rossm at 8:40 AM | Comments (0)
December 2, 2005
Toad's Wild Ride
Do you remember that funky ride at Disneyland where you get in the old car and go careening through the mansion? That pretty much sums up this Thursday's staff development time!
First, I met with Brian W., Mike S., Dale and Summer to plan the move of Safety/Parking off of Wilb and onto Aero. We discovered that a lot of the work has already been done, and divided up the remaining work among ourselves. I called Cheri and Nancy at public safety and got a time scheduled to move this behemoth. Good deal!
Let me just sum up the rest of the morning with - Good Job Troy! He bounced the Aero database, with some guidance from Travis, and all was well again. Many questions raised about developing on the production database....
Time's up! Nuts! Well, Dale called me and the PHP books he ordered came in, so we're going to pursue that......next week.
Posted by rossm at 9:53 AM | Comments (0)
November 28, 2005
PHP and Oracle - Running a Stored Procedure
It occurred to me that, in order for PHP to be useful at all, we needed to be able to either put it inside a stored procedure or at least be able to call a stored procedure from PHP. After a bit of messing around we determined that putting PHP inside a stored procedure is not going to work. The Apache web server does not know what to do with the PHP code. On the other hand, it IS possible to call and run a stored procedure from PHP.
We have had success in connecting to our Oracle database, running simple SQL queries, and displaying the results. The connection to the database was pretty straightforward using the db.php include file and db::connect() built-in function.
There was not much in the way of resources out there that talked about this particular problem. We did find a post that got us started, and used the oci_connect and ocilogon built-ins. These gave us fits, and we never did get these functions to work for us. We experienced a problem with the database connection, where it just would not work. Apparently, this has happened before, and there is no apparent cause for it. The connection just never gets made, no error, no nothing. Need more work on this problem before we go to a production environment.
We are getting errors in using functions like this: oci_new_collection(), but no errors in functions like this: ocilogon(), so we may have some kind of versionitis working against us.
In summary, we have learned that PHP can not be embedded within our stored procedures using our current configuration. Stored procedures can theoretically be called from PHP, but we have not settled on a reliable way to do this yet. The following is the code we have to work from for now:
Connection established
require_once 'DB.php';
$dboptions = array(
'debug' => 2,
'portability' => DB_PORTABILITY_ALL,
);
$dsn='oci8://fic30:aeroace@aero.wou.edu';
$user="fic30";
$pwd="aeroace";
$sid="aero.wou.edu";
# $dbh= oci_connect($user,$pwd,$sid);
$dbh=ocilogon($user,$pwd,$sid);
$dbconn = DB::connect($dsn, $dboptions);
if (PEAR::isError($dbconn)) {die ($dbconn->getMessage());} #if we could not connect, quit the whole file and print message
else {echo "
$sql="BEGIN PHP2(:p_result_value); END;";
# $sql = "select * from um";
$stmt=OCIParse($dbh,$sql);
# $res=$dbconn->query($sql);
$result_values=oci_new_collection($dbh,"DALE_TABLE","FIC30");
OCIBindByName($stmt,"p_result_value",$result_values,-1,OCI_BNTY);
$rslt=OCIExecute($stmt);
echo "Number of elements in collection:";
print_r($result_values->size());
echo "";
echo"Results:";
print_r($result_values->getElem(0));
print_r($result_values->getElem(1));
?>
Posted by rossm at 1:13 PM | Comments (0)
November 17, 2005
November 17th = PHP Continues, Wilbur DB Link
This week Dale and I decided to press on with learning more PHP. We worked a bit more on our database connection code, and learned how to print out the results from a simple db query using an array. PHP stored the results from our query in an array (ums), and we were able to print out to the screen all of the different kinds of units of measure there are.
Next, we went to the Zend website and did a bunch of beginning tutorials there, such as include files, security, and file structure rules. Oh yes, we started on a tutorial on Regular Expressions, but decided that, um, it was getting mighty deep mighty fast and we, um, didn't want to get sidetracked from our pursuit of PHP. Later on the Regular Expressions!
I just found another tutorial which looks good - it's more than 11 parts, here .
Our time got cut short a bit because Brian W. came over and needed to do some priority work on Wilbur db links, so we helped him. We also had some good discussions about resurrecting our Unix/Solaris box and Oracle 10g install group as we all miss that one! I was able to talk with Mike Ellis and Ron S. about the pros and cons of PL/SQL, JS, PHP, and Pearl. Ron doesn't use PL/SQL, Mike E. doesn't use hardly anything BUT PL/SQL (because it's compiled on the DB, a notably good reason). I'm thinking we should be able to use a Merry Mix of all these things to optimize our applications.
Next week is Thanksgiving! (Yay!) so we will not meet again until December 1st. It looks like moving the last two schemas off of Wilb and on to Aero is becoming a heavy priority, so we may cool our jets on PHP for a week or two, gather together some UCS brainpower, and get those two schemas moved once and for all. They are tough ones as they are heavily intertwined with the Banner databases.
Posted by rossm at 3:29 PM | Comments (0)
November 10, 2005
Out With the Solaris/Oracle Box, in With PHP
Well, our group project of building the Solaris box and installing Oracle 10g thereon has hit a major snag. Shaun got "pulled" to work on another project and so did the hardware! We were disappointed, but will take up with the Oracle install when we can.
In the mean time, Dale and I decided to press on with PHP. Using my primary programming strategy - stealing from someone else - I "borrowed" some code from Ron who is way ahead of us in PHP. In this way, Dale and I were able to connect to AERO through PHP, and run a simple select statement. Here's what the code looks like:
require_once 'DB.php';
//This is a call to the php module that does database communication - kind of like an include file.
$dsn = 'oci8://username:password@sid';
//Set a variable with the Oracle connection string information.
$dboptions = array(
'debug' => 2,
'portability' => DB_PORTABILITY_ALL,
);
//A bit of fanciness Ron uses to implement portability. One of the main uses for this is to implement associative arrays (another new term).
$dbconn = DB::connect($dsn, $dboptions);
//do it - connect up using the variables we created.
if (PEAR::isError($dbconn)) {die ($dbconn->getMessage());} #if we could not connect, quit the whole file and print message
$sql = "select * from um";
$res = $dbconn->query($sql);
//Set the $sql string and run the query. Could also put the query inside the query() brackets.
if (PEAR::isError($res)) { die ($res->getMessage()); }
//If the query errs, capture the error and quit.
print "Num rows:" . $res->numRows();
//TaDa - this is what printed for us - Num rows: 39.
while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
$ums[$row['abbr']] = $row['description'];
#print "$row[abbr] - $row[description]
"; #debug
}
//What the heck, fill an array with the query results. Next time figure out how to use this.....
$res->free(); #get rid of the query now that we have the data into the $ums array
$dbconn->disconnect();
//close connection.
There it is. Enough time for Dale to eat a donut, too.
We all agreed that PHP is not going to be useful unless we can master DB tasks with it. In talking to Mike Ellis, he raised an interesting question - why would we use PHP, which is not compiled, rather than PL/SQL, which is compiled?? Good question. I don't know.
Posted by rossm at 12:51 PM | Comments (0)
November 3, 2005
PHP 101
Today Dale and I worked on a beginning PHP tutorial. We believe that our applications will be more robust if we learn to code up some PHP. Ron has gotten PHP enabled on AERO, so we were all set to go.
The first thing I noticed was how easy it was to get a simple page up. I literally saved a notepad file with a .php extension, and that's all it took.
Next we learned a bit about super-global variables in PHP. The $_SERVER super-global returns quite a bit of information about the browser being used. This code:
_?php echo $_SERVER['HTTP_USER_AGENT']; ?>
returns this kind of information:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR
1.0.3705; .NET CLR 1.1.4322)
You can then look through this output for the string 'MSIE' to determine whether a person is using Internet Explorer. The code looks like this:
if (strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE') !== FALSE) {
echo 'You are using Internet Explorer.
';
}
?>
and outputs the phrase after the echo above if you are using IE.
The next concept we covered was that you can put HTML tags in with the PHP tags, and they will be interpreted correctly. You do not have to switch between PHP and HTML mode. This is not true of Javascript, HTML and PL/SQL. Very neat!
Next, we learned a bit about function calls in PHP. There are a number of built in functions (the strpos() call used above is a good example) and they are called pretty much like any other language.
The coolest thing we covered today was the implementation of a simple form like this (all <'s replaced with _'s to display):
_form action="action.php" method="post">
_p>Your name: _input type="text" name="name" />_/p>
_p>Your age: _input type="text" name="age" />_/p>
_p>_input type="submit" />_/p>
_/form>
The form looks like this:
We then created the action.php file, which looks like this:
Hi _?php echo $_POST['name']; ?>.
You are _?php echo $_POST['age']; ?> years old.
And gave us output like this:
Hi Mike. You are 34 years old.
Note that no variables are created at all. The $_POST built-in from PHP handles it. You just have $_POST display the value from the form text box. I thought the "submit query" button that is displayed is kind of like magic, where did it come from? It must also be built-in to PHP.
Unfortunately, we ran out of time here. My first imPHPressions are that PHP will simplify some aspects of our applications for us, and will be a powerful new tool to use to make our apps more robust. I thought we were able to cover a lot of territory pretty simply, and am anxious to expand my knowledge of PHP.
By the way, we were following a beginning tutorial from the official PHP site at: http://us2.php.net
Posted by rossm at 11:14 AM | Comments (1)
October 27, 2005
Solaris 10g Box locked down and readied for Oracle 10g
Since I was sick last week, this was the first time for me to participate in the Unix/Solaris + Oracle 10g group. The goal of this group is to install Solaris 10 on a box from scratch (which they did last week), lock down the security on the box, then install Oracle 10g on it.
This week Shaun, Mike Ellis, Dale, and myself worked on locking down security on the box. We were given a list of commands from Travis and Troy. We executed these commands, which did such things as lock down access to certain directories to root; disabled services such as FTP, Telnet, and smtp; and changed the default shell to tcsh. We actually screwed up the tcsh part (put in tsch by mistake). This was interesting because when we went to log in after a reboot, all we got was an error message saying there was no shell. Travis rescued us, though, and showed us how to use stop+a to get a command line, then go in and fix the error. The box then booted up just fine.
The next thing we did was to modify the /etc/system file by adding oracle parameters. This is required before performing the actual 10g install. Mike Ellis looked up the meaning of the parameters, and we entered these values:
shmmax=4294967295
shmmin=1
shmni=100
shmseg=10
semmsl=256 (this is 10 + max processes allowed by the db)
semmns=430
semopm=300 (we originally had 100, recommended is 500, we averaged)
semvmx=32767
Next week we will do the Oracle 10g install and see if these parameters are good enough for it to work!
While modifying the /etc/system file, Shaun demonstrated his VI editing skills, and shared with each of us a VI reference sheet he had. We all need some work on our VI skills. Travis gave us a few pointers as well.
In summary, this was not the most exciting work, but it was very necessary! I'm grateful for the chance to see how to lock down a Unix box, and was able to get a good grasp on it in a short amount of time. I'll probably get a good grasp on VI about the same time h, e, double hockeysticks freezes over.....
Posted by rossm at 11:28 AM | Comments (0)