Pl/SQL display results of select statement

oracle, plsql

Solution

Assuming you are using SQL*Plus, the simplest option is probably to do something like

Set ServerOutput on size 100000;
variable rc refcursor;
declare
  countTab number := 0;
  countCol number := 0;
  currDate varchar2(30);
  scale number := 0;
Begin
  select count(*) 
    into countCol 
    from USER_TAB_COLUMNS 
   where TABLE_NAME = 'EVAPP_INTERFACE' 
     and COLUMN_NAME = 'TARGET_AMNT_LTV_NUM' 
     and DATA_SCALE is null; 
  IF  (countCol <> 0) then   
    DBMS_OUTPUT.put_line('  EVAPP_INTERFACE.TARGET_AMNT_LTV_NUM values begin'); 
    open :rc 
     FOR 'select APPSEQNO, TARGET_AMNT_LTV_NUM ' ||
         '  from evapp_interface ' ||
         ' where TARGET_AMNT_LTV_NUM > 999999999999';
  END IF;
END;
/

PRINT rc;

If you want to display the result from PL/SQL, you'd need to open the cursor, fetch the results into local variables, and then do something with the local variables such as writing them to `DBMS_OUTPUT`.

Problem

``` Set ServerOutput on size 100000; declare countTab number := 0; countCol number := 0; currDate varchar2(30); scale number := 0; Begin select count(*) into countCol from USER_TAB_COLUMNS where TABLE_NAME = 'EVAPP_INTERFACE' and COLUMN_NAME = 'TARGET_AMNT_LTV_NUM' and DATA_SCALE is null; IF (countCol <> 0) then DBMS_OUTPUT.put_line(' EVAPP_INTERFACE.TARGET_AMNT_LTV_NUM values begin'); execute immediate 'select APPSEQNO, TARGET_AMNT_LTV_NUM from evapp_interface where TARGET_AMNT_LTV_NUM > 999999999999'; END IF; END; \ ``` I am trying to display the results of the select query. I tried running just the select statements as is, but it gives an exception saying it can't find the columns mentioned. So, I tried putting the table name infront of the columns, and it complained that I needed to use `INTO` , and I used that as well, but still it did not like the syntax.

Original source