1 2 3 Previous Next 35 Replies Latest reply: Feb 20, 2013 8:23 AM by 992245 Go to original post RSS
      • 15. Re: how to change the column's position  in table?
        BluShadow
        It's bad practice because you any "unit" of code that's been through a proper design process should really have it explicitly specified what data should be retrieved via any select statements. It also adds to code maintainability, especially when someone unfamiliar with the code comes along and sees the query; they can actually see what the intended retrieved data should be rather than having to trawl through lines of code afterwards to see which columns are used from the table and which aren't.

        ;)
        • 16. Re: how to change the column's position  in table?
          APC
          should really have it explicitly specified what data should be retrieved via any select statements.
          Ah, yes, but suppose the answer is "all".

          For instance, an ETL process which takes all the columns out of a table in one schema and squirts them into a table in a second schema. In that circumstance I really am not interested in individual columns, I am interested in all columns regardless. So SELECT * is clearer - more readable - and easier than counting up all the columns to make sure I got every one. It is also safer, because if the two tables in each schema don't happen to match the routine will hurl an ORA-00913 or ORA-00947, which is what I would want to happen. Plus I don't have to change my code whenever a column gets added to a table.

          Obviously, if we are only using one or two columns from a table we should explicitly select them. But there are occasions when SELECT * is appropriate and indeed a better solution.

          Cheers, APC
          • 17. Re: how to change the column's position  in table?
            BluShadow
            Eee by gum. We could argue all day about this.

            I can see what your saying, but I won't be including any select * 's in the ETL process I'm working on, and if I found it in the design specs I'd be borrowing Billy's lead pipe. ;)

            If the ETL process does a SELECT *, then how is the actual process going to know about the new columns and what it should do with them? Seems like an odd and unclear requirement. I'd consider bad design, even if you don't.

            ;)
            • 18. Re: how to change the column's position  in table?
              RadhakrishnaSarma
              there are occasions when SELECT * is appropriate and
              indeed a better solution.
              I've been in this forum for almost a year and haven't seen any of your answers misleading(not exactly what I meant, but haven't got an appropriate word in English to my mind). I meant you always provide fool-proof solutions and not this time, I'm afraid. I think naming the column individually in the SELECT is ALWAYS a fool-proof solution and especially in the context of OP's reqt.

              Even with ETL, if you take Informatica and connnect two schema tables and ask it to generate the SQL for it, the Designer will give you the select statements with all columns individually select like
              select table1.col1, table1.col2 ...
              from table1
              Not even remotely meant any offence

              Cheers
              Sarma.
              • 19. Re: how to change the column's position  in table?
                518838
                All,
                Thanks for all your comments, & I really got a clear picture of the do's & don'ts for query selection.
                But here the problem is still with me, what to do?
                Because currently running application is quite old, & also running successfully at production.
                I also agree that using "select *" is a bad practice, but nothing can be done with already runnig application. As most of the s/w applications have a lots of bugs, & we've live with those bugs.
                If I go for Blushadow's comments(replacing all select * to individual column names) , it will be a change to a already running application, & I'll have to check its impact with all the dependent scripts & programs(SQL, Java etc..), & the overall change will be quite bigger, requiring a lot of analysis, a lot of time, & a lot of regression testing, which will increase the cost of our project also, which client will not get agree upon.
                In this case, the option remains with changing the column position so that already running application don't get affected.

                If I'm looking for DBMS_REDEFINITION package, I'm not able to find it out in database(9i)
                Pls guide me the usage of it, how can I use it.

                Thanks
                Deepak
                • 20. Re: how to change the column's position  in table?
                  APC
                  As most of the s/w applications have a lots of bugs, & we've live with those bugs.
                  "To tolerate a problem is to insist on it."
                  Software for Your Head
                  If I'm looking for DBMS_REDEFINITION package, I'm not able to find it out in database(9i)
                  Tim Hall has a useful article on his Oracle-Base site.

                  Cheers, APC
                  • 21. Re: how to change the column's position  in table?
                    518838
                    I'm using the same article, but while runnig the
                    command
                    DBMS_REDEFINITION.Can_Redef_Table('SCOTT', 'EMPLOYEES'),

                    its giving below error : BEGIN Dbms_Redefinition.Can_Redef_Table

                    ('SCOTT', 'EMPLOYEES'); END;
                    *
                    ERROR at line 1:
                    ORA-06550: line 1, column 7:
                    PLS-00201: identifier 'DBMS_REDEFINITION' must be declared
                    ORA-06550: line 1, column 7:
                    PL/SQL: Statement ignored
                    • 22. Re: how to change the column's position  in table?
                      APC
                      PLS-00201: identifier 'DBMS_REDEFINITION' must be declared
                      Can you do this
                      SQL> desc sys.DBMS_REDEFINITION
                      If not you may need to move to a more powerful user (such as SYSTEM). This package needs some high powered privileges.
                           * CREATE ANY TABLE 
                           * ALTER ANY TABLE
                           * DROP ANY TABLE
                           * LOCK ANY TABLE
                           * SELECT ANY TABLE
                      Cheers, APC
                      • 23. Re: how to change the column's position  in table?
                        518838
                        I'm firing this from user SYS which is having all the privileges


                        SQL> show user
                        USER is "SYS"
                        SQL> desc sys.DBMS_REDEFINITION
                        ERROR:
                        ORA-04043: object sys.DBMS_REDEFINITION does not exist
                        • 24. Re: how to change the column's position  in table?
                          BluShadow
                          Are you sure your database version is 9i? Are you sure you're not just looking at the version of your client sqlplus program (a very common mistake to make).
                          • 25. Re: how to change the column's position  in table?
                            27876
                            Additionally, what is the output of:
                            SQL> select * from v$version ;
                            ?
                            • 26. Re: how to change the column's position  in table?
                              APC
                              Tsk, tsk. Don't you know SELECT * is always bad practice? ;)

                              Cheers, APC
                              • 27. Re: how to change the column's position  in table?
                                27876
                                Tsk, tsk. Don't you know SELECT * is always bad practice? ;)
                                Well, in hind sight, I kind of supported your assertion that SELECT * in some situations is probably OK to use.

                                :-)
                                • 28. Re: how to change the column's position  in table?
                                  RadhakrishnaSarma
                                  Okay! Let me reframe that ALWAYS case.

                                  Unless the table(or view) has only one column, using select * is always a bad practice.
                                  SQL> desc v$version
                                   Name                                                              Null?    Type
                                   ----------------------------------------------------------------- -------- ------------
                                   BANNER                                                                     VARCHAR2(64)
                                  
                                  SQL>
                                  Cheers
                                  Sarma.
                                  • 29. Re: how to change the column's position  in table?
                                    APC
                                    Unless the table(or view) has only one column
                                    Thin end of the wedge. The view might have only one column this week but might have thirty-seven columns next week. You should have stuck to your guns :D

                                    The fact is, SQL*Plus can manage SELECT * easily so it is always safe to use this. This lends weight to the idea that that problem is not necessarily with SELECT * but with the way we handle the FETCH-ing ...

                                    Cheers, APC