Forum… | Dizwell Informatics

Dizwell Post author23 October, 2012 at 10:59 am

It’s a “very critical request”… for you. Not for anyone else here. None of us are paid to assist. So please keep the “this is very urgent and I’m waiting for a response” stuff out of it.

That said, I have some difficulty trying to understand what you are trying to do and your example seems on the complicated side. So forgive me if I’ve misunderstood, but I think all you are trying to do is 1) assign a SQL query to an environment variable and then 2) get SQL*Plus to execute the contents of that variable. Right? If so, can I simplify a little.

Basically, you want this:

code_list="select * from scott.emp"

…to do the actual assignment of the code to a variable.

Then you want this:

sqlplus -s fred/wilma <<EOF
$code_list;
exit
EOF

…to have your code run.

Now, if you want to run your more complex example:

code_list="set pagesize 0
set tab off
set feedback off
select decode(rownum, 1, ' ', 'union ') ||'select distinct '''||trial_name||''' as Schema_Name,
viewname from '||trial_name||'.CA_DATA a where view
name not in(select distinct object_name from dba_objects a
where a.owner='''||trial_name ||''' and (a.object_name like ''CV%''))'
from (
select distinct owner as trial_name from dba_objects
where owner like 'SCOTT%'
or OWNER like 'S%')"

…and when I run that like so:

sqlplus -s fred/wilma <<EOF
$code_list;
exit
EOF

…I get this output:

 select distinct 'SYSTEM' as Schema_Name,
viewname from SYSTEM.CA_DATA a where view
name not in(select distinct object_name from dba_objects a
where a.owner='SYSTEM' and (a.object_name like 'CV%'))

union select distinct 'SCOTT' as Schema_Name,
viewname from SCOTT.CA_DATA a where view
name not in(select distinct object_name from dba_objects a
where a.owner='SCOTT' and (a.object_name like 'CV%'))

union select distinct 'SYSMAN' as Schema_Name,
viewname from SYSMAN.CA_DATA a where view
name not in(select distinct object_name from dba_objects a
where a.owner='SYSMAN' and (a.object_name like 'CV%'))

union select distinct 'SH' as Schema_Name,
viewname from SH.CA_DATA a where view
name not in(select distinct object_name from dba_objects a
where a.owner='SH' and (a.object_name like 'CV%'))

union select distinct 'SYS' as Schema_Name,
viewname from SYS.CA_DATA a where view
name not in(select distinct object_name from dba_objects a
where a.owner='SYS' and (a.object_name like 'CV%'))

union select distinct 'SI_INFORMTN_SCHEMA' as Schema_Name,
viewname from SI_INFORMTN_SCHEMA.CA_DATA a where view
name not in(select distinct object_name from dba_objects a
where a.owner='SI_INFORMTN_SCHEMA' and (a.object_name like 'CV%'))

…which looks OK to me, though I can’t be sure it’s what you wanted.

I think my generic point is: don’t put back-ticks around the bit where you assign a value to the “code_list” variable, because that actually causes sqlplus to run and assign the results of your query to the variable, rather than merely the text of the query. In other words, if I did this:

code_list=`sqlplus -s fred/wilma <<EOF
select * from scott.emp
exit
EOF`

…then when you try to echo the value of $code_list, you’ll see this sort of thing:

[hjr@bethe ~]$ echo $code_list
EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected.

…meaning that the shell has actually tried to evaluate your back-ticked expression by running SQL*Plus and executing the query. I don’t *think* that’s what you want. I think you want to be able to assign a SQL statement to the variable and have SQL*Plus run whatever it finds in that variable, as though it were executing a SQL script, for example.

If I’ve misunderstood your request, then please feel free to clarify. But maybe make your example simpler, and please don’t expect answers to a schedule that’s significant to you.

Finally, though I don’t want to sound like a grumpy old man, and I perfectly understand why you’d ask your question on a post named ‘forum’, we actually don’t run a forum here any more and instead have a ‘Q & A’ page (which I’ve now made clear in the post text). So if you need to clarify your question, or post something new to better explain what you’re after, please use that Q&A page.

2 thoughts on “Forum…”