Hi all,

I have created a .sql file which has insert statements, invoking
procedures and other functions.

when i execute the script, It is running fine by outputting "procedure
ran successfully".

But i want to see the output as

inserted 28569 rows successfully.

procedure ran successfully

I mean ,if we want to see all actions performed,how should I go about
it.where shouldi include begin and end

eg: here is the .sql file

declare
RetVal NUMBER;
P_QUERY VARCHAR2(200);
P_DIR VARCHAR2(200);
P_FILENAME VARCHAR2(200);
P_INCLUDE_HEADER NUMBER;
P_SEPARATOR VARCHAR2(200);
P_FIELDS_ENCLOSED_BY VARCHAR2(200);

pu_unknown_excep EXCEPTION;
PRAGMA EXCEPTION_INIT (pu_unknown_excep, -20009);

BEGIN
SELECT TO_CHAR(SYSDATE,'YYYYMMDD') INTO TODAY FROM dual;

insert into BBT_TBL
(select
to_char(trunc(sysdate),'YYYYMM') RPT_YYYYMM ,
EMPLOYERGROUPID ,
EMPLOYEESSN ,
RELATIONSHIPCODE ,
GENDER ,
DATE_OF_BIRTH
from BBT_IN_ELG_XTB XTB);

--commit;

Rollback;


--calling procedure

declare
BOM INT;
TERM_BY_DEL INT;
ADJ_BY_ADD INT;
EOM INT;
HIRE_CUR_MTH INT;
TERM_CUR_MTH INT;

begin

MTHLY_BILLING_REPORT(BOM,TERM_BY_DEL,ADJ_BY_ADD,EO M,HIRE_CUR_MTH,TERM_CUR_MTH);
end;


BEGIN
P_QUERY := 'SELECT * FROM MTHLY_BILL_RPT_V';
P_DIR := '/mar/tmp';
P_FILENAME := 'bbt_Rpt';
P_INCLUDE_HEADER := 1;
P_SEPARATOR := ',';
P_FIELDS_ENCLOSED_BY := NULL;

RetVal := DUMP_CSV ( P_QUERY, P_DIR, P_FILENAME, P_INCLUDE_HEADER,
P_SEPARATOR, P_FIELDS_ENCLOSED_BY );
-- COMMIT;
ROLLBACK;
END;
end;
/
exit;

Dear sappi,

after processing a Statement (update, delete etc.) in pl/sql, you can
get the number of rows with:

select sql%rowcount into <variable> from dual;

Then: dbms_output.put_line ('inserted ' || <variable> || ' rows
successfully.');

Best regards

roy

Your really running PL/SQL so you need to give this in the .sql file
that your running
(as I shown below with my script) or manually when you open SQL Plus.

> set serveroutput on

This will show all the output for you........you might want output it
to a text file so you can later review or check it for stats or
problems

Good Luck

George

http://georgenet.net/oracle



------------------------------------------------------------------------------------------------

script.sql <--- you can enter all this into your .sql file !!!!

Prompt ====================================
Prompt
Prompt Creating the DUPLEX CR MEMO file
Prompt with the update for tracking
Prompt ====================================
set serveroutput on format wrap
set linesize 150
column dt new_value _dt
column txt new_value _txt
select to_char(sysdate,'yymmddhh24mi') dt from dual;
select to_char('.txt') txt from dual;
spool argp_testdup&_dt&_txt
execute apps.apps_ar_forms_dup_trackcm2.print_crmemo_duple x;
SPOOL OFF
Prompt ====================================
Prompt Invoice file to be printed is completed
Prompt
Prompt The file is created and exists in your
Prompt bin directory
Prompt ====================================

thanks for all your mails.

actually we are scheduling this .sql file and i just want to see that

this many rows inserted
procedure ran scuccesfully

I tried set serveroutput on

but it was just displaying the procedure ran succesfullu..how baut
insert ?

thanks for your help

Just after insert put this statement:
dbms_output.put_line(sql%rowcount||' rows inserted.');

Hi,
Thanks for you reply.

If i ran the .sql file it will give you the output as

**rows inserted

procedure ran succesffuly

etc etc

now if i want to store this information to a oracle table,how should i
go about it.
this is needed as we will be scheduling this .sql file and so we can
see the actions performed later by going to a table

sappi

swapna.marri@gmail.com wrote:
> Hi,
> Thanks for you reply.
>
> If i ran the .sql file it will give you the output as
>
> **rows inserted
>
> procedure ran succesffuly
>
> etc etc
>
> now if i want to store this information to a oracle table,how should i
> go about it.
> this is needed as we will be scheduling this .sql file and so we can
> see the actions performed later by going to a table
>
> sappi
>

Just add another insert to your PL/SQL block to insert the value into a
different table. Then you can query that value later. Another option is
to use UTL_FILE to log the information to a log file, which can be
viewed outside of the database.

HTH,
Brian


--
================================================== =================

Brian Peasland
oracle_dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown