mysql and oracle issue using dblink [message #667443] |
Fri, 29 December 2017 04:17 |
|
nicolewells
Messages: 2 Registered: December 2017
|
Junior Member |
|
|
My issue is between mysql and oracle communication:
Step 1 (Creating a view in mysql for corresponding table)
E.g:
CREATE VIEW piwik_log_link_view ( idvis, cusk )
AS SELECT CAST( CONV( HEX( idvisitor ), 16, 10 ) AS CHAR(1000) )
, custom_var_k1
FROM piwik_log_link_visit_action
Step 2: (Firing a query from Oracle(Learn Oracle in Mindmajix) through dblink)
E.g:
SELECT plv."idvis"
, plv."cusk"
FROM "piwik_log_link_view"@mysql_piwik plv
Result:
Showing error: ORA-00904: "PLV"."cusk": invalid identifier.
If change step 2 to fetch only one column
I.E.
SELECT plv."idvis"
FROM "piwik_log_link_view"@mysql_piwik plv
Result :
Showing the first column.. .
Incase of putting * also in select query shows only one column.
Regards
Sarahjohn
|
|
|
|
Re: mysql and oracle issue using dblink [message #667446 is a reply to message #667443] |
Fri, 29 December 2017 07:54 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
To follow up on Littlefoot's comment, when dealing with Oracle you should never enclose object names in double-quotes, either when creating or referencing them. Using double-quotes over-rides Oracle's default method of dealing with case-sensitivity on object names and just makes life more difficult.
Carefully consider the following:
SQL> conn scott/tiger
Connected.
SQL> create table plv (idivs varchar2(10),
2 cusk varchar2(10)
3 )
4 ;
Table created.
SQL> insert into plv values ('aaa','bbb');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from plv;
IDIVS CUSK
---------- ----------
aaa bbb
1 row selected.
SQL> select "idivs",
2 "cusk"
3 from plv;
"cusk"
*
ERROR at line 2:
ORA-00904: "cusk": invalid identifier
SQL> select idivs,
2 cusk
3 from plv;
IDIVS CUSK
---------- ----------
aaa bbb
1 row selected.
SQL> select table_name,
2 column_name
3 from user_tab_cols
4 where table_name='PLV';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
PLV IDIVS
PLV CUSK
2 rows selected.
SQL> drop table plv purge;
Table dropped.
SQL> --
SQL> create table plv ("idivs" varchar2(10),
2 "cusk" varchar2(10)
3 )
4 ;
Table created.
SQL> insert into plv values ('aaa','bbb');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from plv;
idivs cusk
---------- ----------
aaa bbb
1 row selected.
SQL> select "idivs",
2 "cusk"
3 from plv;
idivs cusk
---------- ----------
aaa bbb
1 row selected.
SQL> select idivs,
2 cusk
3 from plv;
cusk
*
ERROR at line 2:
ORA-00904: "CUSK": invalid identifier
SQL> select table_name,
2 column_name
3 from user_tab_cols
4 where table_name='PLV';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
PLV idivs
PLV cusk
2 rows selected.
SQL> drop table plv purge;
Table dropped.
SQL> --
SQL> spo off
|
|
|