How can I use Oracle SQL developer to run stored procedures

Question

EDIT6    This is what ended up working for me  from accepted answer    var ret1 number var tran cnt number var msg cnt number var rc refcursor exec  tran cnt    0 exec  msg cnt    123 exec get account Vret val   gt   ret1  Vtran count   gt   tran cnt  Vmessage count   gt   msg cnt  Vaccount id   gt  1  rc1   gt   rc  print  tran cnt print  msg cnt print  rc     The SQL Developer makes this super-hard   impossible   I do not care if the utility is command-line-based  I just want to be able to run and view it quickly  It would be nice if it captured errors well as well  it would be nice to be able to log in gradually interactively   as well as specifying everything at once  similar to how a typical ftp   sftp cmd-based client works    My platform is Windows Server 2008   Cygwin   EDIT  Perhaps you would know how to script this using Python   EDIT 2  In MSFT SQL server I can simply type this   get user 1    then highlight it and hit F5  and I get   login   name     NULL    Somename   printed to the output window  The Oracle SQL developer does not help with this at all  I am not sure how to pass in a 1  I am not sure how to see the actual rows records that come back   EDIT3  When I type just var rc refcursor  and select it and run it  I get this error  GUI    An error was encountered performing the requested operation   ORA-00900  invalid SQL statement 00900 00000 -  invalid SQL statement    Cause    Action  Vendor code 900Error at Line  2   EDIT4   I am trying to run a procedure the definition of which starts like this   create or replace procedure get account       Vret val out number      Vtran count in out number      Vmessage count in out number      Vaccount id     IN NUMBER       rc1 in out sys refcursor  as begin       I get an error   Error starting at line 2 in command  exec  rc    get account 1   Error report  ORA-06550  line 1  column 24  PLS-00306  wrong number or types of arguments in call to  GET ACCOUNT  ORA-06550  line 1  column 7  PL SQL  Statement ignored 06550  00000 -   line  s  column  s  n s   Cause     Usually a PL SQL compilation error   Action  rc ------   I am so close     please help     EDIT 5     Script that I am running  functionally the same   the error was always the same   var ret1 number var tran cnt number var msg cnt number var rc refcursor exec  tran cnt    0 exec  msg cnt    123 exec get account Vret val   gt   ret1  Vtran count   gt   tran cnt  Vmessage count   gt   msg cnt  Vaccount id   gt  1  rc1   gt  rc    Script Output  on F5   Could be several messages from several runs     Error report  ORA-06550  line 1  column 134  PLS-00201  identifier  RC  must be declared ORA-06550  line 1  column 7  PL SQL  Statement ignored 06550  00000 -   line  s  column  s  n s   Cause     Usually a PL SQL compilation error   Action  anonymous block completed anonymous block completed  Error starting at line 7 in command  exec get account Vret val   gt   ret1  Vtran count   gt   tran cnt  Vmessage count   gt   msg cnt  Vaccount id   gt  1  rc1   gt  rc  Error report  ORA-06550  line 1  column 134  PLS-00201  identifier  RC  must be declared ORA-06550  line 1  column 7  PL SQL  Statement ignored 06550  00000 -   line  s  column  s  n s   Cause     Usually a PL SQL compilation error   Action  anonymous block completed anonymous block completed  Error starting at line 7 in command  exec get account Vret val   gt   ret1  Vtran count   gt   tran cnt  Vmessage count   gt   msg cnt  Vaccount id   gt  1  rc1   gt  rc  Error report  ORA-06550  line 1  column 134  PLS-00201  identifier  RC  must be declared ORA-06550  line 1  column 7  PL SQL  Statement ignored 06550  00000 -   line  s  column  s  n s   Cause     Usually a PL SQL compilation error   Action    Why does it say line 1  column 134  No line stretched that far

User · Answer

Have you heard of  SQuirreL SQL Client    http   squirrel-sql sourceforge net

User · Answer

There are two possibilities  both from Quest Software  TOAD  amp  SQL Navigator   Here is the TOAD Freeware download  http   www toadworld com Downloads FreewareandTrials ToadforOracleFreeware tabid 558 Default aspx  And the SQL Navigator  trial version   http   www quest com sql-navigator software-downloads aspx

User · Answer

Not only is there a way to do this  there is more than one way to do this  which I concede is not very Pythonic  but then SQL Developer is written in Java     I have a procedure with this signature  get maxsal by dept  dno number  maxsal out number    I highlight it in the SQL Developer Object Navigator  invoke the right-click menu and chose Run    I could use ctrl F11   This spawns a pop-up window with a test harness    Note  If the stored procedure lives in a package  you ll need to right-click the package  not the icon below the package containing the procedure s name  you will then select the sproc from the package s  Target  list when the test harness appears    In this example  the test harness will display the following   DECLARE   DNO NUMBER    MAXSAL NUMBER  BEGIN   DNO    NULL     GET MAXSAL BY DEPT      DNO   gt  DNO      MAXSAL   gt  MAXSAL        DBMS OUTPUT PUT LINE  MAXSAL        MAXSAL   END    I set the variable DNO to 50 and press okay   In the Running - Log pane  bottom right-hand corner unless you ve closed moved hidden it  I can see the following output   Connecting to the database apc  MAXSAL   4500 Process exited  Disconnecting from the database apc     To be fair the runner is less friendly for functions which return a Ref Cursor  like this one  get emps by dept  dno number  return sys refcursor   DECLARE   DNO NUMBER    v Return sys refcursor  BEGIN   DNO    50     v Return    GET EMPS BY DEPT      DNO   gt  DNO        -- Modify the code to output the variable   -- DBMS OUTPUT PUT LINE  v Return        v Return   END    However  at least it offers the chance to save any changes to file  so we can retain our investment in tweaking the harness     DECLARE   DNO NUMBER    v Return sys refcursor    v rec emp rowtype  BEGIN   DNO    50     v Return    GET EMPS BY DEPT      DNO   gt  DNO         loop     fetch v Return into v rec      exit when v Return notfound      DBMS OUTPUT PUT LINE  name        v rec ename     end loop  END    The output from the same location   Connecting to the database apc  name   TRICHLER name   VERREYNNE name   FEUERSTEIN name   PODER Process exited  Disconnecting from the database apc       Alternatively we can use the old SQLPLus commands in the SQLDeveloper worksheet   var rc refcursor  exec  rc    get emps by dept 30   print rc   In that case the output appears in Script Output pane  default location is the tab to the right of the Results tab    The very earliest versions of the IDE did not support much in the way of SQL Plus   However  all of the above commands have been supported since 1 2 1   Refer to the matrix in the online documentation for more info          When I type just var rc refcursor    and select it and run it  I get this   error  GUI      There is a feature - or a bug - in the way the worksheet interprets SQLPlus commands   It presumes SQLPlus commands are part of a script   So  if we enter a line of SQL Plus  say var rc refcursor and click Execute Statement  or F9   the worksheet hurls ORA-900 because that is not an executable statement i e  it s not SQL    What we need to do is click Run Script  or F5    even for a single line of SQL Plus           I am so close     please help     You program is a procedure with a signature of five mandatory parameters   You are getting an error because you are calling it as a function  and with just the one parameter   exec  rc    get account 1    What you need is something like the following   I have used the named notation for clarity   var ret1 number var tran cnt number var msg cnt number var rc refcursor  exec  tran cnt    0 exec  msg cnt    123  exec get account  Vret val   gt   ret1                     Vtran count   gt   tran cnt                     Vmessage count   gt   msg cnt                     Vaccount id     gt  1                    rc1   gt   rc    print tran count  print rc   That is  you need a variable for each OUT or IN OUT parameter   IN parameters can be passed as literals   The first two EXEC statements assign values to a couple of the IN OUT parameters   The third EXEC calls the procedure   Procedures don t return a value  unlike functions  so we don t use an assignment syntax   Lastly this script displays the value of a couple of the variables mapped to OUT parameters

User · Answer

I am not sure how to see the actual   rows records that come back    Stored procedures do not return records  They may have a cursor as an output parameter  which is a pointer to a select statement  But it requires additional action to actually bring back rows from that cursor   In SQL Developer  you can execute a procedure that returns a ref cursor as follows  var rc refcursor exec proc name  rc    After that  if you execute the following  it will show the results from the cursor   print rc

User · Answer

My recommendation is TORA

[oracle] How can I use Oracle SQL developer to run stored procedures?

Examples related to oracle

Examples related to stored-procedures

Examples related to plsql

Examples related to oracle-sqldeveloper

Examples related to sys-refcursor