I want to call variable name packages because the name of the package changes in some states. For example:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.