Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Call and execute query from tables

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Call and execute query from tables
# 1  
Old 02-01-2015
DB Call and execute query from tables
Hi..

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

Code:
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.

Code:
#!/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  
Old 02-02-2015
Please let us know what you're doing.


Show us what's in query.sql. Any error msgs when running above?
# 3  
Old 02-02-2015
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.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script automation using cron which query's MySQL Tables

What I have: I have a input.sh (script which basically connect to mysql-db and query's multiple tables to write back the output to output1.out file in a directory) note: I need to pass an integer (unique_id = anything b/w 1- 1000) next to the script everytime I run the script which generates... (3 Replies)
Discussion started by: kkpand
3 Replies

2. Shell Programming and Scripting

Shell script to execute sql query.

Hi Experts, Need your support. Not able to use sql query alias in shell script. Could you please help me in finding right way to use alias with sql query in shell script. Below is the code i am using. #!/bin/bash sqlplus -s abc/abc@abc << EOF> bcd.csv set trimspool on select zone_id... (4 Replies)
Discussion started by: as7951
4 Replies

3. Shell Programming and Scripting

Want to use function and execute the below query in script.

#!/bin/bash function getDeliveredDispatches(firstDateTime, lastDateTime, limit) { var cursor = db.dispatches.find( {$and: }} ]}, {"deliveryGroupId" : 1, "batchId": 1, "statusHistory.code" : 1} ); var wrongDispatchesIds = ; print("Number of dispatches selected based on filter = " +... (2 Replies)
Discussion started by: neel2462
2 Replies

4. Shell Programming and Scripting

Which shell script will call if i execute sh (without filename)?

Hi Friends, The below shell script is written by third party to create B2k_session_id.iam trying to execute this script.When i execute below script it is calling some other scripts.How to find which scripts is calling? . `execom commfunc.com` echo " $PRESENTATION_MODE " if then echo... (1 Reply)
Discussion started by: vadlamudy
1 Replies

5. Shell Programming and Scripting

need to store query output fields in variables edit them and update the same in tables.

Hi , I have a query like select err_qty,drop_qty,unbld_qty,orig_qty from usage_data; I need to store the values of these fetched fields in variables, Need to edit them and update the new values into the table. Can anyone please help me in writing this piece of code:( (1 Reply)
Discussion started by: Rajesh Putnala
1 Replies

6. Shell Programming and Scripting

Shell Script to execute Oracle query taking input from a file to form query

Hi, I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database. For instance: USER CITY --------- ---------- A CITY_A B CITY_B C ... (2 Replies)
Discussion started by: DevendraG
2 Replies

7. Programming

Query to join two tables in SQL

Hi, I have 2 tables. Table1 name map_code data_code id aaa 2732C 3333B 10 bbb 1223F 2545v 11 ccc 12 Table2 name map_code data_code id text_description aaa 2732C 3333B 10 description 1 bbb 1223F 2545v 11 ... (3 Replies)
Discussion started by: vanitham
3 Replies

8. Web Development

mysql query for multiple columns from multiple tables in a DB

Say I have two tables like below.. status HId sName dName StartTime EndTime 1 E E 9:10 10:10 2 E F 9:15 10:15 3 G H 9:17 10:00 logic Id devName capacity free Line 1 E 123 34 1 2 E 345 ... (3 Replies)
Discussion started by: ilan
3 Replies

9. Shell Programming and Scripting

Query Oracle tables and return values to shell script that calls the query

Hi, I have a requirement as below which needs to be done viz UNIX shell script (1) I have to connect to an Oracle database (2) Exexute "SELECT field_status from table 1" query on one of the tables. (3) Based on the result that I get from point (2), I have to update another table in the... (6 Replies)
Discussion started by: balaeswari
6 Replies

10. Shell Programming and Scripting

Tables to query to find users for database from shell script

I am coding shell script. I need to connect to different databases like DB2, Oracle and Sybase. I would then need to query tables where it has all the groups, users for that database. I would also need who has what kind of permissions. EG: I know for DB2 some TABAUTH table needs to be... (0 Replies)
Discussion started by: pinnacle
0 Replies
Login or Register to Ask a Question