14 Replies Latest reply: Mar 12, 2010 10:45 AM by BluShadow RSS

    SQL command length

    user11032875
      I am kind of tired searching for solution on web. Let us hope some expert help.

      Is there a limit on command length in sql prompt? I am using Oracle 10g and found that sql prompt doesnt take command line longer than 256 char.

      SQL> update oe_order_headers_all set attribute19 = (select attribute19 from xx_quote_additional_info where username = 'DTUKADIABjk') where order_number = 14502 and attribute19 = (select attribute19 from xx_quote_additional_info where username = 'DTUKADIABjk') ;

      This are 256 char if I want to add something to this line, it wont take.....have to add to next line and then run.

      Only issue is I have to devide long query into multiple lines and then run. can not directly copy from code and run.
        • 1. Re: SQL command length
          009
          Hi,

          One way is to use Ed command , you have to set command prompt properties otherwise.

          *009*
          • 2. Re: SQL command length
            user11032875
            Could you provide more detail about how to use Ed command to set command length more than 258 char (it is 258 not 256)?
            • 3. Re: SQL command length
              user11032875
              Also if you can let us know how to set command properties.
              • 4. Re: SQL command length
                babaravi
                hi,

                check the following link.

                [http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/limits003.htm]
                • 5. Re: SQL command length
                  009
                  Hi,

                  ED or EDIT opens a text editor (notepad in case of windows) in which you can paste/write your script, save it and Type / + Enter/return. If you are using Win-OS, right click on title bar and set Layout-Screen buffer properties to your need, if you want to alter CMD-properties.

                  *009*

                  Edited by: 009 on Mar 11, 2010 9:05 PM
                  • 6. Re: SQL command length
                    user11032875
                    I am sorry but not able to find answer to my questions from the url.

                    Appreciate if you can point out , which property do I need to set from this for resolving my issue?
                    • 7. Re: SQL command length
                      user11032875
                      Edit command kind of stucks. Not able to get out of that.
                      SQL> Ed
                      Wrote file afiedt.buf
                      258
                      :
                      ?
                      /+
                      ?
                      ..


                      I am trying to set this in Oracle EBS, linux. Not sure if command properties would work for linux.
                      • 8. Re: SQL command length
                        009
                        Sorry but I don't have any idea about linux cmd-property change.
                        SQL> ed
                        Wrote file afiedt.buf
                        
                          1* SELECT * FROM TAB WHERE TNAME NOT LIKE 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
                        XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
                        XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
                        XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
                        XXXXXXXXXXXXXXXXXXX'
                        SQL> /
                        
                        TNAME                          TABTYPE  CLUSTERID
                        .
                        .
                        .
                        37 rows selected.
                        *009*
                        • 9. Re: SQL command length
                          user11032875
                          tried all the posibilities,


                          SQL> Ed
                          Wrote file afiedt.buf
                          258
                          :
                          ?

                          ?
                          ^C
                          ?
                          ^Z
                          ?
                          +
                          ?
                          /+
                          ?
                          /
                          ?
                          exit
                          ?
                          :w
                          ?
                          :q!
                          ?


                          any help?
                          • 10. Re: SQL command length
                            009
                            Do you know, which editor by default SQLPlus uses? Try to set vi editor as its default editor, Vi Ed in Linux

                            *009*
                            • 11. Re: SQL command length
                              user11032875
                              Thanks 009,

                              That solve partial of my issue. I set following command,

                              DEFINE _EDITOR=vi

                              Now this opens vi editor and I can paste my long command. then / , But for each change again have to go to vi editor doesnt sound good to me.

                              I tried sqlplus on my local desktop with Oracle 11g database. It allow you to enter as may characters as command. But my sqlplus with Oracle 9i database allows only 258.

                              I believe there should be some SET command to set length.
                              • 12. Re: SQL command length
                                BluShadow
                                user11032875 wrote:
                                Thanks 009,

                                That solve partial of my issue. I set following command,

                                DEFINE _EDITOR=vi

                                Now this opens vi editor and I can paste my long command. then / , But for each change again have to go to vi editor doesnt sound good to me.
                                Using an editor to edit your query doesn't sound good? ?:|

                                Sounds ideal to me.
                                I tried sqlplus on my local desktop with Oracle 11g database. It allow you to enter as may characters as command. But my sqlplus with Oracle 9i database allows only 258.
                                You intially said you were using 10g, not 9i. That makes a difference. Upgrade your SQL*Plus client to 10g or 11g.
                                • 13. Re: SQL command length
                                  user11032875
                                  Sound good, means not as comfortable ..... it is the same thing as using my notepad and copy/paste query to sqlplus.

                                  I thought if there is something using which I donot require to exit out of sqlplus prompt......You think only option is to upgrade to 10g? now this doesnt sound good to me. Better I would continue following this cumbersome method. :)

                                  I am sorry that mistakenly I specified 10g but 10g and 11g does support long command length.
                                  • 14. Re: SQL command length
                                    BluShadow
                                    user11032875 wrote:
                                    Sound good, means not as comfortable ..... it is the same thing as using my notepad and copy/paste query to sqlplus.
                                    Not quite, as you get taken straight into the editor automatically from the command line and then you just exit the editor, saving it as you go and it appears back at the command line. No need to copy or paste anything. ;)