• 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

suityou01

Potty-trained
Joined
Jan 20, 2009
Messages
21,122
Likes received
773
Thanks received
222
Location
Clown World
Why won't the following compile?

Code:
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
 
Last edited: 18:47
Why won't the following compile?

Code:
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. :eyes

Did you commit.
 
It says compiled. :eyes

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:
 
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;
 
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 :rolleyes:
 
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.
 
Last edited: 19:59
So having confirmed that the Bobs are treating you with the respect you seem to deserve here is the noddy code you need.

Code:
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
 
Last edited: 20:07

Advertisers

Back
Top