5 Replies Latest reply: Jan 9, 2012 9:42 AM by Paul M. RSS

    script with sqlplus

    francy77
      hi all,

      i have a script like this one:

      SPOOL aaa.LOG;
      SET DEFINE OFF;
      SET TIMING ON;
      SET TIME ON;
      SET ECHO ON;


      insert into MIA_PM (DS_EVENTO,DT_INSERIMENTO) VALUES('oo"gg"',sysdate);
      commit;

      SHOW ERRORS;
      SPOOL OFF;




      and want to execute it via sqlplus. The file is called 000.sql and run it in this way:

      14:06:27 SQL> @000.sql


      unfortunately the sqlplus tell me:

      14:06:27 SQL> @000.sql
      14:11:47 SQL> ´╗┐SPOOL aaa.LOG;
      SP2-0734: comando con inizio "´╗┐SPOOL a..." sconosciuto. Il resto della riga Þ stato ignorato.



      but there aren't any other character before the word SPOOL. where is the problem??


      thanks
      Francesco
        • 1. Re: script with sqlplus
          Paul M.
          but there aren't any other character before the word SPOOL.
          Are you sure ? Did you check it ? How ? On Linux/Unix you may try
          $ od -c 000.sql
          • 2. Re: script with sqlplus
            francy77
            thanks, i'm using windows but with Cygwin i saw that there are other chracter.


            $ od -c 000.sql
            0000000 357 273 277 S P O O L a a a . L O G
            0000020 ; \r \n S E T D E F I N E O F
            0000040 F ; \r \n S E T T I M I N G O
            0000060 N ; \r \n S E T T I M E O N ;
            0000100 \r \n S E T E C H O O N ; \r \n
            0000120 \r \n \r \n i n s e r t i n t o
            0000140 M I A _ P M ( D S _ E V E N
            0000160 T O , D T _ I N S E R I M E N T
            0000200 O ) V A L U E S ( ' o o " g g
            0000220 " ' , s y s d a t e ) ; \r \n c
            0000240 o m m i t ; \r \n \r \n S H O W E
            0000260 R R O R S ; \r \n S P O O L O F
            0000300 F ; \r \n
            0000304
            • 3. Re: script with sqlplus
              Paul M.
              with Cygwin i saw that there are other chracter.
              Hoping Cygwin works correctly with tr command you may try
              $ tr -cd '\11\12\15\40-\176' < 000.sql > clean_file.sql
              000.sql won't be changed, while clean_file.sql shouldn't have garbage anymore.
              • 4. Re: script with sqlplus
                francy77
                i've tried and it works correctely.

                but i'm not able to understand what means \11\12\15\40-\176 in the tr command. Could you explain it?


                thanks
                Francesco
                • 5. Re: script with sqlplus
                  Paul M.
                  i'm not able to understand what means \11\12\15\40-\176 in the tr command.
                  The command removes all the characters from the input file other than the ASCII octal values that are shown between the single quotes.

                  Specifically the following characters pass through this filter:

                  octal 11: tab
                  octal 12: linefeed
                  octal 15: carriage return
                  octal 40 through octal 176: all the "good" keyboard characters

                  All the other characters are stripped out.
                  $ man ascii
                  $ man tr