Unit test the Oracle SQL stored procedure in TOAD

Question

I have a complex stored procedure written by someone and I need to understand part of it to check what data it returns. So, started to test it by passing some data in toad. this is an Oracle stored procedure. I get error:

Encountered the symbol "SELECT" when expecting one of the following:

( - + case mod new not null

Code:

      declare 
                              cRegion char;
                              cState_Code char;
                              nFY NUMBER;
                              nREPORT_ID NUMBER;
                              nSECTION_ID NUMBER;
                              nSUBSECTION_ID NUMBER;
                              nDISPLAY_NUMBER INTEGER;
                              nQUESTION_NUMBER INTEGER;
                              nQUESTION_PART_NUMBER INTEGER default null;



                              BEGIN

(select QD.FY_ST_QUESTION_DTL_TABLE_ID,qi.display_number,QI.QUESTION_NUMBER,
                                qd.question_part_number
                            from FY_ST_QUESTION_INFO qi,
                                 FY_ST_QUESTION_DETAIL qd
                           where qi.region = '00'
                             and qi.state_code = '05'
                             and qi.fy = '2015'
                             and qi.report_id = '1'
                             and qi.section_id = '2'
                             and qi.subsection_id = '2'
                             and qi.display_number = '23'
                             and qi.QUESTION_NUMBER = '21'

                             and QD.FY_ST_QUESTION_INFO_TABLE_ID = '16613')

                       )fq left outer join
                       ( select distinct FY_ST_QUESTION_DTL_TABLE_ID, AD.ROW_NUMBER
                           from UPLOAD_TEMP_DATA ad
                          where ad.region = '00'
                            and ad.state_code = '05'
                            and ad.fy = '2015'
                            and ad.report_id = '1'
                            and ad.section_id = '2'
                            and ad.subsection_id = '2'
                            and ad.display_number = '23'
                            and ad.QUESTION_NUMBER = '21'
                            and ad.QUESTION_PART_NUMBER = '0'
                       )aq
                     using(FY_ST_QUESTION_DTL_TABLE_ID)

Thanks

Answer 1

I fixed the problem and now I know my mistake. I figured the outcome from the complex code before this appended it in the select clause above. Now, I know the result.