-1

Below is the SQL Server's syntax to select variable as a record

DECLARE @number AS INTEGER;
SET @number = 10;
SELECT @number;

How can I do this in ORACLE?

Thanks in advance.

Regards,
Sagar Nannaware

Sagar Nannaware
  • 11
  • 1

1 Answers1

1

Edited based on comment: One way you can access the variable value assigned by a procedure is through a function again. Example:

CREATE OR REPLACE PROCEDURE your_procedure(out_number OUT number)
    IS
    BEGIN
      out_number:=1;
    END;

function to retrieve the procedure's output 

CREATE OR REPLACE FUNCTION your_function
    RETURN number
AS
    o_param  number;
BEGIN
    o_param := NULL;
    your_procedure(o_param);
    RETURN o_param;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
       return 0; --basically how you want to handle your errors.

END your_function;

Now you can select the output of the procedure 

select your_function from dual;

Useful link how to access an Oracle procedure's OUT parameter when calling it?

If you are trying to create a variable to access anywhere in your application in oracle. You can do it by creating function and calling it from dual.

SQL>create or replace function foo return number
  as
   x number;
   begin
   x:=1;
   return 1;
   end;

Function created.

SQL>select foo from dual;

     FOO
----------
         1

Please check following link for more details [example link] (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388)

Community
  • 1
  • 1
scheruku
  • 11
  • 2
  • First of all, thank you for your reply. I can not use the user defined function to return value, as variable which I have mentioned is output parameter to the stored procedure and assigned through it. So is there any other way? – Sagar Nannaware Jan 10 '14 at 07:11