October 31, 2009 at 2:19 am
Recently my company moved from SQL Server 2005 to Oracle. In SQL Server certain field names like name, date and description were used. I had no problem querying data with the names but now in Oracle the same field names cause me errors. I am using PL SQL Developer and SSIS. When I have to query any of the Oracle Tables with one of those three field names, I get an invalid error.
I have tried double quotes, Brackets and neither work. Has anyone else had this problem and if so what is the work around? Thanks
October 31, 2009 at 5:51 am
millerfw (10/31/2009)
Recently my company moved from SQL Server 2005 to Oracle. In SQL Server certain field names like name, date and description were used. I had no problem querying data with the names but now in Oracle the same field names cause me errors. I am using PL SQL Developer and SSIS. When I have to query any of the Oracle Tables with one of those three field names, I get an invalid error.I have tried double quotes, Brackets and neither work. Has anyone else had this problem and if so what is the work around? Thanks
By default the object names in oracle ara in all caps. Since you have migrated the db from SQL server to Oracle, you need to check the objects name seem to be organized differently. Do the following in a SQL Plus window.
SELECT * FROM TAB;
Check the exact names and then issue the command to check the field names.
for instance
DESC "Test"
or
DESC "test"
use the filed names exactly as returned by the above command (inside the double quotes).
You are right on using the double quotes. Brackets will not work. What you seem to be missing is the correct case.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
November 1, 2009 at 1:49 pm
In PL SQL Developer I can see that the case is lower and when I run the query in PL SQL Developer it runs fine. When I use the same exact query in SSIS I get the "ORA-00904:"LL10"."DESCRIPTION": invalid identifier. I can do a preview and the query returns data but the minute I execute the package I get the error.
November 2, 2009 at 8:33 am
Can you post an example query?
November 2, 2009 at 8:44 am
Here is the entire query. It is basically the lines with the word description that are causing the problem in SSIS. Thanks
select c2.inventoryid,
CASE WHEN i.accepted = 0 THEN 'NEW' ELSE 'UPDATE' END as Action,
o.siteidentifier SITEID,
cc.name Cost_Center_Name,
cc.costcenternumber Cost_Center_Number,
ll7.description Cost_Center_Type,
iec.expensecode EXPENSE_CODE,
i.itemname Circuit_Name,
c2.circuitid CIRCUIT_ID,
pc.circuitid PARENT_CIRCUITID,
ap.name TYPE1,
TO_CHAR(ll1.description)APPLICATION,
dr.SpeedDisplay SPEED,
dn.itemname DATA_NETWORK,
c2.mileage DISTANCE,
C2.DISTANCEIN DISTANCE_TYPE,
c2.providertype PROVIDER_TYPE,
c2.tietrunk TIE_TRUNK,
c.name CARRIER,
ll2.description LINE_CODING,
ll3.description FRAMING,
ll9.description SIGNALING,
ll5.description FACILITY_TYPE,
ll6.description HUNT_TYPE,
i.btn BTN,
bs.name BILLING_SYSTEM_NAME,
a.accountnumber ACCOUNT1,
i.notes DESCRIPTION1,
c2.assignment ASSIGNMENT,
ll8.description INV_STATUS,
i.installdate INSTALL_DATE,
i.canceldate CANCEL_DATE,
i.disconnectdate DISCONNECT_DATE,
i.billingstartdate BILLING_START_DATE,
i.billingterminationdate BILLING_END_DATE,
cep.leccircuitid ALOC_LEC_CIRCUITID,
lc.name ALOC_LEC_CARRIER,
cep.ChargePercentage ALOC_CHARGE_PCT,
cep.hcoord ALOC_TERM_HCoord,
cep.vcoord ALOC_TERM_VCoord,
tei.identifier ALOC_TERM_EQUIPMENT,
tei.typename ALOC_TERM_EQUIPMENT_TYPE,
ri.InterfaceNumber ALOC_TERM_INTERFACE_NUM,
case when cep.ExtendedIndicator = 1 then 'YES' else 'NO' end ALOC_EXTENDED_DEMARK,
cep.EndPointBuilding ALOC_DEMARC_BUILDING,
cep.EndPointFloor ALOC_DEMARC_FLOOR,
cep.EndPointSuite ALOC_DEMARC_SUITE,
cep.EndPointRoom ALOC_DEMARC_ROOM,
cep.EndPointDepartment ALOC_DEMARC_DEPT,
ll4.description ALOC_TERM_JACK_TYPE,
cep.PinPosition ALOC_PIN_POSITION,
cep.EndPointNotes ALOC_PANEL_INFO,
cc2.circuitid ALOC_CONNECTING_CIRCUIT_ID,
car2.name ALOC_CONN_CIRCUIT_CARRIER,
o2.siteidentifier CONNECTING_CIRCUIT_SITEID ,
ll10.description PRIORITY
from circuit2 c2
join v_inventory i on c2.inventoryid = i.inventoryid
left outer join circuitendpoint cep on c2.inventoryid = cep.circuitinventoryid and cep.locationcategory = 'A'
left outer join inventory dn on cep.datanetworkinventoryid = dn.inventoryid
left outer join orgentity o on i.orgentityid = o.orgentityid
left outer join circuit2 pc on c2.parentid = pc.inventoryid
left outer join costcenterbillingitem ccb on c2.inventoryid = ccb.inventoryid and ccb.allocationtypeid = 2
left outer join costcenter cc on ccb.costcenterid = cc.costcenterid
left outer join account a on c2.accountid = a.accountid
left outer join billingsystem bs on a. BillingSystemID= bs.BillingSystemID
left outer join carrier c on c2.carrierid = c.carrierid
left outer join accessproduct ap on c2.accessproductid = ap.productid
left outer join datarate dr on c2.datarateid = dr.datarateid
left outer join carrier lc on cep.leccircuitcarrierid = lc.carrierid
left outer join v_inventory tei on cep.TerminatingEquipmentID = tei.inventoryid
left outer join routerinterface ri on cep.routerinterfaceid = ri.routerinterfaceid
left outer join lookuplistitem ll1 on c2.applicationluid = ll1.lookuplistitemid and ll1.lookuplistid = 134 -- application
left outer join lookuplistitem ll2 on c2.linecodingluid = ll2.lookuplistitemid and ll2.lookuplistid = 130 -- line coding
left outer join lookuplistitem ll3 on c2.framingluid = ll3.lookuplistitemid and ll3.lookuplistid = 131 -- framing
left outer join lookuplistitem ll4 on cep.jacktypeid = ll4.lookuplistitemid -- jack type
left outer join lookuplistitem ll5 on c2.VoiceFacilityTypeLUID = ll5.lookuplistitemid and ll5.lookuplistid = 132 -- voice facility type
left outer join lookuplistitem ll6 on c2.VoiceHuntTypeLUID = ll6.lookuplistitemid and ll6.lookuplistid = 133 -- voice hunt type
left outer join inventoryexpensecode iec on c2.inventoryid = iec.inventoryid
left outer join lookuplistitem ll7 on cc.CategoryLookupID = ll7.lookuplistitemid
left outer join lookuplistitem ll8 on i.StatusLUID = ll8.lookuplistitemid
left outer join lookuplistitem ll9 on c2.SignalLUID = ll9.lookuplistitemid
left outer join lookuplistitem ll10 on i.PriorityLUID = ll10.lookuplistitemid--priority
left outer join circuitendpoint ccep on cep.ConnectingCircuitEndPointID = ccep.CircuitEndPointID
left outer join circuit2 cc2 on ccep.CircuitInventoryID = cc2.inventoryid
left outer join carrier car2 on cc2.carrierid = car2.carrierid
left outer join v_inventory i2 on i2.inventoryid=cc2.inventoryid
left outer join orgentity o2 on o2.orgentityid= i2.orgentityid
Where i.deleted = 0 and i.ignored = 0
November 12, 2009 at 8:40 am
Having faced the same problem, I concluded that you just cannot use ORACLE reserved words for column names etc.
Brackets, quotes etc do not help.
Lower/upper case is irrelevant. By default, names are case-insensitive. I think there may be an option for case-sensitive names but it's not recommended.
The convention seems to be that when you convert your database, add a trailing underscore to a column name that is a reserved word.
I think that that is what the ORACLE migration wizard does.
Martin
Edit - see correction from Pablo in next post.
November 13, 2009 at 8:52 am
Martin Wills (11/12/2009)
Having faced the same problem, I concluded that you just cannot use ORACLE reserved words for column names etc.Brackets, quotes etc do not help.
Statements above do not reflect reality.
Look...
SQL>
SQL> create table testtable ("name" varchar2(10), "date" date, "description" varchar2(10));
Table created.
SQL> insert into testtable values('AAAAA', sysdate, 'BBBBB');
1 row created.
SQL> commit;
Commit complete.
SQL> select "name", "date", "description" from testtable;
name date descriptio
---------- --------- ----------
AAAAA 13-NOV-09 BBBBB
SQL>
_____________________________________
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.November 16, 2009 at 4:24 am
You're right Pablo, I'm wrong (= I don't reflect reality) :blush:.
Double quotes do work around the reserved words.
Single quotes and square brackets do not work. I must have given up at that stage.
Thanks Pablo for providing the correct info.
Martin
November 17, 2009 at 12:30 pm
Glad to help.
_____________________________________
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.November 17, 2009 at 2:22 pm
I appreciate the comments but nothing is working. When I use the SQL Server Manager to Import the data from Oracle it works fine with no errors. I save the import as an SSIS package. When I execute the SSIS package I get the error. Makes no sense at all.
November 17, 2009 at 2:40 pm
millerfw (11/17/2009)
I appreciate the comments but nothing is working. When I use the SQL Server Manager to Import the data from Oracle it works fine with no errors. I save the import as an SSIS package. When I execute the SSIS package I get the error. Makes no sense at all.
Is it an Oracle side error? ... in the ORA-NNNNN form?
Please copy/paste full error stack.
_____________________________________
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.November 18, 2009 at 3:57 am
Advice given so far is accurate, reserved words can be used but they must be enclosed in double quotes.
Once enclosed in double quotes the identifier becomes case sensitive and it must exactly match the name stored in Oracle dictionary.
For your query lines such as:
cc.name Cost_Center_Name,
ll7.description Cost_Center_Type,
should look like:
cc."name" Cost_Center_Name,
ll7."description" Cost_Center_Type,
Which assumes that the column names as transferred from SQL Server were also lower case. If they were capitalised then the lines should look like:
cc."Name" Cost_Center_Name,
ll7."Description" Cost_Center_Type,
January 23, 2010 at 9:04 am
January 23, 2010 at 9:06 am
March 31, 2010 at 6:08 am
rojen83 (1/23/2010)
Handle Transaction (Rollback/Commit) when applicable. Regards, ... If that's the case, temporary tables will not work. I have tried it for a couple cases
:blink: either this is spam or you posted in the wrong thread.
_____________________________________
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.Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply