{"action":"created","issue":{"url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/78","repository_url":"https://api.github.com/repos/laurenz/oracle_fdw","labels_url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/78/labels{/name}","comments_url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/78/comments","events_url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/78/events","html_url":"https://github.com/laurenz/oracle_fdw/issues/78","id":146089043,"number":78,"title":"Access to tables with other schemas and dblinks","user":{"login":"lorenati","id":18171687,"avatar_url":"https://avatars.githubusercontent.com/u/18171687?v=3","gravatar_id":"","url":"https://api.github.com/users/lorenati","html_url":"https://github.com/lorenati","followers_url":"https://api.github.com/users/lorenati/followers","following_url":"https://api.github.com/users/lorenati/following{/other_user}","gists_url":"https://api.github.com/users/lorenati/gists{/gist_id}","starred_url":"https://api.github.com/users/lorenati/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lorenati/subscriptions","organizations_url":"https://api.github.com/users/lorenati/orgs","repos_url":"https://api.github.com/users/lorenati/repos","events_url":"https://api.github.com/users/lorenati/events{/privacy}","received_events_url":"https://api.github.com/users/lorenati/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/laurenz/oracle_fdw/labels/problem","name":"problem","color":"fbca04"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2016-04-05T19:36:49Z","updated_at":"2016-04-06T16:06:13Z","closed_at":null,"body":"In oracle with the user myuser and the schema myschema I can select this view:\r\n\r\nselect * from anotherschema.a_view@adblink\r\n\r\nCan I do the same thing in oracle_fdw?  so far I have this:\r\n\r\nCREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'MyServer');\r\n\r\nCREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'myuser', password 'mypassword');\r\n\r\nSET search_path = ds_oracle;\r\n\r\nCREATE FOREIGN TABLE a_view (\r\n\t\taid bigint,\r\n\t\tacolumn double precision\r\n       ) SERVER oradb OPTIONS ( schema 'anotherschema', table 'a_view@adblink');\r\n\r\nBut I get this error when I perform a select of that table:\r\n\r\nERROR:  Oracle table \"anotherschema\".\"a_view@adblink\" for foreign table \"a_view\" does not exist or does not allow read access\r\nDETAIL:  ORA-00942: table or view does not exist\r\nHINT:  Oracle table names are case sensitive (normally all uppercase).\r\n********** Error **********\r\n\r\nERROR:  Oracle table \"anotherschema\".\"a_view@adblink\" for foreign table \"a_view\" does not exist or does not allow read access\r\nSQL state: HV00R\r\nDetail: ORA-00942: table or view does not exist\r\nHint: Oracle table names are case sensitive (normally all uppercase).\r\n\r\nAm I missing something? I've tried the names with uppercase also"},"comment":{"url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/comments/206443575","html_url":"https://github.com/laurenz/oracle_fdw/issues/78#issuecomment-206443575","issue_url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/78","id":206443575,"user":{"login":"lorenati","id":18171687,"avatar_url":"https://avatars.githubusercontent.com/u/18171687?v=3","gravatar_id":"","url":"https://api.github.com/users/lorenati","html_url":"https://github.com/lorenati","followers_url":"https://api.github.com/users/lorenati/followers","following_url":"https://api.github.com/users/lorenati/following{/other_user}","gists_url":"https://api.github.com/users/lorenati/gists{/gist_id}","starred_url":"https://api.github.com/users/lorenati/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lorenati/subscriptions","organizations_url":"https://api.github.com/users/lorenati/orgs","repos_url":"https://api.github.com/users/lorenati/repos","events_url":"https://api.github.com/users/lorenati/events{/privacy}","received_events_url":"https://api.github.com/users/lorenati/received_events","type":"User","site_admin":false},"created_at":"2016-04-06T16:06:13Z","updated_at":"2016-04-06T16:06:13Z","body":"I made a view (a_view_test) containing a select * from a_view@adblink; I get this error:\r\n\r\nERROR:  error describing remote table: OCIStmtExecute failed to describe table\r\nDETAIL:  ORA-02070: database ADATABASE does not support set transaction in this context\r\nORA-02063: line preceding adblink\r\n********** Error **********"}}