{"id":2016,"date":"2018-12-04T22:28:38","date_gmt":"2018-12-04T21:28:38","guid":{"rendered":"http:\/\/van-maanen.com\/?p=2016"},"modified":"2018-12-04T22:28:38","modified_gmt":"2018-12-04T21:28:38","slug":"variables-in-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=2016","title":{"rendered":"Variables in Oracle"},"content":{"rendered":"<p>The question is simple: we have an Oracle query and we want to use one search criterium as a parameter. Let us take a simple example. We want to use a parameter in the SQL statement: select * from doel where teller = parameter. So we can write the SQL only once and we can apply the SQL with different parameter values.<br \/>\nFor this example, we use a table doel.<\/p>\n<pre>\ndrop table doel;\n\nCREATE TABLE \"DOEL\" \n   (\t\"TELLER\" NUMBER, \n\t\"NAAM\" VARCHAR2(20 BYTE)\n   );\nInsert into DOEL (TELLER,NAAM) values ('1','Piet');\nInsert into DOEL (TELLER,NAAM) values ('2','Jan');\nInsert into DOEL (TELLER,NAAM) values ('3','Klaas');\ncommit;\n<\/pre>\n<p>Solution 1. We write a SQL statement with a bind variable. We define the bind variable first and we then use that bind variable in a SQL statement.<\/p>\n<pre>\nvar zoek number;\nexec :zoek := 2;\nselect * from doel where teller =:zoek;\n<\/pre>\n<p>Solution 2. We create a situation where we use a substitution variable.<\/p>\n<pre>\naccept p_zoek prompt \"Please enter teller number: \" default 10;\n\nselect * from doel where teller = &p_zoek\n\/\n<\/pre>\n<p>Solution 3. We explicitly define a substitution variable. We use that substitution variable in a subsequent SQL statement.<\/p>\n<pre>\nDEFINE P_zoek = 2;\n\nSELECT * \nFROM doel \nWHERE teller = &P_zoek;\n<\/pre>\n<p>Solution 4. We write a PL\/SQL procedure. This PL\/SQL procedure has a parameter. This procedure can be executed with exec zoek(2);.<\/p>\n<pre>\nCREATE OR REPLACE PROCEDURE ZOEK \n  (P_ZOEK IN NUMBER) AS \n  select_s  VARCHAR(225);\nBEGIN\n  select_s := ' select * from doel where teller = '||P_ZOEK;\n  EXECUTE IMMEDIATE select_s;\nEND ZOEK;\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>The question is simple: we have an Oracle query and we want to use one search criterium as a parameter. Let us take a simple example. We want to use a parameter in the SQL statement: select * from doel where teller = parameter. So we can write the SQL only once and we can [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-2016","post","type-post","status-publish","format-standard","hentry","category-nice-to-know"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/2016","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2016"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/2016\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2016"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2016"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2016"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}