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