OraFAQ Forum: SQL & PL/SQL » mysql and oracle issue using dblink

Home » SQL & PL/SQL » SQL & PL/SQL » mysql and oracle issue using dblink

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: mysql and oracle issue using dblink [message #667444 is a reply to message #667443]

Fri, 29 December 2017 05:05

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Try to remove double quotes, everywhere; then run SELECT again.

Report message to a moderator

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

Report message to a moderator

Previous Topic:	last date of the year
Next Topic:	regexp_substr

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 00:14:23 CDT 2024