12 Replies Latest reply: Nov 25, 2013 8:01 AM by Christian Erlinger RSS

    x-file with db function returning a rowtype to forms. Positional binding?

    juliojgs

      How to explain this ...

       

      First things first:

      Forms [32 bits] Versión 11.1.2.1.0 (Producción)

      Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

       

       

      Now the db testcase:

      I've got two schemas, quite similar, SCHEMA_A and SCHEMA_B

       

      Imagine the same table: mytable (colx varchar2, coly varchar2, colz varchar2) in both schemas,

       

      BUT in SCHEMA_B the order of the cols is not the same. In schema B it is

      mytable (colx, colz, coly).

      (Yeah, I know, I know, but leave it for the sake of the x-file)

       

      Let's suppose the table has only one row, and that I have a db function in both schemas such as this:

       

      function get_myrow return mytable%rowtype is

      l_row mytable%rowtype;

      begin

      select * into l_row from mytable;

      return l_row;

      end;

       

       

      Here, the forms testcase:

      I make a form, where I show the value in an item

      For example:

      :item := get_myrow().coly

       

       

      And now, the x-file:

       

      If I compile the form in the builder, connected as SCHEMA_A, when I run it in SCHEMA_B, the item shows colz value!!

       

      It is as if forms is doing some kind of positional binding in the fmx. Instead of asking for coly column to the record type variable, it shows "the second value" of the record type.

       

       

      Now, I will have to find this disordered tables and reorder them (dbms_redefinition? hints are welcome).

       

       

      Any thougts? Bug as feature?

        • 1. Re: x-file with db function returning a rowtype to forms. Positional binding?
          MLBrown

          I don't quite understand what you are doing returning data through a function like that, but besides that have you tried changing your function to select the column names instead of *

           

          function get_myrow return mytable%rowtype is

            l_row mytable%rowtype;

          begin

            select colx, coly, colz

              into l_row from mytable;

            return l_row;

          end;

          • 2. Re: x-file with db function returning a rowtype to forms. Positional binding?
            CraigB

            If I compile the form in the builder, connected as SCHEMA_A, when I run it in SCHEMA_B, the item shows colz value!!

            Can you explain how you are doing this just so it is clear and we don't have to make assumptions?  Are you running the Form from the Forms Builder or from the Application Server (WLS)?

             

            My first thought is that since you are connected to SCHEMA_A and running against SCHEMA_B that you are getting the result from SCHEMA_A.Get_MyRow function and not from SCHEMA_B's Get_MyRow function.  This is why I am asking for clarification on how you are connected and how you are running your form.

            It is as if forms is doing some kind of positional binding in the fmx. Instead of asking for coly column to the record type variable, it shows "the second value" of the record type.

            Well, keep in mind that since you used %ROWTYPE in your declaration of your variable, it is in fact positional.  Had you explicitly defined each column of your L_ROW variable with the specific column you wanted in the order you wanted them in, I don't believe you would be having this issue.  Additionally, the fact that you are using "SELECT *" instead of explicitly naming the columns is also part of the problem.  Using "SELECT *" is problematic and not recommened; it is always best to explicitly name the columns in your SELECT statement.  What happens when your table changes (such as a new column) but your function hasn't been updated?  Your Function will produce an error because it can't handle the additional column.

            Now, I will have to find this disordered tables and reorder them (dbms_redefinition? hints are welcome).

            As I mentioned above, rather than reorganize your table so the columns align, I recommend you simply specify the order in which you want the columns selected.  It has always been a best practice to not rely on the order the Oracle Database keeps/returns things and to explicitly specify the columns you want, the order you want them in, and any sorting your program logic needs.  This ensures your program will operate correctly in every scenario.

             

            Craig...

            • 3. Re: x-file with db function returning a rowtype to forms. Positional binding?
              CraigB

              MLBrown wrote:

               

              ...

              function get_myrow return mytable%rowtype is

                l_row mytable%rowtype;

              begin

                select colx, coly, colz

                  into l_row from mytable;

                return l_row;

              end;

               

              You're got part of it right, but the variable declaration is still wrong.  While this mgith work in the OP's scenario because the columns are all varchar2's but is the precision of the columns?  By using "L_ROW  MYTABLE%ROWTYPE" you still are going to have problems? What will happen if ColX is VARCHAR2(10), ColY is VARCHAR2(5) and ColZ is VARCHAR2(25)?  Your modified query will work in SCHEMA_A, but could, ad most likely, will fail in SCHEMA_B because now your query is trying to put ColZ into ColY precision.  This will produce a Value Error or a Truncation error.

               

              Craig...

              • 4. Re: x-file with db function returning a rowtype to forms. Positional binding?
                juliojgs

                About the "select * is problematic" , I agree 99% of the times. But this time, as I'm storing the values in the rowtype variable, is the way to ensure that the estructure and the values fit.

                You said:

                What happens when your table changes (such as a new column) but your function hasn't been updated?  Your Function will produce an error because it can't handle the additional column.

                And it's just the opposite. My function will recompile itself, as the rowtype is perfectly capable of storing the values in the new rowtype.

                In fact, it's the MLBrown function the one that would produce an error if I change columns in the table, and that's the best case scenario, as the function could be lucky and work storing the values in the wrong places, leaving the bug hidden.

                 

                Anyway, I understand this "select *" issue is arguable, so forget about the select * , I'll take it out of this thread with a more simple testcase:

                 

                create table mytable (

                colx varchar2(10),

                coly varchar2(10),

                colz varchar2(10));

                 

                Consider this "select free" version of the function:

                 

                function get_myrow return mytable%rowtype is

                  l_row mytable%rowtype;

                begin

                l_row.colx := 'a';

                l_row.coly := 'b';

                l_row.colz := 'c';

                  return l_row;

                end;

                 

                Forget also about schema_a and schema_b, I can reproduce it with just one schema!!!

                 

                Now I build a form with just a button. When- button-pressed:

                message (get_myrow.coly);

                 

                I compile and run it, and I get a b value. ok.

                 

                Now I drop the table and recreate but like this:

                create table mytable (

                colx varchar2(10),

                colz varchar2(10),

                coly varchar2(10));

                 

                Now run the form (without compiling) and see how the message shown is c !!!

                The result is different, depending on how whas the rowtype structure when I compiled the fmb.


                Are you indeed telling that is acceptable an escenary where I ask for get_myrow().coly value and I get colz value instead?

                How's that possible? Because somebody shortcutted in the fmx compiling process, and converted my call for named coly value to a call to "the second column of the record variable".


                I'm not asking for a numbered position , I'm asking for coly value, not for "the second column of the rowtype variable the day I compiled the form". Had I want a possitional binding, I'd use a frikkin varray.

                • 5. Re: x-file with db function returning a rowtype to forms. Positional binding?
                  CraigB

                  Now run the form (without compiling) and see how the message shown is c !!!

                  The result is different, depending on how whas the rowtype structure when I compiled the fmb.

                  I see your point. The fact that you did not recompile your form explains everything - at least to me.  Because your newly created table's datatypes match that of the previous table - even though the order of the columns has changed, the signature of the your function doesn't change.  Consequently, it is using the same ROWTYPE of the previous edition of the table.  Admittedly, this is conjecture on my part but it does explain what is happening.  I suggest you try your scenario again only this time, when you reorder your columns change the data type or precision of one of your columns and see what happens.

                   

                  Craig...

                  • 6. Re: x-file with db function returning a rowtype to forms. Positional binding?
                    juliojgs

                    I'm sorry, maybe I misled you. The function is a database function, not a forms function.

                    So if I change the datatype of coly , to number for example, the asignation

                    l_row.coly := 'b';

                    will make the function incompilable.


                    Anyway I'm gonna try another thing. I'll get rid of the mytable%rowtype and make my own record type.

                    I'll post the results

                    • 7. Re: x-file with db function returning a rowtype to forms. Positional binding?
                      Andreas Weiden

                      Using a ROWTYPE or your own record-type doesn't matter. Even if the two types in SCHEMAA and SCHEMAB look the same, they are still different types. So, it MIGHT work when you use it in forms, but is is not quaranteed. Forms resolves the "space in memory" needed for a type a compile-time, so if you change your structure afterwards, you will also need to compile your forms-again. If you really have to stay with your two schemas, i would do the following:

                       

                      1. Create a third schema C which only creates your type-definitions (e.g. in a package specification).

                      2. In your other schemas A and B use that types defined in schema C.

                      • 8. Re: x-file with db function returning a rowtype to forms. Positional binding?
                        Christian Erlinger
                        Now I drop the table and recreate but like this:

                        create table mytable (

                        colx varchar2(10),

                        colz varchar2(10),

                        coly varchar2(10));

                         

                        Now run the form (without compiling) and see how the message shown is c !!!

                         

                        And what happened to your stored procedure? It got invalidated. Then you recreated your table. And when you first accessed your stored procedure, guess what happened? The database *compiled* your stored procedure. In your form this can't happen, as the .fmx file is no source file, and the forms runtime is no forms compiler. The database has both those things: your stored procedure in source format, and a PL/SQL compiler.

                         

                        The result is different, depending on how whas the rowtype structure when I compiled the fmb.

                         

                        This is expected behaviour. Those things are actually fixed *at compile time*, and are not resolved everytime at runtime when the PL/SQL engine encounters a %rowtype construct. Think about it: If a %rowtype record, or +select * from xyz+ needs to be resolved at runtime every time you have written it down it would make your stored procedures incredibly slow, as every time you have a %rowtype or a select * in your PL/SQL code the database would need to hit the data dictionary in order to retrieve the column order, column names and data types of your table. So it simply compiles those things into your PL/SQL code, and every time you add or drop a column your database PL/SQL code gets invalidated and recompiled by itself. The Forms PL/SQL engine doesn't have this luxury as the .fmx file contains no source code, so you have to take care of that yourself: when you change a table structure, recompile the depending forms.

                         

                        A simple sample where you can actually see that things like +select * from [....]+ gets resolved at compile time is if you create a table, and a +select * from table+ view upon that table. Now check user_views for the SQL statement of your view, and you'll in fact see that the database transformed your +select * from [....]+ into +select a,b,c,d from [....]+. Those views are even more evil then the %rowtype records, as a simple recompile won't do it there if you add or drop a column of it's base table: you need to recreate them.

                         

                        cheers

                        • 9. Re: x-file with db function returning a rowtype to forms. Positional binding?
                          juliojgs

                          I understand your point, and know that's what happens. Of course, the dependent store procedures are recompiled (if they can), and the fmx is fixed so it can't recompile itself. It either works or doesn't

                          What I still don't buy is the positional binding.

                          I mean, I haven't tried it but I'm pretty sure that if I have a form with a block based on that table, then I reorder the columns in the db, the fmx still works as usual, as the relying sql is binding columns by their names. The execute_query will bring me the data in their correct items, as the binding between item and db column is maintained as "named" in the fmx.

                          But the binding I did with named binding to the record type in plsql inside the form (in the fmb source) is being substituted by positional binding in the compiled fmx from the start. This is what I don't buy: Same product, different features.

                           

                          Am I asking too much of an fmx? ... maybe, but forms is a very expensive product.

                          • 10. Re: x-file with db function returning a rowtype to forms. Positional binding?
                            Christian Erlinger
                            I mean, I haven't tried it but I'm pretty sure that if I have a form with a block based on that table, then I reorder the columns in the db, the fmx still works as usual, as the relying sql is binding columns by their names.

                            This isn't PL/SQL. If you stick your nose into the SQL*Net traffic your form submits to the database you'll see that forms actually issues

                             

                            select col1, col2, col3, col4 from your_table
                            

                             

                            The block doesn't issue a

                             

                            select * from your_table
                            

                             

                            so if the columns are of the same name / data type / precision / scale / length semantics why shouldn't that work?

                             

                            But the binding I did with named binding to the record type in plsql inside the form (in the fmb source) is being substituted by positional binding in the compiled fmx from the start. This is what I don't buy: Same product, different features.

                            Actually those are different things. I guess the block data is retrieved via some OCI dynamic SQL queries; if you write down PL/SQL code this will be compiled and submitted by the Forms PL/SQL engine (which ultimately will result in OCI calls; however there is a layer in between that). And the Forms PL/SQL engine will behave exactly the same as the Database PL/SQL engine with the exception that it can't recompile invalidated Program Units. If the database wouldn't recompile your invalidated PL/SQL Program Units by itself you'd have the very same problem with Stored Procedures.

                             

                            Am I asking too much of an fmx? ... maybe, but forms is a very expensive product.

                            That's a good one . A Windows 7 Pro license costs me 300€, a Debian license costs me...nothing. Now for my current Raspberry Pi project: why on earth am I not able to install Windows 7 on my Raspberry Pi?!? With a 300€ license fee this should be possible...

                             

                            Jokes aside: as far as the underlying technology (and I consider the forms compiler as such a thing) goes there hasn't changed much since the 6i version; and as far as your rowtype problem goes: this isn't exactly related to forms but to the PL/SQL engine. And I don't think Oracle will change that but simply tell you to recompile your forms when you do changes to your underlying tables.

                             

                            If you don't want to recompile your forms against every database you are running them then you have to make sure your data structures on the deployment database are exactly the same as they are on the development database. Otherwise you'll run into problems you encountered, or ORA-04062 signature of package has been changed errors

                             

                            cheers

                            • 11. Re: x-file with db function returning a rowtype to forms. Positional binding?
                              juliojgs

                              That's the reason I didn'n mark this post as a question, it's just common oracle products whiny chitchat.

                               

                              I've been querying my data structures differences and thinking on an easy way to have them match exactly.

                              • 12. Re: x-file with db function returning a rowtype to forms. Positional binding?
                                Christian Erlinger

                                We did this by standardizing the way DDLs are executed against the development database, and against the test/production databases. Our developers don't issue direct DDL statements against the database; we have a user interface where they add metadata (like add column x to table y etc.) which they then can execute against the development database. At the end of the development cycle this metadata is used to execute the DDLs against the test/production database exactly the same way as they were executed against the development database. So as far as the column names, data types, column orders, names of the objects etc. go the dev/test/prod database are 100% the same, and we are able to compile once and deploy everywhere.

                                 

                                cheers