Solved

Correct Time to use PLSQL Cursor?

Posted on 2012-03-22
8
323 Views
Last Modified: 2012-06-21
Morning Experts, I am looking at a way to consolidate some of my codes I run.  For instance every morning there are 3 queries I run daily.

The first Query:

select DISTINCT SSNO  from VX_VO_TEMP A
WHERE EXISTS (SELECT * FROM VX_ROPES_COUNTS_3 B WHERE A.SSNO = B.SSNO)
order by 1 desc

Gives me a listing of distinctive SSNo's that I use to run the other queries:

SELECT distinct rm4_report_number, rm4_report_agency
  FROM rim_04mast, rim_06mast, mni_01mast
 WHERE     rm4_dbnumber = rm6_dbnumber
       AND dbnumber = rm6_mni_link
       AND dbseq = rm6_mni_link_seq
       AND ssno = $SSNO
       order by 1 desc

This gives me a listing using the above SSNO(I put $SSNO here, because I would like to set this as a variable to automatically run for each distinctive SSNO)

The other query is much the same using the same list of SSNO's.


SELECT distinct rm4_report_number,rm4_report_suppno,
       rm5_offense_type,
       chg_charge_desc,
       rm5_offense_time1,
       rm5_offense_date1,
       rm6_suspect,
       rm6_arrestee,
       rm6_other,
       rm6_fullname
  FROM rim_04mast,
       rim_05mast,
       rim_06mast,
       MNI_01MAST,
       sys_charges
 WHERE     rm4_dbnumber = rm5_dbnumber
       AND rm5_dbnumber = rm6_dbnumber
       AND rm5_sequence = rm6_sequence
       AND rm6_mni_link = dbnumber
       AND rm6_mni_link_seq = dbseq
       AND rm5_offense_type = chg_charge
       AND (chg_agency = 'PD' OR chg_agency = 'SO' OR chg_agency = 'XX')
       AND ssno = $SSNO
       order by 1,2 desc

Is there a way to combine all these so I just kick off one "Package" and I can export everything into an excel spreadsheet every day so I can just cut it and give it to the departments that its needed?

Thanks all -
0
Comment
Question by:hej613
  • 3
  • 3
  • 2
8 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 37753207
you can merge the first into the other 2 like:

SELECT distinct rm4_report_number, rm4_report_agency
  FROM rim_04mast, rim_06mast, mni_01mast
 WHERE     rm4_dbnumber = rm6_dbnumber
       AND dbnumber = rm6_mni_link
       AND dbseq = rm6_mni_link_seq
       AND ssno in (select DISTINCT SSNO  from VX_VO_TEMP A
WHERE EXISTS (SELECT * FROM VX_ROPES_COUNTS_3 B WHERE A.SSNO = B.SSNO)
)
       order by 1 desc

Not sure how you want to merge the two main ones.

The term 'package' means somethign specific in Oracle.  Are you thinking an actual Oracle package?
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 37753234
something like this?

if you don't want to use utl_file,  you could change those to dbms_output.put_line  and then spool to a file


DECLARE
    v_file   UTL_FILE.file_type;
BEGIN
    v_file  := UTL_FILE.fopen('DTEMP', 'test_out.txt', 'w', 32767);

    FOR x IN (SELECT   DISTINCT ssno
                  FROM vx_vo_temp a
                 WHERE EXISTS
                           (SELECT *
                              FROM vx_ropes_counts_3 b
                             WHERE a.ssno = b.ssno)
              ORDER BY 1 DESC)
    LOOP
        FOR y
            IN (SELECT   DISTINCT rm4_report_number, rm4_report_agency
                    FROM rim_04mast, rim_06mast, mni_01mast
                   WHERE     rm4_dbnumber = rm6_dbnumber
                         AND dbnumber = rm6_mni_link
                         AND dbseq = rm6_mni_link_seq
                         AND ssno = x.ssno
                ORDER BY 1 DESC)
        LOOP
            BEGIN
                UTL_FILE.put_line(v_file, y.rm4_report_number || ',' || y.rm4_report_agency);
            END;
        END LOOP;


        FOR z
            IN (SELECT   DISTINCT rm4_report_number,
                                  rm4_report_suppno,
                                  rm5_offense_type,
                                  chg_charge_desc,
                                  rm5_offense_time1,
                                  rm5_offense_date1,
                                  rm6_suspect,
                                  rm6_arrestee,
                                  rm6_other,
                                  rm6_fullname
                    FROM rim_04mast, rim_05mast, rim_06mast, mni_01mast, sys_charges
                   WHERE     rm4_dbnumber = rm5_dbnumber
                         AND rm5_dbnumber = rm6_dbnumber
                         AND rm5_sequence = rm6_sequence
                         AND rm6_mni_link = dbnumber
                         AND rm6_mni_link_seq = dbseq
                         AND rm5_offense_type = chg_charge
                         AND (chg_agency = 'PD' OR chg_agency = 'SO' OR chg_agency = 'XX')
                         AND ssno = x.ssno
                ORDER BY 1, 2 DESC)
        LOOP
            BEGIN
                UTL_FILE.put_line(
                    v_file,
                       z.rm4_report_number
                    || ','
                    || z.rm4_report_suppno
                    || ','
                    || z.rm5_offense_type
                    || ','
                    || z.chg_charge_desc
                    || ','
                    || z.rm5_offense_time1
                    || ','
                    || z.rm5_offense_date1
                    || ','
                    || z.rm6_suspect
                    || ','
                    || z.rm6_arrestee
                    || ','
                    || z.rm6_other
                    || ','
                    || z.rm6_fullname);
            END;
        END LOOP;
    END LOOP;

    UTL_FILE.fflush(v_file);
    UTL_FILE.fclose(v_file);
END;
0
 
LVL 1

Author Comment

by:hej613
ID: 37753356
Thank you both - SlightUV, I'm not 100% what I mean at this point.. My idea goal would be come in the mornings and click a button and have a text file I can just import into excel and distribute it to the people that need it...  

SDS I'm going to take a look at what you posted, Because honestly its over my head, I'll have to break it out bit by bit till I can understand it. :)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37753387
Why click a button?

You can schedule it inside the database and even have the database email you the information.

When you figure out what you are after, ask and we can probably provide exactly what you need.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:hej613
ID: 37753412
Our scheduling at the office here is strange, so instead of getting our DBA guy involved, I would just prefer to come in and kick off the job..

I'm after having one "Script" that I can run that will provide me a CSV file I can import... The above code with UTL_FILE may do that, but I am still trying to get it working. :)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37753435
you need to create a directory object

using my example above....


create or replace directory DTEMP as 'C:\your\path\goes\here'

note, the path is for a file system on the database server,  it is NOT a local path for your client machine.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37753460
>>I would just prefer to come in and kick off the job..

Then I doubt UTL_FILE will work for you since as noted, it is on the database server itself.

I would look strongly at a simple sqlplus script running from your client machine.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37753505
it might be easiest to go with a stored procedure that sends an email to you as slighwv suggested

You can schedule the procedure or run it yourself.

You can find an example of sending a csv by email here...


http://www.experts-exchange.com/Database/Oracle/A_7749-How-to-send-email-attachments-with-Oracle.html

With the csv example you would use the same loop I posted above except change the utl_file calls to the clob appending.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now