I have the following example code
DECLARE
myRow table%rowtype
myVar table2.column%type
BEGIN
SELECT table.col1, table.col3, table.col4, table2.column
INTO myRow
FROM table
JOIN table2
On table.col6 = table2.col1;
END;
How can I refactor so that it is a valid statement? Can I somehow store the joined column onto myRow or myVar?
Your PL/SQL is valid and acceptable provided:
If table TABLE does not contain exactly 4 columns then you need to select into something else, perhaps just 4 variables:
DECLARE
v_col1 table.col1%type;
v_col3 table.col3%type;
v_col4 table.col4%type;
v_column table2.column%type;
BEGIN
SELECT table.col1, table.col3, table.col4, table2.column
INTO v_col1, v_col3, v_col4, v_column
FROM table
JOIN table2
On table.col6 = table2.col1;
END;
If your query returns more than 1 row you will get a TOO_MANY_ROWS exception; and if it returns no rows you will get a NO_DATA_FOUND exception.