{"id":580,"date":"2012-01-11T21:35:00","date_gmt":"2012-01-11T21:35:00","guid":{"rendered":"http:\/\/tech.avant.net\/q\/?p=580"},"modified":"2019-06-01T04:55:32","modified_gmt":"2019-06-01T04:55:32","slug":"sqlplus-edit-stored-procedures","status":"publish","type":"post","link":"https:\/\/tech.avant.net\/q\/sqlplus-edit-stored-procedures\/","title":{"rendered":"sqlplus, edit stored procedures"},"content":{"rendered":"<p>Previously, we discussed <a href=\"\/q\/sqlplus-view-stored-procedures\/\">viewing stored procedures<\/a> in sqlplus.<\/p>\n<p>It came to my attention in an <a href=\"http:\/\/asktom.oracle.com\/pls\/asktom\/f?p=100:11:0::::P11_QUESTION_ID:2517568023529\">Ask Tom<\/a> article of a very handy way to edit a package in sqlplus.<\/p>\n<p>Set your $SQLPATH environment variable to wherever you wish to store useful scripts, including a login.sql &#8212; make sure to set an editor in login.sql, e.g.,<\/p>\n<pre>define _editor=vim\n<\/pre>\n<p>In the Ask Tom example, simply create a getcode.sql and save somewhere in your $SQLPATH &#8212; for reference,<\/p>\n<pre class=\"sh_oracle\">set feedback off\nset heading off\nset termout off\nset linesize 1000\nset trimspool on\nset verify off\nspool &amp;1..sql\nprompt set define off\nselect decode( type||'-'||to_char(line,'fm99999'),\n               'PACKAGE BODY-1', '\/'||chr(10),\n                null) ||\n       decode(line,1,'create or replace ', '' ) ||\n       text text\n  from user_source\n where name = upper('&amp;&amp;1')\n order by type, line;\nprompt \/\nprompt set define on\nspool off\nset feedback on\nset heading on\nset termout on\nset linesize 100\n<\/pre>\n<p>Then, in sqlplus, you can edit an existing procedure\/package\/function and update it in the database, e.g.,<\/p>\n<pre class=\"sh_oracle\">foo_owner@FOO&gt; @getcode PROCS_DATE\nfoo_owner@FOO&gt; edit PROCS_DATE\n\nfoo_owner@FOO&gt; @PROCS_DATE\n\nPackage created.\n\nPackage body created.\n\nfoo_owner@FOO&gt; \n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Previously, we discussed viewing stored procedures in sqlplus. It came to my attention in an Ask Tom article of a very handy way to edit a package in sqlplus. Set your $SQLPATH environment variable to wherever you wish to store useful scripts, including a login.sql &#8212; make sure to set an editor in login.sql, e.g., [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[18],"tags":[],"_links":{"self":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/580"}],"collection":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/comments?post=580"}],"version-history":[{"count":5,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/580\/revisions"}],"predecessor-version":[{"id":1002,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/580\/revisions\/1002"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=580"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=580"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=580"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}