conver anonymous procedure to store procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: conver anonymous procedure to store procedure

  1. #1
    Join Date
    Mar 2002
    Posts
    3

    Unhappy

    Dear friends,

    I had a anoymous procedure for checking tablespace usage,
    it can be run without problem. When I conver it to a store procedure, it have compilation error. Can anyone help me??

    the anonymous procedure as follow (run on ora 8.1.6):

    declare
    name char(20);
    mbytes char(20);
    used char(20);
    free char(20);
    pct_used char(20);
    cursor test_cur is
    select a.tablespace_name name,
    sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) /
    1048576 mbytes, round(((sum(b.bytes)/
    count( distinct a.file_id||'.'||a.block_id ) -
    sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
    round((sum(a.bytes)/count( distinct b.file_id ) /
    1048576),2) free,
    round(100 * ( (sum(b.bytes)/
    count( distinct a.file_id||'.'||a.block_id ))
    -(sum(a.bytes)/count( distinct b.file_id ) )) /
    (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2)
    pct_used
    from sys.dba_free_space a, sys.dba_data_files b
    where a.tablespace_name = b.tablespace_name
    group by a.tablespace_name, b.tablespace_name;
    test_val test_cur%rowtype;

    begin
    open test_cur;
    dbms_output.put_line('Table Space ' ||' '||
    'mbytes ' ||' '||
    'used ' ||' '||
    'free ' ||' '||
    'pct_used ');
    dbms_output.put_line('--------------------------------------------------------------------------------------------------');
    loop
    fetch test_cur into test_val;
    exit when test_cur%notfound;
    name := test_val.name;
    mbytes := test_val.mbytes;
    used := test_val.used;
    free := test_val.free;
    pct_used := test_val.pct_used;
    dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '|| pct_used);
    end loop;
    close test_cur;
    end;


    The store proc (with error) as follow:

    create procedure dsp_space
    AS
    name char(20);
    mbytes char(20);
    used char(20);
    free char(20);
    pct_used char(20);
    cursor test_cur is
    select a.tablespace_name name,
    sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) /
    1048576 mbytes, round(((sum(b.bytes)/
    count( distinct a.file_id||'.'||a.block_id ) -
    sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
    round((sum(a.bytes)/count( distinct b.file_id ) /
    1048576),2) free,
    round(100 * ( (sum(b.bytes)/
    count( distinct a.file_id||'.'||a.block_id ))
    -(sum(a.bytes)/count( distinct b.file_id ) )) /
    (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2)
    pct_used
    from sys.dba_free_space a, sys.dba_data_files b
    where a.tablespace_name = b.tablespace_name
    group by a.tablespace_name, b.tablespace_name;
    test_val test_cur%rowtype;

    begin
    open test_cur;
    dbms_output.put_line('Table Space ' ||' '||
    'mbytes ' ||' '||
    'used ' ||' '||
    'free ' ||' '||
    'pct_used ');
    dbms_output.put_line('--------------------------------------------------------------------------------------------------');
    loop
    fetch test_cur into test_val;
    exit when test_cur%notfound;
    name := test_val.name;
    mbytes := test_val.mbytes;
    used := test_val.used;
    free := test_val.free;
    pct_used := test_val.pct_used;
    dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '|| pct_used);
    end loop;
    close test_cur;
    end;
    /
    kelvin

  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    What's the error ?

  3. #3
    Join Date
    Jul 2001
    Location
    ksa
    Posts
    37

    Smile replace as by is

    hi leungcwk
    try it now it will be work
    execute dsp_space

    note : I used oracle8i 8.1.7

    -----------------------------------------------------------------------
    create or replace procedure dsp_space
    is
    name char(20);
    mbytes char(20);
    used char(20);
    free char(20);
    pct_used char(20);
    cursor test_cur is
    select a.tablespace_name name,
    sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) /
    1048576 mbytes, round(((sum(b.bytes)/
    count( distinct a.file_id||'.'||a.block_id ) -
    sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
    round((sum(a.bytes)/count( distinct b.file_id ) /
    1048576),2) free,
    round(100 * ( (sum(b.bytes)/
    count( distinct a.file_id||'.'||a.block_id ))
    -(sum(a.bytes)/count( distinct b.file_id ) )) /
    (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2)
    pct_used
    from sys.dba_free_space a, sys.dba_data_files b
    where a.tablespace_name = b.tablespace_name
    group by a.tablespace_name, b.tablespace_name;
    test_val test_cur%rowtype;
    begin
    open test_cur;
    dbms_output.put_line('Table Space ' ||' '||
    'mbytes ' ||' '||
    'used ' ||' '||
    'free ' ||' '||
    'pct_used ');
    dbms_output.put_line('--------------------------------------------------------------------------------------------------');
    loop
    fetch test_cur into test_val;
    exit when test_cur%notfound;
    name := test_val.name;
    mbytes := test_val.mbytes;
    used := test_val.used;
    free := test_val.free;
    pct_used := test_val.pct_used;
    dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '|| pct_used);
    end loop;
    close test_cur;
    end;
    /


    -----------------------------------------------------------------------

    If There Is somthing to do it just do it with oracle ....

  4. #4
    Join Date
    Mar 2002
    Posts
    3

    Re: replace as by is


    Thanks Mohammed.
    However, I still get compilation errors.
    There seems some problem on cursor declaration.
    If you have any idea to fix the error, please feed back
    to me. thanks



    SQL> l
    1 create or replace procedure dsp_space
    2 is
    3 name char(20);
    4 mbytes char(20);
    5 used char(20);
    6 free char(20);
    7 pct_used char(20);
    8 cursor test_cur is
    9 select a.tablespace_name name,
    10 sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) /
    11 1048576 mbytes, round(((sum(b.bytes)/
    12 count( distinct a.file_id||'.'||a.block_id ) -
    13 sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
    14 round((sum(a.bytes)/count( distinct b.file_id ) /
    15 1048576),2) free,
    16 round(100 * ( (sum(b.bytes)/
    17 count( distinct a.file_id||'.'||a.block_id ))
    18 -(sum(a.bytes)/count( distinct b.file_id ) )) /
    19 (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2)
    20 pct_used
    21 from sys.dba_free_space a, sys.dba_data_files b
    22 where a.tablespace_name = b.tablespace_name
    23 group by a.tablespace_name, b.tablespace_name;
    24 test_val test_cur%rowtype;
    25 begin
    26 open test_cur;
    27 dbms_output.put_line('Table Space ' ||' '||
    28 'mbytes ' ||' '||
    29 'used ' ||' '||
    30 'free ' ||' '||
    31 'pct_used ');
    32 dbms_output.put_line('-----------------------------------------------------
    ---------------------------------------------');
    33 loop
    34 fetch test_cur into test_val;
    35 exit when test_cur%notfound;
    36 name := test_val.name;
    37 mbytes := test_val.mbytes;
    38 used := test_val.used;
    39 free := test_val.free;
    40 pct_used := test_val.pct_used;
    41 dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '||
    pct_used);
    42 end loop;
    43 close test_cur;
    44* end;
    SQL> /

    Warning: Procedure created with compilation errors.

    SQL> show error
    Errors for PROCEDURE DSP_SPACE:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    8/8 PLS-00341: declaration of cursor 'TEST_CUR' is incomplete or
    malformed

    9/1 PL/SQL: SQL Statement ignored
    21/28 PLS-00201: identifier 'SYS.DBA_DATA_FILES' must be declared
    24/10 PL/SQL: Item ignored
    34/1 PL/SQL: SQL Statement ignored
    34/21 PLS-00320: the declaration of the type of this expression is
    incomplete or malformed

    36/1 PL/SQL: Statement ignored

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    36/9 PLS-00320: the declaration of the type of this expression is
    incomplete or malformed

    37/1 PL/SQL: Statement ignored
    37/11 PLS-00320: the declaration of the type of this expression is
    incomplete or malformed

    38/1 PL/SQL: Statement ignored
    38/9 PLS-00320: the declaration of the type of this expression is
    incomplete or malformed


    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    39/1 PL/SQL: Statement ignored
    39/9 PLS-00320: the declaration of the type of this expression is
    incomplete or malformed

    40/1 PL/SQL: Statement ignored
    40/13 PLS-00320: the declaration of the type of this expression is
    incomplete or malformed




    -----------------------------------------------------------------------

    [/B][/QUOTE]

    [Edited by leungcwk on 03-19-2002 at 11:14 PM]
    kelvin

  5. #5
    Join Date
    Mar 2002
    Posts
    1

    Ans : Convert anonymous to stored procedure

    create or replace procedure dsp_space
    is
    name char(20);
    mbytes char(20);
    used char(20);
    free char(20);
    pct_used char(20);
    cursor test_cur is
    select a.tablespace_name name,
    sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) / 1048576 mbytes, round(((sum(b.bytes)/ count( distinct a.file_id||'.'||a.block_id )
    - sum(a.bytes)/count( distinct b.file_id ))/ 1048576),2) used,
    round((sum(a.bytes)/count( distinct b.file_id ) / 1048576),2) free,
    round(100 * ( (sum(b.bytes)/ count( distinct a.file_id||'.'||a.block_id ))
    -(sum(a.bytes)/count( distinct b.file_id ) )) /
    (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )),2) pct_used
    from sys.dba_free_space a, sys.dba_data_files b
    where a.tablespace_name = b.tablespace_name
    group by a.tablespace_name, b.tablespace_name;
    begin
    dbms_output.put_line('Table Space ' ||' '|| 'mbytes ' ||' '|| 'used ' ||' '|| 'free ' ||' '|| 'pct_used ');
    FOR test_rec in test_cur Loop
    name := test_rec.name;
    mbytes := test_rec.mbytes;
    used := test_rec.used;
    free := test_rec.free;
    pct_used := test_rec.pct_used;
    dbms_output.put_line(name ||' '|| mbytes ||' '|| used ||' '|| free ||' '|| pct_used);
    End Loop;
    end;
    /

  6. #6
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    the problem has nothing to do with this code. it is with sys tables.

    first grant select on dba_free_space and dba_data_files to the user that is going to execute the procedure.

    connect sys/password

    grant select on dba_free_space, dba_data_files to user;

    and then compile your procedure.

    - you cannot use sys tables with your schema procedure if you don't have priviledges to use them.


    F.

  7. #7
    Join Date
    Mar 2002
    Posts
    3

    Talking

    Originally posted by Mnemonical

    the problem has nothing to do with this code. it is with sys tables.

    first grant select on dba_free_space and dba_data_files to the user that is going to execute the procedure.

    connect sys/password

    grant select on dba_free_space, dba_data_files to user;

    and then compile your procedure.

    - you cannot use sys tables with your schema procedure if you don't have priviledges to use them.


    F.
    Thanks Mnemonical,

    This is the solution! problem fixed, thank you.

    Regards,
    Kelvin
    kelvin

Posting Permissions

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


Click Here to Expand Forum to Full Width