1 2 3 Previous Next 202 Replies Latest reply: Jan 12, 2007 7:21 AM by Billy~Verreynne RSS

    The Oracle database engine and forms - functionality query

      I have an interest in establishing how the Oracle System Catalogues cope with particular changes. I have never used Oracle, but I have carried out the same test on another RDBMS. If I had access to Oracle I would have carried out this test myself.

      The test goes as follows.

      Create a new database.

      Create a new simple table, with just a few columns.

      Create a form for the table, and add a few rows.

      Rename the table or a column – if you can’t, then the RDBMS is Code Class.

      If you can rename the table or a column, then do so, and invoke the form that you used before changing the table or column name. If it doesn’t work, the RDBMS is Code Class; if it does then the RDBMS is Identifier Class.

      In an Identifier Class RDBMS changes of column or relation/table name will not interfere with the operation of any form already in place based on that table.

      I'll be very grateful if anybody can give me a definitive answer on this, either through already having explored the issue or by running the test.
        • 1. Re: The Oracle database engine and forms - functionality query
          Eduardo Legatti
          >> I have never used Oracle

          I guess that is better for you access the Oracle Documentations first.

          • 2. Re: The Oracle database engine and forms - functionality query
            Create a form for the table, and add a few rows.
            what technology are you using to create this Form?

            Anyway, "Code Class" and "Identifier Class" - they're kind of specialist terms. Are you anything to do with identifiers.org? I haven't bothered to read up on this site because all the interesting stuff seems to be in PDF documents and I couldn't be bothered downloading them all. It's a pity the site's author didn't spend six weeks building a usable web site to publish their fourteen years of pondering relational theory's intersection with philosophy and lingusitics. Perhaps heavyweight ideas require a heavyweight publication format.

            Cheers, APC
            • 3. Re: The Oracle database engine and forms - functionality query
              William Robertson
              I've never heard of those categories, or the Oracle System Catalogues.

              No the database does not automagically modify application code when you rename database objects, and I don't see how it could. I guess that makes it Code Class.

              However I'm puzzled about how you categorise an entire RDBMS based purely on how Forms responds to database objects being renamed. What about applications developed with other tools? I'm pretty sure they will respond the same way though.
              • 4. Re: The Oracle database engine and forms - functionality query
                Hello Belo,

                thanks for your reply. When I was working as an accountant I had 2 Brazilian clients, one importing Brazilian Music and one importing (Clenio Lemos) and the other importing frozen fruit products (Gabriel Gaya). So I have good feelings for Brazil.

                The question I am asking (and getting some hints of a proper reply) is at a completely different level from anything I will learn from Documentation.

                I'm intimately aware of the finest detail of how another RDBMS works, and I'm checking that Oracle works in the same way.

                If you have access to an Oracle RDBMS please run the test. I'm aware that most people decline to do so because they are concerned that they will damage some live data. Starting by creating a new database completely eliminates the smallest possibility of any damage to existing data - although I did my initial test in a live database on the basis that I could drop the table at the end of the test.

                Please let me know how you get on, or let me know that you won't be running the test.
                • 5. Re: The Oracle database engine and forms - functionality query
                  Hello APC,

                  yes, I'm the Identifiers.org person. I had a lengthy discussion with one of my sons, who is a web designer, aroung the fact that I could not in a straightforward way get what I wanted into HTML using the tools currently available to me. If you aren't moved to look at the content of the site, either on screen or from prints of information on screen, then that's your business. If you read the content you will probably realise that the content of the website has interesting implications for the database and applications industry.

                  Please tell me what technology you would use to build this form. I expect that Oracle offers something in this area - the RDBMS that I have used extensively certainly does. I have a contact who has worked in Oracle for a supplier to a global bank for the last 7 years, who tells me that there is a newish version of Forms that allows you to create the form without pre-defined links to tables and columns, but that's just a variation on the basic situation, in which a form is fundamentally connected with tables and columns. The situation that I'm testing doesn't alter between the two.

                  Please would you run the test? If you know that table and column names can't ever be changed in Oracle then that's all I need to know. Otherwise please do the test - you will understand with your extensive Oracle background that it will not put any live data at risk.

                  • 6. Re: The Oracle database engine and forms - functionality query
                    William Robertson
                    Just checking: are you asking whether client-side application code developed in Oracle Forms 10g will be automatically modified by the server in response to you renaming tables? I don't even see how that could be possible since (for one thing) the code resides in binary files on the desktop or application server and not within the database.
                    • 7. Re: The Oracle database engine and forms - functionality query
                      Hello William, and thank you so much for your perceptive observations.

                      You have given me the answer I was looking for, "the database does not automagically modify application code when you rename database objects" (I presume the "magically" bit was conscious rather than otherwise). So the Oracle RDBMS is indeed Code Class. I suspect that Oracle Financials will be as well, albeit in master tables rather than in system tables. In an IC RDBMS it is not necessary for the database to go out and change lots of identical values - that's counter to normalisation - the rub is how you store information and make joins or links, distinguishing identity from attribute (a table name is an attribute, but it is not an identifier, so it should not be used for joins.) In an IC RDBMS or application the key values (codes, names) are only stored in one location - identifier values, which are rock solid permanent, are duplicated instead.

                      You extend the scope of discussion to other tools - say reports and query language statements, or whatever you all in Oracle call them. The situation is exactly the same for these as for forms, for exactly the same reason, so the remedy is not peculiar to forms - it is the result of a conceptual design mistake in the database engine that I call the "Codes Mistake", because I first located it in applications, where "codes" give what are effectively the same problems, because they contain identity, attribute and relative position in one single value.

                      I know the design principles on which a database engine could be constructed that will be Identifier Class; the same applies to applications.

                      My website is www.identifiers.org; it is not light material, but from the questions you have asked you will not get fazed by it. It's largely complete up the the "Proof" page. Another forum person has been put off by the mixture of HTML and PDF (which people should be able to view and print without downloading as far as I know) but the technical material did not lend itself to its presentation in HTML using the tools that I have. Please be patient in this respect.

                      With many thanks again; please give me any feedback that you have. You are the first database professional to have connected with the issues, albeit by stating clearly what you cannot as yet get hold of.

                      • 8. Re: The Oracle database engine and forms - functionality query
                        which people should be able to view and print without downloading as far as I know)
                        Well Firefox does display its download dialogue rather than showing the page via the Abode plugin. Fortunately it also has a view as HTML button. Unfortunately, reading from section to section requires multiple navigations, which is not user friendly. Also, HTML would make it a cinch to turn this sentence ...

                        "On another website page there are pictures of props that I have made to communicate the concept of the Codes Mistake and of an identifier;"

                        ... into a link. As it stands, the chances on me finding this page unless I stumble across it is nil. Particularly as searching across multiple PDF documents is hard.

                        By the way using[i] too many different font styles in the same sentence is the digital equivalent of writing letters[i] in green ink.

                        Enough of stylistic whinging. On to the substance. Would you mind explaining what you mean by the phrase "Codes Mistake"? In the question you posed here you are talking about whether making changes to the data model (table name, column names) would be reflected in the applications that use the database. Given that applications written in a surprising number of different languages can act as clients to to a database server the answer to this question is "no". But in the section called "Hello Abdul" you seem to be identifying a problem caused by changes to the data itself.

                        So what is the codes mistake? If it is that tools like Oracle break external applications when the data model changes will that is inevitable. You talk about changing column names but that is a trivial example and in my experience rarely happens. Far more likely is that a column is added to a table. Now that might break an application, especially if it contains SQL SELECT or INSERT statements that don't explicitly list the columns. But what is teh alternative? It nmight be appriopriate for the application to see that column. This is a business rule which cannot be devolved to the database.

                        Of course we could just design data schemas like this:
                        SQL>  DESC 123
                        1234     NOT NULL NUMBER
                        1235               DATE
                        1236               VARCHAR2(30)
                        I think this dread entity featured in H P Lovecraft's eldritch classic "The Remote Procedure Call of Cthulu".

                        If the "codes mistake" is simply the problem of changing keys when business rules change why that is just a variation of the old natural keys vs surrogate keys debate which flares up here from time to time (see Why do we use Meaningful PK or Meaningless PK ?).

                        I have spent some time reading the documents you have published but without gaining enlightenment into the problem you feel you are solving.

                        Cheers, APC
                        • 9. Re: The Oracle database engine and forms - functionality query
                          William Robertson
                          > You extend the scope of discussion to other tools - say reports and query language statements, or whatever you all in Oracle call them.

                          OK, let's forget Oracle Forms for now. Say you develop an application in Java. Then I go into the database and rename a table. Does the database, which is running on a remote Unix server, edit your Java source code on your PC and recompile the application? No it does not. Is this the Codes Mistake?
                          • 10. Re: The Oracle database engine and forms - functionality query
                            Hello again William,

                            I continue to rejoice in your questions.

                            The core of the matter is that the relevant Identifier Class system table will hold 2 critical values rather than just the one as at present, which in this case is the table name.

                            The two values are the identifier value of the table, say 2187, which never ever changes, and the table name value (which is an attribute of the table rather than an identifier), say at present "areas". In an identifier class RDBMS or applications attribute values are normalised and can be updated at will - identifiers form the permanent glue.

                            An identifier class variant of Java would accept the input of "areas" but would store the identifier value, 2187, in the Java script, by using "areas" to lookup the identifier value in the system table. An Identifier class version of Java would have what I have called an "Attribute To Indentifier Conversion Relational Operator" in it, which I implemented in a 4GL product which proved what I have been doing, although it may be that Java is already effectively a 4GL product, where you can lookup the identifier value for a row from any other column value in that row.

                            When I then changed "areas" to "geog_areas" this would have no impact on your Java form, because the form would connect to the system tables via the identifier value rather than through the name. The form would still work, and the next time you went to modify it you would see "geog_areas" instead of "areas".

                            What I've done is to split out identity from attribute and from relative position, where relative position is only a database concept. In our minds we naturally split out identity from attribute (permanence from transience) but the way we have thought and spoken since Ted Codd's day has not made the right distinction - we have thought and spoken as if identity resides in attribute, which it doesn't. RDBMSs and applications alike have been seriously hampered by this mistake, which I call the "Codes Mistake".

                            I would be very grateful if you would let me know in what sort of work you are - I'm very interested to find out where the good questions come from.


                            • 11. Re: The Oracle database engine and forms - functionality query
                              William Robertson
                              Thanks for the clarification. You are not expecting the database to actively update the application code if a database object is renamed. You are simply hoping that a database's API might allow applications to bind to it without depending on the literal names in use at compilation time, if I understand this right.

                              I think however this would just move the problem. Yes, if the application somehow used object_id 2187, object_type = TABLE instead of just 'AREAS' (which no application development tool currently does), you could rename the AREAS table without breaking the application, but shouldn't you be allowed to drop the table and replace it with a new AREAS table? Then the fancy name-independent code would be broken while the old-fashioned system would still work. It seems to me that the latter is the more common scenario.
                              • 12. Re: The Oracle database engine and forms - functionality query
                                Then the fancy name-independent code would be broken while the old-fashioned
                                system would still work. It seems to me that the latter is the more common scenario.
                                I concur with William. Changing object names in the data model is fairly rare. It is also trivial to handle in most RDBMS products, through views and synonyms. Besides these days an increasing number of front end applications talk to the database through a layer of abstractions such as EJBs or PL/SQL packages. I agree that this merely transfers the problem of handling change but it tends to keep it close to the database itself.

                                In my experience a far bigger problem is when the data model changes. For instance we have a table with a candidate key of sales area which looks like this: VARCHAR2(10). The actual data looks like 'SE123', 'SE456', 'NW789'. Uh oh, smart key. This actually needs to be decomposed into two columns: region VARCHAR2(2) and sub-region NUMBER(3,0). Of course nobody does anything about it until this a re-org of the sales areas. How would an Identifier Class database make this task any easier?

                                Cheers, APC
                                • 13. Re: The Oracle database engine and forms - functionality query
                                  Hello again William,

                                  now it becomes challenging. The conceptual shift required to get to Identifiers is similar in scale to the shift involved in going from Newtonian Mechanics to Quantum Mechanics or from Cartesian Space-Time to Special Relativity, with the added proviso that the concept of identity built into the Relational Model is implicit rather than explicit as respectively in the cases of Newton and Descartes.

                                  Your "you are not expecting.." sentence is a perfect statement of where I am - I'm not looking for the RDBMS to have any impact on application code - that would be a truly ridiculous expectation; this would make some crazy version of a cascade update, with remoteness as an extra complication. In IC applications (including RDBMSs) cascade updates are never needed.

                                  The next sentence also has a very strong aspect ("allow applications ... to .. compilation time) but is weaker in the "simply hoping" and "might". I havn't developed a RDBMS that does that, although I hope to participate in such a development exercise soon, but I have developed an application that does exactly what you say. Binding/joining depends on something permanent rather than something transient, where names and abbreviations are all transient, as are all pure attributes once they have had identity (and relative position) stripped out them.

                                  In your next paragraph I disagree that there is only a shift of problem. The dropping of whole tables or the deletion of columns is on a different scale from the changes that I am talking about, which do not but could get sensibly reflected in the current Code Class RDBMSs and applications. I'm aware that no application development tool allows what you say, (the problem is actually in the system tables) but I have achieved the completely analogous result in the application that I have developed.

                                  Your comments about the AREAS table precisely highlight the issues of permanence and transience on which all of my work sits. What are you doing when you drop the table and replace it with a table with the same name? Do you want to think of the "new" table as the SAME as the "old" table or is it something entirely DIFFERENT, in which case giving it the name of a table that you have dropped might easily result in serious confusion.

                                  If the new table is doing the same conceptual job as the old, then I would certainly want the continuity of its existence to be explicitly preserved, via the enduring identifier value (which reflects the fact that we are thinking of the table as having continuity of existence). So I would alter the table rather than dropping it and recreating it.

                                  If I have ended up in a cul-de-sac (I've been there) and I have to throw away the table and all its related operators, then that is what happens if I don't get it right first time. I don't want any continuity, and I'll have to junk all the form and report etc operators unless I want to preserve some bits for my next attempt. I certainly won't expect anything to continue to work, because there is nothing to work on.

                                  Your use of the expression "fancy name-independent code" is disappointing in all respects. I experience your use of "fancy" as sarcastic, and my full expression for "name-independent code" is "attribute independent and relative position independent identifier" whose acronym is "airpii". This is not just name-independent, it's independent of all other attribute and relative position values in the same row. It's certainly not a code: the word does not have a place in Identifier talk, since it has been replaced by abbreviations and acronyms, which are undiluted attribute; they don't have identity mixed up in them as with the "codes" of the "Codes Mistake". (In RDBMSs the mistake is called the "Names Mistake" because table and column names bear identity as well as attribute; there are no codes.)

                                  I very much hope that our dialogue can continue - other dialogues have stopped at this sort of stage. On my website www.identifiers.org there is a page called "Identifiers - a Visual Representation" that sets out to communicate identifiers visually, that I created to communicate this entirely new concept in a different way after another database professional got stuck. I understand from another forum user that there may be a problem getting hold of the pdf file concerned if using Firefox - please let me know.

                                  I have an RDBMS application on a SCO partition on a PC that proved identifiers for me. I can input a work instruction successfully using one set of abbreviations (the codes of old) and the stored instruction row makes no reference to the abbreviations that I have input - there's just a single identifier value in the appropriate column of the work instructions relation. The relationship between the abbreviations and the identifier value becomes clear from rows in other relations.

                                  If I then want for some good reason (some purely operational entity has been renamed) to change one of the abbreviations when also changing the full name, I can do so in the relevant relation - this is an abbreviation rather than a code that I am now dealing with; I can change it because it's not also used for joins.

                                  When I input the next work instruction I use the new abbreviation rather than the old; the form is entirely happy and works fine, and exactly the same identifier value ends up in the appropriate column of the work instruction relation. So I have input a different set of values to achieve exactly the same result. Is this crazy? No! This reflects the ways in which we naturally think and talk, and at last the application makes sense - I don't have to use a code that does not tie up with the name. The core of the issue is that I have been able to reflect external CHANGE fully and successfully in the database. We are surrounded by change, but RDBMSs and applications currently do not allow us to process it properly.

                                  I acknowledge that no application development tool works in this way at present, but that's the fault of the architecture of system tables, which I intend to remedy. One can't remove the Codes Mistake from these tables.

                                  However Identifier Class Applications can be developed using Code Class RDBMSs, because the developer has control over the application relation design, which can be carried out under identifier principles. I redeveloped from relation design scratch a very successful application, so I had a good placebo. In my environment I needed a 4GL form development tool so that I could get identifier values (and other attribute values) from attribute input rather than being limited to searching for a "code" to get other row values.

                                  If seeing is believing and of interest, then we might arrange for you to see the IC application working.


                                  • 14. Re: The Oracle database engine and forms - functionality query
                                    I've followed this discussion so far, and I do get the impression that the main point is changing the identifcation of objects (tables, columns, ...) from using names to something else. This may solve the problem of renaming objects by adding complexity in another place, but it won't solve adding or deleting objects. This looks like a pure acedemic debate to me with very limited use for daily development ... but I could be wrong of course.

                                    1 2 3 Previous Next