Can I declare a variable AFTER select in Oracle SQL?

I'm using a proprietary ASPX Query Editor that FORCES me to start every query with SELECT. It will not allow me to send any code to the database where the first word is anything other than SELECT.

So I'm curious, is there a way I can declare and set variables in a select query AFTER the SELECT statement?

declare @var datetime

set @var = (select sysdate from dual)

select @var from dual

Application error: "SQLQuery should start with 'SELECT' keyword."

To be clear, this is an error from the application interface, not an Oracle error.

EDIT: The code snippet above isn't PL/SQL (I don't think) but I used it to show the error.

edited Dec 29 '18 at 7:12

XING

7,4443929

asked Dec 28 '18 at 16:20

sleven

225

1

That doesn't look like PL/SQL to me. Does this editor have it's own language and somehow translates that?

– sticky bit
Dec 28 '18 at 16:23

1

What will your variables be used for? Are you actually trying to run a PL/SQL block within a query, or just avoid repetition of fixed values, or something else? From your previous comment; How does it accept native PL/SQL if it only allows text beginning with 'select' - those two statements seem contradictory?

– Alex Poole
Dec 28 '18 at 16:36

1

If the editor requires you to use SELECT as the first word, then it doesn't accept PL/SQL code, pure and simple. And it won't even allow you to write standard SQL queries using old features that exist in the SQL Standard (and, therefore, in many database products) - for example the WITH clause. Drop that editor and choose something else; SQL Developer, for example, is excellent, and it's free.

– mathguy
Dec 28 '18 at 16:40

1

If it doesn't start with declare or begin then it isn't a PL/SQL block... it doesn't really sound like it allows anything except a simple query. Maybe if you include an example of what you want to do it might help. (And switching to a different client sounds like a good idea, if you can...)

– Alex Poole
Dec 28 '18 at 16:50

1

Put all the "variables" you want to use in a subquery that is cross joined

– Caius Jard
Dec 28 '18 at 16:51

|
show 3 more comments

I'm using a proprietary ASPX Query Editor that FORCES me to start every query with SELECT. It will not allow me to send any code to the database where the first word is anything other than SELECT.

So I'm curious, is there a way I can declare and set variables in a select query AFTER the SELECT statement?

declare @var datetime

set @var = (select sysdate from dual)

select @var from dual

Application error: "SQLQuery should start with 'SELECT' keyword."

To be clear, this is an error from the application interface, not an Oracle error.

EDIT: The code snippet above isn't PL/SQL (I don't think) but I used it to show the error.

edited Dec 29 '18 at 7:12

XING

7,4443929

asked Dec 28 '18 at 16:20

sleven

225

1

That doesn't look like PL/SQL to me. Does this editor have it's own language and somehow translates that?

– sticky bit
Dec 28 '18 at 16:23

1

What will your variables be used for? Are you actually trying to run a PL/SQL block within a query, or just avoid repetition of fixed values, or something else? From your previous comment; How does it accept native PL/SQL if it only allows text beginning with 'select' - those two statements seem contradictory?

– Alex Poole
Dec 28 '18 at 16:36

1

If the editor requires you to use SELECT as the first word, then it doesn't accept PL/SQL code, pure and simple. And it won't even allow you to write standard SQL queries using old features that exist in the SQL Standard (and, therefore, in many database products) - for example the WITH clause. Drop that editor and choose something else; SQL Developer, for example, is excellent, and it's free.

– mathguy
Dec 28 '18 at 16:40

1

If it doesn't start with declare or begin then it isn't a PL/SQL block... it doesn't really sound like it allows anything except a simple query. Maybe if you include an example of what you want to do it might help. (And switching to a different client sounds like a good idea, if you can...)

– Alex Poole
Dec 28 '18 at 16:50

1

Put all the "variables" you want to use in a subquery that is cross joined

– Caius Jard
Dec 28 '18 at 16:51

|
show 3 more comments

I'm using a proprietary ASPX Query Editor that FORCES me to start every query with SELECT. It will not allow me to send any code to the database where the first word is anything other than SELECT.

So I'm curious, is there a way I can declare and set variables in a select query AFTER the SELECT statement?

declare @var datetime

set @var = (select sysdate from dual)

select @var from dual

Application error: "SQLQuery should start with 'SELECT' keyword."

To be clear, this is an error from the application interface, not an Oracle error.

EDIT: The code snippet above isn't PL/SQL (I don't think) but I used it to show the error.

edited Dec 29 '18 at 7:12

XING

7,4443929

asked Dec 28 '18 at 16:20

sleven

225

I'm using a proprietary ASPX Query Editor that FORCES me to start every query with SELECT. It will not allow me to send any code to the database where the first word is anything other than SELECT.

So I'm curious, is there a way I can declare and set variables in a select query AFTER the SELECT statement?

declare @var datetime

set @var = (select sysdate from dual)

select @var from dual

Application error: "SQLQuery should start with 'SELECT' keyword."

To be clear, this is an error from the application interface, not an Oracle error.

EDIT: The code snippet above isn't PL/SQL (I don't think) but I used it to show the error.

sql-server sybase

edited Dec 29 '18 at 7:12

XING

7,4443929

asked Dec 28 '18 at 16:20

sleven

225

edited Dec 29 '18 at 7:12

XING

7,4443929

asked Dec 28 '18 at 16:20

sleven

225

edited Dec 29 '18 at 7:12

XING

7,4443929

edited Dec 29 '18 at 7:12

XING

7,4443929

edited Dec 29 '18 at 7:12

XING

7,4443929

asked Dec 28 '18 at 16:20

sleven

225

asked Dec 28 '18 at 16:20

sleven

225

asked Dec 28 '18 at 16:20

sleven

225

1

That doesn't look like PL/SQL to me. Does this editor have it's own language and somehow translates that?

– sticky bit
Dec 28 '18 at 16:23

1

What will your variables be used for? Are you actually trying to run a PL/SQL block within a query, or just avoid repetition of fixed values, or something else? From your previous comment; How does it accept native PL/SQL if it only allows text beginning with 'select' - those two statements seem contradictory?

– Alex Poole
Dec 28 '18 at 16:36

1

If the editor requires you to use SELECT as the first word, then it doesn't accept PL/SQL code, pure and simple. And it won't even allow you to write standard SQL queries using old features that exist in the SQL Standard (and, therefore, in many database products) - for example the WITH clause. Drop that editor and choose something else; SQL Developer, for example, is excellent, and it's free.

– mathguy
Dec 28 '18 at 16:40

1

If it doesn't start with declare or begin then it isn't a PL/SQL block... it doesn't really sound like it allows anything except a simple query. Maybe if you include an example of what you want to do it might help. (And switching to a different client sounds like a good idea, if you can...)

– Alex Poole
Dec 28 '18 at 16:50

1

Put all the "variables" you want to use in a subquery that is cross joined

– Caius Jard
Dec 28 '18 at 16:51

|
show 3 more comments

1

That doesn't look like PL/SQL to me. Does this editor have it's own language and somehow translates that?

– sticky bit
Dec 28 '18 at 16:23

1

What will your variables be used for? Are you actually trying to run a PL/SQL block within a query, or just avoid repetition of fixed values, or something else? From your previous comment; How does it accept native PL/SQL if it only allows text beginning with 'select' - those two statements seem contradictory?

– Alex Poole
Dec 28 '18 at 16:36

1

If the editor requires you to use SELECT as the first word, then it doesn't accept PL/SQL code, pure and simple. And it won't even allow you to write standard SQL queries using old features that exist in the SQL Standard (and, therefore, in many database products) - for example the WITH clause. Drop that editor and choose something else; SQL Developer, for example, is excellent, and it's free.

– mathguy
Dec 28 '18 at 16:40

1

If it doesn't start with declare or begin then it isn't a PL/SQL block... it doesn't really sound like it allows anything except a simple query. Maybe if you include an example of what you want to do it might help. (And switching to a different client sounds like a good idea, if you can...)

– Alex Poole
Dec 28 '18 at 16:50

1

Put all the "variables" you want to use in a subquery that is cross joined

– Caius Jard
Dec 28 '18 at 16:51

That doesn't look like PL/SQL to me. Does this editor have it's own language and somehow translates that?

– sticky bit
Dec 28 '18 at 16:23

What will your variables be used for? Are you actually trying to run a PL/SQL block within a query, or just avoid repetition of fixed values, or something else? From your previous comment; How does it accept native PL/SQL if it only allows text beginning with 'select' - those two statements seem contradictory?

– Alex Poole
Dec 28 '18 at 16:36

If the editor requires you to use SELECT as the first word, then it doesn't accept PL/SQL code, pure and simple. And it won't even allow you to write standard SQL queries using old features that exist in the SQL Standard (and, therefore, in many database products) - for example the WITH clause. Drop that editor and choose something else; SQL Developer, for example, is excellent, and it's free.

– mathguy
Dec 28 '18 at 16:40

If it doesn't start with declare or begin then it isn't a PL/SQL block... it doesn't really sound like it allows anything except a simple query. Maybe if you include an example of what you want to do it might help. (And switching to a different client sounds like a good idea, if you can...)

– Alex Poole
Dec 28 '18 at 16:50

Put all the "variables" you want to use in a subquery that is cross joined

– Caius Jard
Dec 28 '18 at 16:51

|
show 3 more comments

1 Answer
1

active

oldest

votes

You could potentially define your variables in a nested CTE:

select * from (

  -- CTE to define variables (sort of)

  with cte (some_date) as (

    select date '2018-12-31' from dual

  )

  -- your real query that joins to the CTE and used cte.some_date

  select d.*

  from cte

  cross join dual d

  where sysdate < cte.some_date

);

or more simply in an inline view:

select d.*

from (

  select date '2018-12-31' as some_date from dual

) t

cross join dual d

where sysdate < t.some_date;

Either way, cte or t is a single-row table so cross-joining makes its columns visible, but doesn't multiply the number of rows in the eventual result set (or rather, only multiplies it by 1, which is the same thing).

Whether your 'client' accepts either form is another matter...

answered Dec 28 '18 at 16:55

Alex Poole

130k6101176

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53961379%2fcan-i-declare-a-variable-after-select-in-oracle-sql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

You could potentially define your variables in a nested CTE:

select * from (

  -- CTE to define variables (sort of)

  with cte (some_date) as (

    select date '2018-12-31' from dual

  )

  -- your real query that joins to the CTE and used cte.some_date

  select d.*

  from cte

  cross join dual d

  where sysdate < cte.some_date

);

or more simply in an inline view:

select d.*

from (

  select date '2018-12-31' as some_date from dual

) t

cross join dual d

where sysdate < t.some_date;

Whether your 'client' accepts either form is another matter...

answered Dec 28 '18 at 16:55

Alex Poole

130k6101176

add a comment |

You could potentially define your variables in a nested CTE:

select * from (

  -- CTE to define variables (sort of)

  with cte (some_date) as (

    select date '2018-12-31' from dual

  )

  -- your real query that joins to the CTE and used cte.some_date

  select d.*

  from cte

  cross join dual d

  where sysdate < cte.some_date

);

or more simply in an inline view:

select d.*

from (

  select date '2018-12-31' as some_date from dual

) t

cross join dual d

where sysdate < t.some_date;

Whether your 'client' accepts either form is another matter...

answered Dec 28 '18 at 16:55

Alex Poole

130k6101176

add a comment |

You could potentially define your variables in a nested CTE:

select * from (

  -- CTE to define variables (sort of)

  with cte (some_date) as (

    select date '2018-12-31' from dual

  )

  -- your real query that joins to the CTE and used cte.some_date

  select d.*

  from cte

  cross join dual d

  where sysdate < cte.some_date

);

or more simply in an inline view:

select d.*

from (

  select date '2018-12-31' as some_date from dual

) t

cross join dual d

where sysdate < t.some_date;

Whether your 'client' accepts either form is another matter...

answered Dec 28 '18 at 16:55

Alex Poole

130k6101176

You could potentially define your variables in a nested CTE:

select * from (

  -- CTE to define variables (sort of)

  with cte (some_date) as (

    select date '2018-12-31' from dual

  )

  -- your real query that joins to the CTE and used cte.some_date

  select d.*

  from cte

  cross join dual d

  where sysdate < cte.some_date

);

or more simply in an inline view:

select d.*

from (

  select date '2018-12-31' as some_date from dual

) t

cross join dual d

where sysdate < t.some_date;

Whether your 'client' accepts either form is another matter...

answered Dec 28 '18 at 16:55

Alex Poole

130k6101176

answered Dec 28 '18 at 16:55

Alex Poole

130k6101176

answered Dec 28 '18 at 16:55

Alex Poole

130k6101176

answered Dec 28 '18 at 16:55

Alex Poole

130k6101176

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Bdtjtk