Having trouble with variables in oracle sql developer

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 coul开发者_JAVA技巧d 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;

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

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;

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

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;