RE: Dynamic SQL

[Bala, Prakash]

Title: Dynamic SQL

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-----
From: Burton, Laura L. [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 19, 2002 4:57 PM
To: Multiple recipients of list ORACLE-L
Subject: Dynamic SQL

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