2

Please help me debug this:

Working:

When I execute:

set serveroutput on

declare
    middle_name     varchar2(30);
begin 
    middle_name := '0000000231';

    for x in (
            select distinct tablespace_name
            from all_tables
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_tab_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_indexes
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_ind_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lobs
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lob_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            order by 1
        )
    loop
            dbms_output.put_line('nir2');
    end loop;
end;
/

I see:

nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2
nir2

PL/SQL procedure successfully completed.

But when I execute:

create or replace package "PKG_PROVISIONING" as
    procedure prc_create_tablespace(    
        v_datadir in varchar2
    );      
end PKG_PROVISIONING;
/


create or replace package body "PKG_PROVISIONING" as
    procedure prc_create_tablespace(v_datadir in varchar2)
    is
        middle_name     varchar2(30);
    begin
        dbms_output.put_line('nir');
        middle_name := '0000000231';
        for x in (
            select distinct tablespace_name
            from all_tables
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_tab_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_indexes
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_ind_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lobs
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            union
            select distinct tablespace_name
            from all_lob_partitions
            where tablespace_name like '%\_'||middle_name||'\_%' escape '\'
            order by 1
        )
        loop
            dbms_output.put_line('nir2');
        end loop;
    end prc_create_tablespace;
end PKG_PROVISIONING;
/

I see:

exec PKG_PROVISIONING.prc_create_tablespace('test');
nir

PL/SQL procedure successfully completed.

Both are from the same user. this use has DBA privelage.

1 Answer 1

3

Many times, when the code is not correctly working iinside package/procedure and is ok in anonymous PL/SQL; it comes from the AUTHID clause restrictions:

http://psoug.org/definition/AUTHID.htm

1
  • Thanks a lot! adding it to the package definition fix it.
    – Nir
    Jan 14, 2014 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.