Assign SELECT to variable in SQL-Commander from DBVisualizer

Question

I need to assign a select to a local variable. I already googled a lot and came to the following solution (which is not working, so it's not really a solution):

select @identId = (select ident_id FROM tablename WHERE number='0000001670');

I am getting the following error:

 [SELECT - 0 row(s), 0.000 secs]  [Error Code: 936, SQL State: 42000]  ORA-00936: missing expression.

A SET does not do the trick either. I tried as well DEFINE, but without any success as well. I need to assign this select to a variable, because i am going to delete this table and i need the value later on. On the other hand does it reduce my code duplication.

Does it have to look something like this:

select ident_id as identId FROM zam_partnerdublette WHERE pa_nummer='0000001670';

But then I can't use the variable later. I tried:

delete from tablename where ident_id=@identId;
delete from tablename  where ident_id=&identId;
delete from tablename  where ident_id=identId;

But all of them resulted in different errors:

[DELETE - 0 row(s), 0.000 secs]  [Error Code: 936, SQL State: 42000]  ORA-00936: missing expression
[DELETE - 0 row(s), 0.000 secs]  [Error Code: 1008, SQL State: 72000]  ORA-01008: Not every variable has an assigned value
[DELETE - 0 row(s), 0.000 secs]  [Error Code: 904, SQL State: 42000]  ORA-00904: "IDENTID": unknown identifier

Is this orcale or sql server, that error ORA-00936 looks like an oracle error? techonthenet.com/oracle/errors/ora00936.php — Tanner
– Tanner, Commented Aug 13, 2014 at 15:53

Tanner · Accepted Answer · 2014-08-13 16:00:57Z

1

Try this:

SELECT @identId = ident_id FROM tablename WHERE number='0000001670';

That's the SQL Server way, based on the tag you have included on the question. If it is Oracle that you're using, based on the error codes in your post, you would do this:

DECLARE @identId NUMBER;
SELECT ident_id INTO @identId FROM tablename WHERE number='0000001670';

edited Aug 13, 2014 at 16:00

answered Aug 13, 2014 at 15:51

Tanner

22.8k9 gold badges70 silver badges89 bronze badges

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

7 Comments

Chris311 Over a year ago

With select @identId = ident_id FROM tablename WHERE number='0000001670'; i get the following error: [SELECT - 0 row(s), 0.000 secs] [Error Code: 936, SQL State: 42000] ORA-00936: missing expression

Tanner Over a year ago

@Chris311 is it an oracle source? have you tried the second option?

Chris311 Over a year ago

I reduced the problem and only executed the first line. DECLARE @identId varchar2(12); results in error: [DECLARE - 0 row(s), 0.000 secs] [Error Code: 6550, SQL State: 65000] ORA-06550: Row 1, Column 9: PLS-00103: Found symbol "@" which is expected as one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior symbol "@" was ignored. ORA-06550: Row 1, Column 29: PLS-00103: Found symbol "end-of-file" expected as one of the following: := ; not null default character

Chris311 Over a year ago

Without '@': results in error: [DECLARE - 0 row(s), 0.000 secs] [Error Code: 6550, SQL State: 65000] ORA-06550: Row 1, Column 28: PLS-00103: Found symbol "end-of-file" expected as one of the following: := ; not null default character

dougp Over a year ago

I couldn't make this work, which aligns with the comments. I'm very confused. If this didn't work, why is it marked as the correct answer?

|

Taersious · Accepted Answer · 2022-09-01 20:43:01Z

1

DBVisualizer is very particular about its variables. You have to use something like this:

--/
your tsql;
/

It makes it very hard to intermix DBVisualizer variables with SQL variables. See this and this: https://confluence.dbvis.com/display/UG130/Using+DbVisualizer+Variables https://confluence.dbvis.com/display/UG130/Executing+Complex+Statements

answered Sep 1, 2022 at 20:43

Taersious

7891 gold badge9 silver badges20 bronze badges

Collectives™ on Stack Overflow

Assign SELECT to variable in SQL-Commander from DBVisualizer

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related