Oracle SQL - How to define a date bind variable based on a select statement

Question

Using Oracle SQL, I have several SQL Queries with manual bind variables that are used to create a table as follows:

What I would like to do is to change the define anfang to (select c_year_beginning from master_date_automation).

I have tried several combinations of bind variables but could not make it work (edited with DEL comments):

begin
execute immediate 'DROP TABLE A_TEST_TABLE'
end;
/
begin
execute immediate 'create table A_TEST_TABLE (
  user varchar2(100),
  product    varchar2(100),
  datum date)';
end;
/
BEGIN
DECLARE
v_c_year_ytd DATE;

BEGIN
SELECT c_year_ytd Into v_c_year_ytd FROM master_date_automation;

BEGIN
SELECT usr.datum || ','
       || usr.user || ','
       || usr.product
INTO A_TEST_TABLE
FROM   users_table usr
WHERE  usr.datum = v_c_year_ytd
       AND kto.kontonummer = '00510199065';
END;
END;
END;

ERROR message (edited after Del comment):

Error report -
ORA-06550: line 12, column 6:
PLS-00403: expression 'A_TEST_TABLE' cannot be used as an INTO-target of a SELECT/FETCH statement
ORA-06550: line 12, column 21:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 9, column 1:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Would be really thankful if someone could help me out.

Thousand thanks in advance!

Answer 1

So, to sum up my comments from above, you don't need PL/SQL to do this. You could do all of your script with straight SQL:

CREATE TABLE A_TEST_TABLE
(
  user varchar2(100),
  product    varchar2(100),
  datum      date
);

INSERT INTO a_test_table
SELECT usr.user,
       usr.product,
       usr.datum
FROM users_table
WHERE usr.datum = (SELECT c_year_ytd FROM master_date_automation)
AND usr.user= '123456789'

Drop the table if you need to first.