Hi friends
I have written my first code using dynamic SQL which is as follows
CREATE OR REPLACE PROCEDURE Insrt_Chld_Dtl AS
CURSOR_ID INTEGER;
V_FORMAT_ID NUMBER(20):=1;
COL_NM VARCHAR2(50);
VLS VARCHAR2(50);
V_COL_NO VARCHAR2(20);
SEARCH_STRING VARCHAR2(20);
--ID VARCHAR2(100);
PRNT_VL VARCHAR2(100);
COL_CNT INTEGER;
V_SQL VARCHAR2(200);
CURSOR FETCH_MST_DTLS(V_FORMAT_ID VARCHAR2,V_COL VARCHAR2) IS
SELECT WHETHER_COMBO, ABV, DESCRIPTION, COL_NO
FROM TCL_FIELD_MASTER
WHERE FORMAT_ID = V_FORMAT_ID
AND COL_NO = V_COL;
REC_FETCH_MST_DTLS FETCH_MST_DTLS%ROWTYPE;
CURSOR CUR_COL_CNT IS SELECT COUNT(*) INTO COL_CNT FROM USER_TAB_COLUMNS WHERE table_name='F2_CLEANED';
CURSOR CUR_COLMN_NM IS SELECT COLUMN_NAME FROM all_tab_columns WHERE table_name='F2_CLEANED';
ELECT_STM VARCHAR2(200);
REC_PRCSSD INTEGER;
CUR_HDL INTEGER;
BEGIN
FOR REC_COLMN_NM IN CUR_COLMN_NM LOOP
COL_NM := REC_COLMN_NM.COLUMN_NAME;
V_SQL := 'SELECT '||COL_NM|| ' FROM F2_CLEANED WHERE SS_ID = ''xx''';
INSERT INTO A_TEMP VALUES(V_SQL);
COMMIT;
EXECUTE IMMEDIATE V_SQL INTO V_COL_NO;
BEGIN
CUR_HDL := dbms_sql.OPEN_CURSOR;
SELECT_STM := ' SELECT DISTINCT '|| COL_NM || ' FROM F2_CLEANED WHERE SS_ID <> ''xx''';
--SELECT_STM := ' SELECT DISTINCT '|| COL_NM || ' FROM F2_CLEANED WHERE SS_ID <> :vl';
INSERT INTO A_TEMP VALUES(SELECT_STM);
COMMIT;
DBMS_SQL.PARSE(CUR_HDL,SELECT_STM,dbms_SQL.native);
--dbms_sql.bind_variable(CUR_HDL, 'vl', 'xx');
--dbms_sql.define_column(CUR_HDL, VLS);
REC_PRCSSD := DBMS_SQL.EXECUTE(CUR_HDL);
DBMS_OUTPUT.PUT_LINE (REC_PRCSSD);
LOOP
IF DBMS_SQL.FETCH_ROWS(CUR_HDL) > 0 THEN
-- fetch columns from the row
dbms_sql.column_value(CUR_HDL,COL_CNT,COL_NM);
ELSE
EXIT;
END IF;
END LOOP;
dbms_sql.close_cursor(CUR_HDL);
END LOOP;
END LOOP;
CLOSE FETCH_MST_DTLS;
END Insrt_Chld_Dtl;The procedure is compiled successfully but when i execute I get the following error
ORA-01007: variable not in select list
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1229
ORA-06512: at "SYS.DBMS_SQL", line 346
ORA-06512: at "WBSETCL_CTAD.INSRT_CHLD_DTL", line 62
ORA-06512: at line 2
I am not really able to figure out the problem . It would be great if I can get some help. I think I have not passed proper parameters for the line containing
dbms_sql.column_value(CUR_HDL,COL_CNT,COL_NM);
Regards
