Q

Calling variable name packages

I want to call variable name packages because the name of the package changes in some states. For example:

 
 if i = 1 then exm_pck.pr_exm(x,x)
 elsif i = 2 then exm_pck.pr_exm(x,x)
 .
 .
 .
The names of the packages and the states are in the a table.
 
 select pck_name , state_name
 into v_pck_name, v_state_name
 from xxx_table
 where state_name = 'XXX'
How can I call the v_pck_name without using a dynamic SQL command?

In your PL/SQL block, you can use a simple IF-THEN-ELSE statement to choose how to call a stored procedure, based on a value you have. To illustrate, look at the following sample code:

BEGIN
  SELECT pck_name, state_name INTO v_pck_name,
v_stat_name
  FROM xxx_table
  WHERE state_name='XXX';
-- Depending on package name, run the appropriate package
  IF (v_pck_name='PKG1') THEN
     exm_pkg1.pr_exam(x,x);
  ELSIF (v_pck_name='PKG2') THEN
     exm_pkg2.pr_exam(x,x);
  ELSE
     DBMS_OUTPUT.PUT_LINE('No valid package');
  END IF;
END;
It should be easy to see how the IF-THEN-ELSE is used to determine which package to use.

For More Information


This was first published in May 2003

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close