Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

shell and sql

538299Feb 28 2008 — edited Mar 1 2008
Hello

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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 29 2008
Added on Feb 28 2008
9 comments
2,254 views