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!

Well, your error has nothing to do with your issue. When you are chaining blocks in the same script, put a / on a new line after each END; — Del
– Del, Commented Aug 16, 2021 at 14:11
Additionally, I think you need to look at your script/scripts again. Many of those commands can/should be done outside of the block so you don't need to use EXECUTE IMMEDIATE. Additionally, check your BEGIN and END like parenthesis. You are declaring V_C_YEAR_YTD outside of the block it is being called in. — Del
– Del, Commented Aug 16, 2021 at 14:15
On top of that, you don't need to COMMIT after a DDL statement (DROP, CREATE, ETC.) not only is there an implicit commit called by Oracle, but it doesn't apply to those changes. — Del
– Del, Commented Aug 16, 2021 at 14:17
Thank you very much Del! I have tried the following but has not worked either: — alexdico
– alexdico, Commented Aug 16, 2021 at 14:18

Del · Accepted Answer · 2021-08-16 14:22:50Z

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.

answered Aug 16, 2021 at 14:22

Del

1,6311 gold badge10 silver badges18 bronze badges

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

Comments

MT0 · Accepted Answer · 2021-08-16 15:35:41Z

You don't need to use dynamic SQL inside a PL/SQL block to create the tables; you can do it using SQL statements:

DROP TABLE A_TEST_TABLE;

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

Note: USER is a reserved word and you CANNOT use it as an unquoted identifier; you will need to find another identifier such as user_name or you will have to use a quoted identifer and refer to it as "USER" everywhere it is used (however, that is bad practice).

Then if you want to use PL/SQL you can use:

DECLARE
  v_c_year_ytd DATE;
BEGIN
  SELECT c_year_ytd
  Into   v_c_year_ytd
  FROM   master_date_automation;

  INSERT INTO a_test_table (datum, user_name, product)
  SELECT datum,
         user_name
         product
  FROM   users_table
  WHERE  datum = v_c_year_ytd
  AND    kontonummer = '00510199065';
END;
/

But it would be much simpler to just use one SQL statement:

INSERT INTO a_test_table (datum, user_name, product)
SELECT datum,
       user_name
       product
FROM   users_table
WHERE  datum = (SELECT c_year_ytd FROM master_date_automation)
AND    kontonummer = '00510199065';

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related