Home » SQL & PL/SQL » SQL & PL/SQL » Sequential Processing of Entire Table (Oracle 10g)
Sequential Processing of Entire Table [message #329806] Thu, 26 June 2008 10:47 Go to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Hi,

I have searched this DB for something on this, but did not find an answer.

I need to sequentially process all the rows in a table to build an extract file (Flat) to send to another system. The Flat File will need other information from other tables, so I can't just dump it.

I was thinking that I would do a Select on the primary key with low-values in it and a row num of 1 to start and then in a loop, select greater than that last key and a row num of 1. I know it sounds convoluted, so that’s why I’m asking the question.

Is there a better way?

Thanks,
Lou
Re: Sequential Processing of Entire Table [message #329820 is a reply to message #329806] Thu, 26 June 2008 11:47 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
Try composing your query, then load that data using user defined records and nested table. You can do per batch processing by using LIMIT on it.

Regards,
Rhani
Re: Sequential Processing of Entire Table [message #329821 is a reply to message #329806] Thu, 26 June 2008 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
spool file
select * from table;
spool off

Regards
Michel
Re: Sequential Processing of Entire Table [message #329826 is a reply to message #329821] Thu, 26 June 2008 12:11 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Michel,

Thank you....

I have never used the "Spool File" feature.

Do I have to define a Flat File?

How do I acces the columns to get other table rows?

Do I just put my code inside the "Select" and the "Spool Off"?

Let me know where I can find information on this. My book doesn't cover this.

Thanks again,
Lou

Re: Sequential Processing of Entire Table [message #329827 is a reply to message #329806] Thu, 26 June 2008 12:13 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Expand your book list HERE!
http://www.oracle.com/pls/db102/portal.portal_db?selected=3
Re: Sequential Processing of Entire Table [message #329836 is a reply to message #329826] Thu, 26 June 2008 12:40 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,

>I have never used the "Spool File" feature.
I think she means you should use "sqlplus". Create a text file, type "spool <filenameoffile>" then put your query then put "spool off" for the last line. This will create a file with the results of your query.

>Do I have to define a Flat File?
i think you dont have to.

>How do I acces the columns to get other table rows?
join it in your main table - you select statement

>Do I just put my code inside the "Select" and the "Spool Off"?
no

>Let me know where I can find information on this. My book doesn't cover this.
you can look at the oracle documentation given link above. you could also try googling other related article if the explanation in that docu seems difficult. (as for me some of that documentation is hard for me to understand, as im not as intelligent as the other people here, and im not good at understanding english also).

HTH,
Rhani
Re: Sequential Processing of Entire Table [message #329839 is a reply to message #329836] Thu, 26 June 2008 12:52 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Rhani,

Thank you. Your English is very good.

I could just do a Dump of the table and then run through the flat file and parse out what I need for the Extract file and add information from other tables with a direct read.

I was looking for a method to just start on a table at the begining (Lowest Key) and run through the end, sequentially, and add columns from other tables.

I'll keep looking.....

Thanks for your help, though.

Lou
Re: Sequential Processing of Entire Table [message #329840 is a reply to message #329839] Thu, 26 June 2008 12:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just create the select statement with the data you need;
Then execute it between spool statements.

Regards
Michel
Re: Sequential Processing of Entire Table [message #329848 is a reply to message #329840] Thu, 26 June 2008 13:47 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Michel,

So, the "Spool File" makes the "Select" get only one row???

Then I would access each column as usual and get the other rows from other tables using the Key from the first table.

Then the "Spool Off" ends the loop??? Or do I need a "Loop", too?

Do you have some sample code? Can I find some on the Web???

Thanks,
Lou
Re: Sequential Processing of Entire Table [message #329913 is a reply to message #329848] Fri, 27 June 2008 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Spool just manages the output file (open/close).
There is no loop needed.

Quote:
I would access each column as usual and get the other rows from other tables using the Key from the first table.

It is basic SQL, see SQL Reference.

Example:
SQL> spool t
SQL> select e.*, d.dname from emp e, dept d where d.deptno = e.deptno;
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO DNAME
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- --------------
      7369 SMITH      CLERK           7902 17/12/1980        800                    20 RESEARCH
      7499 ALLEN      SALESMAN        7698 20/02/1981       1600        300         30 SALES
      7521 WARD       SALESMAN        7698 22/02/1981       1250        500         30 SALES
      7566 JONES      MANAGER         7839 02/04/1981       2975                    20 RESEARCH
      7654 MARTIN     SALESMAN        7698 28/09/1981       1250       1400         30 SALES
      7698 BLAKE      MANAGER         7839 01/05/1981       2850                    30 SALES
      7782 CLARK      MANAGER         7839 09/06/1981       2450                    10 ACCOUNTING
      7839 KING       PRESIDENT            17/11/1981       5000                    10 ACCOUNTING
      7844 TURNER     SALESMAN        7698 08/09/1981       1500          0         30 SALES
      7900 JAMES      CLERK           7698 03/12/1981        950                    30 SALES
      7902 FORD       ANALYST         7566 03/12/1981       3000                    20 RESEARCH
      7934 MILLER     CLERK           7782 23/01/1982       1300                    10 ACCOUNTING

12 rows selected.

SQL> spool off

Regards
Michel

[Updated on: Fri, 27 June 2008 01:13]

Report message to a moderator

Re: Sequential Processing of Entire Table [message #330873 is a reply to message #329913] Tue, 01 July 2008 10:30 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Michel,

Thank you for this.

I just found out that I need to change some of the columns before I put out the Flat File.

Can I use the Spool and after the Select say....

If ctm_code := 'P' Then
Spool_File.ctm_cde = 'A';
End If;

???

Can I put code inbetwen the "Spool" and the "Spool Off" statements to manipulate the output?

Thanks,
Lou
Re: Sequential Processing of Entire Table [message #330874 is a reply to message #330873] Tue, 01 July 2008 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does "Spool_File.ctm_cde" mean?
What do you want exactly.
You can put what you want between "spool" and "spool off", it is just a commant to record what you see/do inside a file.

Regards
Michel
Re: Sequential Processing of Entire Table [message #330875 is a reply to message #329806] Tue, 01 July 2008 10:37 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>If ctm_code := 'P' Then
is NOT valid SQL
Re: Sequential Processing of Entire Table [message #330876 is a reply to message #330873] Tue, 01 July 2008 10:38 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Anything you can use in a select statement, can be used while spool is on.

If ctm_code := 'P' Then 
Spool_File.ctm_cde = 'A';
End If;


can be done something like this:

case when ctm_code = 'P' then 'A' ctm_code


Unless := in your code is actually representing some other logic I don't get? I assumed you meant: "if code equals P then show A in result file"
Re: Sequential Processing of Entire Table [message #330877 is a reply to message #330874] Tue, 01 July 2008 10:39 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Michel,

"Spool_File.ctm_nbr" means the ctm_nbr of the Spool_File (In your example "T").

If I do the Spool and then the Select, can I access each column as I would normally?

Spool Spool_File;

Select * from cdsadr_M adr;

If adr.ctm_cde = '1' Then
Spool_File.ctm_cde = 'A';
End If;

Spool Off;

I'm trying to change the output file value before it goes out.

Is this possiable?

Thanks,
Lou
Re: Sequential Processing of Entire Table [message #330878 is a reply to message #330876] Tue, 01 July 2008 10:42 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Skooman,

That's right. I'm trying to change the output as it comes in from the table.

How do I define the Spooled File (Flat File)???? Like this:

Spool FileName.dat;

The output would go into a file named "FileName.dat"?

Thanks,
Lou
Re: Sequential Processing of Entire Table [message #330879 is a reply to message #330878] Tue, 01 July 2008 10:44 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Yes. See the example Michel posted earlier.
Re: Sequential Processing of Entire Table [message #330881 is a reply to message #330879] Tue, 01 July 2008 10:47 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Skooman,

Got it....


Thanks a bunch, both of you...

Lou
Re: Sequential Processing of Entire Table [message #330883 is a reply to message #330877] Tue, 01 July 2008 10:50 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"t" is the name of the output file (actually "t.lst" if there is no extension).
If you want "FileName.dat" use "spool FileName.dat"

Read SQL*Plus User's Guide and Reference

Regards
Michel
Previous Topic: Problem: Passing parameter
Next Topic: Link: Exception handling
Goto Forum:
  


Current Time: Sat Dec 03 12:00:24 CST 2016

Total time taken to generate the page: 0.07553 seconds