0

I am an Oracle novice and just started to use it a few days ago.

I want to convert this procedure from SQL Server to Oracle. Essensially I want to call a procedure that returns nothing... therefore no change to my application.

create PROCEDURE [dbo].[LC_UNBOOKTRADE]
    @EXEID VARCHAR(50),

AS
SELECT ''

GO
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
user3706392
  • 1
  • 1

1 Answers1

2

First, a procedure in Oracle does not and cannot return anything. A function returns something. A procedure can have an out parameter.

Second, unless you're using 12.1, procedures in Oracle can't implicitly return a cursor by executing a select statement like they can in SQL Server. It would be much more common in Oracle to use a view for this sort of thing.

Third, in Oracle, the empty string is NULL so if you have code that expects a non-NULL empty string to be returned, you're going to have to change that.

Potentially, something like this would be the closest equivalent code

CREATE OR REPLACE PROCEDURE lc_unbookTrade( 
    p_exeid IN VARCHAR2,
    p_rc   OUT SYS_REFCURSOR )
AS
BEGIN
  OPEN p_rc
   FOR SELECT null
         FROM dual;
END;

Depending on the code you're using to call your procedure, though, this may not be a transparent change.

Community
  • 1
  • 1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thank you this compliled fine but I want to be able to call the procedure and return a null value i.e. EXEC lc_unbookTrade (p_exeid); – user3706392 Jun 18 '14 at 17:31
  • 1
    @user3706392 - Justin's said you can't return anything from a procedure. Maybe if you add the code you're currently using to call your SQL Server procedure to the question, it will indicate whether there is a transparent Oracle equivalent. Otherwise you're asking for the impossible. – Alex Poole Jun 18 '14 at 17:46
  • The procedure is the same as above but what I want is when you run Execute lc_unbookTrade (exeid). The result to be an empty string – user3706392 Jun 19 '14 at 12:47
  • @user3706392 - A procedure in Oracle cannot return anything. A function can return anything. A function, however, cannot return an empty string (at least if you consider the empty string to be distinct from NULL). If you wanted to use the SQL*Plus `exec` command to call a function that returns `null`, however, you'd need to provide a variable in which to return the result. – Justin Cave Jun 19 '14 at 17:25