ORACLE select into variable

Question

This should be easy as answers from here and here are suggesting. But, I am receiving an error:

ORA-00905: missing keyword 00905. 00000 - "missing keyword" *Cause:
*Action: Error at Line: 7 Column: 36

My code:

vEXTERNAL_ACCOUNT_ID VARCHAR2(20);

  (SELECT EXTERNAL_ACCOUNT_ID INTO vEXTERNAL_ACCOUNT_ID FROM TOF_ORDER_DATA WHERE ID = 
 (SELECT TOF_ORDER_DATA_ID FROM TOF_WFI WHERE WFI_ID = 3466444));

All I want is to set vEXTERNAL_ACCOUNT_ID from query above (it returns only one result). I don't understand what am I doing wrong here?

The parentheses around the select are completely useless.

user330315
– user330315

2020-04-17 06:28:54 +00:00
Commented Apr 17, 2020 at 6:28 — user330315
– user330315, Commented Apr 17, 2020 at 6:28

Littlefoot · Accepted Answer · 2020-04-17 06:21:42Z

2

This is PL/SQL, so:

declare
  vexternal_account_id varchar2(20);
begin  
  select external_account_id 
    into vexternal_account_id 
    from tof_order_data 
    where id = (select tof_order_data_id 
                from tof_wfi 
                where wfi_id = 3466444
               );

  dbms_output.put_line('Value = ' || vexternal_account_id);
end;

declare the variable
use begin-end keywords
remove outmost brackets
in order to display value returned by query, use dbms_output.put_line
- don't forget to set serveroutput on in tool you use!

edited Apr 17, 2020 at 6:21

answered Apr 17, 2020 at 6:18

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

FrenkyB Over a year ago

That works. How to check for vexternal_account_id value? SELECT vexternal_account_id is throwing error.

Littlefoot Over a year ago

Use dbms_output.put_line; I edited code I previously posted - have a look.

FrenkyB Over a year ago

I don't see any output. I am using sql developer. Sorry, it's ok - I've forgot to put serveroutput on.

Littlefoot Over a year ago

As I said: execute set serveroutput on first to enable output. How? Just like that - copy that line into SQL Developer and execute it. Then run your PL/SQL code.

Collectives™ on Stack Overflow

ORACLE select into variable

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related