How to resolve ORA-00933: SQL command not properly ended in oracle?

Question

Here is the package created by passing 3 input parameters to function

CREATE OR replace PACKAGE "PKG_CAMPAIGN_EMAIL_QTY"
AS
  FUNCTION Getcampaignoutgoingemailqty(
    tablename     IN VARCHAR2,
    ActivatedDate IN DATE,
    CompletedDate IN DATE)
  RETURN NUMBER;
END pkg_campaign_email_qty;

/

Here is the query to get the count

SELECT
(
pkg_campaign_email_qty.Getcampaignoutgoingemailqty(
      9142632263013677974, 
      To_date('20/10/2015', 'DD/MM/YYYY'), 
      To_date('30/11/2015', 'DD/MM/YYYY')
      ) 
) AS
email
FROM   dual;

Getting ORA-00933: SQL command not properly ended in oracle

here is the package body

CREATE OR REPLACE PACKAGE BODY "PKG_CAMPAIGN_EMAIL_QTY" as
FUNCTION getCampaignOutgoingEmailQty(tableName IN VARCHAR2 ,ActivatedDate DATE,CompletedDate DATE) RETURN NUMBER IS
OutgoingEmailQuantity NUMBER;
  begin
        EXECUTE IMMEDIATE 'select NVL(COUNT(1),0) from campaign_'||tableName||'
            join flat_interactions out_email on campaign_'||tableName||'.fullname=out_email.o_parent_id and  out_email.N9135700037713613964=9135706250013621563 and out_email.D9135699928113613119 between TO_DATE(''ActivatedDate'',''MM/dd/YYYY'') and TO_DATE(''CompletedDate'',''MM/dd/YYYY'')' INTO OutgoingEmailQuantity;
RETURN OutgoingEmailQuantity ;
   EXCEPTION
      WHEN OTHERS THEN
       RETURN 0;
  end getCampaignOutgoingEmailQty;
end PKG_CAMPAIGN_EMAIL_QTY;
/

Please could you update your question to show the code for Getcampaignoutgoingemailqty (which would be in the package body). — Boneist, Nov 03 '15 at 10:58
I don't get that error running your code. I get zero, because of the exception you're squashing within the package function - never use `when others` like that, you have no idea what is going wrong - which is because of the date handing. — Alex Poole, Nov 03 '15 at 12:13

score 1 · Accepted Answer · answered Nov 03 '15 at 12:03

I don't know if this is the cause of the error you're seeing, but there's an issue with the function code - if you look inside the execute immediate, you've got

'<snip> and out_email.D9135699928113613119 between TO_DATE(''ActivatedDate'',''MM/dd/YYYY'') and TO_DATE(''CompletedDate'',''MM/dd/YYYY'')'

This means that, passing those parameters in, you'll end up trying to execute a sql statement of:

select NVL(COUNT(1),0)
from   campaign_9142632263013677974
       join flat_interactions out_email on campaign_9142632263013677974.fullname = out_email.o_parent_id 
                                           and out_email.N9135700037713613964 = 9135706250013621563
                                           and out_email.D9135699928113613119 between TO_DATE('ActivatedDate','MM/dd/YYYY') 
                                           and TO_DATE('CompletedDate','MM/dd/YYYY');

So, you're trying to convert the strings "ActivatedDate" and "CompletedDate" into dates when clearly they're not dates.

Rather than do that, I would use bind variables, something like:

create or replace package body pkg_campaign_email_qty
as
  function getcampaignoutgoingemailqty(tablename in varchar2 ,activateddate date,completeddate date)
  return number
  is
    outgoingemailquantity number;
  begin
    execute immediate 'select NVL(COUNT(1),0)'||chr(10)||
                      'from   campaign_'||tablename||chr(10)||
                      '       join flat_interactions out_email on campaign_'||tablename||'.fullname=out_email.o_parent_id'||chr(10)||
                      '                                           and out_email.N9135700037713613964=9135706250013621563'||chr(10)||
                      '                                           and out_email.D9135699928113613119 between :ActivatedDate and :CompletedDate' into outgoingemailquantity using activateddate, completeddate;
    return outgoingemailquantity;
  exception
    when others then
      return 0;
  end getcampaignoutgoingemailqty;
end pkg_campaign_email_qty;
/

N.B. untested, since you didn't provide any table definitions.

As an aside, with your calling query, the outer brackets are unnecessary and I would remove them, so your query would become:

SELECT pkg_campaign_email_qty.Getcampaignoutgoingemailqty(9142632263013677974, 
                                                          To_date('20/10/2015', 'DD/MM/YYYY'), 
                                                          To_date('30/11/2015', 'DD/MM/YYYY')) email
FROM   dual;

When you say `#Date From#`, do you mean that to be a parameter? What is going to be calling this function? Some front end code (eg. C#, Java, etc) or users running it manually? If the latter, where would they run it? As a script or as an ad-hoc statement in a GUI such as Toad? — Boneist, Nov 03 '15 at 13:30

score 1 · Answer 2 · answered Nov 03 '15 at 12:08

You are using wrong the dates in the dynamic sql.

I will use binds for that:

  EXECUTE IMMEDIATE 
    'select NVL(COUNT(1),0) 
     from campaign_'||tableName||'
     join flat_interactions out_email on 
        campaign_'||tableName||'.fullname=out_email.o_parent_id and  
        out_email.N9135700037713613964=9135706250013621563 and 
        out_email.D9135699928113613119 between :ActivatedDate and :CompletedDate' 
  INTO OutgoingEmailQuantity 
  USING ActivatedDate, CompletedDate;

What you want to do is harder to follow:

  EXECUTE IMMEDIATE 
    'select NVL(COUNT(1),0) 
     from campaign_'||tableName||'
     join flat_interactions out_email on 
        campaign_'||tableName||'.fullname=out_email.o_parent_id and  
        out_email.N9135700037713613964=9135706250013621563 and 
        out_email.D9135699928113613119 between 
          TO_DATE('||to_char(ActivatedDate,'MM/dd/YYYY')||',''MM/dd/YYYY'') 
          and 
          TO_DATE('||to_char(CompletedDate,'MM/dd/YYYY')||',''MM/dd/YYYY'')' 
  INTO OutgoingEmailQuantity;

score -1 · Answer 3 · answered Nov 03 '15 at 10:58

-1

SELECT
(
    pkg_campaign_email_qty.Getcampaignoutgoingemailqty(9142632263013677974, To_date('20/10/2015', 'DD/MM/YYYY'), To_date('30/11/2015', 'DD/MM/YYYY')) ) AS
    email
    FROM   dual 
);  <- missing

answered Nov 03 '15 at 10:58

EvilTeach

28,120
21
85
141

Same problem that was missed while copying query – Rahmath Nov 03 '15 at 11:09

How to resolve ORA-00933: SQL command not properly ended in oracle?

3 Answers3