Using version 18.2 of SQL Developer, Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production, on Windows.
In my continuing effort to move more and more of what I do into SQL Developer I've stumbled across something that doesn't seem to work. I have the code snippet below, this is a demo of creating SQL Baselines using command line commands. Running this in SQL Plus or SQLci it runs as expected and the plan gets loaded without being prompted for the SQL_ID (the substitution parameter PSQLID). This happens within the little PLSQL block that calls DBMS_SPM.load_plans_from_cursor_cache and the bottom of the code below. However when I run this code within SQL Dev, I'm prompted for the SQL_ID (the substitution parameter PSQLID). It appears that SQL Dev is unaware that the parameter was populated with the select just above the PLSQL block. Or that the select doesn't work within SQL Dev to populated the substitution parameter, I'm not sure which it is.
If this is "how it works" in SQL Dev, I can work around it, but it's a bummer. Please test this to see if you have the same issue, You could use any select in place of the on I'm grabbing the plan on (the select * from employees statement), that shouldn't make a difference. Thanks and have a great day.
-- now to capture a base line
-- turn off serveroutput to get the right SQL_ID later
set feedback on
set serveroutput off
-- use the following command to run a select on employees
select * /*+ qb_name(emp_main) */ from employees
where first_name = 'David';
-- these commands will get the SQL_ID of the statement just run
COLUMN PREV_SQL_ID NEW_VALUE PSQLID
COLUMN PREV_CHILD_NUMBER NEW_VALUE PCHILDNO
SELECT prev_sql_id,prev_child_number FROM v$session WHERE audsid = userenv('sessionid');
-- turn on serveroutput to see the number of plans loaded.
set serveroutput on
-- this block will load the plan based on the SQL_ID just captured
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '&PSQLID');
dbms_output.put_line('Plans loaded: '||l_plans_loaded);
END;
/