In order to select a value / a result from a query into a variable in PL SQL, you can use the following syntax.

Source Code

Procedure Unit_Test_refresh_Rule_Details Is
	actual Number(3) := 0;
Begin
	/* Get the count of processed rules from staging table. */
	Select count(*) into actual from sod_cat_ent_rule_stage;
	dbms_output.put_line( actual );
End; 

In case you have to use dynamic sql, meaning you will have to build a query string at runtime because you do not know a table name or other parameters yet, you have to apply execute immediate ... into.

However there are some tricks to apply before it will work.

query_string := q'{select count(*) from }' || count_names(r);
execute immediate query_string into actual;

In order to escape quotes, if you have to apply an insert statement you can use q'{ ... }'.

After building a dynamic sql query string you can use execute immediate ... into in order to achieve the same as select ... ino ...