• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Oracle stored proc simple problem

This is why Oracle does my f****** nut in.

Consider the following stored procedure call :

Code:
DECLARE
commentTypeId integer;
BEGIN
Exec SP_ADD_COMMENT_TYPE ('New comment Type', commentTypeId);
END;

According to the examples on the web this is fine. According to the examples given on here this is fine.

According to the SQL Developer tools THIS IS NOT FINE. :tantrum:



I have tried it with and without EXEC. I have tried it with and without CALL. I have tried it in SQL+.

:rolleyes:

I'm not sure what examples you've been looking at, but I've never seen exec or call included in a PL/SQL block.

EXEC is a SQL*Plus command to execute the procedure from the command line.

The following works fine for me in APEX, so should be fine in any other version of the database from 8i onwards (all I did was remove the word exec from your example):

Code:
create or replace procedure sp_add_comment_type ( p_comment IN VARCHAR2, p_commentID OUT PLS_INTEGER ) AS
BEGIN
  p_commentID := -1;
END;
/

Procedure created.
0.11 seconds

DECLARE
  commentTypeId integer;
BEGIN
  SP_ADD_COMMENT_TYPE ('New comment Type', commentTypeId);
END;
/

Statement processed.
0.00 seconds

If I add exec or call to the code, then I get the error message you describe. If I don't then the code works fine for me.
 
I haven't got a clue but have you tried it without the space:



Most programming languages don't have a space between a function and parameters...

Oracle doesn't care one way or the other whether there are 0, 1 or >1 spaces included.

It's clever like that :)
 

Advertisers

Back
Top