May 5, 2010 at 8:04 am
We have inherited an application written by a consultant. One of the SSIS packages connects to an Oracle database and the data is then further processed. The users requested an additional field to be added. Whenever we select this field and parse the subsequent query, we get this error - 'ORA-00933 - SQL command not properly ended'.
I attach a couple of versions of the SQL script (with and without quotes). I have not got a clue about Oracle these days (haven't used it since mid 1990s).
If I remove the offending field (a.ID_DESC) the package works fine so I am stumped. Has anyone any ideas?
:crazy:
Madame Artois
May 5, 2010 at 8:37 am
i think in Oracle, a semicolon is manditory to determine the end of a statement;
i reformatted your queries and pasted them into SLq Developer, and got the same error.
when i added the semicolon, it moved on to missing table/view errors.
SELECT b.INC_PK,
a.ID_IN_REF,
a.ID_MOBS_STOP,
a.ID_IT_ENUM1,
a.ID_EASTING,
a.ID_NORTHING,
a.ID_TODAYS_DATE,
TO_NUMBER(TO_CHAR(a.ID_TODAYS_DATE, 'YYYYMMDD'), '99999999') AS CalendarId,
b.INC_FK_IN,
b.INC_FRS_ID,
b.INC_DATE_CREATED,
b.INC_INCIDENT_CATEGORY,
b.INC_PROPERTY_TYPE,
b.INC_IS_PROPERTY_REGULATED,
b.INC_PROPERTY_CATEGORY,
b.INC_HAZMATS_INVOLVED,
b.INC_IS_RTC,
b.INC_SPECIAL_SERVICE_TYPE,
b.INC_FALSE_ALARM_REASON,
b.INC_ATTACK_ON_FIRE_FIGHTERS,
b.INC_TYPE_OF_ATTACK,
b.INC_FIGHTER_SERIOUS_INJURIES,
b.INC_FIGHTER_SLIGHT_INJURIES,
b.INC_EVAC_ASSISTANCE_INVOLVED,
b.INC_EVAC_WITH_ASSISTANCE,
b.INC_EVAC_WITHOUT_ASSISTANCE,
b.INC_EVACUATION_FRS_ASSISTANCE,
b.INC_VICTIMS_INVOLVED,
b.INC_LOCATION_ADDRESS,
b.INC_STAGE,
b.INC_FIRE_CLASSIFICATION,
b.INC_IS_PRIMARY_FIRE,
c.PR_WARD,
a.ID_DESC
FROM ID_DETAILS a
INNER JOIN INC_INCIDENT b ON a.ID_IN_REF = b.INC_FK_IN
INNER JOIN PR_OPERTY c ON a.ID_IN_REF = c.PR_IN_REF
WHERE (a.ID_TODAYS_DATE >= to_date('2009-03-01', 'YYYY-MM-DD'))
AND (a.ID_IT_ENUM1 IN ('AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AL', 'AS', 'BC', 'AM'));
SELECT b.INC_PK,
a.ID_IN_REF,
a.ID_MOBS_STOP,
a.ID_IT_ENUM1,
a.ID_EASTING,
a.ID_NORTHING,
a.ID_TODAYS_DATE,
TO_NUMBER(TO_CHAR(a.ID_TODAYS_DATE, 'YYYYMMDD'), '99999999') AS CalendarId,
b.INC_FK_IN,
b.INC_FRS_ID,
b.INC_DATE_CREATED,
b.INC_INCIDENT_CATEGORY,
b.INC_PROPERTY_TYPE,
b.INC_IS_PROPERTY_REGULATED,
b.INC_PROPERTY_CATEGORY,
b.INC_HAZMATS_INVOLVED,
b.INC_IS_RTC,
b.INC_SPECIAL_SERVICE_TYPE,
b.INC_FALSE_ALARM_REASON,
b.INC_ATTACK_ON_FIRE_FIGHTERS,
b.INC_TYPE_OF_ATTACK,
b.INC_FIGHTER_SERIOUS_INJURIES,
b.INC_FIGHTER_SLIGHT_INJURIES,
b.INC_EVAC_ASSISTANCE_INVOLVED,
b.INC_EVAC_WITH_ASSISTANCE,
b.INC_EVAC_WITHOUT_ASSISTANCE,
b.INC_EVACUATION_FRS_ASSISTANCE,
b.INC_VICTIMS_INVOLVED,
b.INC_LOCATION_ADDRESS,
b.INC_STAGE,
b.INC_FIRE_CLASSIFICATION,
b.INC_IS_PRIMARY_FIRE,
c.PR_WARD,
a.ID_DESC
FROM ID_DETAILS a
INNER JOIN INC_INCIDENT b ON (a.ID_IN_REF = b.INC_FK_IN)
INNER JOIN PR_OPERTY c ON (a.ID_IN_REF = c.PR_IN_REF)
WHERE (a.ID_TODAYS_DATE >= to_date('2009-03-01', 'YYYY-MM-DD'))
AND (a.ID_IT_ENUM1 IN ('AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AL', 'AS', 'BC', 'AM'));
Lowell
May 5, 2010 at 8:59 am
When I copied and pasted your code into the query I got the same error. When I added the item and added the comma and semi colon at the end I got a new error
ORA-00911:invalid character
Their help text says
identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters.
:sick: of Oracle!
Madame Artois
May 5, 2010 at 9:11 am
some errors are driver specific; your error said this specifically: spaces, tabs, or carriage returns as delimiters.
my code obviously had lots of CrLf's for readability; can you jsut add a semicolon to the end of your original statement, instead of using mine? i think that will fix it.
Lowell
May 6, 2010 at 2:54 am
I tried just adding the semi-colon but got the same error. I tried just adding the field with and without the semi colon but got the same error.
I tried clicking 'Preview' with the semi colon and got this error report. Could it be that the Data Source connection is the reason?
Oracle is driving me :crazy:
Madame Artois
May 6, 2010 at 2:56 pm
Please do this test for me.
Using your original code instead of adding column a.ID_DESC please add a different column, if possible of the number or varchar datatype.
Does it works or does it returns ORA-00933?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 7, 2010 at 1:59 am
I have tried to add another field and I get exactly the same error message. Are we getting somewhere (hopefully!)?
Madame Artois
May 7, 2010 at 6:24 am
Please connect to target Oracle instance via SQLPlus and execute query with and without offending column. What happens in each case?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 7, 2010 at 6:38 am
Sorry I do not have SQL Plus. I do not use Oracle at all; this is a 'heritage' database
Madame Artois
May 7, 2010 at 7:20 am
S Hodkinson (5/7/2010)
Sorry I do not have SQL Plus. I do not use Oracle at all; this is a 'heritage' database
any chance of asking whoever adminis Oracle database to do it for you? 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 7, 2010 at 7:40 am
No really unless I want to stir up a hornet's nest. The true knowledge dbas of this database are an outside company (the database will become redundant in the future).
I cannot understand why the query works now within the package but if I add any other field to the query I get this error.
Madame Artois
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply