Re: PL/SQL question...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 27 May 1998 21:22:18 GMT
Message-ID: <356e81c2.5250229@192.86.155.100>

A copy of this was sent to smaclennan_at_nospam.ism.ca (Scott MacLennan) (if that email address didn't require changing) On Wed, 27 May 1998 15:14:44 GMT, you wrote:

>Ok,
>
>If I need to loop through each column (each of which is an attribute),
>how can I substitute a string for the column name in a function? It
>works for COUNT(v_attribute) but not for MIN(v_attribute)...
>
>Here's an eg.
>declare
> v_attribute varchar2(10);
>
>BEGIN
>v_attribute:='age';
>
>select count(v_attribute) into v_temp from big_table; <==WORKS
>
>select min(v_attribute) into v_temp from big_table; <== Nope
>END;
>
>Thanks...

it didn't work for count(v_attribute) either tho (well it did work but not the way you expected it to).

select count(v_attribute) into v_temp from big_table

is the same as (in your case)

select count('age') into v_temp from big_table

which is not the same as select count(age) into v_temp from big_table.

Let me demonstrate:

SQL> create table x ( age int );
Table created.

SQL> insert into x values ( NULL );
1 row created.

SQL> insert into x values ( 1 );
1 row created.

SQL> declare

  2     v_attribute varchar2(10) default 'age';
  3     v_cnt       number;
  4  begin
  5     select count(v_attribute) into v_cnt from x;
  6     dbms_output.put_line( v_cnt );
  7     select count(age) into v_cnt from x;
  8     dbms_output.put_line( v_cnt );

  9 end;
 10 /
2
1
PL/SQL procedure successfully completed.

See how the output is 2, 1? count(v_attribute) is 2, count(age) is 1. Count( <value> ) counts the number of non-null occurrences of value across rows. Count('age') simply counts the number of rows (its the same as count(*).) count(age) counted the number of non-null ages in the table.

So, how do you do what you want to do in pl/sql? dbms_sql...

create or replace function demo( p_fieldname in varchar2,

                                 p_tablename in varchar2 )

return varchar2
is

    l_theCursor     integer;
    l_columnValue   varchar2(2000);
    l_status        integer;

begin

    l_theCursor := dbms_sql.open_cursor;

    dbms_sql.parse( l_theCursor,

                    'select ' || p_fieldname || ' from ' || p_tablename,
                     dbms_sql.native );

    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );

    l_status := dbms_sql.execute(l_theCursor);     if ( dbms_sql.fetch_rows(l_theCursor) <= 0 ) then

        l_columnValue := NULL;
    else

        dbms_sql.column_value( l_theCursor, 1, l_columnValue );     end if;
    dbms_sql.close_cursor(l_theCursor);

    return l_columnValue;
end;

Then you can do things like:

SQL> exec dbms_output.put_line( demo( 'min(sal)', 'emp' ) ); 800
PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line( demo( 'count(mgr)', 'emp' ) ); 13
PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line( demo( 'count(*)', 'emp' ) ); 14
PL/SQL procedure successfully completed.

But remember, roles are never enabled during the execution of a procedure.

Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence dbms_sql won't be able to do it either.

so if you get an access error (no such object error) in the pl/sql routine, Grant select on the table directly to the owner of the procedure and it'll work.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed May 27 1998 - 16:22:18 CDT