Q

Writing a stored procedure that accepts a variable

I would like to write a stored procedure that accepts a variable and uses that as the table name in a select statement. What I have below will not compile.

 
CREATE OR REPLACE PROCEDURE USP_STAT_FEATURE_COUNT (
         P_TABLENAME                                 IN  VARCHAR2
        ,P_PPMS                                      IN  VARCHAR2
        ,P_FEATURE_COUNT                        OUT NUMBER
)
IS

/******************************************************************************
   NAME:       USP_STAT_FEATURE_COUNT
   PURPOSE: Count of all drawings in the system ******************************************************************************/

TBL varchar2(50);

BEGIN

TBL := P_TABLENAME;

 SELECT COUNT(OBJECTID) INTO P_FEATURE_COUNT FROM P_TABLENAME WHERE PPMS = P_PPMS;

COMMIT;

END USP_STAT_FEATURE_COUNT;
/
You cannot do this! Why, you ask. Because on one hand you want the SQL statement to be dynamic based on the Table Name and a column value, on the other end you are using an STATIC SQL statement. Also, you only need COMMIT if you want to make any database changes and not if you are SELECTing data into a PL/SQL variable.

You need to use Native dynamic SQL (or DBMS_SQL if it is more complicated) to achieve your objectives.

Okay here is a version that will compile.

 
CREATE OR REPLACE PROCEDURE USP_STAT_FEATURE_COUNT (
         P_TABLENAME                                 IN  VARCHAR2
        ,P_PPMS                                      IN  VARCHAR2
        ,P_FEATURE_COUNT                        OUT NUMBER
)
IS

/******************************************************************************
   NAME:       USP_STAT_FEATURE_COUNT
   PURPOSE: Count of all drawings in the system ******************************************************************************/

--TBL varchar2(50);

BEGIN

-- TBL := P_TABLENAME;  This statement is unnecessary

 --  You had P_TABLE_NAME  Should be consistent with the
--  parameter name which does not have an underscore 
--  character between "TABLE" and "NAME"
 
     EXECUTE IMMEDIATE ' SELECT COUNT(OBJECTID) FROM ' ||  P_TABLENAME  || ' WHERE PPMS = ' ||  P_PPMS
 INTO P_FEATURE_COUNT;

--  COMMIT  You do not need to commit; 

END USP_STAT_FEATURE_COUNT;
/ 
This was first published in January 2005

Dig deeper on Using Oracle PL-SQL

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close