Using ALL_CONS_COLUMN view inside a function

I hope someone can help me sort out something that makes no sense to me.

I have written an Oracle function that returns a VARCHAR2 (see below) You can see it has a cursor for a select of rows from the ALL_CONS_COLUMN view.

The function works fine when I run with p_owner set to the current user, but I have hit a snag when I put in other owners.

Here is an example when I run my function with parameters of 'SYS' and 'REGISTRY_PK', it returns a NULL string because no rows are found. BUT when I run this select, I get 2 rows returned.   Select * from ALL_CONS_COLUMNS
   Where owner = 'SYS' and constraint_name = 'REGISTRY_PK'

When I hit this problem, I looked for the simplest case I could find that had a problem. It is based on the statement.   select count(*) cc from all_tables where owner = 'SYS'

The value returned inside a function is 27 while the query returns 703.

This is an Oracle 10g database. I am only using example with SYS as owner since others can see if they get the same results. I had this problem with p_owner set other users as well.

Does anyone know what is going on?

How do I get a function to return a VARCHAR2 list the column names?

Thank you for the help, Brett

• this function generates a string to use in a SQL query
• the string concatenates all columns of constraint
• in order to have a single varchar to match CREATE OR REPLACE FUNCTION concatenateConstraintColumns(p_owner VARCHAR2, p_constraint_name VARCHAR2) RETURN VARCHAR2 IS v_column VARCHAR2(40); v_sql VARCHAR2(1000); v_join VARCHAR2(12) := ' || ''|'' || '; cursor column_cursor is select * from all_cons_columns where owner = p_owner and constraint_name = p_constraint_name order by position; BEGIN for column in column_cursor loop v_column := column.column_name; if (column.position = 1) then v_sql := v_column; else v_sql := v_sql || v_join || v_column; end if; end loop; return v_sql; END concatenateConstraintColumns;

--
http://www.freelists.org/webpage/oracle-l