DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: PROCEDURE

  1. #1
    Join Date
    Mar 2002
    Posts
    46
    How can I test a procedure with SQL plus. What is the command?

    ex:
    CREATE OR REPLACE PROCEDURE add_status
    (status_code CHAR,
    status_desc_e VARCHAR2,
    status_desc_f VARCHAR2
    ) IS
    cSQL_Statement VARCHAR2(200);
    BEGIN
    cSQL_Statement := 'INSERT INTO E_SIA_STATUS ' ||
    ' VALUES(:code, :desc_e, :desc_f)';
    EXECUTE IMMEDIATE cSQL_Statement
    USING status_code, status_desc_e, status_desc_f;
    EXCEPTION
    WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20101,
    'Error in procedure Add_status.');
    END add_status;
    /


    How execute this procedure wtih SQL plus?

    Thanks in advance
    DONTBSHY

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    > exec add_status('A','desc status - e','desc status - f');
    or
    > exec owner_name.add_status('A','desc status - e','desc status - f');

  3. #3
    Join Date
    Aug 2000
    Posts
    462
    an alternative that won't require you to recompile every time is to create an anonymous block instead:

    declare
    status_code char (1) := 'G';
    status_desc_e varchar2(20) := 'ok';
    status_desc_f varchar2(20) := 'bad';
    csql_statement varchar2(200);
    begin
    . . .
    exception
    . . .
    end;
    /

    --Simply type it all in at the command prompt
    Oracle DBA and Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width