This discussion is archived
1 2 3 Previous Next 35 Replies Latest reply: Feb 20, 2013 6:23 AM by 992245 Go to original post RSS
  • 15. Re: how to change the column's position  in table?
    BluShadow Guru Moderator
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Additionally, what is the output of:
    SQL> select * from v$version ;
    ?
  • 26. Re: how to change the column's position  in table?
    APC Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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