{"id":613,"date":"2012-02-07T23:45:50","date_gmt":"2012-02-07T23:45:50","guid":{"rendered":"http:\/\/tech.avant.net\/q\/?p=613"},"modified":"2012-12-25T22:38:37","modified_gmt":"2012-12-25T22:38:37","slug":"sqlplus-utility-scripts","status":"publish","type":"post","link":"https:\/\/tech.avant.net\/q\/sqlplus-utility-scripts\/","title":{"rendered":"sqlplus, utility scripts"},"content":{"rendered":"

I would like to use sqlplus in development projects, and I would like it to be easy to use.<\/p>\n

First off, I want command-history and tab-completion. The easiest approach is to use rlwrap<\/a> which uses the GNU readline library as a wrapper to command-line programs such as sqlplus.<\/p>\n

If you install rlwrap, you can then set an alias in your .bashrc (or similar)<\/p>\n

\r\nalias sqlplus='rlwrap sqlplus'\r\n<\/pre>\n
That’s it, sqlplus will now have GNU readline compatible command-line history and editing! If you wish to access sqlplus directly without rlwrap (for loading scripts, etc), simply unalias, i.e.,<\/p>\n
\r\n$ \\sqlplus\r\n<\/pre>\n
I would also like intuitive, terse, and easy-to-remember commands to perform basic tasks like inspecting tables and schemas, viewing and editing packages of stored procedures, etc.<\/p>\n
I started a github repository [https:\/\/github.com\/timwarnock\/sqlpath<\/a>] of utility scripts to make this easier.<\/p>\n
Set your $SQLPATH environment variable to the directory containing these scripts, which allow for the following operations.<\/p>\n
\r\nfoo@DB> @show databases<\/strong>\r\n\r\nSCHEMA\r\n----------------\r\nFOO\r\nSPAM\r\n\r\n2 rows selected.\r\n<\/pre>\n
Change to the SPAM schema,<\/p>\n
\r\nfoo@DB> \r\nfoo@DB> @use spam<\/strong>\r\nspam@DB> \r\n<\/pre>\n
List all tables with ‘abc’ anywhere in the table name,<\/p>\n
\r\nspam@DB> @show tables abc<\/strong>\r\n\r\nTABLE_NAME\r\n----------------\r\nABC_USERS\r\nABC_GROUPS\r\nFOO_ABC_MAP\r\n\r\n3 rows selected.\r\n<\/pre>\n
List all packages with ‘abc’ anywhere in the package name,<\/p>\n
\r\nspam@DB> @show packages abc<\/strong>\r\n\r\nNAME\r\n----------------\r\nPROCS_ABC\r\nABC_DATE\r\n\r\n2 rows selected.\r\n<\/pre>\n
View the source code of a package of stored procedures,<\/p>\n
\r\nspam@DB> @show code abc_date<\/strong>\r\n\r\nTEXT\r\n---------------------------------------------------------------------------------------\r\nPACKAGE             \"ABC_DATE\" AS\r\n  FUNCTION DATE_ID(IN_DATE DATE) RETURN NUMBER RESULT_CACHE;\r\nEND;\r\nPACKAGE BODY  \"ABC_DATE\" AS\r\n\r\n  FUNCTION DATE_ID(IN_DATE DATE) RETURN NUMBER RESULT_CACHE\r\n  IS\r\n    ID NUMBER;\r\n  BEGIN\r\n    SELECT D.ID INTO ID FROM DATES_DIM D WHERE SQL_DATE = TRUNC(IN_DATE);\r\n    RETURN ID;\r\n  END;\r\nEND;\r\n\r\n13 rows selected.\r\n<\/pre>\n
View the explain plan output (also consider ‘set autotrace on’)<\/p>\n
\r\nspam@DB> explain plan for SELECT D.ID FROM DATES_DIM D WHERE SQL_DATE = '07-feb-12';\r\n\r\nExplained.\r\n\r\nspam@DB> @explain<\/strong>\r\n\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------------------------------------\r\nPlan hash value: 1171817064\r\n\r\n------------------------------------------------------------------------------------------------\r\n| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |\r\n------------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT            |                  |     1 |    13 |     2   (0)| 00:00:01 |\r\n|   1 |  TABLE ACCESS BY INDEX ROWID| DATES_DIM        |     1 |    13 |     2   (0)| 00:00:01 |\r\n|*  2 |   INDEX UNIQUE SCAN         | DATESDIMSQLDTUNQ |     1 |       |     1   (0)| 00:00:01 |\r\n------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n   2 - access(\"SQL_DATE\"='07-feb-12')\r\n<\/pre>\n
Edit a package in vim and automatically recompile<\/p>\n
\r\nspam@DB> @editcode abc_date<\/strong>\r\n... \r\n... opens ABC_DATE.sql in vim\r\n... \r\n\r\nPackage created.\r\n\r\nPackage body created.\r\n\r\nspam@DB> \r\n<\/pre>\n
Please see the github repository [https:\/\/github.com\/timwarnock\/sqlpath<\/a>] for periodic updates.<\/p>\n","protected":false},"excerpt":{"rendered":"
I would like to use sqlplus in development projects, and I would like it to be easy to use. First off, I want command-history and tab-completion. The easiest approach is to use rlwrap which uses the GNU readline library as a wrapper to command-line programs such as sqlplus. If you install rlwrap, you can then […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[18,14],"tags":[],"_links":{"self":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/613"}],"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=613"}],"version-history":[{"count":8,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/613\/revisions"}],"predecessor-version":[{"id":694,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/613\/revisions\/694"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=613"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=613"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=613"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}