Call and execute query from tables

Shell Programming and Scripting
1

Hi..

We have a table DB_QUERIES, in which sql queries are stored.

SQL> desc DB_QUERIES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 QUERY_ID                                  NOT NULL NUMBER(10)
 QUERY_DETAIL                                       CLOB
 QUERY_TYPE                                         VARCHAR2(20)

I need to run, queries stored in QUERY_DETAIL column of this table which has "declare statement.. "
somehow it is not working..

I'm trying below.

#!/bin/ksh

sqlplus -s $ORACLE_USER/$ORACLE_PASS@$ORACLE_SID << EOF
set head off
set long 1800
spool query1.sql
select QUERY_DETAIL from DB_QUERIES where QUERY_ID = '001';
spool off
EOF


sqlplus -s $ORACLE_USER/$ORACLE_PASS@$ORACLE_SID
set serveroutput on
set long 1800
SET SQLTERMINATOR ON
@query1.sql;
exit;

It works when I use simple query like "select sysdate from dual", but it is not working for big queries which contains declare and procedures.

Please let me know whats going wrong.
If you could give me sample query that would also help..
Thanks in advance..

2

Please let us know what you're doing.

Show us what's in query.sql . Any error msgs when running above?

3

You limit the number of characters read from QUERY_DETAIL to 1800. Are those big queries longer? Another problem may be that the output is wrapped. This can be avoided by setting longchunksize to the same value as long .