CODEWITHSUNDEEP
sqlplsqloracle-sqldeveloper
alexdico
alexdico

Reputation: 37

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

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

Manual Bind Variables Definition

Bind Variables usage inside the create Table select

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

Master Date Automation Table

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!

Upvotes: 0

Views: 738

Answers (2)

MT0
MT0

Reputation: 168096

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';

Upvotes: 1

Del
Del

Reputation: 1599

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.

Upvotes: 1

Related Questions