• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 624
  • Last Modified:

Testing of oracle stored procedureand function within pakage

Hi,
How can I retrieve the output from a stored procedure and function either written stanalon or written within pakage body in TOAD or in SQL*plus?  
Please find code for your ready reference.
--------------------------------------------------------
--  DDL for Package PKG_ZIR_NEW_HOMEPAGE
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE DATA_HOLDER.PKG_ZIR_NEW_HOMEPAGE AS 
   TYPE refcur IS REF CURSOR; 
   PROCEDURE get_zcbody_rec (p_t_id IN NUMBER, 
   p_body_id out NUMBER, 
   p_headline out VARCHAR2, 
   Ref_cursor OUT refcur); 
   PROCEDURE get_zchead_rec (p_t_id IN NUMBER, 
   p_body_id out NUMBER, 
   p_headline out VARCHAR2, 
   p_name out VARCHAR2, 
   Ref_cursor OUT refcur); 
   PROCEDURE get_bodyhead_rec (p_t_id IN NUMBER, 
   p_body_id out NUMBER, 
   p_headline out VARCHAR2, 
   Ref_cursor OUT refcur); 
   PROCEDURE get_videolink_rec (p_t_id IN NUMBER, 
   p_body_id out NUMBER, 
   p_headline out VARCHAR2, 
   p_teaser out VARCHAR2, 
   p_video_link out VARCHAR2, 
   p_small_image_link out VARCHAR2, 
   Ref_cursor OUT refcur); 
   PROCEDURE get_comp_rec (p_report_date in date, 
   p_comp_name out VARCHAR2, 
   p_m_ticker out VARCHAR2, 
   p_q1_end out date, 
   Ref_cursor OUT refcur); 
END pkg_ZIR_new_homepage;

/

--------------------------------------------------------
--  DDL for Package Body PKG_ZIR_NEW_HOMEPAGE
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY DATA_HOLDER.PKG_ZIR_NEW_HOMEPAGE AS 
   PROCEDURE get_zcbody_rec(p_t_id IN NUMBER, 
   p_body_id out NUMBER, 
   p_headline out VARCHAR2, 
   Ref_cursor OUT refcur) AS 
   BEGIN 
   LOG_AUDIT_TRAIL (p_procedure => LOG_ERRORS_PKG.WHO_AM_I); 
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO('pkg_ZIR_new_homepage_test'); 
   DBMS_APPLICATION_INFO.SET_MODULE('homepage1','pkg_ZIR_new_homepage_test'); 
      OPEN Ref_cursor FOR 
           SELECT body_id,  
                  headline 
             FROM zc_body 
            WHERE t_id = 7  
              AND data_p <= TO_CHAR (SYSDATE, 'dd-MON-YY') 
         ORDER BY data_p DESC; 
         DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO(null); 
   EXCEPTION 
    WHEN OTHERS THEN 
         log_errors_pkg.log_error( 
             p_procedure => log_errors_pkg.who_am_i, 
             p_ora_error => SQLERRM, 
             p_user     => USER, 
             p_time     => SYSDATE); 
    DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(null); 
    COMMIT; 
   END get_zcbody_rec ; 
  
   PROCEDURE get_zchead_rec (p_t_id IN NUMBER, 
   p_body_id out NUMBER, 
   p_headline out VARCHAR2, 
   p_name out VARCHAR2, 
   Ref_cursor OUT refcur)  AS 
   BEGIN 
   LOG_AUDIT_TRAIL (p_procedure => LOG_ERRORS_PKG.WHO_AM_I); 
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO('pkg_ZIR_new_homepage_test'); 
   DBMS_APPLICATION_INFO.SET_MODULE('homepage2','pkg_ZIR_new_homepage_test'); 
      OPEN Ref_cursor FOR 
           SELECT b.body_id,  
                  b.headline,  
                  a.NAME AS autor 
             FROM zc_body b 
                  LEFT JOIN zc_autor a 
                     ON a.ID = b.a_id 
            WHERE b.t_id = 57  
              AND b.data_p <= TO_CHAR (SYSDATE, 'DD-MON-YY') 
         ORDER BY b.data_p DESC; 
         DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO(null); 
   EXCEPTION 
    WHEN OTHERS THEN 
         log_errors_pkg.log_error( 
             p_procedure => log_errors_pkg.who_am_i, 
             p_ora_error => SQLERRM, 
             p_user     => USER, 
             p_time     => SYSDATE); 
    DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(null); 
    COMMIT; 
   END get_zchead_rec; 
  
   PROCEDURE get_bodyhead_rec (p_t_id IN NUMBER, 
   p_body_id out NUMBER, 
   p_headline out VARCHAR2, 
   Ref_cursor OUT refcur) AS 
   BEGIN 
   LOG_AUDIT_TRAIL (p_procedure => LOG_ERRORS_PKG.WHO_AM_I); 
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO('pkg_ZIR_new_homepage_test'); 
   DBMS_APPLICATION_INFO.SET_MODULE('homepage3','pkg_ZIR_new_homepage_test'); 
      OPEN Ref_cursor FOR 
           SELECT body_id,  
                  headline 
             FROM zc_body 
            WHERE t_id = 6  
              AND data_p <= TO_CHAR (SYSDATE, 'dd-MON-YY') 
         ORDER BY data_p DESC; 
         DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO(null); 
   EXCEPTION 
    WHEN OTHERS THEN 
         log_errors_pkg.log_error( 
             p_procedure => log_errors_pkg.who_am_i, 
             p_ora_error => SQLERRM, 
             p_user     => USER, 
             p_time     => SYSDATE); 
    DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(null); 
    COMMIT; 
   END get_bodyhead_rec; 
  
   PROCEDURE get_videolink_rec (p_t_id IN NUMBER, 
   p_body_id out NUMBER, 
   p_headline out VARCHAR2, 
   p_teaser out VARCHAR2, 
   p_video_link out VARCHAR2, 
   p_small_image_link out VARCHAR2, 
   Ref_cursor OUT refcur) AS 
   BEGIN 
   LOG_AUDIT_TRAIL (p_procedure => LOG_ERRORS_PKG.WHO_AM_I); 
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO('pkg_ZIR_new_homepage_test'); 
   DBMS_APPLICATION_INFO.SET_MODULE('homepage4','pkg_ZIR_new_homepage_test'); 
      OPEN Ref_cursor FOR 
           SELECT body_id, 
                  headline, 
                  teaser, 
                  video_link, 
                  small_image_link 
             FROM zc_body 
            WHERE t_id = 55  
              AND video_link IS NOT NULL 
         ORDER BY data_p DESC,  
                  body_id DESC; 
                  DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO(null); 
   EXCEPTION 
    WHEN OTHERS THEN 
         log_errors_pkg.log_error( 
             p_procedure => log_errors_pkg.who_am_i, 
             p_ora_error => SQLERRM, 
             p_user     => USER, 
             p_time     => SYSDATE); 
    DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(null); 
    COMMIT; 
   END get_videolink_rec; 
  
   PROCEDURE get_comp_rec (p_report_date in date, 
   p_comp_name out VARCHAR2, 
   p_m_ticker out VARCHAR2, 
   p_q1_end out date, 
   Ref_cursor OUT refcur) AS 
   BEGIN 
   LOG_AUDIT_TRAIL (p_procedure => LOG_ERRORS_PKG.WHO_AM_I); 
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO('pkg_ZIR_new_homepage_test'); 
   DBMS_APPLICATION_INFO.SET_MODULE('homepage5','pkg_ZIR_new_homepage_test'); 
      OPEN Ref_cursor FOR 
           SELECT m.comp_name, 
                  m.m_ticker, 
                  r.q1_end, 
                  CASE  
                     WHEN e.q1_eps_est IS NOT NULL THEN TO_CHAR (e.q1_eps_est)  
                     ELSE 'N/A'  
                  END q1_eps_est, 
                  r.report_date 
             FROM master_table m,  
                  eps_reports r,  
                  estimates e 
            WHERE e.m_ticker(+) = m.m_ticker  
              AND m.m_ticker = r.m_ticker  
              AND TO_CHAR (r.report_date, 'rrmmdd') = '.$tt.' 
         ORDER BY 1; 
         DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO(null); 
   EXCEPTION 
    WHEN OTHERS THEN 
         log_errors_pkg.log_error( 
             p_procedure => log_errors_pkg.who_am_i, 
             p_ora_error => SQLERRM, 
             p_user     => USER, 
             p_time     => SYSDATE); 
    DBMS_APPLICATION_INFO.SET_MODULE(null,null); 
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(null); 
    COMMIT; 
   END get_comp_rec; 
END pkg_ZIR_new_homepage;

/

Open in new window

0
Pradeep_Tiwari
Asked:
Pradeep_Tiwari
  • 2
  • 2
1 Solution
 
MilleniumaireCommented:
You can acheive this by writing your own version of dbms_output, which writes data to a table, rather than output!
Below is the package I've written and I simply add calls to it from my code as follows:

sldebug.put_line('My test');

You can then query the DEBUG table to view the results of your run.

Note: The package I developed allows indentation, and named statements to be used.  It also uses autonomous transactions to ensure the debug information is commited without affecting the current transaction.
CREATE TABLE SL.DEBUG
(
  ID    VARCHAR2(30 BYTE),
  SEQ   NUMBER,
  TEXT  VARCHAR2(4000 BYTE)
);


CREATE OR REPLACE package SL.sldebug as
  procedure clear (p_id varchar2);
  procedure put_line (p_id varchar2, p_text varchar2, p_clear varchar2);
  procedure put_line (p_id varchar2, p_text varchar2, p_clear varchar2, p_indent number);
  procedure put_line (p_id varchar2, p_text varchar2);
  procedure put_line (p_id varchar2, p_text varchar2, p_indent number);
  procedure put_line (p_text varchar2);
  procedure put_line (p_text varchar2, p_indent number);
end;


CREATE OR REPLACE package body SL.sldebug is
  g_save_id debug.id%type;
  g_indentation pls_integer;
  g_spaces      pls_integer := 4;

  procedure clear (p_id varchar2) is
    pragma autonomous_transaction;
  begin
    g_save_id := p_id;
    delete from debug where id = p_id;
	g_indentation := 0;
    commit;
  end;

  procedure put_line (p_id varchar2, p_text varchar2, p_clear varchar2) is
    pragma autonomous_transaction;
  begin
    clear(p_id);
    insert into debug (id, seq, text) values (p_id, debug_s.nextval, lpad(' ',g_indentation)||p_text);
    g_save_id := p_id;
    commit;
  end;

  procedure put_line (p_id varchar2, p_text varchar2, p_clear varchar2, p_indent number) is
    pragma autonomous_transaction;
  begin
    clear(p_id);
    if p_indent < 0 then
      g_indentation := g_indentation + (p_indent * g_spaces);
    end if;
    if p_text is not null then
      insert into debug (id, seq, text) values (p_id, debug_s.nextval, lpad(' ',g_indentation)||p_text);
    end if;
    if p_indent >= 0 then
      g_indentation := g_indentation + (p_indent * g_spaces);
    end if;
    g_save_id := p_id;
    commit;
  end;

  procedure put_line (p_id varchar2, p_text varchar2) is
    pragma autonomous_transaction;
  begin
    insert into debug (id, seq, text) values (p_id, debug_s.nextval, lpad(' ',g_indentation)||p_text);
    g_save_id := p_id;
    commit;
  end;

  procedure put_line (p_id varchar2, p_text varchar2, p_indent number) is
    pragma autonomous_transaction;
  begin
    if p_indent < 0 then
      g_indentation := g_indentation + (p_indent * g_spaces);
    end if;
    if p_text is not null then
      insert into debug (id, seq, text) values (p_id, debug_s.nextval, lpad(' ',g_indentation)||p_text);
    end if;
    if p_indent >= 0 then
      g_indentation := g_indentation + (p_indent * g_spaces);
    end if;
    g_save_id := p_id;
    commit;
  end;

  procedure put_line (p_text varchar2) is
    pragma autonomous_transaction;
  begin
    insert into debug (id, seq, text) values (g_save_id, debug_s.nextval, lpad(' ',g_indentation)||p_text);
    commit;
  end;

  procedure put_line (p_text varchar2, p_indent number) is
    pragma autonomous_transaction;
  begin
    if p_indent < 0 then
      g_indentation := g_indentation + (p_indent * g_spaces);
    end if;
    if p_text is not null then
      insert into debug (id, seq, text) values (g_save_id, debug_s.nextval, lpad(' ',g_indentation)||p_text);
    end if;
    if p_indent >= 0 then
      g_indentation := g_indentation + (p_indent * g_spaces);
    end if;
    commit;
  end;
end;

Open in new window

0
 
Pradeep_TiwariAuthor Commented:
i am littile bit confused on your answer.
i just want to how to test  the output from a stored procedure and function either written stanalon or written within pakage body
0
 
MilleniumaireCommented:
Sorry, I didn't mean to confuse you, I was just trying to provide what I consider to be a better way of debugging stored procedures by having the debug messages go to a table.

In way you access the output of dbms_output depends on how you call the stored procedure/package/function.  You need to enable server output in the tool you are using.  For example, if it is called from sqlplus then you need to include the following line at the beginning of the sqlplus script:

set server output on

You may also wish to increase the standard buffer size as follows:

set server output on size unlimited

All output from dbms_output is sent to a buffer, which in the case of sqlplus will then be written to standard output once the pl/sql block has completed.

It isn't possible to control where the output of this buffer is written within Oracle, however, you can use redirection to send standard output it to a file.
0
 
husker1neCommented:
Are you using pl/sql developer?  

I use pl/sql developer, which is owned by the same company that now owns Toad.

I believe in Toad, all you have to do is the following;
dbms_output.put_line ('Test output: ' || variable_name);

I imagine you already know this, but in my code, I have these kinds of statements all over the place in my initial debugging stages.
0
 
Pradeep_TiwariAuthor Commented:
issue closed
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now