[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Debug an Oracle Function

Posted on 2014-08-18
2
Medium Priority
?
531 Views
Last Modified: 2014-08-18
I need to analyze the following function.

    RETURN VARCHAR IS swbs VARCHAR(4000);
      
        swbsCount number;
        swbsHolder VARCHAR(4000);
        counter int;

        BEGIN

            swbs := '';
            swbsHolder := '';
            counter := 1;

            Declare
                CURSOR CUR_CURSOR  is
                    select rownum as numb,
                       COALESCE(swbs,'-'),' '
                      as swbs_info
                        from t_psa_swbs a
                                    where PSA_MASTER_SEQNUM = 3;

            Begin

Cursor rows returned:
numb (rownum)      swbs_info
1                  0004
2                  0003
3                  0001
4                  0002


FOR cur_rec IN CUR_CURSOR  --
                LOOP
                    select cur_rec.swbs_info into swbsHolder from dual;
                    swbs := swbs || swbsHolder ||'   ' ;
                end loop;
            End;

        return swbs;

This looks like it would return the string "0004 0003 0001 0002 " 

How can I test the function?

How can I debug the function and watch each step ?
Thanks,
0
Comment
Question by:Dovberman
2 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40268552
To 'test' it:
select function_name from dual;

For 'debugging' it, you'll need a tool that has a pl/sql debugger built into it.

Take a look at Oracle's SQL Developer:
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/plsql_debug/plsql_debug_otn.htm

That said, there are MUCH better ways of string concatenation that what you have posted.

In 11g and above check out LISTAGG.  It is a built-in function that does this:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030
0
 

Author Closing Comment

by:Dovberman
ID: 40268777
Excellent. Thanks
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.
Suggested Courses
Course of the Month20 days, 12 hours left to enroll

865 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