Home » SQL & PL/SQL » SQL & PL/SQL » mysql and oracle issue using dblink
mysql and oracle issue using dblink [message #667443] Fri, 29 December 2017 04:17 Go to next message
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 #667444 is a reply to message #667443] Fri, 29 December 2017 05:05 Go to previous messageGo to next message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try to remove double quotes, everywhere; then run SELECT again.
Re: mysql and oracle issue using dblink [message #667446 is a reply to message #667443] Fri, 29 December 2017 07:54 Go to previous message
EdStevens
Messages: 976
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
Previous Topic: last date of the year
Next Topic: regexp_substr
Goto Forum:
  


Current Time: Mon Oct 15 20:13:40 CDT 2018