hej613
asked on
Correct Time to use PLSQL Cursor?
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_repo rt_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 -
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_repo
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 -
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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. :)
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. :)
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.
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.
>>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.
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.
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...
https://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.
You can schedule the procedure or run it yourself.
You can find an example of sending a csv by email here...
https://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.
ASKER
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. :)