Wiki Home

Basic Oracle For VFP

Namespace: VB
Wednesday Night Lectures Basic Oracle For VFP by Mark McCasland, held on 2001/04/18

Proposed Outline:

1. Tables, Constraints and RI
2. Indexes
3. Sequences
4. Triggers
5. Roles and Synonyms
6. Script Files
7. Export and Import Utilities

If we do not cover every topic, I will schedule a second session.
What can we do to prepare for this?

As far as preparation, a basic knowledge of SQL is really all that is necessary. I intend to cover topics like SQL script files, sequences and triggers. -- Mark
Afterwords there may be a bit of VFE fun as I trace down an issue:

The view has an ordinary data entry screen. When I enter information into the field in an existing record and save it, I get from VFE a messagebox with ORA-00997 Illegal use of LONG datatype.

If I change the field and do a tableupdate(.f.,.t.) in the command window and it works with no problems.
Question which may not belong here :

Many years ago I heard of some overlaying tool that realtime "converted" Seek's and whatever Fox-DB-command to Oracle-commands, thus leaving the app untouched but useing Oracle as DBMS;
Does this (still) exist ? and if yes, does it work for SQLServer too ? or just for any ODBC-compliant DBMS (7's DBServer) ?
Or am I wrong at all ?
Thanks. -- Peter Stordiau (never mind, ref. Better Integration Of VFP With SQL Server)

[21:05] {MarkMcCasland} OK. Let's try to get going here. First topic -- Tables, Constraints and RI

[21:05] {CarlKarsten} let er rip

[21:06] {MarkMcCasland} The first time I did a VFP app, I created the db in VFP then upsized. I do not do that anymore.

[21:06] {MarkMcCasland} The key is creating and maintaining SQL script files

[21:06] {MarkMcCasland} These files have a SQL extension and can be run like DOS batch files in SQLPlus

[21:06] {MarkMcCasland} SQL Plus is a command line software pkg that connect directly to your Oracle DB.

[21:06] {BobMariani} What if we already have a complete project and need to update to Oracle.... scripts or upsizinging wizard?

[21:07] {MarkMcCasland} When I used the upsizing wiz, I only let it create the files.

[21:07] {MarkMcCasland} I then opened thos tables and copied the SQL from the memo files into a SQL Script file

[21:08] {MarkMcCasland} I then used SQL plus to run the scripts to create the tables, indexes, etc.

[21:08] {MarkMcCasland} I then created remote views in a VFP DBC to load the data by appending from the VFP tables.

[21:08] {MarkMcCasland} Make sure you TABLE BUFFER the views!

[21:09] {MarkMcCasland} That way, you control the TableUpdate(). With Record buffering, you get a tableupdate

[21:09] {MarkMcCasland} executed after each appended record.

[21:09] {CarlKarsten} Is there ever an automatic update when using table buffering?

[21:09] {MarkMcCasland} No.

[21:09] {MarkMcCasland} You can close a view without doing a tableupdate and nothing gets sent back to the server

[21:10] {CarlKarsten} got it

[21:10] {BobMariani} Is there any good articles or books on upsizing VFP to Oracle?

[21:10] {MarkMcCasland} Not that I am aware of. Plenty of threads on UT if you search on Oracle in the VFP forum.

[21:11] {MarkMcCasland} I don't think there is much on VFP upsizing to Oracle though.

[21:11] {MarkMcCasland} There are several members there that are quite good at answering question on VFP/Oracle issues.

[21:11] {BobMariani} how are fields with RTF formated data handled?

[21:12] {MarkMcCasland} You would need to store that code in a LONG data type field

[21:12] {MarkMcCasland} I want to post some code on creating a table with some constraints.

 	(KeyID           number(8)    CONSTRAINT pk_permits_keyid PRIMARY KEY,
  	Facility_ID      number(8)    CONSTRAINT fk_permits_facility REFERENCES Facility(KeyID),
  	Permit_Num       char(9),
  	State_Num        char(9),
  	City             varchar(30),
  	State            char(2),
  	Original_Issue   date,
  	Current_Issue    date,
 	Effective        date,
 	Expiration       date,
  	Public_Notice    date,
  	Facility_Type    char(1)         DEFAULT ' ',
  	Major_Ind        number(1)       DEFAULT 0 CONSTRAINT ck_permits_major_Ind   CHECK (Major_Ind BETWEEN 0 and 1),

[21:12] {BobMariani} What do I use for Gen fields? I assume binary?

[21:13] {MarkMcCasland} Here there are several constraints - a PK, FK, and a Check constraint along with a default

[21:14] {MarkMcCasland} [Bob] I am not sure of that one.

[21:14] {MarkMcCasland} Your choices would be a special Oracle data type -- Long, Raw, Long Raw, Blob or Clob

[21:15] {MarkMcCasland} My guess would be Raw or Long Raw.

[21:15] {MarkMcCasland} Disclaimer: all of what I am doing is applicable to Oracle version 8.x and higher

[21:16] {MarkMcCasland} In Oracle there is no LOGICAL data type. So of you notice my Major_Ind field

[21:16] {MarkMcCasland} is a numeric data type of length 1, I put a check constraint on it to make sure it only accepts a value of 0 [false] or 1 [ true]

[21:17] {MarkMcCasland} The Facility_ID is a Foreign key and references the KeyID column in the Facility table

[21:17] {MarkMcCasland} Now the RI part. A sample of code to cascade deletes

 create table waterbody_parms
     (KeyID          Number(8)    CONSTRAINT pk_wb_parms_keyid  PRIMARY KEY,
      WaterBody_ID   Number(8)    CONSTRAINT fk_waterbody_keyid REFERENCES NPDES.Waterbody (KeyID) ON DELETE CASCADE,
      Parm_ID        Number(8)    CONSTRAINT fk_parmcodes_keyid REFERENCES NPDES.Parameter_Codes (KeyID))
      Tablespace NPDES;

[21:18] {MarkMcCasland} Some Oracle documentation may be confusing. But what happens is you put

[21:19] {MarkMcCasland} the DELETE CASCADE in the child table FK. If the parent record is deleted, the child records in this table will be deleted

[21:20] {MarkMcCasland} If you already have a table with no PK index:

[21:20] {MarkMcCasland} alter table permits add constraint pk_permits_keyid primary key (keyid) using index tablespace npdes;

[21:21] {MarkMcCasland} To drop a PK:

 alter table permits drop primary key;

[21:21] {MarkMcCasland} Ready for Indexes?

[21:22] {CindyWinegarden} Yep

[21:22] {MarkMcCasland} Real easy:

 create index I_ADDRESS_HISTORY_FACILITY_ID on npdes.ADDRESS_HISTORY (FACILITY_ID) tablespace npdes; 

[21:22] {MarkMcCasland} The column[s] to be indexed are in parens.

[21:23] {MarkMcCasland} Most server setups include multiple drives and the indexes are typically on a different drive than the tables.

[21:23] {MarkMcCasland} Unfortunately, our Oracle DBA is chincy.

[21:24] {CarlKarsten} is the name of the index ever used for anything?

[21:24] {MarkMcCasland} Now if all goes wrong and you need to import a database and you want to start over, here is some deadly code to run from a SQL script file:

[21:24] {KellyConway} I have q's at some point re: how to set up an index to make sure it is used and/or how to hint

[21:25] {MarkMcCasland} [CK] no. Oracle uses it automagically. You never have to specify an index in SQL

[21:25] {KellyConway} But doesn't it only use one index per query or something silly like that?

[21:25] {MarkMcCasland} [KC]All you have to do is create an index on every field you query against -- same pricipal as in VFP except there is no such thing as an index on DELETED()

[21:26] {KellyConway} I was told you have to create concatenated keys that include everything in every where clause...

[21:26] {KellyConway} Obviously not the case from what you are saying - thanks.

[21:26] {MarkMcCasland} No. Oracle will first parse your SQL and optimize it. Basically, the same principle as VFP

[21:26] {KellyConway} Thanks

[21:27] {MarkMcCasland} In Oracle, when you create a database, you get the Oracle DBA to create a user name which is usually the name of your app. In my example the user name is NPDES

[21:28] {MarkMcCasland} So, anything you create will be created with that user ID as the owner. No one can issue any SQL commands against your objects

[21:28] {MarkMcCasland} unless you give them certain privileges. More on that later. Now for some cool code to delete all your objects under your user ID [schema]

set feedback off
  set head off
  spool d:\vfp5apps\npdes99\scripts\drop_all_objects.sql
  select 'drop ' || object_type || ' ' || object_name || ';'
  from user_objects where object_type != 'INDEX'
  order by object_type, object_name;
  spool off
  set head on
  set feedback ON 

[21:29] {MarkMcCasland} What this does is create a concatenated string 'drop table mytable'

[21:29] {MarkMcCasland} The object type can be table or view. I omitted INDEX because when you drop a table, all its indexes are dropped as well

[21:30] {MarkMcCasland} SPOOL just tells SQL Plus to create a file [drop_all_objects.sql]

[21:30] {MarkMcCasland} This is the same as directing all output to a text file using Text Merge

[21:31] {MarkMcCasland} The SELECT command queries the USER_OBJECTS table for all the objects I have created. 'DROP ' is a literal

[21:31] {MarkMcCasland} the || is the concatenation operator which is analagous to the plus sign in VFP

[21:32] {MarkMcCasland} This really is useful in development when you are working at 2 places.

[21:32] {MarkMcCasland} I have Personal Oracle here at home on my PC. When I go back to the office I export my database.

[21:33] {MarkMcCasland} Before I import the database on my office server, I blow away the current development DB before importing.

[21:33] {MarkMcCasland} Ready for SEQUENCES?

[21:33] {MarkMcCasland} Sequences are a slick and mindless way of generating PKs.

[21:33] {CarlKarsten} not that I want you to explain all the possibilities, but are there other ways to move data like that?

[21:34] {MarkMcCasland} The Import and Export are ways of backing up and restoring your database.

[21:34] {MarkMcCasland} For existing database where you need to load massive rows of data, there is the SQL Loader utility

[21:34] {CarlKarsten} and a good way to move your data between development machines?

[21:35] {MarkMcCasland} IMO, Export then Import is better during development.

[21:35] {CarlKarsten} got it

[21:35] {MarkMcCasland} Once you go production, you of course can not blow everything away.

[21:36] {MarkMcCasland} So using Remote Views and APPEND FROM or SQL Loader are the best ways to load large quantities of data

[21:36] {MarkMcCasland} In an intermediate WNL, I could go over some of that stuff.

[21:36] {MarkMcCasland} Now for a sequence:

 create sequence S_NPDES_ADDRESS_HISTORY increment by 1 start with 1 nocache; 

[21:37] {MarkMcCasland} The beauty of this is Oracle does all the work for you. Just 1 caveat -- you need to pair the sequence with an Insert trigger.

[21:38] {MarkMcCasland} Here is a trigger example:

 create or replace trigger ACTION_TYPE_BEFORE_INSERT
  before insert on npdes.ACTION_TYPE for each row
  v_Id Number;
   If :new.KeyID Is Null or :new.KeyID < 1 Then
   select s_npdes_ACTION_TYPE.nextval into v_Id from dual;
   :new.KeyID := v_Id;
  End If;

[21:38] {MarkMcCasland} This is also a Stored Procedure [PL/SQL code -- kinda like VB { gasp! } ]

[21:39] {MarkMcCasland} So when you create that remote view or use SPT to insert a row, never make the KeyID field updatable or pass that field in SPT

[21:39] {MarkMcCasland} In my tables, I also have 2 fields -- LastUpdate and UpdatedBy.

[21:40] {MarkMcCasland} I use triggers to populate those fields as well so I know who the last person to update the row was and when.

[21:40] {MarkMcCasland} Here is a trigger for that:

 create or replace trigger ADDRESS_HISTORY_BEFORE_INSUPDT
  before insert or update on npdes.ADDRESS_HISTORY for each row
  v_Id   Number;
  v_User Char(8);
  v_Date Date;
   select USER into v_User from dual;
   :new.Updated_By := v_User;
   select SYSDATE into v_Date from dual;
   :new.Last_Update := v_Date;
   If :new.KeyID Is Null or :new.KeyID < 1 Then
      select s_npdes_ADDRESS_HISTORY.nextval into v_Id from dual;
      :new.KeyID := v_Id;
   End If;

[21:41] {CarlKarsten} what is "dual"?

[21:41] {MarkMcCasland} All of this code is stored in a SQL script file. In SQLPlus all you do to run it is issue the command -- @my_script_file

[21:41] {MarkMcCasland} Dual is a dummy table that always returns exactly 1 row.

[21:41] {MarkMcCasland} The purpose is that every SQL command has to have a FROM clause

[21:42] {KellyConway} Which always made me wonder why they call it "Dual" vs. "Single" :-)

[21:42] {MarkMcCasland} So when you are not selecting from a specific table, just SELECT SYSDATE FROM DUAL to return exactly 1 row of results

[21:42] {CarlKarsten} is that normal, or do you have to set up that table?

[21:42] {MarkMcCasland} Oracle creates it when the Oracle instance is started

[21:42] {CarlKarsten} right-o

[21:43] {MarkMcCasland} Every one cool with Sequences and Triggers? I have been kinda covering scripts.

[21:43] {KellyC} Is is worth mentioning...

[21:43] {MarkMcCasland} So I can go on to miscellaneous topics. Or just field any questions.

[21:43] {KellyC} that you can also query the sequence as part of the insert?

[21:44] {CarlKarsten} Sequences is your PK genarator, right?

[21:44] {MarkMcCasland} {KellyC] Scripts?

[21:44] {MarkMcCasland} [CarlK] Correct

[21:44] {KellyC} Meaning, do I have one to demonstrate?

[21:44] {MarkMcCasland} All you need to generate INTEGER PKs is a sequence and a trigger.

[21:44] {MarkMcCasland} [Kelly]I did not understand your comment/question

[21:45] {KellyC} You can do something like SELECT {sequence}.NextVal from dual to get the next value

[21:45] {KellyC} Then you can do your insert with that.

[21:45] {MarkMcCasland} Correct

[21:45] {KellyC} If you don't want to use triggers, that is.

[21:45] {MarkMcCasland} Correct.

[21:46] {MarkMcCasland} If you want to know what the current value of the sequence is -- select {sequence}.currval from dual

[21:46] {RickSchummer} Is there a cost to Personal Oracle or is this available like MSDE?

[21:47] {MarkMcCasland} If you want you can create a remote view to your sequences and use REQUERY() to keep incrementing the PK value

[21:47] {MarkMcCasland} [Rick] PO is free if you download it from the Oracle web site. Got a T1 or better?

[21:47] {RickSchummer} Nope 144 DSL {g}

[21:47] {MarkMcCasland} That works!

[21:49] {MarkMcCasland} A most useful URL for Oracle 8 Documentation:

[21:50] {MarkMcCasland} How about a word on Roles and Synonyms?

[21:50] {KellyC} Sure

[21:50] {MarkMcCasland} Once you create your database, it is basically useless to users unless they have privileges to it.

[21:51] {MarkMcCasland} The basic privileges are Insert, Update, Select and Delete

[21:51] {MarkMcCasland} The hard way is to grant these priviliges to every user for every table.

[21:51] {MarkMcCasland} The easy way is to create a Role -- e.g., create role myapp_read_only

[21:52] {MarkMcCasland} Then you would GRANT SELECT ON USERID.TABLE1 TO MYAPP_READ_ONLY

[21:53] {MarkMcCasland} Then you create a role like MYAPP_EDIT

[21:53] {MarkMcCasland} Then grant that role INSERT, UPDATE, DELETE ON USERID.TABLE1 TO MYAPP_EDIT

[21:54] {MarkMcCasland} In these examples the USERID is the ID of your database schema [i.e., the user ID where the database tables and other objects exists]

[21:55] {MarkMcCasland} The users with Edit privileges also have to have select privileges so you grant the MYAPP_READ_ONLY role to the MYAPP_EDIT role.


[21:55] {MarkMcCasland} Do the same for a DELETE privilege.

[21:56] {MarkMcCasland} now all you do is GRANT MYAPP_EDIT TO SOMEUSER

[21:56] {MarkMcCasland} When the user is fired or quits the company, a GOOD Oracle DBA will drop that user. The user will automatically dropped from all roles.

[21:57] {MarkMcCasland} I like low maintenance.

[21:57] {MarkMcCasland} Briefly on Synonyms.

[21:58] {CarlKarsten} wait a sec

[21:58] {CarlKarsten} Have you (or anyone) ever setup a database where the users had full access to everything - much like a dir of dbf's ?

[21:58] {MarkMcCasland} One more word on Roles. You also have to grant select privileges on sequences for any user granted insert and update privileges

[21:59] {MarkMcCasland} No. All user access is handled via Roles. I only allow a few users the abililty to manage parts of the database.

[21:59] {MarkMcCasland} There is a particular strategy used by the creators of Data Clas that only grant users EXECUTE privileges.

[22:00] {MarkMcCasland} Everything is done with stored procedures.

[22:00] {MarkMcCasland} The advantage of that is no user can hack the DB with something like MS Access.

[22:01] {MarkMcCasland} Since I grant privilieges like Insert, Update and Delete, I put a lot of work in on the database.

[22:02] {MarkMcCasland} Specifically, using triggers, sequences, FKs, constraints, etc.

[22:03] {MarkMcCasland} I guess we should wrap up with general Q & A. I will schedule an intermediate session for later.

[22:03] {MarkMcCasland} I would like to mention synonyms because they are important as well.

[22:03] {KellyC} Have you ever done any major tablespace reorgs?

[22:03] {KellyC} Oh, go ahead with synonyms, sorry.

[22:04] {MarkMcCasland} [Kelly] What you should do is an export of everything in that tablespace before messing with it.

[22:04] {MarkMcCasland} Creating a Synonym: create public synonym ACTION_TYPE for npdes.ACTION_TYPE;

[22:04] {MarkMcCasland} This is needed to prevent users from having to fully qualifying there SQL.

[22:04] {KellyC} Right, but an Oracle rep told me about a COPY command that I could use to "quickly" move a table from one tablespace to another, defragmenting it at the same time. Sound like a good idea to you?

[22:05] {MarkMcCasland} Very good, but that sounds more like defragging a table and not a tablespace. Two very different things.

[22:05] {KellyC} After exporting and/or backing up first, of course.

[22:06] {KellyC} Well, I will do it table by table to separate my tables into more tablespaces and to also defrag.

[22:06] {MarkMcCasland} Say I log on as Mark, and I want to query Kelly's tables. If there is no synonym, I have to issue:

[22:06] {MarkMcCasland} select * from Kelly.SomeTable where...

[22:06] {MarkMcCasland} If there is a synonym, I just issue:

[22:06] {KellyC} The database I've inherited is 100G all in two tablespaces (one for data, one for indices).

[22:06] {MarkMcCasland} select * from SomeTable where...

[22:07] {MarkMcCasland} [Kelly] Good design. Even better if the tablespaces are on different drives.

[22:07] {MarkMcCasland} Now for more general Q & A.

[22:07] {CarlKarsten} what does npdes stand for or mean?

[22:08] {KellyC} I have 5 large disks to spread across. Was just worried about the COPY command vs. export/import, and getting the proper syntax. Wondered if anyone had used it. Thanks.

[22:08] {MarkMcCasland} NPDES is the user ID created for my database. When you create a DB, you never want to create one under your own ID that is for use by other users.

[22:08] {MarkMcCasland} If you quit, your users are up the creek if the DBA has to remove your ID.

[22:09] {MarkMcCasland} So you create a user ID specifically for the production database.

[22:09] {MarkMcCasland} NPDES is an acronym for National Pollutant Discharge Elimination System

[22:10] {MarkMcCasland} I am not familiar with the copy command. What I would normally do is create a temp table using SQL:

[22:10] {MarkMcCasland} create table temp1 as select * from oldtable;

[22:10] {MarkMcCasland} Then you can drop the oldtable and recreate it using the same SQL technique.

[22:11] {MarkMcCasland} This generally will fail, however, because I do not have a table with no constraints

[22:11] {KellyC} Mark, you said my 100G database with just 2 tablespaces is a good design, right? So, you wouldn't recommend creating more tablespaces, say one each for large tables? My Oracle rep advised this, but I'm not sure it's any better than just spreading the 2 tablespaces across my 5 disks.

[22:11] {MarkMcCasland} It is good because it is more than I can do in my situation.

[22:11] {KellyC} Right. I think I can get away with today, because the guy before me didn't know what a constraint (or several other things for that matter) is. {g}

[22:12] {MarkMcCasland} Sounds like you have a RAID configuration? There are techniques for really optimizing the DB.

[22:13] {KellyC} No, 5 mirrored pairs.

[22:14] {CarlKarsten} when you are ready for another q: What is the minimum cost to implement Oracle? What would a small (5 user) office require?

[22:14] {MarkMcCasland} Different TS for large tables is a good idea if you have the resources but only if the TSs are on different drives.

[22:14] {KellyC} I should let you wrap up with other Q&A, Mark. Thanks very much.

[22:14] {MarkMcCasland} You would have to get the licensing costs of the Oracle server software form there web site.

[22:14] {MarkMcCasland} [Kelly] Youre welcome.

[22:15] {MarkMcCasland} Generally, Oracle has brought their prices down to be competitive with SQL Server.

[22:15] {CarlKarsten} What about general costs like :hardware, install time, dba time

[22:16] {MarkMcCasland} Oracle is quite robust and hard to corrupt. So, if you develop a good Oracle DB, you can probably get by on minimal DBA knowledge.

[22:16] {KellyC} Another Q when others are done -- where's a good book list?

[22:17] {MarkMcCasland} Oracle will install on an NT or Novell server quite easily. You of course have to have an NT Administrator or Novell Admin available to do this. We all know how cooperative they are.

[22:17] {CarlKarsten} even minimal dba - sounds like you still need a dba - could this be someone who only comes by ever 6 months to check up on things?

[22:17] {MarkMcCasland} I will post a book list at the close of this session.

[22:18] {MarkMcCasland} [Carl] I would think so as long as one was available on call during business hours.

[22:18] {CarlKarsten} ok

[22:18] {KellyC} [Carl] I'm the DBA at my shop now and I never saw Oracle until last October. We just had someone in to tune us and it helped quite a bit. I learned much too.

[22:18] {MarkMcCasland} Actually, for heavily utilized DBs, monitoring the use of disk space is a must.

[22:19] {KellyC} Should have said "DBA" - since I'm really a developer and just got the DBA role dropped into my lap.

[22:19] {MarkMcCasland} Look into taking about 2 weeks of Oracle DBA classes from Oracle.

[22:19] {RickSchummer} Go for it Kelly, DBA's seem to make serious mula

[22:20] {MarkMcCasland} Good ones can start at $80k or better

[22:20] {CarlKarsten} I am thking of companies that are going to spend $30k per year on computers - hardware/ os/software/develpment/staff

[22:20] {CarlKarsten} wondering if Oracle would fit in a place like that

[22:20] {RickSchummer} I have seen good ones command $150-200 an hour

[22:20] {KellyC} Anyone using Oracle should check for tools from TOAD is a good query/schema tool. Spotlight on Oracle is a very cool "dashboard" app that lets you see data flows and rates between pieces of the architecture and then drill in on problem areas. Expensive, but worth it and they have a 30-day trial. Versions for SQL Server and Windows 2.

[22:21] {CarlKarsten} I have seen bad ones get $180 ah hour

[22:21] {MarkMcCasland} [Carl]yes it could fit in your 5 to 10 user example

[22:21] {MarkMcCasland} Books:

[22:22] {MarkMcCasland} Oracle8: The Complete Reference [ISBN 0-07-88-2396-X]

[22:22] {KellyC} [Carl] I think it's an option, but I'd definitely have those folks look at SQL Server too. Probably more resources available for maybe a lower rate, no?

[22:22] {MarkMcCasland} Oracle PL/SQL Tips & Techniques [ISBN 0-07-882438-9]

[22:23] {MarkMcCasland} I would give them cost estimates on both. It also depends on the expertise on who will actually provide the most support.

[22:23] {CarlKarsten} My largest client had only 50 people useing my app, and never more than 25 at once

[22:24] {MarkMcCasland} Oracle 8i: Advanced PL/SQL Programming [ISBN 0-07-212146-7]

[22:24] {MarkMcCasland} [end-of-book-list]

[22:24] {CarlKarsten} this was on a fpdos app

[22:24] {CarlKarsten} I think MSDE would have even been ok

[22:25] {MarkMcCasland} From what I here, performance of MSDE starts to gradually fall off at 5 users.

[22:25] {CarlKarsten} i think it is 5 jobs

[22:25] {CarlKarsten} hook up all the users, just make sure they don't all do something at once

[22:25] {CarlKarsten} I have been meaning to test that

[22:25] {MarkMcCasland} 5 concurrent

[22:26] {CarlKarsten} right

[22:26] {CarlKarsten} I currently have 10 clients runnig the same app on standalone desktop machines

[22:26] {CarlKarsten} im sure they could use msde ;)

[22:26] {KellyC} Visual Basic Oracle 8 [ISBN: 1-861001-78-9] - seems good so far (half way through)

[22:27] {MarkMcCasland} Thanks, Kelly. I will look into that one.

[22:27] {MarkMcCasland} Does it cover Stored Procedures, packages, etc?

[22:28] {KellyC} SP yes, packages no.

[22:28] {KellyC} It's from Wrox.

[22:28] {CarlKarsten} Does anyone else have any questions?

[22:28] {CarlKarsten} (other than me?)

[22:29] {MarkMcCasland} Carolina finally won a game [in OT]

[22:29] {KellyC} I have plenty, but can wait for version 2. Thanks, Mark!

[22:29] {CarlKarsten} Mark - can you take a guess at a VFE issue?

[22:29] {CarlKarsten} did you see it on the wiki?

[22:29] {CarlKarsten} pfft - I can just cut/paste

[22:30] {MarkMcCasland} No. I worked till 6:30 [CDT] then took a break before coming here.

[22:30] {CarlKarsten} The view has an ordinary data entry screen. When I enter information into the field in an existing record and save it, I get from VFE a messagebox with ORA-00997 Illegal use of LONG datatype.

[22:30] {CarlKarsten} If I change the field and do a tableupdate(.f.,.t.) in the command window and it works with no problems

[22:31] {CarlKarsten} does that sound like anything familiar?

[22:31] {MarkMcCasland} Have you talked to the VFE developers?

[22:32] {CarlKarsten} posted this morning, no responce yet

[22:32] {CarlKarsten} VFE basically floats errors up

[22:32] {MarkMcCasland} A LONG datatype needs to be a MEMO data type on the VFP side.

[22:32] {MarkMcCasland} I have never had a problem using a LONG data type in my remote views.

[22:33] {CarlKarsten} any idea what you could do in VFP to casuse that error?

[22:33] {CarlKarsten} cause

[22:34] {MarkMcCasland} From the Oracle docs:

[22:34] {MarkMcCasland} Cause: A value of datatype LONG was used in a function or in a DISTINCT, WHERE, CONNECT BY, GROUP BY, or ORDER BY clause. A LONG value can only be used in a SELECT clause.

[22:34] {MarkMcCasland} Action: Remove the LONG value from the function or clause

[22:35] {MarkMcCasland} Maybe you should only send update to LONG data type using Key Fields only.

[22:35] {CarlKarsten} only in SELECT - so how do you insert/update?

[22:35] {MarkMcCasland} Ignore that part. My bet is the update is trying to use Key Fields and Updated fields.

[22:36] {MarkMcCasland} So it is trying to compare the old value in the LONG field to the new value.

[22:36] {CarlKarsten} ahh - and the WHERE longthing = something is hoseing it

[22:36] {MarkMcCasland} Right.

[22:37] {CarlKarsten} but then it wouldn't work at the command prompt either

[22:38] {MarkMcCasland} Hmmm. Is VFE also only updating 1 row at a time?

[22:38] {MarkMcCasland} [i.e., first tableupdate parameter also false]

[22:38] {CarlKarsten} ahh - good q - I can check

[22:38] {Rose} Mark, what did you just refer to (during your brief absence then return with "From the O docs:")?

[22:38] {Rose} ? The URL mentioned above “…etc”?

[22:38] {MarkMcCasland} They may also be using SPT instead of TableUpdate.

[22:39] {MarkMcCasland} [Rose], Correct.

[22:39] {CarlKarsten} no SPT

[22:40] {CarlKarsten} TABLEUPDATE(0)

[22:41] {MarkMcCasland} Check your WhereType using Cursor Get Prop before you try it from the command window.

[22:41] {CarlKarsten} That has got to be a record for me - the quickes search and rescue of a line of VFE code ever

[22:41] {MarkMcCasland} I suspect it will return 1 Key Fields only

[22:41] {MarkMcCasland} OK, that means current record only and .F. for second parameter [no force]

[22:43] {MarkMcCasland} Then set the WhereType to 3 [Key and Modified fields]. See if you get the same error. You will have to issue the aerror() function to retreive any ORA error messages.

[22:43] {CarlKarsten} Unfortunately, I am not the one with the problem, dbc or server - just 2 lines of post.

[22:44] {Rose} Anything more you could say about exporting data (for someone who is BARELY getting rolling with Oracle?

[22:45] {Rose} Specificly, we have an offsite developer who has always done lots of support for us, usually we will

[22:45] {MarkMcCasland} Carl are you going to post all this on the wiki? If so, Rose you should start by going through all we covered here tonight.

[22:46] {MarkMcCasland} The next piece of advice, is to get at least 1 of the books I mentioned, and post Qs on UT.

[22:47] {MarkMcCasland} Start with the Oracle8 Complete Reference.

[22:48] {CarlKarsten} Any more questions?

[22:48] {Rose} How about a bit of syntax for “Export all December records from mytable”?

[22:48] {Rose} Meanwhile, thank-YOU for the reference suggestions!

[22:49] {MarkMcCasland} Export is an all or nothing thing. You can export an individual table but not specific rows.

[22:49] {Rose} oooh, that isn't what I had expected! We have lots of rows, but want to eMail just selected records.

[22:49] {MarkMcCasland} If you just need to query specific months you can create a VIEW in Oracle much like you would in VFP

[22:49] {CarlKarsten} Rose - does it need to be "exported", or can you use VFP to create the file?

[22:50] {CarlKarsten} yea, what Mark said

[22:50] {MarkMcCasland} Use a VFP parameterized view. Then create the file for e-mailing on the VFP side.

[22:51] {Rose} Carl, It is the specific data that needs to be looked at. Maybe SELECT specific record sets into VFP?

[22:51] {Rose} Are you suggesting that?

[22:51] {CarlKarsten} right

[22:51] {MarkMcCasland} I am with the parameterized view

[22:51] {CarlKarsten} use a VFP view to get the data from Oracle, and then just COPY TO foo TYPE delimited

[22:51] {Rose} Mark, thanks for the parameterized view suggestion. I can picture implementing that.

[22:52] {Rose} Thanks, Both

[22:52] {MarkMcCasland} You're welcome.

[22:52] {CarlKarsten} or whatever your favorite VFP command is to create the file you need

[22:52] {CindyWinegarden} _VFP.DataToClip( , , 3)

[22:53] {CindyWinegarden} Open Excel, Paste.

[22:54] {CarlKarsten} everyone catch that? Data To Clip is part of VFP

[22:55] {CindyWinegarden} Data To Clip() makes tab-delimited text.

[22:55] {MarkMcCasland} Where is she issuing those commands from?

[22:55] {CindyWinegarden} I do it in the command window

[22:55] {CindyWinegarden} Or you can use automation

[22:56] {MarkMcCasland} Open Excel, Clip from the command window?

[22:56] {CarlKarsten} it isn't a VFP function

[22:56] {CarlKarsten} it is a method of _VFP

[22:56] {ArielGimenez} datatoclip!!! nice tip Carl

[22:56] {CindyWinegarden} Mark - open a VFP table or cursor

[22:57] {CindyWinegarden} Now, type in the command window: _VFP.DataToClip(,,3)

[22:57] {CindyWinegarden} Then open Excel or Notepad or whatever and paste (CTRL+V)

[22:57] {CindyWinegarden} See what happens

[22:57] {CarlKarsten} Since we seem to have gotten off of Oracle, I would like to give Mark a big thank you

[22:58] {CindyWinegarden} Yes. Thanks for presenting, Mark!

[22:58] {CarlKarsten} and it sounds like he is going to come back for round 2 sometime, right?

[22:58] {Rose} Seconding Carl's motion: Mark, Here is a BIG Thank-YOU.

[22:58] {ArielGimenez} Thanks Mark!

[23:00] {MarkMcCasland} I will look in to doing an intermediate Oracle WNL early this summer.

[23:01] {CarlKarsten} oh yea - does windows ship with an Oracle client (driver?)

[23:01] {MarkMcCasland} Thanks to all for being a good audience. This is the first time I have been anywhere online with Mike Helland where he kept his virtual mouth shut the entire time { gd&r }

[23:02] {CarlKarsten} I think he is sleeping

[23:02] {MarkMcCasland} No but the MS ODBC Drivers [MDAC] includes the drivers.

[23:02] {CindyWinegarden} Carl, I have always heard that the MS ODBC drivers for Oracle were better than Oracle's!

[23:02] {MarkMcCasland} You have to get the Oracle client from Oracle

[23:02] {MarkMcCasland} They are better when using MS software on front end.

[23:02] {CarlKarsten} um, what's the difference?

[23:03] {MarkMcCasland} There have been problems [at least in the past] when you mixed a MS front-end and Oracle ODBC drivers.

[23:04] {MarkMcCasland} Specifically, they had to do with multiple joins between 2 tables.

[23:04] {CarlKarsten} what is the "Oracle client"?

[23:04] {MarkMcCasland} Over the past 3 or 4 years the drivers have gotten much better.

[23:04] {CindyWinegarden} Carl, there is an interactive SQL client for Oracle

[23:04] {MarkMcCasland} The client is SQL*Net

[23:04] {CarlKarsten} ahh - like a command window ?

[23:05] {MarkMcCasland} This is the software required to connect to Oracle server

[23:05] {MarkMcCasland} SQL*Plus is the command window analogy.

[23:05] {CarlKarsten} ok, I am back to being confused

[23:05] {MarkMcCasland} You have to install the client on every workstation that needs to connect to Oracle regardless of the front end you use.

[23:05] {CarlKarsten} the driver in MDAC dosn't do that?

[23:06] {MarkMcCasland} SQL*Plus only needs SQL*Net to connect [no ODBC drivers required]

[23:06] {MarkMcCasland} The ODBC driver is for use by MS Access, VFP, etc.

[23:06] {MarkMcCasland} The Oracle develper tools only need SQL*Net and maybe the Oracle ODBC drivers, but I do not think so.

[23:07] {CarlKarsten} the odbc driver just connects to the Oracle client?

[23:08] {MarkMcCasland} More like the ODBC driver is used to translate the MS Access or VFP SQL to the SQL required by the Oracle Server, then that is sent to the server via SQL*Net. SQL*Net is analogous to the wire.

[23:08] {MarkMcCasland} The ODBC driver is analogous to the switch board [PBX?]

[23:09] {CarlKarsten} so it's VFP -- odbc -- SQL*Net -- Network -- Oracle Server

[23:09] {MarkMcCasland} Yep.

[23:09] {CarlKarsten} perhaps a bit more on the Server side

[23:09] {CarlKarsten} but thats the DBA's problem, right?

[23:10] {MarkMcCasland} Right. Since Oracle is a true server, the processing is all done there.

[23:11] {CarlKarsten} what is the chage for the SQL*Net part?

[23:11] {CarlKarsten} $

[23:12] {MarkMcCasland} Don't know. You can check their web site for prices. I think it is included when you licence the server. i.e., If you get a 5 user Oracle server license, you get to install the client on 5 PCs.

[23:12] {CarlKarsten} ok, so it isn't free

[23:13] {MarkMcCasland} Since that is 5 concurrent connections, you would probably have to buy an additional client licence for each PC over 5.

[23:13] {MarkMcCasland} I think a clinet license is included for each Server User you get

[23:18] {MarkMcCasland} Best advice on Oracle pricing: Call Oracle and ask for a sales rep. Their pricing is quite confusing.

[23:18] {CarlKarsten} at least they are keeping up wiht MS in that regard

[23:19] {CarlKarsten} I am not even exactly sure what "free" means right now

[23:19] {MarkMcCasland} I think if they posted succinct pricing on their web sites, they might be afraid of getting into a price war. Keep'em guessing.

[23:19] {CarlKarsten} for instance: can I distribute the enterprise manager that comes on the sql2000 eval CD?

[23:20] {MarkMcCasland} Doubtful.

[23:20] {CarlKarsten} the server expires, but the manager dosn't

[23:21] {MarkMcCasland} When in doubt, get it in writing [EULA] from MS.

[23:21] {MarkMcCasland} With that, It is time for me to go. Good night, and se y'all on the UT. GO STARS!

[23:22] {CarlKarsten} Thanks again Mark.

Contributors Mark McCasland, Carl Karsten, Cindy Winegarden
Category Wednesday Night Lectures
( Topic last updated: 2001.04.26 02:01:55 PM )