8 Replies Latest reply on Apr 3, 2012 12:50 PM by Kim Berg Hansen

    Verify if some object exists

    Petri BR
      Hi.

      I have to create a way in my scripts to verify if an object of the database exists or not.

      If it exists I do nothing.
      If it doens’t I create it.

      Is the na specific command to do that checking?


      Thanks.
        • 1. Re: Verify if some object exists
          jeneesh
          use dba_objects

          But, why this is required?
          • 2. Re: Verify if some object exists
            Petri BR
            To transport changes between enviroments.

            So the same part of a script will be executed more than once, so the script would execute just once.
            • 3. Re: Verify if some object exists
              Frank Kulash
              Hi,

              When you say "scripts", do you mean .sql files that run in SQL*Plus?

              SQL*Plus doesn't have any real IF ... THEN ... ELSE handling. Say you want to create a table called fubar if it doesn't already exist. About the best you can do is to write 2 sub_scripts:
              (1) create_fubar.sql which creates the table
              (2) do_nothing.sql which does nothing, or maybe prints a message like :Fubar already exists:.

              In your main script you can dynamically call one or the other of those 2 sub-scipts like this:
              COLUMN     sub_script_col       NEW_VALUE sub_script
              
              DEFINE  sub_script = do.nothing.sql
              
              SELECT     'create_fubar.sql'     AS sub_script_col
              FROM     user_tables
              WHERE     table_name     = 'FUBAR'
              ;
              
              
              @&sub_script
              • 4. Re: Verify if some object exists
                Petri BR
                Yes. When I say "scripts", I mean .sql files that run in SQL*Plus.

                I'll try that solution, but I'd like to make a solution in a sigle file.
                • 5. Re: Verify if some object exists
                  Frank Kulash
                  Hi,
                  Petri BR wrote:
                  ... I'll try that solution, but I'd like to make a solution in a sigle file.
                  Yes, that would be better if it were possible, but, unfotunately, I don't think there is a way to do it in a single SQL*Plus script.


                  You could do everything in PL/SQL, and call the PL/SQL from a SQL*Plus script.
                  • 6. Re: Verify if some object exists
                    ScottishPower
                    Hi,

                    I have created below script 4 years ago may be it will usefull for you but not exact

                    SET serveroutput ON
                    DECLARE
                    v_Number NUMBER;
                    V_NUMBER1 NUMBER;
                    v_str VARCHAR2(100);
                    OBJECTS_NAME VARCHAR2(100):='&object';
                    v_tab VARCHAR2(100);
                    BEGIN
                    SELECT COUNT(* ) --for tables checking
                    INTO v_Number
                    FROM User_Tables
                    WHERE Table_Name = UPPER(OBJECTS_NAME);
                    v_str :='TABLE_NAME';
                    IF V_NUMBER =0 THEN -- for synonyms checking
                    SELECT COUNT(* )
                    INTO v_Number
                    FROM user_synonyms
                    WHERE synonym_Name = UPPER(OBJECTS_NAME);
                    v_str :='SYNONYM';
                    END IF;
                    IF V_NUMBER = 0 THEN -- for synonym of synonym checking
                    SELECT COUNT(* )
                    INTO v_Number
                    FROM all_synonyms
                    WHERE synonym_Name = UPPER(OBJECTS_NAME);
                    v_str :='SYNONYM';
                    END IF;
                    IF V_NUMBER = 0 THEN -- for Index checking
                    SELECT COUNT(* )
                    INTO v_Number
                    FROM user_objects
                    WHERE object_name = UPPER(OBJECTS_NAME); -- OBJECT_TYPE='INDEX';
                    --dbms_output.put_line(v_number);
                    v_str:='INDEX';
                    END IF;
                    IF V_NUMBER = 0 THEN -- for COLUMN checking
                    BEGIN
                    FOR i IN
                    (SELECT table_name FROM all_tab_cols WHERE column_name =UPPER(OBJECTS_NAME)
                    )
                    LOOP
                    dbms_output.put_line(UPPER(OBJECTS_NAME)|| ' Coulmn available in ' || i.table_name || ' table ');
                    END LOOP;
                    v_str:='colum';
                    END;
                    END IF;
                    IF V_NUMBER = 0 THEN
                    SELECT count(*)
                    into v_number FROM all_tab_cols WHERE column_name =UPPER(OBJECTS_NAME);
                    end if;
                    IF v_Number >= 1 AND v_str='TABLE_NAME' THEN
                    dbms_Output.Put_Line(UPPER(OBJECTS_NAME) ||'-----> TABLE IS AVAILABLE IN THE SCHEMA ' );
                    ELSE
                    dbms_Output.Put_Line(UPPER(OBJECTS_NAME) ||'-----> NO SUCH TABLE AVAILABLE IN THE SCHEMA ' );
                    END IF;
                    IF v_Number >= 1 AND v_str='SYNONYM' THEN
                    dbms_Output.Put_Line(UPPER(OBJECTS_NAME) ||'-----> SYNONYM IS AVAILABLE IN THE SCHEMA ' );
                    ELSE
                    dbms_Output.Put_Line(UPPER(OBJECTS_NAME) ||'-----> NO SUCH SYNONYM AVAILABLE IN THE SCHEMA ' );
                    END IF;
                    IF v_Number >= 1 AND v_str='INDEX' THEN
                    dbms_Output.Put_Line(UPPER(OBJECTS_NAME) ||'-----> INDEX IS AVAILABLE IN THE SCHEMA ' );
                    ELSE
                    dbms_Output.Put_Line(UPPER(OBJECTS_NAME) ||'-----> NO SUCH INDEX AVAILABLE IN THE SCHEMA ' );
                    END IF;
                    IF v_Number = 0 or V_STR <>'colum' THEN
                    dbms_Output.Put_Line(UPPER(OBJECTS_NAME) ||'-----> NO SUCH COLUMN AVAILABLE IN THE SCHEMA ' );
                    END IF;


                    END;
                    • 7. Re: Verify if some object exists
                      Kim Berg Hansen
                      A possible way in a script (but I don't really think it is very nice ;-) );
                      declare
                        test_table_name user_tables.table_name%type;
                      begin
                        select table_name
                        into test_table_name
                        from user_tables
                        where table_name = 'FUBAR';
                      exception
                        when no_data_found then
                        execute immediate q'[
                      create table fubar (
                        col1 number
                      )
                      ]';
                      end;
                      /
                      (I haven't tested the above - just giving it as an idea you might work with if you really need it ;-) )
                      • 8. Re: Verify if some object exists
                        Kim Berg Hansen
                        Or an alternative that Oracle use themselves in Streams. (DBMS_STREAMS_ADM can create a script for you - I just remembered I have seen a technique used in there ;-) )
                        begin
                        execute immediate q'[
                        create table fubar (
                          col1 number
                        )
                        ]';
                        exception
                          when others then
                          if sqlcode = -955 then
                            null; --object already exists
                          else
                            raise;
                          end;
                        end;
                        /
                        Try to do the creation - if it raises ORA-00955 then the table already did exist and the error can be ignored.