Oracle stored proc simple problem

[suityou01]

Why won't the following compile?

CREATE OR REPLACE PROCEDURE sp_AddTestData
(sTEST_DATA IN NVARCHAR2, iTEST_ID OUT INTEGER) AS
BEGIN
  iTEST_ID := seqTestData.nextval;
  INSERT INTO TEST_DATA VALUES (iTEST_ID,sTEST_DATA);
END

PROCEDURE sp_AddTestData compiled
Warning: execution completed with warning

I'm tired and not an Oracle guy but need this simple stored proc working so I can run some tests from the BPM framework to troubleshoot some issues we are facing with calling Oracle stored procs.

TIA

Suity

[MarillionFan]

Why won't the following compile?

CREATE OR REPLACE PROCEDURE sp_AddTestData
(sTEST_DATA IN NVARCHAR2, iTEST_ID OUT INTEGER) AS
BEGIN
  iTEST_ID := seqTestData.nextval;
  INSERT INTO TEST_DATA VALUES (iTEST_ID,sTEST_DATA);
END

I'm tired and not an Oracle guy but need this simple stored proc working so I can run some tests from the BPM framework to troubleshoot some issues we are facing with calling Oracle stored procs.

TIA

Suity

It says compiled.

Did you commit.

[suityou01]

It says compiled.

Did you commit.

It's compiled but with a warning. And there is a big red cross next to the stored proc. Something ain't right. I did issue a commit now you mentioned it. Still no different.

Really all I want this this sp compiled and some code to execute it. Even this is giving me gip.

DECLARE NewID integer;
CALL sp_AddTestData('This is a test',NewID);

Gives

ORA-06550: line 2, column 44:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

[lilelvis2000]

What's the warning? TOAD, SQL Developer should provide a list of the warnings.

Your script should be like

DECLARE
NewID integer;
begin
CALL sp_AddTestData('This is a test',NewID);
end;

[suityou01]

What's the warning? TOAD, SQL Developer should provide a list of the warnings.

Your script should be like

DECLARE
NewID integer;
begin
CALL sp_AddTestData('This is a test',NewID);
end;

Thanks LE will try that

I'm using SQL Developer, and there are no warnings in the message window
I wondered if there was another immediate window under the view menu, but not that I can see

Warnings would give a good clue I agree

[lilelvis2000]

aha! sql developer I use that too.

Okay so on the tree, right mouse on the procedure and edit...
Then click on the compile button (the gears)
You should then get a compiler log window open at the bottom.
That lists the errors in your PL/SQL

In your case I suspect something with the sequence..its the only obvious things I can see.

[doodab]

Oracle Database Online Documentation 11g Release 2 (11.2)

perhaps start here:

Developing Stored Subprograms and Packages

then move on to:

Contents

[eek]

So having confirmed that the Bobs are treating you with the respect you seem to deserve here is the noddy code you need.

CREATE OR REPLACE PROCEDURE ADD_LOCATION (
(sTEST_DATA IN NVARCHAR2, iTEST_ID OUT INTEGER) 
AS
BEGIN
INSERT INTO TEST_DATA VALUES (seqTestData.nextval, sTEST_DATA );  

 SELECT seqTestData.CURRVAL INTO iTEST_ID FROM DUAL;

END

[Spacecadet]

Some times I do wonder who some people on here can breath and do anything else at the same time.

Insults in English please

[eek]

Insults in English please

Sorry I was running late for brownies (read into that anything you want because I know your minds).