Oracle stored proc simple problem

[TheFaQQer]

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

Personally, I'd do the fetch into the variable before the insert. Generally because I like to include audit and debug statements like "About to insert record ID x" before the insert.

CREATE OR REPLACE PROCEDURE sp_AddTestData (sTEST_DATA IN NVARCHAR2, iTEST_ID OUT INTEGER) AS
BEGIN
  SELECT seqTestData.NEXTVAL INTO iTEST_ID FROM DUAL;

  INSERT INTO TEST_DATA VALUES (iTEST_ID, sTEST_DATA );  

END;

But the overall impact is the same

[TheFaQQer]

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;

Try it without "CALL" in there

[lilelvis2000]

Surely a bump in the right direction is better than giving the answer away?

[suityou01]

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

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.

[suityou01]

Surely a bump in the right direction is better than giving the answer away?

Certainly the vendor feels this way, my weekly reports list the actual question that was asked, then the responses given from the vendor support team and the actual percentage progress that could be made on the problem given the response. (This is not Oracle related) but makes for interesting team meetings. Vendor support is more cards against the chest than it used to be. Perhaps a sign of the financial times.

[eek]

Certainly the vendor feels this way, my weekly reports list the actual question that was asked, then the responses given from the vendor support team and the actual percentage progress that could be made on the problem given the response. (This is not Oracle related) but makes for interesting team meetings. Vendor support is more cards against the chest than it used to be. Perhaps a sign of the financial times.

Is the end client in this meeting as you tell the vendor (and I guess your employer) how crap their staff are?

Oh and the code above is noddy code. It took me 45 seconds to find it via google.

[doodab]

Who is the vendor? McCain?

[TheFaQQer]

Who is the vendor? McCain?

They sound cheap as chips

[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]

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?