1

I want to write a single string of multiple SQL statements, to be sent through ODBC, which has constants stated as 'variables' to improve legibility/maintenance. I believe I need to use the DECLARE block. I do not want to write a procedure or a function, it's an ad-hoc query.

The following test CTE works fine in isolation (without a begin/end block):

WITH X AS ( SELECT 'HELLO' from DUAL ) SELECT * FROM X;

But when I run this

declare
badgerId CHAR(32); /*todo, use me later, a few times within multiple CTEs*/
begin
WITH X AS ( SELECT 'HELLO' from DUAL ) SELECT * FROM X;
end;

I get the error:

Error starting at line 2 in command:
declare
badgerId CHAR(32); /*todo, use me later*/
begin
WITH X AS ( SELECT 'HELLO' from DUAL ) SELECT * FROM X;
end;
Error report:
ORA-06550: line 4, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

What is this gibberish? In SQL Server I can just type.

DECLARE @badgerId CHAR(32);

at any time in the SQL statement, which is awesome. What's the Oracle equivalent?

Cheers!

GilesDMiddleton
  • 2,279
  • 22
  • 31
  • You are not me, clearly! Next time try answering the question instead of patronizing the person asking. – GilesDMiddleton Nov 13 '14 at 16:13
  • 1
    [This might be helpful](http://stackoverflow.com/a/3400602/266304). Depends if you're really getting a single value back, or multiple rows, in which case you might want a ref cursor. – Alex Poole Nov 13 '14 at 16:26

2 Answers2

2

You're getting this error because you are selecting the data but not assigning it to the variable declared (badgerId). The below should work,

declare
badgerId CHAR(32); /*todo, use me later, a few times within multiple CTEs*/
begin
WITH X AS ( SELECT 'HELLO' from DUAL ) SELECT * into badgerId FROM X;
end;
demongolem
  • 9,474
  • 36
  • 90
  • 105
Shankar
  • 879
  • 8
  • 15
0

Lots of confusion here. The problem described eight years ago is that cte definitions apparently can not be done inside of a BEGIN-END block. That does appear to be an actual restriction/limitation. I've searched for a statement somewhere to that effect and haven't found one, but it's what I observe. I've reformatted the code and put it in this script twice, once inside a BE block and once not. If you run just the top 7 lines it works fine; if you try to run the whole thing or just the bottom section it does not. I'm running Oracle PL/SQL version 19 enterprise.

Sample code and resulting error

Karl Hoaglund
  • 603
  • 10
  • 10
  • Hi Karl - I think, it wasn't so much the BEGIN/END being a problem, but that I was surprised that Oracle gave such a weird error when it could have simply ignored the DECLARE because it was unused. Or told me that the unused declare of badgerId was the reason? It was long long ago :-) – GilesDMiddleton Dec 06 '22 at 17:58