I develop a lot of shell scripts. I would not define myself an old dinosaur that keeps avoiding python or other modern languages. It is just that most of my scripts automate OS commands that I would normally run interactively in an interactive shell… tar, cp, expdp, rman, dgmgrl, etc… and of course,\u00a0some SQL*Plus executions.<\/p>\n
For database calls, the shell is\u00a0not\u00a0appropriate: no drivers, no connection, no statement, no resultset… that’s why I need to make SQL*Plus executions (with some hacks to make them work correctly), and that’s also why I normally\u00a0use python or perl for data-related tasks.<\/p>\n
Using SQL*Plus in shell scripts<\/strong><\/p>\n For SQL*Plus executions within a shell scripts there are some hacks, as I have said, that allow to get the data correctly.<\/p>\n As example, let’s use this table (that you might have found in my recent posts):<\/p>\n In order to get, as example, the result of this query:<\/p>\n and assign the values to some variables (in a shell loop), it is common to do something like this:<\/p>\n As you can see, there are several hacks:<\/p>\n It is not rock solid (unexpected data might compromise the results) and there are dependencies (sqlplus binary, credentials, etc.). But for many simple tasks, that’s more than enough.<\/p>\n Here’s the output:<\/p>\n <\/p>\n Using ORDS instead<\/strong><\/p>\n Recently I have come across a situation where I had no Oracle binaries but needed to get some data from a table. That is often a situation where I use python or perl, but even in these cases, I\u00a0need compatible software and drivers!<\/p>\n So I used ORDS instead (that by chance, was already configured for the databases I wanted to query), and used curl and jq to get the data in the shell script.<\/p>\n First, I have defined the service in the database:<\/p>\n At this point, a direct call gives this:<\/p>\n How to parse the data?<\/p>\n jq<\/strong> is a command-line JSON processor that can be used in a pipeline.<\/p>\n I can get the items:<\/p>\n And I can produce a csv output:<\/p>\n But the best, is the shell formatter, that returns strings properly escaped for usage in shell commands:<\/p>\n At this point, the call to eval is a natural step \ud83d\ude42<\/p>\n The output:<\/p>\n \ud83d\ude09<\/p>\n —<\/p>\n Ludovico<\/p>\n","protected":false},"excerpt":{"rendered":" I develop a lot of shell scripts. I would not define myself an old dinosaur that keeps avoiding python or other modern languages. It is just that most of my scripts automate OS commands that I would normally run interactively … Continue reading SQL> desc OH_GOLDEN_IMAGES\r\n Name Null? Type\r\n ----------------------------------------- -------- ----------------------------\r\n NAME NOT NULL VARCHAR2(50)\r\n OH_TYPE VARCHAR2(10)\r\n VERSION VARCHAR2(10)\r\n FULLPATH VARCHAR2(200)\r\n CREATED TIMESTAMP(6)\r\n DESCRIPTION VARCHAR2(2000)\r\n\r\nSQL> insert into OH_GOLDEN_IMAGES values ('18_3_0_cerndb1', 'RDBMS', '18.3.0', '\/test\/path\/18_3_0_cerndb1.zip', sysdate-10, 'First version 18.3.0');\r\n\r\n1 row created.\r\n\r\nSQL> insert into OH_GOLDEN_IMAGES values ('18_3_0_cerndb2', 'RDBMS', '18.3.0', '\/test\/path\/18_3_0_cerndb2.zip', sysdate-1, '18_3_0_cerndb1 + Patch XXX');\r\n\r\n1 row created.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.<\/pre>\nSELECT name, version, fullpath, TO_CHAR(created,'YYYY-MM-DD') as created\r\nFROM oh_golden_images WHERE oh_type='RDBMS' order by created<\/pre>\n
REPO_CREDENTIALS='scott\/tiger@orcl'\r\nRESULT=`$ORACLE_HOME\/bin\/sqlplus -s $REPO_CREDENTIALS 2>&1 <<EOF | grep \";\"\r\n set line 200 pages 1000\r\n set echo off feedback off heading off\r\n alter session set nls_timestamp_format='YYYY-MM-DD';\r\n SELECT name || ';' ||version || ';' || fullpath || ';' || created\r\n FROM oh_golden_images\r\n WHERE oh_type='RDBMS'\r\n order by created;\r\n exit;\r\nEOF\r\n`\r\n\r\nfor line in $RESULT ; do\r\n L_GI_Name=`echo $line | awk -F\\; '{print $1}'`\r\n L_GI_Version=`echo $line | awk -F\\; '{print $2}'`\r\n L_GI_Path=`echo $line | awk -F\\; '{print $3}'`\r\n L_GI_Date=`echo $line | awk -F\\; '{print $4}'`\r\n echo \"doing something with variables $L_GI_Name $L_GI_Date $L_GI_Path $L_GI_Version\"\r\ndone\r\n<\/pre>\n\n
$ sh sqlplus_test.sh\r\ndoing something with values 18_3_0_cerndb1 2018-08-19 \/test\/path\/18_3_0_cerndb1.zip 18.3.0\r\ndoing something with values 18_3_0_cerndb2 2018-08-28 \/test\/path\/18_3_0_cerndb2.zip 18.3.0\r\n<\/pre>\n
BEGIN\r\n ORDS.DEFINE_SERVICE(\r\n p_module_name => 'ohctl',\r\n p_base_path => 'ohctl\/',\r\n p_pattern => 'list\/',\r\n p_method => 'GET',\r\n p_source_type => ORDS.source_type_collection_feed,\r\n p_source => 'SELECT name, version, fullpath, TO_CHAR(created,''YYYY-MM-DD'') as created FROM oh_golden_images WHERE oh_type=''RDBMS'' order by created',\r\n p_items_per_page => 0);\r\n\tCOMMIT;\r\nEND;\r\n\/<\/pre>\n
$ curl $rest_ep\/ohctl\/list\/\r\n{\"items\":[{\"name\":\"18_3_0_cerndb1\",\"version\":\"18.3.0\",\"fullpath\":\"\/test\/path\/18_3_0_cerndb1.zip\",\"created\":\"2018-08-19\"},{\"name\":\"18_3_0_cerndb2\",\"version\":\"18.3.0\",\"fullpath\":\"\/test\/path\/18_3_0_cerndb2.zip\",\"created\":\"2018-08-28\"}],\"hasMore\":false,\"limit\":0,\"offset\":0,\"count\":2,\"links\":[{\"rel\":\"self\",\"href\":\"https:\/\/rest_endpoint\/ohctl\/list\/\"},{\"rel\":\"describedby\",\"href\":\"https:\/\/rest_endpoint\/metadata-catalog\/ohctl\/list\/\"}]}\r\n<\/pre>\n$ curl -s $rest_ep\/ohctl\/list\/ | jq --raw-output '.items[]'\r\n{\r\n \"created\": \"2018-08-19\",\r\n \"fullpath\": \"\/test\/path\/18_3_0_cerndb1.zip\",\r\n \"version\": \"18.3.0\",\r\n \"name\": \"18_3_0_cerndb1\"\r\n}\r\n{\r\n \"created\": \"2018-08-28\",\r\n \"fullpath\": \"\/test\/path\/18_3_0_cerndb2.zip\",\r\n \"version\": \"18.3.0\",\r\n \"name\": \"18_3_0_cerndb2\"\r\n}<\/pre>\n$ curl -s $rest_ep\/ohctl\/list\/ | jq --raw-output '.items[] | @csv \"\\([.created]),\\([.fullpath]),\\([.version]),\\([.name])\"'\r\n\"2018-08-19\",\"\/test\/path\/18_3_0_cerndb1.zip\",\"18.3.0\",\"18_3_0_cerndb1\"\r\n\"2018-08-28\",\"\/test\/path\/18_3_0_cerndb2.zip\",\"18.3.0\",\"18_3_0_cerndb2\"\r\n<\/pre>\n
$ curl -s $rest_ep\/ohctl\/list\/ | jq --raw-output '.items[] | @sh \"L_GI_Date=\\([.created]); L_GI_Path=\\([.fullpath]); L_GI_Version=\\([.version]); L_GI_Name=\\([.name])\"'\r\nL_GI_Date='2018-08-19'; L_GI_Path='\/test\/path\/18_3_0_cerndb1.zip'; L_GI_Version='18.3.0'; L_GI_Name='18_3_0_cerndb1'\r\nL_GI_Date='2018-08-28'; L_GI_Path='\/test\/path\/18_3_0_cerndb2.zip'; L_GI_Version='18.3.0'; L_GI_Name='18_3_0_cerndb2'\r\n<\/pre>\n
IFS=\"\r\n\"\r\nfor line in `curl -s $rest_ep\/ohctl\/list\/ | jq --raw-output '.items[] | @sh \"L_GI_Date=\\([.created]); L_GI_Path=\\([.fullpath]); L_GI_Version=\\([.version]); L_GI_Name=\\([.name])\"'` ; do\r\n eval $line\r\n echo \"doing something with values $L_GI_Name $L_GI_Date $L_GI_Path $L_GI_Version\"\r\ndone\r\n<\/pre>\n
$ sh ords_test.sh\r\ndoing something with values 18_3_0_cerndb1 2018-08-19 \/test\/path\/18_3_0_cerndb1.zip 18.3.0\r\ndoing something with values 18_3_0_cerndb2 2018-08-28 \/test\/path\/18_3_0_cerndb2.zip 18.3.0\r\n<\/pre>\n