RE: Dynamic SQL

From: Bala, Prakash <prakash.bala_at_cingular.com>
Date: Wed, 20 Feb 2002 06:43:22 -0800
Message-ID: <F001.0041437B.20020220064322@fatcity.com>

Laura,  

Here is an example:  

create or replace package pkg_drill_thru as

  type CompanyPhone is ref cursor;   

  Procedure get_mobile_numbers(
    activity_dt date,

    company_cd     varchar2,
    channel_cd     varchar2,
    subscriber_cd  varchar2,
    initial_add    number,
    volun_disc     number,
    involun_disc   number,
    volun_recon    number,
    involun_recon  number,
    no_install     number,

    company_phone in out CompanyPhone);  

end;
/  

create or replace package body pkg_drill_thru as  

  Procedure get_mobile_numbers(
    activity_dt date,

    company_cd     varchar2,
    channel_cd     varchar2,
    subscriber_cd  varchar2,
    initial_add    number,
    volun_disc     number,
    involun_disc   number,
    volun_recon    number,
    involun_recon  number,
    no_install     number,

    company_phone in out CompanyPhone)
  as
    sql_str varchar2(2000);
  begin
    sql_str := 'select nvl(company_desc, a.company_code) company, a.channel_code, mobile_number

      from dly_transaction_detail a, company_dimension b
      where activity_date = :1
         and subscriber_code = upper(:2)
         and a.company_code in (select company_code
                                from company_dimension
                                start with parent_company_code = :3
                                connect by parent_company_code = prior
company_code
                                union
                                select :4 from dual)
         and a.channel_code in (select channel_code
                                from channel_dimension
                                start with parent_channel_code = :5
                                connect by parent_channel_code = prior
channel_code
                                union
                                select :6 from dual)
         and a.company_code = b.company_code';
    
    if initial_add = 1 then
      sql_str := sql_str || ' and initial_add = 1';
    elsif volun_disc = 1 then
      sql_str := sql_str || ' and volun_disc = 1';
    elsif involun_disc = 1 then
      sql_str := sql_str || ' and involun_disc = 1';
    elsif volun_recon = 1 then
      sql_str := sql_str || ' and volun_recon = 1';
    elsif involun_recon = 1 then
      sql_str := sql_str || ' and involun_recon = 1';
    elsif no_install = 1 then
      sql_str := sql_str || ' and no_install = 1';

    end if;  

    open company_phone for sql_str using activity_dt, subscriber_cd, company_cd, company_cd, channel_cd, channel_cd;   end;   

end;

Prakash  

-----Original Message-----
Sent: Tuesday, February 19, 2002 4:57 PM To: Multiple recipients of list ORACLE-L

We have Oracle version 8.0.5 and need to use dynamic sql. Through research I know that there is a dbms_sql package that is suppose to support this, but we cannot find an example of what we are needing to do. We have been told that we can do it easily in '8i' but we are not able to upgrade yet.

We are trying to populate a reference cursor via a procedure with a select statement. Has anyone done this and if so can you furnish an example? I may need to tell more about what we are doing and if so please tell me.

Thank you,

Laura

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Bala, Prakash
  INET: prakash.bala_at_cingular.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Feb 20 2002 - 08:43:22 CST