By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,569 Members | 1,321 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,569 IT Pros & Developers. It's quick & easy.

spool data

P: 75
I Have created following PL/sql blok


DECLARE
vl_n_cnt NUMBER:=0;
vl_max_date varchar2(100);
CURSOR c1 IS SELECT CREATED_DTM,EVENT_DTM,ACCOUNT_NUM,EVENT_COST_MNY,E VENT_TYPE_ID
FROM ist.costedevent_test ;

BEGIN
select max(to_char (CREATED_DTM,'ddmmyyyy') ) into vl_max_date from costedevent_test;
DBMS_OUTPUT.PUT_LINE('000' || ' '||vl_max_date);
FOR r1 in c1
LOOP
vl_n_cnt:=vl_n_cnt+1;
SELECT count(*) into vl_n_cnt FROM ist.costedevent_test;
DBMS_OUTPUT.PUT_LINE(r1.CREATED_DTM || ' '||to_char (r1.EVENT_DTM,'ddmmyyyy')|| ' '|| to_char (r1.EVENT_COST_MNY,'ddmmyyyy')|| ' '||r1.ACCOUNT_NUM|| ' '||r1.EVENT_COST_MNY|| ' '||r1.EVENT_TYPE_ID);
END LOOP;
DBMS_OUTPUT.PUT_LINE('999'||' '||vl_n_cnt);
END;

Now I wanted to get data in txt file ..
I used spool command for that
sql> spool on
sql> Spool c:\out.txt

But in out.txt file I am getting

SQL> @output.sql
25 /
000 12102007
12102007 20072007 GB0202026840 121 512
12102007 21072007 GB0202026840 131 512
999 2 PL/SQL procedure successfully completed.

SQL> spool off

Could u tell me how to avoid sql query, feedback in txt file ..
I want only data in spool file ...

Thnaks
Nov 12 '07 #1
Share this Question
Share on Google+
1 Reply


amitpatel66
Expert 100+
P: 2,367
I Have created following PL/sql blok


DECLARE
vl_n_cnt NUMBER:=0;
vl_max_date varchar2(100);
CURSOR c1 IS SELECT CREATED_DTM,EVENT_DTM,ACCOUNT_NUM,EVENT_COST_MNY,E VENT_TYPE_ID
FROM ist.costedevent_test ;

BEGIN
select max(to_char (CREATED_DTM,'ddmmyyyy') ) into vl_max_date from costedevent_test;
DBMS_OUTPUT.PUT_LINE('000' || ' '||vl_max_date);
FOR r1 in c1
LOOP
vl_n_cnt:=vl_n_cnt+1;
SELECT count(*) into vl_n_cnt FROM ist.costedevent_test;
DBMS_OUTPUT.PUT_LINE(r1.CREATED_DTM || ' '||to_char (r1.EVENT_DTM,'ddmmyyyy')|| ' '|| to_char (r1.EVENT_COST_MNY,'ddmmyyyy')|| ' '||r1.ACCOUNT_NUM|| ' '||r1.EVENT_COST_MNY|| ' '||r1.EVENT_TYPE_ID);
END LOOP;
DBMS_OUTPUT.PUT_LINE('999'||' '||vl_n_cnt);
END;

Now I wanted to get data in txt file ..
I used spool command for that
sql> spool on
sql> Spool c:\out.txt

But in out.txt file I am getting

SQL> @output.sql
25 /
000 12102007
12102007 20072007 GB0202026840 121 512
12102007 21072007 GB0202026840 131 512
999 2 PL/SQL procedure successfully completed.

SQL> spool off

Could u tell me how to avoid sql query, feedback in txt file ..
I want only data in spool file ...

Thnaks
Try using following SET comands before calling @output.sql:

SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET VERIFY OFF
Nov 12 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.