Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2009
    Posts
    46

    Unanswered: Procedure Compilation Error

    Hello Guys

    I wanted to generate hotback script which I could schedule for my hot backups.

    However, when I tried to compile it always results in error and I don't know where the problem really is.

    I would therefore be very grateful if anyone could assist me resolve this error in the Script below:


    Platform: Windows Server 2003 R2
    Database version : 10gR2 (10.2.0.1.0)

    Instance : smartbnk

    Username for compile : SYSTEM

    Script :
    ======

    CREATE OR REPLACE PROCEDURE GENERATE_HOT_BACKUP_SCRIPT(p_filename IN VARCHAR2) AS
    CURSOR all_tablespaces IS
    SELECT
    DISTINCT
    TABLESPACE_NAME
    FROM
    DBA_TABLESPACES;
    CURSOR all_data_files(p_tablespace_name IN VARCHAR2) IS
    SELECT
    DISTINCT
    FILE_NAME
    FROM
    DBA_DATA_FILES
    WHERE
    TABLESPACE_NAME = p_tablespace_name;
    -- Local variables
    p_file_loc VARCHAR2(80) := 'D:\oracle\product\10.2.0\oradata\smartbnk';
    p_file_name VARCHAR2(80) := p_filename;
    p_file_handler UTL_FILE.FILE_TYPE;
    BEGIN
    -- Create the output file and return the handler to the file
    p_file_handler := UTL_FILE.FOPEN(p_file_loc, p_file_name, 'W');
    -- Determine if the file has been opened successfully
    IF UTL_FILE.IS_OPEN(p_file_handler) THEN
    BEGIN
    FOR i IN all_tablespaces
    LOOP
    UTL_FILE.PUT_LINE(p_file_handler,'alter tablespace '||i.TABLESPACE_NAME||' begin backup;');
    FOR j IN all_data_files(i.TABLESPACE_NAME)
    LOOP
    UTL_FILE.PUT_LINE(p_file_handler,'host copy '||j.FILE_NAME||'D:\oracle\hotbackup');
    END LOOP;
    UTL_FILE.PUT_LINE(p_file_handler,'alter tablespace '||i.TABLESPACE_NAME||' end backup;' );
    END LOOP;
    -- Backup the control file
    UTL_FILE.PUT_LINE(p_file_handler,'alter database backup controlfile to trace as ''D:\oracle\hotbackup\control.new'';');
    -- Switch the logfile
    UTL_FILE.PUT_LINE(p_file_handler,'alter database switch logfile;' );
    DBMS_OUTPUT.PUT_LINE('File created successfully');
    END;
    ELSE
    DBMS_OUTPUT.PUT_LINE('File not created');
    END IF; -- End if file open is successful
    -- Close the file
    UTL_FILE.FCLOSE(p_file_handler);
    END;
    /


    Compilation Error:
    =============
    Warning: Procedure created with compilation errors.

    SQL> show error
    Errors for PROCEDURE GENERATE_HOT_BACKUP_SCRIPT:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    3/5 PL/SQL: SQL Statement ignored
    7/13 PL/SQL: ORA-00942: table or view does not exist
    9/7 PL/SQL: SQL Statement ignored
    13/14 PL/SQL: ORA-00942: table or view does not exist
    28/18 PL/SQL: Statement ignored
    28/72 PLS-00364: loop index variable 'I' use is invalid
    29/17 PL/SQL: Statement ignored
    29/41 PLS-00364: loop index variable 'I' use is invalid
    33/16 PL/SQL: Statement ignored
    33/70 PLS-00364: loop index variable 'I' use is invalid
    SQL>

    ==============

    Thanks in advance
    FoKwame

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Look it up first!

    What did you not understand about: "ORA-00942: table or view does not exist"?

    If you actually read the fine Oracle® Database Reference you will find out that view ALL_TABLESPACES does not exist!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Feb 2009
    Posts
    62
    I suspect that the problem is to do with privileges.

    To access a table/view owned by an user other than the user compiling a procedure, the user must have had the SELECT privilege on that table/view granted to them explicitly (ie by a GRANT SELECT ON <table> TO <user>) rather than simply having it provided to them as part of a role.

  4. #4
    Join Date
    Mar 2009
    Posts
    46
    Hi JRow, Thanks for your response,

    but am compiling the procedure with SYSTEM user account. Do I need to grant select privilege on any table(s) to SYSTEM user??

    Any way, according to the first response from LkBrwn, I realised the ALL_TABLESPACE is no longer existing in oracle 10gR2 so I replaced the ALL_TABLESPACE with DBA_TABLESPACES but the procedure could still not compile. The following errors results which am still trying to resolve.


    /6 PL/SQL: ORA-00942: table or view does not exist
    3/1 PL/SQL: SQL Statement ignored
    12/6 PL/SQL: ORA-00942: table or view does not exist
    9/1 PL/SQL: SQL Statement ignored
    28/55 PLS-00364: loop index variable 'I' use is invalid
    28/1 PL/SQL: Statement ignored
    29/25 PLS-00364: loop index variable 'I' use is invalid
    29/1 PL/SQL: Statement ignored
    33/55 PLS-00364: loop index variable 'I' use is invalid
    33/1 PL/SQL: Statement ignored

    Any assistance is very much appreciated

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    It used to be that you had to define utl_file_dir ... now Oracle recommends using create directory ...

    Try:
    create directory log_dir as 'C:\Oracle10\admin\.....';
    create directory user_dir as 'C:\Oracle10\admin\.....';

    grant read on directory log_dir to dba;
    grant read on directory user_dir to public;

    ... see it that doesn't fix you up...

    HTH
    Gregg

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Select any Dictionary (or not)?

    1) Make sure the directory(es) are in utl_file_dir (or do as Gregg suggests).
    2) Login 'sys/pw as sysdba' and:
    GRANT SELECT ANY DICTIONARY to SYSTEM;
    3) Try the following script:
    Code:
    CREATE OR REPLACE PROCEDURE generate_hot_backup_script (p_filename IN VARCHAR2)
    AS
       i                   sys_refcursor;
       i_tablespace_name   VARCHAR2 (36);
       i_file_name         VARCHAR2 (80);
    -- Local variables
       p_file_loc          VARCHAR2 (80)      := 'D:\oracle\product\10.2.0\oradata\smartbnk';
       p_ts_name           VARCHAR2 (36)      := '?';
       p_file_name         VARCHAR2 (80)      := p_filename;
       p_file_handler      UTL_FILE.file_type;
    BEGIN
    -- Create the output file and return the handler to the file
       p_file_handler := UTL_FILE.fopen (p_file_loc, p_file_name, 'W');
    
    -- Determine if the file has been opened successfully
       IF UTL_FILE.is_open (p_file_handler)
       THEN
          BEGIN
             OPEN i FOR 'SELECT tablespace_name, file_name
                           FROM dba_data_files
                          ORDER BY 1, 2';
    
             WHILE 0 = 0
             LOOP
                FETCH i
                 INTO i_tablespace_name, i_file_name;
    
                EXIT WHEN i%NOTFOUND;
    
                IF i_tablespace_name != p_ts_name
                THEN
                   IF p_ts_name != '?'
                   THEN
                      UTL_FILE.put_line (p_file_handler,
                                            'alter tablespace '
                                         || p_ts_name
                                         || ' end backup;'
                                        );
                   END IF;
    
                   UTL_FILE.put_line (p_file_handler,
                                         'alter tablespace '
                                      || i_tablespace_name
                                      || ' begin backup;'
                                     );
                   p_ts_name := i_tablespace_name;
                END IF;
    
                UTL_FILE.put_line (p_file_handler,
                                      'host copy '
                                   || i_file_name
                                   || 'D:\oracle\hotbackup'
                                  );
             END LOOP;
    
             UTL_FILE.put_line (p_file_handler,
                                'alter tablespace ' || p_ts_name || ' end backup;'
                               );
    -- Backup the control file
             UTL_FILE.put_line
                (p_file_handler,
                 'alter database backup controlfile to trace as ''D:\oracle\hotbackup\control.new'';'
                );
    -- Switch the logfile
             UTL_FILE.put_line (p_file_handler, 'alter database switch logfile;');
             DBMS_OUTPUT.put_line ('File created successfully');
          END;
       ELSE
          DBMS_OUTPUT.put_line ('File not created');
       END IF;                                   -- End if file open is successful
    
    -- Close the file
       UTL_FILE.fclose (p_file_handler);
    END;
    /
    SHOW error

    PS: DO NOT create the procedure in SYS or SYSTEM. (See beilstwh's comment bellow)
    Last edited by LKBrwn_DBA; 05-01-09 at 11:38. Reason: Add PS:
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Feb 2009
    Posts
    62
    Yup, system still needs to follow the rules:
    Code:
    SQL> conn system/**********@dev10g
    Connected.
    
    SQL> create or replace procedure test_proc as
      2    v_count pls_integer;
      3  begin
      4    select count(*)
      5    into v_count
      6    from dba_tablespaces;
      7* end;
    SQL> /
    
    Warning: Procedure created with compilation errors.
    
    SQL> show errors
    Errors for PROCEDURE TEST_PROC:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    4/3      PL/SQL: SQL Statement ignored
    6/8      PL/SQL: ORA-00942: table or view does not exist
    
    SQL> conn sys/**********@dev10g as sysdba
    Connected.
    SQL> grant select on dba_tablespaces to system;
    
    Grant succeeded.
    
    SQL> conn system/**********@dev10g
    Connected.
    SQL> create or replace procedure test_proc as
      2    v_count pls_integer;
      3  begin
      4    select count(*)
      5    into v_count
      6    from dba_tablespaces;
      7  end;
      8  /
    
    Procedure created.
    
    SQL>

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    As a side note, SYS should NEVER be used and user procedures should NEVER NEVER be put in system. This is for oracle software only. Make a dba account and use that.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs up Agree wholeheartedly

    Quote Originally Posted by beilstwh
    As a side note, SYS should NEVER be used and user procedures should NEVER NEVER be put in system. This is for oracle software only. Make a dba account and use that.
    Fully agree with beilstwh, create a dba account to manage your db and hold all those anciliary procs/tables/etc you want to execute/keep.

    I myself still find it usefull to create the "identified externally" OPS$ORACLE account and grant it the DBA role.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #10
    Join Date
    Mar 2009
    Posts
    46
    Hello Friends,

    Thanks a lot for your immense suggestions. I appreciate them and would implement them in due course.

    However, am using my test system for this trial and hope using system user should not in any way make the code not to run properly.

    Anyway, used the sys account to :

    GRANTED SELECT ANY DICTIONARY TO SYSTEM

    and

    GRANT SELECT ON DBA_TABLESPACES TO SYSTEM ( this may look as repetition though)

    After this the procedure compiled successfully using the system account

    But then when I Execute the procedure::

    CREATE OR REPLACE PROCEDURE generate_hot_backup_script (p_filename IN VARCHAR2)
    AS
    i sys_refcursor;
    i_tablespace_name VARCHAR2 (36);
    i_file_name VARCHAR2 (80);
    -- Local variables
    p_file_loc VARCHAR2 (80) := 'D:\oracle\product\10.2.0\oradata\smartbnk';
    p_ts_name VARCHAR2 (36) := '?';
    p_file_name VARCHAR2 (80) := p_filename;
    p_file_handler UTL_FILE.file_type;
    BEGIN
    -- Create the output file and return the handler to the file
    p_file_handler := UTL_FILE.fopen (p_file_loc, p_file_name, 'W');

    -- Determine if the file has been opened successfully
    IF UTL_FILE.is_open (p_file_handler)
    THEN
    BEGIN
    OPEN i FOR 'SELECT tablespace_name, file_name
    FROM dba_data_files
    ORDER BY 1, 2';

    WHILE 0 = 0
    LOOP
    FETCH i
    INTO i_tablespace_name, i_file_name;

    EXIT WHEN i%NOTFOUND;

    IF i_tablespace_name != p_ts_name
    THEN
    IF p_ts_name != '?'
    THEN
    UTL_FILE.put_line (p_file_handler,
    'alter tablespace '
    || p_ts_name
    || ' end backup;'
    );
    END IF;

    UTL_FILE.put_line (p_file_handler,
    'alter tablespace '
    || i_tablespace_name
    || ' begin backup;'
    );
    p_ts_name := i_tablespace_name;
    END IF;

    UTL_FILE.put_line (p_file_handler,
    'host copy '
    || i_file_name
    || 'D:\oracle\hotbackup'
    );
    END LOOP;

    UTL_FILE.put_line (p_file_handler,
    'alter tablespace ' || p_ts_name || ' end backup;'
    );
    -- Backup the control file
    UTL_FILE.put_line
    (p_file_handler,
    'alter database backup controlfile to trace as ''D:\oracle\hotbackup\control.new'';'
    );
    -- Switch the logfile
    UTL_FILE.put_line (p_file_handler, 'alter database switch logfile;');
    DBMS_OUTPUT.put_line ('File created successfully');
    END;
    ELSE
    DBMS_OUTPUT.put_line ('File not created');
    END IF; -- End if file open is successful

    -- Close the file
    UTL_FILE.fclose (p_file_handler);
    END;
    /




    the following errors results:

    SQL> exec generate_hot_backup_script;

    begin generate_hot_backup_script; end;

    ORA-06550: line 2, column 7:
    PLS-00306: wrong number or types of arguments in call to 'GENERATE_HOT_BACKUP_SCRIPT'
    ORA-06550: line 2, column 7:
    PL/SQL: Statement ignored

    ---
    Am researching for the solution but since more hands make less work I would be grateful for any assistance that helps in resolving this issue quickly

    Much thanks in advance

  11. #11
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    The procedure is expecting a variable to be passed to it ...

    CREATE OR REPLACE PROCEDURE generate_hot_backup_script (p_filename IN VARCHAR2)

    ex: exec generate_hot_backup_script('Name_Of_A_File');

Posting Permissions

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