shell and sql
538299Feb 28 2008 — edited Mar 1 2008Hello
this sql script works fine:
COL "label" FORMAT a30
COL "req_value" FORMAT a30
COL "cur_value" FORMAT a30
SELECT params.label, params.req_value, cur_sys.cur_value, DECODE(UPPER(cur_value), UPPER(req_value), 'parameter OK', 'parameter NOT OK') status
FROM (
SELECT 'compatibility' label, '10.2.0.3.0' req_value FROM dual
UNION ALL
SELECT '_b_tree_bitmap_plans', 'FALSE' FROM dual
UNION ALL
SELECT 'cursor_sharing', 'FORCE' FROM dual
UNION ALL
SELECT 'db_block_size', '8192' FROM dual
) params
, (
SELECT 'compatibility' label, version cur_value FROM v$instance
UNION ALL
SELECT name, value FROM v$parameter WHERE name IN ('_b_tree_bitmap_plans','cursor_sharing','db_block_size')
)cur_sys
WHERE params.label = cur_sys.label;
If I put it inside a shell script:
#!/bin/sh
sqlplus -s SYS/PASS@ZORO AS SYSDBA <<EOF
COL "label" FORMAT a30
COL "req_value" FORMAT a30
COL "cur_value" FORMAT a30
SELECT params.label, params.req_value, cur_sys.cur_value, DECODE(UPPER(cur_value), UPPER(req_value), 'parameter OK', 'parameter NOT OK') status
FROM (
SELECT 'compatibility' label, '10.2.0.3.0' req_value FROM dual
UNION ALL
SELECT '_b_tree_bitmap_plans', 'FALSE' FROM dual
UNION ALL
SELECT 'cursor_sharing', 'FORCE' FROM dual
UNION ALL
SELECT 'db_block_size', '8192' FROM dual
) params
, (
SELECT 'compatibility' label, version cur_value FROM v$instance
UNION ALL
SELECT name, value FROM v$parameter WHERE name IN ('_b_tree_bitmap_plans','cursor_sharing','db_block_size')
)cur_sys
WHERE params.label = cur_sys.label;
EOF
I receive an error:
SELECT 'compatibility' label, version cur_value FROM v
*
ERROR at line 12:
ORA-00942: table or view does not exist
could you please help?
thanks