Software Applications Programming Errors and solutions.

This is a Example of dynamic SQL. You can wrtie following code to check how dynamic sql works. column1 := 'custno'; column2 := 'custname'; t_table1 := 'cust_test'; t_table2 := 'cust'; testval1 cust.custno%type; t_valeu2 cust.custname%type; t_resultestval1 cust.custno%type; t_resultestval2 cust.custname%type; execsqlcodetest := 'select '||column1||','||column2||' from '||table2||' where ('||column1||','||column2||') in (select '||column1||','||column2||' from '||table1|| ' where '||column1||' like %:b1% and '||column2||' like %:b2%)'; execute immediate execsqlcodetest using testval1, testval2 into t_resultestval1, t_resultestval2;

finding null values columns in the table using sql  declare L_STMT VARCHAR2(32767) := 'SELECT COUNT(1) FROM #1 WHERE #2 IS NULL'; NULL_CNT NUMBER; TBL_NAME VARCHAR2(30) := 'XYZ'; begin FOR I IN (SELECT COLUMN_NAME FROM USER_TABLES WHERE TABLE_NAME = TBL_NAME ORDER BY COLUMN_ID) LOOP L_STMT := REPLACE(L_STMT, '#1',TBL_NAME); L_STMT := REPLACE(L_STMT,'#2',I.COLUMN_NAME); EXECUTE IMMEDIATE L_STMT INTO NULL_CNT; DBMS_OUTPUT.PUT_LINE ('COLUMN OF ' || TBL_NAME ||' : ' ||' NULL COUNT FOR COLUMN '|| I.COLUMN_NAME ||' IS : ' || NULL_CNT; END LOOP; end;