| DBMS_SQL error in a procedure [message #369856] |
Wed, 25 October 2000 16:22 |
merw
Messages: 2
Registered: October 2000
|
Junior Member |
|
|
I am getting the following error when I execute the following procedure , but it compiles well.
I have given below the procedure also. Can anyone help me out by finding the problem? Thanx in advance
SQL> @getmaxvalue;
Procedure created.
SQL> execute getmaxvalue(1,1);
BEGIN getmaxvalue(1,1); END;
*
ERROR at line 1:
ORA-00904: invalid column name
ORA-06512: at "SYS.DBMS_SYS_SQL", line 782
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "STG.GETMAXVALUE", line 35
ORA-06512: at line 1
CREATE OR REPLACE PROCEDURE GetMaxValue (
p_table_id NUMBER,
p_column_id NUMBER) AS
v_CursorID INTEGER;
v_SelectStmt VARCHAR2(500);
v_Dummy INTEGER;
maxp VARCHAR2(50);
p_table VARCHAR2(50);
p_column VARCHAR2(50);
BEGIN
SELECT STRCTRSYSTMNM
into p_table
FROM structure
where STRCTRID = p_table_id;
SELECT FLDSYSTMNM
into p_column
FROM FIELD
where FLDID = p_column_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.COLUMN_VALUE(v_CursorID, 1, maxp,column_error,actual_length);
UPDATE FIELD set
FLDMXMSTRNG = maxp
where STRCTRID = p_table_id and
FLDID = p_column_id
;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
