PL/SQL Procedures and Toad execution?

how to execute stored procedure in toad editor
execute stored procedure toad output cursor
execute procedure in oracle
how to execute package in oracle with parameters
how to create a procedure inside a package in oracle
oracle execute stored procedure with parameters
how to execute package in oracle sql developer
stored procedure in oracle example with in out parameter

I recently started working on a number of large Oracle PL/SQL stored procedures with Toad for Oracle. Number of these procedures updates and inserts stuff into tables. My question is, is there a way to "safely" execute PL/SQL procedures without permanently modifying any of the tables ? Also, how do I safely modify and execute stored procedures for experimentation without actually making changes to the database ?

Doesn't matter if you have Toad or SQ*Plus or anything really - it's all about the code.

First - does your program have any commits or rollbacks IN the stored procedures?

Second - does your program do any DDL work: create a table? That will do an implicit COMMIT. Mind you, if your program calls another program and THAT program has a COMMIT or DDL - you're COMMITTED as its' all in one session.

Third - when you go to execute your stored procedure, does your anonymous block have a COMMIT or ROLLBACK there?

Your tool comes into play for the third bit. Inspect the code behind the 'execute' button.

In SQL Developer (similar to Toad in this regard)...

In this case my SP has a commit in the code - so barring an exception before that line...it's a permanent change.

In the generated anonymous block, there's a ROLLBACK, but it's commented out. When you hit the execute button in your GUI, look at the code there. Change it if necessary.

PL/SQL Procedures and Toad execution?, How can I execute a single stored procedure from within toad? Subject: RE: [​Toad for Oracle - Discussion Forum] An easy way to execute a  PL/SQL procedures are meant to perform some of the actions. Those may include population and modification of tables. Those may include population and modification of tables. And if you don't want to see those changes in table then what's the point running the procedure.

You can create a copy of your database, then play there. Other thing is, you can create a copy of the procedures/functions, packages and tables involve and play with it.

Let's you have this procedure,

CREATE PROCEDURE proc1
IS
BEGIN
    INSERT INTO table1
    (col1, col2)
    VALUES
    ('actual data', 'hello');
    UPDATE table2
       SET col1 = 'actual'
     WHERE col2 = 1;
    COMMIT;
END;

You will create new procedure with same logic inside it.

CREATE PROCEDURE proc1_test
IS
BEGIN
    INSERT INTO table1_test
    (col1, col2)
    VALUES ('test', 'hello');
    UPDATE table2_test
       SET col1 = 'test2'
     WHERE col2 = 1;
    COMMIT;
END;
/

Doing this, it will let you compare your actual data to your test data.

An easy way to execute a single Stored Procedure from a Package , How do I call/execute a stored procedure in Oracle from Toad? Can you give me the syntax? It's the same way we do it in sql plus. Exec <proc> (parameters). First of all, in order to be able to debug PL/SQL code, one must have appropriate database privilege for debugging. (GRANT DEBUG CONNECT SESSION TO user).If you're not granted this privilege, you DB tool (like Quest TOAD) might not even show debugging options or might show it disabled.

You can put rollback at the end of the procedure and comment any Commits/DDL statements. Also you need to be careful for the Pragma statements if any.

Executing Stored Procedure in Toad, Hi All, I Have Used SQL PLUS for executing pl/sql programs . now i want to use toad for the same.i learnt most of the things to execute in toad , but i dont know  Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account. Thanks for the question, Andre.

About Executing Procedure in TOAD, External Procedures. A PL/SQL procedure executing on an Oracle Database instance can call an external procedure written in a 3GL. The 3GL procedure runs in  In this chapter, we will discuss Procedures in PL/SQL. A subprogram is a program unit/module that performs a particular task. These subprograms are combined to form larger programs. This is basically called the 'Modular design'. A subprogram can be invoked by another subprogram or program which is called the calling program.

Coding PL/SQL Procedures and Packages, To execute a stored procedure or function, you only need to include its object name. See "CREATE PROCEDURE" in Oracle Database SQL Reference. Call to these procedures can be made by referring to their name, to execute the PL/SQL statements. It is mainly used to execute a process in PL/SQL. It can have nested blocks, or it can be defined and nested inside the other blocks or packages. It contains declaration part (optional), execution part, exception handling part (optional).

5 Using Procedures, Functions, and Packages, This tutorial shows you step by step how to create, compile, and execute a PL/​SQL procedure from Oracle SQL Developer tool. Also have a look at the "Execute Procedure" function in the Schema Browser. It has an option to load ref cursor results into a grid after execution. It also has the advantage that it will remember the parameters you used last time you executed that procedure. – Igby Largeman May 13 '10 at 22:44

Comments
  • PL/SQL procedures are meant to perform some of the actions. Those may include population and modification of tables. And if you don't want to see those changes in table then what's the point running the procedure.
  • @XING Of course, I understand that. But some of these procedures are well over 3000 lines long - as a newbie, I don't feel comfortable just running them without understanding if any tables are going to be left in an inconsistent state...
  • When you said "create a copy", I thought that you were suggesting a new user, in development environment. A "copy" you suggest is somewhat doubtful; what if you "forget" to modify one of those table names?
  • We'll, that's a good point, Thanks. If OP follow my answer, he/she should checked if all tables in the procedure were already modified before executing those "copied" procedures or functions.
  • Certainly, but "what if" remains. Personally, I'd rather have a development environment, but we're just discussing.
  • I agree with that. That's why, in the first sentence of my answer I said, "You can create a copy of your database, then play there."