Having trouble with variables in oracle sql developer

Question

I want to mention that I'm new using sql developer and I'm trying to fill a temporary table with two values that are found in two variables, but i'm not able to get it right. I'm hoping that maybe one of you guys could have a better idea. This is what I've tried:

DECLARE
     x NUMBER;
     y NUMBER;
BEGIN
EXEC SELECT COUNT(*) INTO :x FROM (select * from view2 where restituita=1);
EXEC SELECT COUNT(*) INTO :y FROM (select * from view2 where restituita=0);
create global temporary table dateRest (rest number,nerest number);
insert into dateRest values(x,y);
select * from dateRest;
END;

OMG Ponies · Accepted Answer · 2010-12-21 20:23:13Z

3

Based on the final results, the query is over complicated -- it only needs to be:

SELECT SUM(CASE WHEN restituita = 1 THEN 1 ELSE 0 END),
       SUM(CASE WHEN restituita = 0 THEN 1 ELSE 0 END)
  FROM view2;

EXEC is TSQL syntax -- EXECUTE is PLSQL/Oracle syntax, but neither is necessary
The colon indicates a bind variable, which is used for supplying a value to a query -- not for retrieving one
the creation of the global temp table needs to be run as dynamic SQL when in a stored procedure or anonymous block -- really don't recommend the use of temp tables if at all possible. But the temp table wouldn't be visible for the insertion, much less selection from

edited Dec 21, 2010 at 20:23

answered Dec 21, 2010 at 18:03

OMG Ponies

334k85 gold badges536 silver badges509 bronze badges

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

4 Comments

Codo Over a year ago

I doubt this will work. I even doubt any of the three statements will work within a PL/SQL block.

Stephanie Page Over a year ago

Agree with Codo. You have to have a destination for SELECT in PL/SQL. either you have to open the select as a cursor, or use INTO variables if the SQL returns one and only one row.

Stephanie Page Over a year ago

Error report: ORA-06550: line 3, column 3: 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:

Alex Poole Over a year ago

Surely OMG is replacing the entire anonymous block, not just the selects within it? Assuming dateRest is only being created for this and doesn't actually need to exist elsewhere.

Codo · Accepted Answer · 2010-12-21 18:11:21Z

I'm afraid you have more errors and problems than lines in your piece of code. It's a mixup of SQL statements, PL/SQL, and variable binding of other languages.

Since you use a declare/begin/end block, you're in the PL/SQL world. In the PL/SQL world:

You cannot use EXEC, because it's a short hand for begin/end if you're not in PL/SQL.
You don't need to use colons for variable binding
You cannot execute a SELECT statement without putting the result somewhere
You cannot execute DDL statements (CREATE TABLE...) directly.
You usually cannot refer to tables that do not exist when the begin/end block is started.

And don't forget to commit.

So the solution probably is: don't use PL/SQL.

create global temporary table dateRest (rest number,nerest number);
insert into dateRest(x, y)
  select (select count(*) from view2 where restituita=1),
    (select count(*) from view2 where restituita=0)
  from dual;
commit;

GolezTrol · Accepted Answer · 2010-12-21 17:58:16Z

0

You should lose the : for parameters within (anonymous) procedures.

[edit]

No, I'm wrong. The : are not needed when you don't use EXEC. I think your problem lies in creating the table. This is not possible in this way, because PLSQL doesn't support the CREATE TABLE statement. You should use:

EXECUTE IMMEDIATE 'CREATE TABLE ....';

answered Dec 21, 2010 at 17:58

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Comments

Stephanie Page · Accepted Answer · 2010-12-21 20:08:09Z

0

The corrected OMG anonymous block would be:

DECLARE
   var1 Number;
   var2 Number;
BEGIN
SELECT SUM(CASE WHEN restituita = 1 THEN 1 ELSE 0 END),
     SUM(CASE WHEN restituita = 0 THEN 1 ELSE 0 END)
   INTO var1, var2
FROM view2;

END;

answered Dec 21, 2010 at 20:08

community wiki

Stephanie Page

Collectives™ on Stack Overflow

Having trouble with variables in oracle sql developer

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related