Writing a stored procedure that accepts a variable
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 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; /
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments