SQLPlus :http://www.orafaq.com/wiki/SQL*Plus_FAQ
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm
1. Transfer values from a sql scripts:
CNT=`sqlplus -s username/password1@dbname @getUVQuery_NULLCNT`;
Note : Remeber to use option "-s" (-s or -silent -- start sqlplus in silent mode. Not recommended for beginners!)
if not use "-s" , will return something as below :
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Feb 9 11:03:08 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
0
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
2.getUVQuery_NULLCNT.sql
set term off
column cnt noprint new_value total
SELECT count(inquiry_id) cnt
FROM inquire_all_current a
WHERE FORM_COMPLETE_STATUS = 'Y'
and ITEM_POPULATE_STATUS = 'N'
and expand_type='uvall'
and uv_query is null
ORDER BY INQUIRY_ID ASC;
set term on
prompt &total
exit
3. Define cursor and use cursor:
DECLARE
CURSOR cur IS
SELECT inquiry_id
FROM inquire_all_current a
WHERE FORM_COMPLETE_STATUS = 'Y'
and ITEM_POPULATE_STATUS = 'N'
and expand_type='uvall'
and uv_query is null
ORDER BY INQUIRY_ID ASC;
BEGIN
For rec in cur
LOOP
UPDATE INQUIRE_ALL_CURRENT
SET ITEM_POPULATE_STATUS='K'
WHERE inquiry_id = rec.inquiry_id;
COMMIT;
END LOOP;
END;
/
EXIT;
4.Define cursor with parameter :
DECLARE
v_cnt NUMBER DEFAULT 1;
v_source_name agg_product.source_name%TYPE;
CURSOR c_check_outstand_data (in_source_name agg_product.source_name%TYPE)IS
SELECT COUNT(1) cnt
FROM agg_pdt_image_name_nonali_stg a
WHERE NOT EXISTS (SELECT 1
FROM agg_pdt_image_name_nonali b
WHERE b.source_name = in_source_name
AND b.product_image_name = a.product_image_name);
BEGIN
v_source_name := '&1';
LOOP
OPEN c_check_outstand_data(v_source_name);
FETCH c_check_outstand_data INTO v_cnt;
CLOSE c_check_outstand_data;
INSERT INTO agg_pdt_image_name_nonali
SELECT v_source_name, product_image_name
FROM agg_pdt_image_name_nonali_stg a
WHERE NOT EXISTS (SELECT 1
FROM agg_pdt_image_name_nonali b
WHERE b.source_name = v_source_name
AND b.product_image_name = a.product_image_name);
COMMIT;
EXIT;
END LOOP;
END;
/
EXIT;
5.output file :
SET FEEDBACK OFF
CLEAR COLUMNS
CLEAR BREAKS
SET TERMOUT OFF
SET AUTOPRINT OFF
SET VERIFY OFF
SET TRIMSPOOL ON
spool kill_RFI_list.lst;
SELECT CAST( inquiry_id as varchar(50) )
FROM inquire_all_current a
WHERE FORM_COMPLETE_STATUS = 'Y'
and ITEM_POPULATE_STATUS = 'N'
and expand_type='uvall'
and uv_query is null
ORDER BY INQUIRY_ID ASC;
spool off;
6. convert Integer to String in sql :
SELECT CAST( inquiry_id as varchar(50) )
FROM inquire_all_current a