Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

cx_Oracle (6.1), django(1.11.7) with Oracle 12cr2 and table / column name lengths greater than 30 by

Santosh Hegde-OracleDec 19 2017 — edited Dec 19 2017

Hello,

I am trying to figure out if cx_Oracle 6.1 has support for table names and column names being longer than 30 characters. We are migrating to Oracle from another database which supported longer identifiers so we have quite a few of these identifiers.

Oracle 12cr2 now supports this so I am able to migrate all the data without having to change identifier names.

However when I try and access the data ( django 1.11.7 and cx_oracle), I end up getting an error:

django.db.utils.DatabaseError: ORA-00904: "TABLE_NAME"."COLUMN_NAME_LONGER_THAN_TH403B": invalid identifier

The actual column name for e.g. could be COLUMN_NAME_LONGER_THAN_THIRTY_BYTES

Note - This is a made up example so the mangling scheme used for the column if this was a real example might be different.

But basically, I see that the column name is shortened to 30 characters with some kind of a mangling scheme. The database obviously throws the invalid identifier error.

Is there some cx_Oracle connection parameter / setting I could be using or some environment variable etc that can resolve this?

Or is this something I need to wait for a higher release of cx_Oracle?

I checked within django quite a bit but I dont think this is happening inside of django which is why I am asking this question in this forum.

Yes, I do have the option of renaming the tables and columns but given that 12cr2 officially supports this I am trying to avoid having to change application code.

Thanks  & Regards

Santosh

Comments

Eight Six
Try this not tested


select y.date, case when x.dt1 is null then max_name ELSE x.name END
from
(
select case when lag(date) over( order by date1) is null then to_date('01/01/0001','mm/dd/yyyy') ELSE END as dt1, date1 as dt2 , name ,
max(name) keep (dense_rank first order by date1 desc) as max_name
from table1
)x
Right Outer Join
table2 y
on (y.date1 >= x.dt1 and y.date1<=x.dt2)



Thanks
Aketi Jyuuzou
I think this is a very nice question :-)
It is very important to mension Oracle version at OP.

I like ScalarSubQuery B-) 
WITH table1 AS(
SELECT To_Date('3/13/2010','mm/dd/yyyy') date1, 'john' name FROM dual UNION ALL
SELECT To_Date('3/17/2010','mm/dd/yyyy') date1, 'smith' name FROM dual ),
table2 AS(
SELECT To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/20/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/25/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('4/20/2010','mm/dd/yyyy') date1 FROM dual)
select date1,
(select
 case max(case when b.date1 >= a.date1 then 1 else 0 end)
 when 1 then max(b.name) Keep(Dense_Rank First order by case when b.date1 >= a.date1
                                                             then 0 else 1 end,b.date1)
 else max(b.name) Keep(Dense_Rank Last order by b.date1) end
   from table1 b) as NAME
from table2 a;

DATE1     NAME
--------  -----
10-01-16  john
10-03-12  john
10-03-16  smith
10-03-20  smith
10-03-25  smith
10-04-20  smith
Boolean algebra in some Rows tutorial by Aketi Jyuuzou
1007678
Etbin
Aketi Jyuuzou wrote:
I think this is a very nice question :-)
and yours is for certain a remarkable answer ... hats off

Regards

Etbin
elmasduro
Aketi, this is great query thanks. i have one more questions. I changed my data in such a way that i introduce a new field call IND.
so my data now looks like this 
WITH table1 AS(
SELECT 111 ind, To_Date('3/13/2010','mm/dd/yyyy') date1, 'john' name FROM dual UNION ALL
SELECT 111 ind,To_Date('3/17/2010','mm/dd/yyyy') date1, 'smith' name FROM dual UNION all
SELECT 112 ind, To_Date('3/12/2010','mm/dd/yyyy') date1, 'john' name FROM dual UNION ALL
SELECT 112 ind,To_Date('3/18/2010','mm/dd/yyyy') date1, 'smith' name FROM dual
),
table2 AS(
SELECT 111 ind,To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 111 ind,To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT 111 ind,To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 111 ind,To_Date('3/20/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 111 ind,To_Date('3/25/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT 111 ind,To_Date('4/20/2010','mm/dd/yyyy') date1 FROM dual union ALL

SELECT 112 ind,To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 112 ind,To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT 112 ind,To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT 112 ind,To_Date('4/20/2010','mm/dd/yyyy') date1 FROM dual 

)
as you can see, each id in table1 has conrresponsdent dates in table2. so each id in table1 should match to
its correspondent id in table2. so the output for the data above should be
IND  DATE1     NAME
---- --------  -----
111  10-01-16  john
111  10-03-12  john
111  10-03-16  smith
111  10-03-20  smith
111  10-03-25  smith
111  10-04-20  smith

112  10-01-16  john
112  10-03-12  john
112  10-03-16  smith
112  10-04-20  smith
how can i change your query so it gives me the output above. as i mentioned above, each ind in table1 should match to the ind in table2.
will the current query take care of this scenario.
thanks

Edited by: elmasduro on May 1, 2010 12:09 PM
Aketi Jyuuzou
This morning of Japan.
I realized this using row_number solution :-)
This solution avoid cross join of my previous solution B-)
WITH table1 AS(
SELECT To_Date('3/13/2010','mm/dd/yyyy') date1, 'john' name FROM dual UNION ALL
SELECT To_Date('3/17/2010','mm/dd/yyyy') date1, 'smith' name FROM dual ),
table2 AS(
SELECT To_Date('1/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/12/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('3/16/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/20/2010','mm/dd/yyyy') date1 FROM dual UNION ALL
SELECT To_Date('3/25/2010','mm/dd/yyyy') date1 FROM dual UNION all
SELECT To_Date('4/20/2010','mm/dd/yyyy') date1 FROM dual)
select date1,name
from (select a.date1,b.name,
      Row_Number() over(partition by a.date1
                        order by b.date1) as rn
        from table2 a
        Join (select date1,name,
              max(date1) over() as maxData1
                from table1) b
          on a.date1 <= b.date1 or b.date1=b.maxData1)
where rn=1;

DATE1     NAME
--------  -----
10-01-16  john
10-03-12  john
10-03-16  smith
10-03-20  smith
10-03-25  smith
10-04-20  smith
Aketi Jyuuzou
how can i change your query so it gives me the output above. as i mentioned above, each ind in table1 should match >to the ind in table2.
will the current query take care of this scenario.
In that case, we have to use inner Join and partition by of OLAP :-)

create table table1(ind,date1,name) as
SELECT 111,To_Date('3/13/2010','mm/dd/yyyy'),'john' name FROM dual UNION ALL
SELECT 111,To_Date('3/17/2010','mm/dd/yyyy'),'smith' name FROM dual UNION all
SELECT 112,To_Date('3/12/2010','mm/dd/yyyy'),'john' name FROM dual UNION ALL
SELECT 112,To_Date('3/18/2010','mm/dd/yyyy'),'smith' name FROM dual;

create table table2(ind,date1) as
SELECT 111,To_Date('1/16/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 111,To_Date('3/12/2010','mm/dd/yyyy') FROM dual UNION all
SELECT 111,To_Date('3/16/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 111,To_Date('3/20/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 111,To_Date('3/25/2010','mm/dd/yyyy') FROM dual UNION all
SELECT 111,To_Date('4/20/2010','mm/dd/yyyy') FROM dual union ALL
SELECT 112,To_Date('1/16/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 112,To_Date('3/12/2010','mm/dd/yyyy') FROM dual UNION all
SELECT 112,To_Date('3/16/2010','mm/dd/yyyy') FROM dual UNION ALL
SELECT 112,To_Date('4/20/2010','mm/dd/yyyy') FROM dual;
select IND,date1,
(select
 case max(case when b.date1 >= a.date1 then 1 else 0 end)
 when 1 then max(b.name) Keep(Dense_Rank First order by case when b.date1 >= a.date1
                                                             then 0 else 1 end,b.date1)
 else max(b.name) Keep(Dense_Rank Last order by b.date1) end
   from table1 b
  where b.ind = a.ind) as NAME
from table2 a
order by IND,DATE1;

IND  DATE1     NAME
---  --------  -----
111  10-01-16  john
111  10-03-12  john
111  10-03-16  smith
111  10-03-20  smith
111  10-03-25  smith
111  10-04-20  smith
112  10-01-16  john
112  10-03-12  john
112  10-03-16  smith
112  10-04-20  smith
Aketi Jyuuzou
of course we can use below solution,too
select ind,date1,name
from (select a.ind,a.date1,b.name,
      Row_Number() over(partition by a.ind,a.date1
                        order by b.date1) as rn
        from table2 a
        Join (select date1,name,ind,
              max(date1) over(partition by ind) as maxData1
                from table1) b
          on a.ind = b.ind
         and (a.date1 <= b.date1 or b.date1=b.maxData1))
where rn=1
order by IND,DATE1;

IND  DATE1     NAME 
---  --------  -----
111  10-01-16  john 
111  10-03-12  john 
111  10-03-16  smith
111  10-03-20  smith
111  10-03-25  smith
111  10-04-20  smith
112  10-01-16  john 
112  10-03-12  john 
112  10-03-16  smith
112  10-04-20  smith
elmasduro
Aketi, thanks a lot for your help. i really appreciate it. keep up the good work. the last query you send me is exactly what i am looking for. thanks again
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 16 2018
Added on Dec 19 2017
#python
1 comment
1,504 views