Pass table name as param showing error

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • KarthikaKKD
    New Member
    • Feb 2013
    • 1

    Pass table name as param showing error

    Im passing table name & column name which im selecting from cursor.But its inserting correctly and also showing error like cursor is not open .

    my proc is as:
    Code:
    CREATE OR REPLACE PROCEDURE TEST_BUILDINGCODE_PROC (IN v_AppId_Ver INTEGER)
    BEGIN
    	
    	DECLARE v_ObjId,v_LayerName,v_TableName,v_ColumnName varchar(30);
    	DECLARE v_CodeNo,v_DataCount integer;
    	DECLARE v_Statement,v_Schema,v_Counter varchar(200);
    	DECLARE v_stmt STATEMENT;
    	
    	DECLARE  C1 CURSOR WITH RETURN FOR
    	SELECT IDVER,LAYERNAME,CODENO,TABLENAME,COLUMNNAME FROM MPD2021.DA_TEST_LAYERCODE WHERE IDVER=v_AppId_Ver;
    	
    	SET v_Schema='MPD2021';
    	
    	SET v_DataCount=(SELECT COUNT(*) FROM MPD2021.DA_TEST_LAYERCODE WHERE IDVER=v_AppId_Ver);
    	SET v_Counter=1;
    	OPEN C1;
    	WHILE(v_Counter<=v_DataCount)
    	DO
    		
    		FETCH C1 INTO v_ObjId,v_LayerName,v_CodeNo,v_TableName,v_ColumnName;
    		--SET v_Schema='KKD';
    		SET v_Statement ='INSERT  INTO  ' || v_Schema || '. '|| v_TableName ||'(IDVERSION,'||v_ColumnName||') VALUES('||v_AppId_Ver||','||v_CodeNo||')';
    		--set v_Statement='INSERT INTO MPD2021.' concat  v_Schema concat ' VALUES ( 'concat ' 4 ' concat ')';
    		
    		--SET v_Statement = 'INSERT  INTO  ' || v_Schema || '. '|| v_TableName ||'('||v_ColumnName||') VALUES('||3||')';
    		PREPARE v_stmt FROM v_Statement;	
    		EXECUTE v_stmt;
    		COMMIT;
    
    		--INSERT INTO TabName (IDVERSION,OBJID,LAYER,v_ColumnName) VALUES(v_AppId_Ver,v_ObjId,v_LayerName,v_CodeNo);
    		
    --	SET	v_DataCount=v_DataCount-1;
    	SET v_Counter = v_Counter + 1;	
    	END WHILE;
    	CLOSE C1;
    END
    Last edited by Rabbit; Feb 13 '13, 05:36 AM. Reason: Please use code tags when posting code.
Working...