How to reuse code in Oracle Script

oracleplsql

I have a script where I am creating some test data. At the beginning of the script I create some tables where I have to insert the data. Before creating them I check that they do not exist.

declare
 tableExists number;
begin

SELECT COUNT(*)
    INTO tableExists
FROM user_tables
WHERE table_name = 'MY_TEST_TABLE';

if tableExists>0 then
    execute immediate 'drop  table MY_TEST_TABLE';
end if;

SELECT COUNT(*)
    INTO tableExists
FROM user_tables
WHERE table_name = 'MY_TEST_TABLE2';

if tableExists>0 then
execute immediate 'drop table MY_TEST_TABLE2';
end if;

--Here I create both tables: MY_TEST_TABLE and MY_TEST_TABLE2

My question is:
Is there a way to avoid repeating the code for checking if the table exists? Is there a way that I could call that code as a method passing the table name as parameter?

I believe I could create/declare a function and then use that. But that kind of function is not temporal. I know this is not like C or C++. But I was wondering if there was a way I could have a sort of "private method" for this script to test the existence of my tables and that way I wouldn't have to repeat the code, but the method wouldn't remain after my script was finished.

What is the correct way, if there is any, to achieve what I want?

Best Answer

You can always nest blocks in PL/SQL

DECLARE
  <<define your other local variables>>

  PROCEDURE drop_if_exists( p_table_name IN VARCHAR2 )
  AS
    l_num_tables pls_integer;
  BEGIN
    SELECT COUNT(*)
      INTO l_num_tables
      FROM user_tables
     WHERE table_name = p_table_name;

    IF( l_num_tables = 1 )
    THEN
      EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name;
    END IF;
  END drop_if_exists;
BEGIN
  drop_if_exists( 'MY_TEST_TABLE' );
  drop_if_exists( 'MY_TEST_TABLE' );

  <<more code here>>
END;

Related Solutions

How to extract Procedure, Function, etc. Source Code from an Oracle Export

If you've got access to the original database, I'd go with DBMS_METADATA.GET_DDL.

You can script it with UTL_FILE so that it goes through each object (from USER_OBJECTS), uses the name and type to extract the object then write it to a file that has the appropriate naming convention.

It will be a lot cleaner than trying to split a single file.

Temporary table inside procedure oracle

It depends on why you are creating the temporary tables in MySQL.

Frequently, people that are creating temporary tables in other databases are doing so in order to work around limitations that don't exist in Oracle where readers don't block writers and writers don't block readers. In other databases, you commonly copy data from a permanent table to a temporary table so that your process doesn't block some other process that needs the same data. Since Oracle provides multi-version read consistency, however, this isn't necessary (or beneficial) in Oracle-- your process can process the data sitting in the real tables without worrying that it is going to block someone else. If that's the situation you're in, the proper response is simply to remove the temporary tables and process the data from the permanent tables.

Assuming that you really need a temporary copy of the data, you can create global temporary tables. You would create these tables outside of your code just like a permanent table and use them just like a permanent table inside your code. The global temporary table ensures that each session can only see the data that session has inserted. The only difference is that you're not dropping and recreating the structure of the table inside your procedure.

Another alternative would be to pull the data into a PL/SQL collection that you work with rather than using a temporary table. PL/SQL collections are stored in the server's PGA (one of Oracle's memory structures) so you generally want to limit the size of the collection particularly if there is a lot of data to process or there could be many sessions processing data simultaneously. You can do something like

DECLARE
  TYPE emp_tbl IS TABLE OF emp%rowtype;
  l_emps emp_tbl;

  CURSOR emp_cur 
      IS SELECT *
           FROM emp;
BEGIN
  OPEN emp_cur;
  LOOP
    -- Fetch 10 rows at a time from the cursor into the collection.
    -- You'd realistically want a larger limit, something between 100 and 1000 generally
    FETCH emp_cur 
     BULK COLLECT INTO l_emps
    LIMIT 10;

    EXIT WHEN l_emps.COUNT = 0;

    -- An example of manipulating the collection in memory
    FOR i IN 1 .. l_emps.COUNT
    LOOP
      l_emps(i).sal := l_emps(i).sal * 2;
    END LOOP;

    -- And an example of using the collection to update a table
    FORALL i IN 1 .. l_emps.COUNT
      UPDATE emp
         SET sal = l_emps(i).sal
       WHERE empno = l_emps(i).empno;
  END LOOP;
END;

Of course, in the example above, it would be far simpler (and more efficient) to simply issue an UPDATE statement that would double every employee's salary.

Best Answer

Related Solutions

How to extract Procedure, Function, etc. Source Code from an Oracle Export

Temporary table inside procedure oracle

Related Question