8 Replies Latest reply on Mar 21, 2019 5:03 AM by Mark Francome

    ERROR:SQL command not properly ended

    krishna prasad
      Share:|

        DB JOB:  Below query does not working , encountering error like  SQL command not properly ended. Please help

       

      SELECT * FROM CMR_AJF

      WHERE NODEID='dwt';

       

       

        • 1. Re: ERROR:SQL command not properly ended
          Mark Francome

          What kind of DB are you running this against?

          • 2. Re: ERROR:SQL command not properly ended
            krishna prasad

            Hi Mark,

             

            Thanks for your reply

             

            its an Oracle database.

            • 3. Re: ERROR:SQL command not properly ended
              Mark Francome

              It couldn't be something as simple as spaces? i.e. -

               

              SELECT * FROM CMR_AJF

              WHERE NODEID = 'dwt';

              1 of 1 people found this helpful
              • 4. Re: ERROR:SQL command not properly ended
                krishna prasad

                Working fine now.Thanks.a lot!!

                It is possible to get the output in a table format?

                • 5. Re: ERROR:SQL command not properly ended
                  Mark Francome

                  You can output to html tables in Oracle.

                   

                  For example, before your SQL run (you might have to hack this around slightly) -

                   

                  set markup HTML ON SPOOL OFF ENTMAP ON table 'border=1 bgcolor=#F08080 cellspacing=3 cellpadding=6 style=\"font-family:Calibri;color:Black\"'

                   

                  if you need it in script version let me know.

                  • 6. Re: ERROR:SQL command not properly ended
                    krishna prasad

                    Thank you Sir!!

                     

                    I will check and update.

                    • 7. Re: ERROR:SQL command not properly ended
                      krishna prasad

                      Hi Mark,

                       

                      Requesting you to share in script version .

                       

                      Thanks.

                      • 8. Re: ERROR:SQL command not properly ended
                        Mark Francome

                        Ok, what I have is a shell script that runs the queries for various states of job completion, puts them in appropriately coloured tables and then emails the output.

                         

                        Obviously tweak as needed and note that the table colours might get interpreted in varying ways.

                         

                        The whole thing is -

                         

                        #!/bin/sh

                         

                        NOTIFYFILE=/tmp/cmajobs${$}.out

                         

                        echo "<font face="calibri" color="Aquamarine" font size="3" ><b>Aquamarine</b><font face="calibri" color="black" font size="3" > = Ended OK.<font face="calibri" color="red" font size="3" > <b>Red</b><font face="calibri" color="black" font size="3" > = Failed.<font face="calibri" color="DarkOrange" font size="3" > <b>DarkOrange</b><font face="calibri" color="black" font size="3" > = Currently Executing. <br />Grey = Waiting on Time Window. <font face="calibri" color="blue" font size="3" ><b>Blue</b> <font face="calibri" color="black" font size="3" >= Waiting on Resources.<br /><br />" >$NOTIFYFILE

                         

                        SQL -s >>$NOTIFYFILE << EOF

                        set feedback off

                        set markup HTML ON SPOOL OFF ENTMAP ON table 'border=1 bgcolor=#F08080 cellspacing=3 cellpadding=6 style=\"font-family:Calibri;color:Black\"'

                         

                        /* Selects job icons that are RED and in FAILED STATE  */

                        select applgroup as "Database", memname as "Process", startrun as "Start Time", endrun as "End Time" from CMR_AJF where SCHEDTAB is not NULL AND STATE = '8' AND STATUS = 'N' AND TASKTYPE <> 'U' AND HOLDFLAG <> 'D' AND MEMNAME like 'dba_rman_backup%'

                        ORDER by applgroup;

                         

                        EOF

                         

                        SQL -s >>$NOTIFYFILE << EOF

                        set feedback off

                        set markup HTML ON SPOOL OFF ENTMAP ON table 'border=1 bgcolor=#F0E68C cellspacing=3 cellpadding=6 style=\"font-family:Calibri;color:SteelBlue\"'

                         

                        /* Selects job icons that are yellow from tables and that    */

                        /* are not dummies and do have memnames (i.e. any executing)        */

                        select applgroup as "Database", memname as "Process", startrun as "Start Time", endrun as "End Time" from CMR_AJF where SCHEDTAB is not NULL AND STATE = '4' AND TASKTYPE <> 'U' AND HOLDFLAG <> 'D' AND MEMNAME like 'dba_rman_backup%'

                        ORDER by applgroup;

                         

                        EOF

                         

                        SQL -s >>$NOTIFYFILE << EOF

                        set feedback off

                        set markup HTML ON SPOOL OFF ENTMAP ON table 'border=1 bgcolor=#DCDCDC cellspacing=3 cellpadding=6 style=\"font-family:Calibri;color:DimGrey\"'

                         

                         

                        /* Selects job icons that are grey and are outside sub time window.  */

                        select applgroup as "Database", memname as "Process", startrun as "Start Time", endrun as "End Time" from CMR_AJF where STATE = 'B' AND STATUS = 'N' AND TASKTYPE <> 'U' AND MEMNAME is not NULL AND HOLDFLAG <> 'D' AND MEMNAME like 'dba_rman_backup%'

                        ORDER by applgroup;

                         

                        EOF

                         

                        SQL -s >>$NOTIFYFILE << EOF

                        set feedback off

                        set markup HTML ON SPOOL OFF ENTMAP ON table'border=1 bgcolor=#87CEFA cellspacing=3 cellpadding=6 style=\"font-family:Calibri;color:MidnightBlue\"'

                         

                        /* Job icons that are blue (i.e. waiting for control/quantitative resources). */

                        select applgroup as "Database", memname as "Process", startrun as "Start Time", endrun as "End Time" from CMR_AJF where SCHEDTAB is not NULL AND STATE = 'D' AND STATUS = 'N' AND TASKTYPE <> 'U' AND HOLDFLAG <> 'D' AND MEMNAME like 'dba_rman_backup%'

                        ORDER by applgroup;

                         

                        EOF

                         

                        SQL -s >>$NOTIFYFILE << EOF

                        set feedback off

                        set markup HTML ON SPOOL OFF ENTMAP ON table'border=1 bgcolor=#7FFFD4 cellspacing=3 cellpadding=6 style=\"font-family:Calibri;color:Black\"'

                         

                        /* Selects job icons that are GREEN and COMPLETED OK    */

                        select applgroup as "Database", memname as "Process", startrun as "Start Time", endrun as "End Time" from CMR_AJF where SCHEDTAB is not NULL AND STATE = '8' AND STATUS = 'Y' AND TASKTYPE <> 'U' AND HOLDFLAG <> 'D' AND MEMNAME like 'dba_rman_backup%'

                        ORDER by applgroup;

                         

                        EOF

                         

                        echo "<font face="calibri" color="black" font size="3" >  </font>" >>$NOTIFYFILE

                         

                        sbrmcntc=`SQL -s<< EOF

                        set head off pagesize 0 trims on trimo on

                        col nb for 99

                        select COUNT(*) as nb from CMR_AJF where TASKTYPE <> 'U' AND STATUS = 'Y' AND STATE = '8' AND HOLDFLAG <> 'D' AND TASKTYPE <> 'U' AND MEMNAME like 'dba_rman_backup%';

                        EOF`

                         

                        sbrnfail=`SQL -s<< EOF

                        set head off pagesize 0 trims on trimo on

                        col nb for 99

                        select COUNT(*) as nb from CMR_AJF where TASKTYPE <> 'U' AND STATUS = 'N' AND STATE = '8' AND HOLDFLAG <> 'D' AND TASKTYPE <> 'U' AND MEMNAME like 'dba_rman_backup%';

                        EOF`

                         

                        echo "<font face="calibri" color="black" font size="3" > Number of dba_rman_backup* Jobs Completed OK = ${sbrmcntc}</b></font>" >>$NOTIFYFILE

                         

                        mailees="Service.DBA@acme.com; Service.ControlM@acme.com; IT.ServiceDesk@acme.com; Dave@acme.com"

                         

                        /usr/sbin/sendmail -t <<-EOMAIL

                        From: Service.ControlM@acme.com

                        To: `echo $mailees`

                        Subject: Oracle dba_rman_backup Jobs (Total OK =$sbrmcntc / Failed =$sbrnfail) on `date +"%d-%b-%Y"`

                        Content-Type: text/html

                        MIME-Version: 1.0

                         

                        `cat ${NOTIFYFILE}`

                        EOMAIL

                         

                        rm ${NOTIFYFILE}

                        2 of 2 people found this helpful