{"id":291,"date":"2012-12-24T15:53:00","date_gmt":"1970-01-01T00:00:00","guid":{"rendered":"http:\/\/www.yazilimmutfagi.com\/?p=291"},"modified":"2012-12-24T15:53:00","modified_gmt":"1970-01-01T00:00:00","slug":"plsql-ora-00942-table-or-view-does-not-exist","status":"publish","type":"post","link":"https:\/\/www.yazilimmutfagi.com\/index.php\/2012\/12\/24\/plsql-ora-00942-table-or-view-does-not-exist\/","title":{"rendered":"PL\/SQL: ORA-00942: table or view does not exist"},"content":{"rendered":"

\n\tPL\/SQL: ORA-00942: table or view does not exist<\/h3>\n
\nPL-SQL scripti içeren bir job, prosedüre \nolustururken asagidaki hatayi alabiliiz.\nORA-12012: error on auto execute of job 222\nORA-06550: line 8, column 18:\nPL\/SQL: ORA-00942: table or view does not exist\n<\/pre>\n
\n\tProcedure içerisinde kullandigimiz tablonun oldugundan eminiz ve gerekli yetkiler saglayan role de bulunmakta. Buna ragmen yukardaki hata aliniyor olabilir.<\/p>\n
\n\tHatanin nedeni , procedure veya job olustururken farkli bir semanin tablosunu kullanirken DML hakki direk verilmelidir. Bir role ile verilen haklar yukardaki gibi bir sorun olmasina neden olacaktir. Asagidaki gibi bir study case ile sorunu ve çözümü görebiliriz.<\/p>\n
\n\t**user_a ile login olalim;<\/p>\n
\ncreate table user_a.test_tablo \n(\nid number,\nname varchar2(50)\n);\nTable created.\n\ninsert into user_a.TEST_TABLO  values (1,'sss')\n1 row inserted.\n\ninsert into user_a.TEST_TABLO  values (2,'qwerty');\n1 row inserted.\n\ninsert into user_a.TEST_TABLO  values (3,'aaaaa');\n1 row inserted.\n\ncreate role test_role;\nRole created.\n\ngrant select on user_a.test_tablo to test_role; \nGrant succeeded.\n \ngrant test_role to user_b;\nGrant succeeded.\n<\/pre>\n
\n\t**Simdi user_b ile login olalim;<\/p>\n
\nselect * from user_a.test_tablo; --sorunsuz , çalisti\n declare\n   v_name varchar2(50);\n   begin\n   select t.name into v_name from user_a.test_tablo t where t.id=3;\n   dbms_output.put_line('3 nolu kayit  :'||v_name);\n   end;\n   \/ \n   --PL\/SQL procedure successfully completed.\n<\/pre>\n
\n\tBuraya kadar hiçbir sorun olmadan çalisti. verdigimiz role ile gerekli yetkiler verildiginden sorun olmadi. DML islemini PLSQL içinde veya guery ile kullanirken sorun olmuyor.<\/p>\n
\ncreate or replace procedure user_b.test_proc as\n    v_name varchar2(50);\n    begin\n    select t.name into v_name from user_a.test_tablo t where t.id=3;\n    dbms_output.put_line('3 nolu kayit  :'||v_name);\n    end test_proc;  \n   \/ \n \nWarning: compiled but with compilation errors\n\n[Warning] ORA-24344: derleme hatasi ile basardi\n4\/45    PL\/SQL: ORA-00942: table or view does not exist\n4\/5     PL\/SQL: SQL Statement ignored\n (14: 0): Warning: compiled but with compilation errors\n<\/pre>\n
\n\tGörüldügü gibi procedure içinde daha önce çalistirdigimiz PLSQL olmasina ragmen sorun verdi.<\/p>\n
\n\tSimdi grantlari role kullanmadan, direk verelim.<\/p>\n
\ngrant select on test_tablo to user_b;\nGrant complete.\n\ncreate or replace procedure user_b.test_proc as\n    v_name varchar2(50);\n    begin\n    select t.name into v_name from user_a.test_tablo t where t.id=3;\n    dbms_output.put_line('3 nolu kayit  :'||v_name);\n    end test_proc;  \n   \/ \n\nProcedure created.\n<\/pre>\n
\n\tUser_b, veritabaninda dba role sahip olsa bile user_a nin tablosnu bir procedure içerinsde kullnmasi için yine direk grant’a ihtiyaç olur.<\/p>\n","protected":false},"excerpt":{"rendered":"
PL-SQL scripti içeren bir job, prosedüre olustururken "ORA-12012: error on auto execute of job 222 ORA-06550: line 8, column 18: PL\/SQL: ORA-00942: table or view does not exist"hatasi alabiliiz.<\/p>\n","protected":false},"author":558,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[423],"tags":[4094,3862,3915,4686],"_links":{"self":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/291"}],"collection":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/users\/558"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/comments?post=291"}],"version-history":[{"count":0,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/291\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/media?parent=291"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/categories?post=291"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/tags?post=291"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}