Ask the Expert

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;
/

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: