Run sql code with variables in Oracle SQL Developer code window

Question

I'm writing code using Oracle SQL Developer. I have a simple select statement that works:

    SELECT
      CFS.CAE_SEC_ID,
      CFS.FM_SEC_CODE,
      CFS.LAST_USER_ID,
      case 
          when 1 = 1 then
            sl.usbank_to_edit
          else
            case 
              when 'ENT\CB174' = CFS.last_user_id then
                sl.owner_to_edit
              else
                sl.to_edit
            end
        end canEdit
    FROM
    CAEDBO.CAE_FOF_SECURITY CFS
    INNER JOIN caedbo.CAE_DATA_SET_ELEMENT CDSE_STAT 
        ON (CDSE_STAT.DATA_SET_ELEMENT_ID = CFS.APPR_STATUS)
    INNER JOIN caedbo.CAE_STATE_LOOKUP sl 
        ON (sl.object_state =  CDSE_STAT.data_set_element_id)
   where 
      cfs.CAE_SEC_ID in (3741, 3744, 3748, 3752);

However I want to add some variables to it and reference the variables in the statement, similar to below, and also run it in the code window. How do I do this correctly?

DECLARE 
  p_USBank_n NUMBER;
  p_user_id_c VARCHAR2(20);
BEGIN  
  p_USBank_n := 1; 
  p_user_id_c := 'ENT\CB174';

    SELECT
      CFS.CAE_SEC_ID,
      CFS.FM_SEC_CODE,
      CFS.LAST_USER_ID,
      case 
          when p_USBank_n = 1 then
            sl.usbank_to_edit
          else
            case 
              when p_user_id_c = CFS.last_user_id then
                sl.owner_to_edit
              else
                sl.to_edit
            end
        end canEdit
    FROM
    CAEDBO.CAE_FOF_SECURITY CFS
    INNER JOIN caedbo.CAE_DATA_SET_ELEMENT CDSE_STAT 
        ON (CDSE_STAT.DATA_SET_ELEMENT_ID = CFS.APPR_STATUS)
    INNER JOIN caedbo.CAE_STATE_LOOKUP sl 
        ON (sl.object_state =  CDSE_STAT.data_set_element_id)
   where 
      cfs.CAE_SEC_ID in (3741, 3744, 3748, 3752);
END;

When I run this in a sql window I get the message below:

Error report:
ORA-06550: line 8, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Solution 1

if I am reading this correctly, I think you are looking for Oracle Substitution variables.

this will prompt you each time to input the values, by using &val it will prompt you @ runtime

  SELECT
      CFS.CAE_SEC_ID,
      CFS.FM_SEC_CODE,
      CFS.LAST_USER_ID,
      CASE 
          when &p_USBank_n = 1 then
            sl.usbank_to_edit
          else
            CASE 
              when '&p_user_id_c' = CFS.last_user_id then
                sl.owner_to_edit
              else
                sl.to_edit
            end
        end canEdit
    FROM
    CAEDBO.CAE_FOF_SECURITY CFS
    INNER JOIN caedbo.CAE_DATA_SET_ELEMENT CDSE_STAT 
        ON (CDSE_STAT.DATA_SET_ELEMENT_ID = CFS.APPR_STATUS)
    INNER JOIN caedbo.CAE_STATE_LOOKUP sl 
        ON (sl.object_state =  CDSE_STAT.data_set_element_id)
   where 
      CFS.CAE_SEC_ID IN (3741, 3744, 3748, 3752);

change it to &&var to have it retain the value, then use

UNDEFINE var 

to clear it

Now you can set these at the top of the page (thus avoiding the prompt) by utilizing DEFINE as such

DEFINE XYZ = 5
DEFINE AAA = to_date('10/10/2010','mm/dd/rrrr')
DEFINE textString = AaBbCc

SELECT &&XYZ b, &&AAA a, '&&textString' textString
  from dual ;

  B                      A                         TEXTSTRING 
  ---------------------- ------------------------- ---------- 
  5                      10.OCT.2010 00:00         AaBbCc     
  --typing define will show you all the "defined" values 
define

  DEFINE XYZ = "5"
  DEFINE TEXTSTRING = "AaBbCc"
  DEFINE AAA = "to_date('10/10/2010','mm/dd/rrrr')"

the double ampersand will 'retain' the value until you UNDEFINE it (see above) or redefine it.

Solution 2

The error message explains the problem; inside a PL/SQL block you have to select INTO something, and you can't just dump the results of a query to screen as you can with plain SQL. (There are ways to do it but probably overly complicated for what it looks like you're trying to achieve here).

If you don't want to use substitution variables as @Harrison suggested, you can use bind variables which you define at the start in a separate anonymous block. You can then refer to the bind variable in the plain SQL:

var p_usbank_n number;
var p_user_id_c varchar2(20);

exec :p_usbank_n := 1;
exec :p_user_id_c := 'ENT\CB174';

select
    cfs.cae_sec_id,
    cfs.fm_sec_code,
    cfs.last_user_id,
    case 
        when 1 = :p_usbank_n then
            sl.usbank_to_edit
        when cfs.last_user_id = :p_user_id_c then
            sl.owner_to_edit
        else
            sl.to_edit
    end as canEdit
from
    caedbo.cae_fof_security cfs
inner join caedbo.cae_data_set_element cdse_stat 
    on (cdse_stat.data_set_element_id = cfs.appr_status)
inner join caedbo.cae_state_lookup sl 
    on (sl.object_state =  cdse_stat.data_set_element_id)
where 
    cfs.cae_sec_id in (3741, 3744, 3748, 3752);