I want to dynamically create a SELECT statement, then use EXECUTE IMMEDIATE and UNLOAD to write to a file... and I'd like to know how many records I've just written. Running 9.0.2.3850 This is what I've got...
So, now that I've writen two files, I'd like to know how many records are out there. My first thought is to use @@Rowcount -- which I've incldued in the code above. But that value does not get updated during this procedure... so what ever was in there before the procedure runs is what I get in the messages while the procedure is running. The only other way I can think of is to run queries against the db tables again to get a record count, but that won't tell me how many records I just wrote out the the file. Is there a better way? thanks! |
Note that @@rowcount is only updated on statements that affect rows within the database - INSERT, UPDATE, or DELETE - and is not updated by a SELECT or UNLOAD statement. There is currently no direct way of knowing how many rows were written to the file by the UNLOAD ... SELECT statement. One method would be to first select the rows into a temp table and then output the rows from the temp table as well as count the number of rows in the temp table. E.g.
The drawback to using the temp table is that all rows need to be copied. If the data set is small then this may not be a concern but if you are unloading millions of rows (or very wide rows) and the data set does not all fit into the server cache then this operation may take some time to complete. A second method would be to read the contents of the file that was just written and count the number of lines (i.e. count the number of \x0A characters). HTH |