Forum Stats

  • 3,852,374 Users
  • 2,264,099 Discussions
  • 7,905,055 Comments

Discussions

Creating Stored Procedure

737934
737934 Member Posts: 7
edited Nov 30, 2009 12:23PM in General Database Discussions
Hi, i am creating my first stored procedure and really need some help...i have managed to put together the following:

CREATE OR REPLACE PROCEDURE DRL_PROCEDURE(var_SOURCE_OBJECTID IN varchar2, var_NEW_OBJECTID OUT varchar2)
IS
var_temp_newobjectid varchar2(20);
BEGIN
SELECT MAX(NEW_OBJECTID)
INTO var_temp_newobjectid
FROM DRL_CONVERSION
WHERE SOURCE_OBJECTID = var_SOURCE_OBJECTID;

var_NEW_OBJECTID := var_temp_newobjectid;
return;
END;

I'm not sure if this is correct however, i've tried executing it with the following but when it does execute no results show...it just says procedure successfully completed:

VARIABLE RETVAL VARCHAR2(20)
EXEC DRL_PROCEDURE('ab',:RETVAL);

Do i need to change it somehow to make it show the result?
Also if you know how i can call the procedure in C# and show the result that way that would be equally helpful...thanks
Tagged:

Best Answer

  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    Hi and welcome to the forums!

    The last step you forgot was to PRINT your result. See the example below:
    SQL> CREATE OR REPLACE PROCEDURE TEST_PROC
      2  (
      3          pOwner  IN VARCHAR2
      4  ,       pCnt    OUT VARCHAR2
      5  )
      6  AS
      7  BEGIN
      8          SELECT  COUNT(*) INTO pCnt
      9          FROM    ALL_OBJECTS
     10          WHERE   OWNER = pOwner;
     11  END;
     12  /
    
    SP2-0804: Procedure created with compilation warnings
    
    SQL> VAR result NUMBER;
    SQL> EXECUTE TEST_PROC(USER,:result);
    
    PL/SQL procedure successfully completed.
    
    SQL> PRINT result
    
        RESULT
    ----------
          7090
    Additionally, you don't need the variable VAR_TEMP_NEWOBJECTID. You can SELECT ... INTO VAR_NEW_OBJECTID instead.

    HTH!

Answers

  • 657203
    657203 Member Posts: 1,116
    Just in sqlplus:
    PRINT RETVAL
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    Hi and welcome to the forums!

    The last step you forgot was to PRINT your result. See the example below:
    SQL> CREATE OR REPLACE PROCEDURE TEST_PROC
      2  (
      3          pOwner  IN VARCHAR2
      4  ,       pCnt    OUT VARCHAR2
      5  )
      6  AS
      7  BEGIN
      8          SELECT  COUNT(*) INTO pCnt
      9          FROM    ALL_OBJECTS
     10          WHERE   OWNER = pOwner;
     11  END;
     12  /
    
    SP2-0804: Procedure created with compilation warnings
    
    SQL> VAR result NUMBER;
    SQL> EXECUTE TEST_PROC(USER,:result);
    
    PL/SQL procedure successfully completed.
    
    SQL> PRINT result
    
        RESULT
    ----------
          7090
    Additionally, you don't need the variable VAR_TEMP_NEWOBJECTID. You can SELECT ... INTO VAR_NEW_OBJECTID instead.

    HTH!
  • 450804
    450804 Member Posts: 460
    Hi,

    personally I would use for your case a function and not a procedure. You will also be able to use it within a SQL statement. However from what i can see from your code. You should use a sequence if you need to insert a new row.

    Cheers,
    David

    OCP 9i
    http://www.oratoolkit.ch/otn.php
This discussion has been closed.