Oracle SQL Developer - Using Variables in Insert/Select

Question

Using Oracle SQL Developer. I have the following sample working:

DECLARE
    v_next_TransId INTEGER; -- declare
BEGIN
    select (max(Transaction_ID)+1) into v_next_TransId from xxpos.pos_transactions;
    dbms_output.Put_line(v_next_TransId); --display

END;

Now I want to use that variable in an insert and/or select, I tried with and without a : prefix. The sample below gives me the ORA-01008: not all variables found.

DECLARE
    v_next_TransId INTEGER; -- declare 
BEGIN
    select (max(Transaction_ID)+1) into v_next_TransId from xxpos.pos_transactions;
    dbms_output.Put_line(v_next_TransId); --display

    -- insert new row will go here 
    -- commit will go here 
    -- now verify the insert worked okay 
    --select * from xxpos.pos_transactions where Transaction_ID = ( select max(Transaction_ID) from xxpos.pos_transactions )
    select * from xxpos.pos_transactions where Transaction_ID = :v_next_TransId;

END;

Without the : in front of the variable, I get this syntax error:

Versions:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

Ahamed Ahamed · Accepted Answer · 2019-04-11T03:09:50

In PLSQL code block, columns of select statement have to be assigned to variables. You have to use select into clause your plsql code block as shown below.

DECLARE
    v_next_TransId INTEGER; -- declare 
    l_pos_transactions xxpos.pos_transactions%ROWTYPE;
BEGIN
    select (max(Transaction_ID)+1) into v_next_TransId from xxpos.pos_transactions;
    dbms_output.Put_line(v_next_TransId); --display

    -- insert new row will go here 
    -- commit will go here 
    -- now verify the insert worked okay 
    --select * from xxpos.pos_transactions where Transaction_ID = ( select max(Transaction_ID) from xxpos.pos_transactions )
    select * into l_pos_transactions
from xxpos.pos_transactions where Transaction_ID = :v_next_TransId;

  DBMS_OUTPUT.PUT_LINE(l_pos_transactions.column1|| ',' || l_pos_transactions.column2);

END;

Oracle SQL Developer - Using Variables in Insert/Select

1 Answers