Reply to: Oracle stored proc simple problem

[TheFaQQer]

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

[TheFaQQer]

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

Consider the following stored procedure call :

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.



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

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):

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.

[lilelvis2000]

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

Consider the following stored procedure call :

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.



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

This is supposed to work

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

But if that isn't working then it may indicate that there is something wrong with the SP..or a permissions issue. Like the execute privilege is not granted to you...which would be odd if you created the SP.

You're using SQL Developer so when you look at the SP in the procedures 'folder' is there a 'x' next to the SP?

[Spacecadet]

have you tried CALL instead of EXEC?

[mudskipper]

Oracle is rusty, but don't you need a colon in front of the variable?

And it's not got a value - what's it trying to do?

[eek]

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

is what I think is missing.

[d000hg]

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

Most - C-style anyway - allow it though Worth a try though.

[darmstadt]

I haven't got a clue but have you tried it without the space:

Exec SP_ADD_COMMENT_TYPE('New comment Type', commentTypeId);

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

[suityou01]

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

Consider the following stored procedure call :

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.

PLS-00103: Encountered the symbol SP_ADD_COMMENT_TYPE when expecting one of the following ......

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

[d000hg]

Leave the coding for the Bobs.

[eek]

Sounds like this project is going to hell in a handcart.

It was destined to do that the day SY submitted his application form.

A little knowledge is a dangerous thing as this topic clearly demonstrates.

[MarillionFan]

Sounds like this project is going to hell in a handcart.

[TheFaQQer]

Suity,

If you want more critique (if this is something that your off-shore team has produced and you want to try and look smart)....

1) Why isn't this a function rather than a procedure?

2) Why use INTEGER rather than PLS_INTEGER as the datatype?

[d000hg]

A simple understanding that I would expect you people to make. I DESIGNED the system. Now I am helping them code it, despite protestations that I am to Oracle what MF is to Microsoft. Go figure.

It's great how you respond to people's help. Maybe keep your sense of aggrieved genius for General.

[TheFaQQer]

Who is the vendor? McCain?

They sound cheap as chips