-
I am trying to use dynamic sql for getting the max value of any(I/P) column from any table(I/P) dynamically.
the following procedure compiles without any problems.But while executing it gives 'invalid column error'. Can You pl help me out??
CREATE OR REPLACE PROCEDURE GetMaxValue (
p_table_id VARCHAR2(50),
p_column_id VARCHAR2(50)) AS
v_CursorID INTEGER;
v_SelectStmt VARCHAR2(500);
v_Dummy INTEGER;
maxp VARCHAR2(500);
p_table VARCHAR2(50);
p_column VARCHAR2(50);
BEGIN
select ---- into p_column
from --- where --- = p_column_id;
select ---- into p_table
from --- where --- = p_table_id;
v_CursorID := DBMS_SQL.OPEN_CURSOR;
v_SelectStmt := 'SELECT max( '||p_column||' ) '||' FROM '||p_table;
DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,maxp,50);
v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
LOOP
IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, maxp);
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
END LOOP;
UPDATE FIELD set
FLDMXMSTRNG = maxp
where STRCTRSYSTMNM = p_table and
FLDSYSTMNM = p_column
;
END;
/
-
Check the column name (p_column) retrieved from the select into statement.
-
First, the passing parameters:
.................
(p_table_id VARCHAR2(50),
p_column_id VARCHAR2(50)) will give warning error( not fatal error) while compiling the procedure, so they should be:
.................
(p_table_id VARCHAR2,
p_column_id VARCHAR2)
Second, the select....... into ........ statements will return more than one rows that satisfy the condition probably, but I'm not sure this is the exact reason for the error occurred, I don't know what are the "---------" means in the two "select into" statements.
Take care