Concat Spool File Possible? How to Store Delete result to Variable? 1

[SolidFish]

I want to turn a spool file off / on and have it concatenate to the same file while I am running my SQL queries. Is this possible? Cant find this anywhere in my $100 Oracle guide...

Also, how can I store the Number Result of a Delete query into a variable?

Thanks so much.

 

[SantaMufasa]

Solid,

1) Oracle PL/SQL's "utl_file" package provides ability to read from, write to, or append to flat files. "utl_file" access is to files on the Oracle database server machine. If this meets your need, then you can research (then use) that feature (or if you are stumped or your $100 Oracle guide does not explain things properly, we can help.)

2) Again in a PL/SQL block (script), you can store the value of "SQL%ROWCOUNT" into a variable immediately following a SQL DML (INSERT, UPDATE, DELETE) statement to know how many rows were affected.

Let us know if this answers your question(s).

Mufasa
(aka Dave of Sandy, Utah, USA @ 02:54 (07Oct04) UTC (aka "GMT" and "Zulu"), 19:54 (06Oct04) Mountain Time)

 

[SolidFish]

Hi. Thanks for the help. Could you show exactly how to do the row count? Im kind of new to SQL so I dont know the exact syntax.

Lets say I was doing:
DELETE FROM myTable WHERE myValue > 0;

How do I count the rows deleted after this?

Thanks so much.

 

[sem]

If you need results to be spooled locally (on client side) you may spool results to a new empty file and then call OS command (for DOS/Windows - COPY old_file1+old_file2 new_file) through HOST built-in to append a new file to an old one. Your last question has already been answered by Dave: check SQL%ROWCOUNT variable.

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Solid,

Here is sample code that uses SQL%ROWCOUNT. (As I mentioned, you can access SQL%ROWCOUNT only from within a PL/SQL block):

set serveroutput on
declare
	cnt	number;
begin
	delete from mytable where myvalue > 0;
	cnt	:= sql%rowcount;
	dbms_output.put_line(cnt||' rows deleted.');
end;
/
25 rows deleted.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 06:30 (07Oct04) UTC (aka "GMT" and "Zulu"), 23:30 (06Oct04) Mountain Time)

 

[SolidFish]

Dave

Many thanks, ROWCOUNT was exactly what I needed. However, I am still a bit confussed between variables and bind variables. I had to setup my statement as the following to make it work:

VARIABLE count NUMBER;

BEGIN
DELETE FROM myTable WHERE myValue > 0;
:count := SQL%ROWCOUNT;
END;
/

Is there an easy explaination on DECLARE verses VARIABLE? And the ":count" verses "count"?

Anyways, thanks again!

 

[SantaMufasa]

Solid,

A bind variable (":count") is a data item that you define within the "environment" in which you are executing your SQL or PL/SQL code. The variable is visible outside your SQL or PL/SQL code block and is "persistent", meaning that the variable stays visible during your entire session or until you explicitly delete the variable.

The PL/SQL variable ("count") typically has visibility and persistence only within the execution scope of the PL/SQL code block. That is, you can access it only from within the "scope" of execution of the PL/SQL block and it disappears when the code finishes its execution.

The one exception to the PL/SQL variable behaviour, above, is an obscure case in which your variable is both persistent and externally visible if you define the variable in the PACKAGE header variable-declaration section of a PL/SQL package.

Did this answer your questions?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:40 (07Oct04) UTC (aka "GMT" and "Zulu"), 09:40 (07Oct04) Mountain Time)

 

[SolidFish]

Thanks for the help!