Dynamic View creation using ref cursors in PL/SQL

I have two tables one which stores account list based on country and another table which has
formatting of details based on country and column in the detail table.  We generate a report based on an account number, the detail format code and detail value code.  This report is based on a view which will be different for each account and country that the account has.

This means I will have to  dynamically create a view each time the account list and details table

the list table query is Select a.acct_num, a.country_nam as Country_Name,
                                                                    b.rec_cde as Field1,
                                                                    b.map         as Field1_val
                                    from  acct_lst a,
                                              map b
                                     where a.country_nam=b.country_nam
                                      and  a.country_nam="BRAZIL"  
                                         UNION
 Select a.acct_num, a.country_nam as Country_Name,
                                                                    b.rec_cde as Field1,
                                                                    b.map         as Field1_val
                                    from  acct_lst a,
                                              map b
                                     where a.country_nam=b.country_nam
                                      and  a.country_nam="INDIA"
... and so on

I would like to know how I can create a stored procedure that will let me create a view for all the countries and accounts.  I am using Oracle 10.2.0.4
LVL 1
tangocoderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Christoffer SwanströmPartnerCommented:
I don't quite understand why you need to repeat the same block all over again since the logic is always the same? Why can't you use a static view defined as

 
Select 
  a.acct_num, 
  a.country_nam as Country_Name,                                                        
  b.rec_cde as Field1,
  b.map as Field1_val
from  
  acct_lst a,
  map b
where 
  a.country_nam = b.country_nam

Open in new window


Or do I miss something?

If you really need to generate the view dynamically you can do it in a procedure using a code sometning like this:

 
CREATE ...

  sqlString VARCHAR2(2048);

  viewTemplate VARCHAR2(2048);

BEGIN

  viewTemplate :='
  Select 
    a.acct_num, 
    a.country_nam as Country_Name,
    b.rec_cde as Field1,
    b.map         as Field1_val
  from  
    acct_lst a,
    map b
  where a.country_nam=b.country_nam
  and  a.country_nam = ';

  sqlString := 'CREATE OR REPLACE VIEW ... AS ';

FOR country IN (SELECT DISTINCT country_nam FROM acct_lst) LOOP
  sqlString := sql_String || country.countr_name || ' UNION ';
END LOOP;

-- to remove the last union
  sqlString := SUBSTR(sqlString, 1, LENGTH(sqlString) - 7);

EXECUTE IMMEDIATE sqlString;

END;

Open in new window


0
slightwv (䄆 Netminder) Commented:
I agree that the requirements really don't make sense.

The SQL you posted can be a single select with an IN list.  Also, Oracle doesn't use double quotes for strings.


Select a.acct_num, a.country_nam as Country_Name,
                                                                    b.rec_cde as Field1,
                                                                    b.map         as Field1_val
                                    from  acct_lst a,
                                              map b
                                     where a.country_nam=b.country_nam
                                      and  a.country_nam in ('BRAZIL','INDIA')


Can you post more information about how the report is generated and where ref cursors are used?
0
Jayesh AcharyaTechnichal ConsultantCommented:

create or replace MASTER_COUNTRY_VIEW as
Select a.acct_num
         , a.country_nam as Country_Name
         , b.rec_cde as Field1
         ,b.map         as Field1_val
from  acct_lst a,
          map b
where a.country_nam=b.country_nam
and  a.country_nam in ('BRAZIL' , 'INDIA' ....);

if all the countries you are looking fro are in the same acct_lst table ...
but if you meant that each country is a different acct_lst table then you can do teh following:

create or replace MASTER_COUNTRY_VIEW as
Select a.acct_num
         , a.country_nam as Country_Name
         , b.rec_cde as Field1
         ,b.map         as Field1_val
from  acct_lst a,
          map b
where a.country_nam=b.country_nam
and  a.country_nam = 'BRAZIL'
UNION
Select a.acct_num
         , a.country_nam as Country_Name
         , b.rec_cde as Field1
         ,b.map         as Field1_val
from  acct_lst a,
          map b
where a.country_nam=b.country_nam
and  a.country_nam = 'INDIA'
..
..
..



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.