My colleague needed to use a function in the database in a query, but found that the function was not defined\/reachable in his schema. So the solution is to ask for the function to be granted to you, or to create the function in your schema, or…<\/p>\n
…use the new WITH enhancement available from Oracle 12c.<\/p>\n
Here is a very simple example, that squares a number and returns it. The function is used in the query. How nice :-p<\/p>\n
\r\nwith\r\n function s(x in integer) return integer is\r\n begin\r\n return x*x;\r\n end;\r\n \r\n q as (select 1 n from dual union all select 2 from dual)\r\n \r\n select q.n,s(q.n) from q\r\n\/\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"My colleague needed to use a function in the database in a query, but found that the function was not defined\/reachable in his schema. So the solution is to ask for the function to be granted to you, or to create the function in your schema, or… …use the new WITH enhancement available from Oracle […]<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[51,132,120,81],"class_list":["post-1880","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-database","tag-oracle","tag-sql","tag-tips","category-1-id","post-seq-1","post-parity-odd","meta-position-corners","fix"],"_links":{"self":[{"href":"https:\/\/www.switchbl8.nl\/blog\/wp-json\/wp\/v2\/posts\/1880","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.switchbl8.nl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.switchbl8.nl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.switchbl8.nl\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.switchbl8.nl\/blog\/wp-json\/wp\/v2\/comments?post=1880"}],"version-history":[{"count":2,"href":"https:\/\/www.switchbl8.nl\/blog\/wp-json\/wp\/v2\/posts\/1880\/revisions"}],"predecessor-version":[{"id":1882,"href":"https:\/\/www.switchbl8.nl\/blog\/wp-json\/wp\/v2\/posts\/1880\/revisions\/1882"}],"wp:attachment":[{"href":"https:\/\/www.switchbl8.nl\/blog\/wp-json\/wp\/v2\/media?parent=1880"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.switchbl8.nl\/blog\/wp-json\/wp\/v2\/categories?post=1880"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.switchbl8.nl\/blog\/wp-json\/wp\/v2\/tags?post=1880"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}