?
Solved

test stored produre in database

Posted on 2012-09-04
3
Medium Priority
?
584 Views
Last Modified: 2012-09-09
found Query database for this stored procedure.  How do I test this in toad.


create or replace PROCEDURE               SP_SEE(p_cursor   OUT   Pk_Test.cursortype) AS

BEGIN
OPEN p_cursor FOR
Select y.ref_num "SP Number"
,x.fyi_application_no "SEE Tracking Number"
--  ,x.sys_author
,case when x.sys_life_cycle = 'W' then 'W' Else '' End "SEE In Management"
,case when x.sys_life_cycle = 'L' then 'L' Else '' End "SEE In Library"
,x.fyi_project_officer PO
,x.sys_author "Company Name"
from
(Select distinct substr(reference_number,3) ref_num from ohma_agl.sp_agl_web) y,
(Select f.fyi_reference_id
,f.fyi_application_no
,sd.sys_author
,sd.sys_life_cycle
,f.fyi_folder_type
,f.fyi_project_officer
from fyiadm.fyi_xmpt_applic_f f
,fyiadm.fyi_sysdata sd
where f.sys_fkey = sd.sys_fkey
and sd.sys_dkey = 0
and sd.sys_delete_date is null
and (lower(sd.sys_author) like '%safety equi%' or sd.sys_author like '%SEE%')
and f.fyi_folder_type = 'General Correspondence') x
--lower(sd.sys_author) like 'safety eq%')  x
where y.ref_num = x.fyi_reference_id(+);
/*Select distinct f.fyi_application_no
,f.fyi_reference_id
,sd.sys_author
,f.fyi_folder_type
,f.fyi_disposition
,f.fyi_project_officer
,f.FYI_APPLICATION_DATE
,wl.FYI_MEMBERID
,f.fyi_project_status
,sd.sys_date_created
,CASE WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') BETWEEN 60 AND 120 THEN '(2) 60-120'
WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') BETWEEN 121 AND 180 THEN '(3) 121-180'
WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') > 180 THEN '(4) >180'
WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') < 60 THEN '(1) <60'
ELSE 'Not categorized'
END   Aging
from fyiadm.FYI_XMPT_APPLIC_F f
,fyiadm.fyi_sysdata sd
,fyiadm.fyi_workflow wf
,fyiadm.fyi_wfworklist wl
where sd.sys_fkey = f.sys_fkey
and wf.sys_wf_fkey = f.sys_fkey
and wf.sys_wf_skey = wl.FYI_SKEY
and sd.sys_dkey = 0
and sd.sys_delete_date is null
and sd.sys_life_cycle = 'W'
and wf.SYS_WF_PROCNAME = 'Exemption Process'
AND wl.fyi_status IN ('New', 'Open', 'Subprocess', 'Complete')
and wl.FYI_MEMBERID not like 'XMPT%'
and sd.sys_date_created <= to_char(trunc(sysdate),'yyyy-mm-dd')
and lower(sd.sys_author) like 'safety eq%'
union
Select distinct f.fyi_application_no
,f.fyi_reference_id
,sd.sys_author
,f.fyi_folder_type
,f.fyi_disposition
,f.fyi_project_officer
,f.FYI_APPLICATION_DATE
,'No Member Id'
,f.fyi_project_status
,sd.sys_date_created
,CASE WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') BETWEEN 60 AND 120 THEN '(2) 60-120'
WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') BETWEEN 121 AND 180 THEN '(3) 121-180'
WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') > 180 THEN '(4) >180'
WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') < 60 THEN '(1) <60'
ELSE 'Not categorized'
END   Aging
from fyiadm.FYI_XMPT_APPLIC_F f
,fyiadm.fyi_sysdata sd
,fyiadm.fyi_workflow wf
where sd.sys_fkey = f.sys_fkey
and wf.sys_wf_fkey = f.sys_fkey
and sd.sys_dkey = 0
and sd.sys_delete_date is null
and sd.sys_life_cycle = 'W'
and sd.sys_date_created <= to_char(trunc(sysdate),'yyyy-mm-dd')
and wf.sys_wf_status <> 'Active'
and lower(sd.sys_author) like 'safety eq%';*/

END;

Open in new window

0
Comment
Question by:mikensu12
  • 2
3 Comments
 
LVL 23

Expert Comment

by:David
ID: 38365160
1.  Start with a query at toadworld.com, where you will find over 300 hits on this question: http://www.toadworld.com/HOME/ToadWorldSearch/tabid/681/Default.aspx?TwRel=True&cx=013798889833398363191:waqfralxdmk&cof=FORID:9&ie=UTF-8&q=debug%20plsql

2.  For a good specific example, I like http://www.toadworld.com/Blogs/tabid/67/EntryId/515/A-New-Way-to-Debug-PL-SQL.aspx

3.  Still the best source more often than not:  Open TOAD for Oracle, select Help | Contents | search in the new window for "debug".  Breakpoints, watches, dbms_output, all the bells and whistles.

HTH
0
 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 1500 total points
ID: 38366858
this looks more like wanting to return the output of the stored proc in toad
not like debugging

run this in the toad editor:
begin
  SP_SEE(:data);
end;

Open in new window


in the variables screen, use output type "cursor"
toad variable types
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38381336
with did you give a B grade ?

there is no comment explaining the B grade
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

850 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