query_string := 'SELECT '||dbms_assert.sql_object_name(trim(both ' ' from return_field))|| 
                   ' FROM '||dbms_assert.schema_name(trim(both ' ' from from_schema))||
                        '.'||dbms_assert.sql_object_name(trim(both ' ' from from_table))||  
                  ' WHERE '||dbms_assert.sql_object_name(key_field) || ' = '||key_value;



 EXECUTE IMMEDIATE query_string into return_result;

无效的sql对象.

从文档中我觉得表中的任何对象都是一个sql对象？
什么错了？

考虑在oracle 10g中执行以下功能

在10g上下文中考虑以下函数

    CREATE OR REPLACE FUNCTION scott.tab_lookup (key_field CHAR,key_value CHAR,from_schema CHAR,from_table CHAR,return_field CHAR,return_type CHAR)
    RETURN VARCHAR2 IS
    result_a varchar2(1000);
    query_string VARCHAR2(4000);

    /*version 0.5*/
    BEGIN

    query_string := 'SELECT '||dbms_assert.qualified_sql_name(trim(from_table||'.'||return_field))|| 
                       ' FROM '||dbms_assert.schema_name(trim(from_schema))||
                            '.'||dbms_assert.sql_object_name(trim(from_table))||  
                      ' WHERE '||dbms_assert.qualified_sql_name(from_table||'.'||key_field) || ' = '||key_value;

      IF(return_type = 'sql') THEN
         result_a := query_string;
      ELSE
         EXECUTE IMMEDIATE query_string 
         --USING key_value  
         into result_a;
      END IF;

      RETURN (result_a);
    EXCEPTION 
    WHEN 
        NO_DATA_FOUND THEN 
           RETURN(NULL);
    WHEN
        TOO_MANY_ROWS THEN 
           RETURN('**ERR_DUPLICATE**');
    WHEN OTHERS
    THEN 
    /*
    ORA-44001   INVALID_SCHEMA_NAME 
    ORA-44002   INVALID_OBJECT_NAME
    ORA-44003   INVALID_sql_NAME
    ORA-44004   INVALID_QUALIFIED_sql_NAME
    */
        IF    sqlCODE = -44001 THEN 
              RETURN('*ERR_INVALID_SCHEMA*');
        ELSIF sqlCODE = -44002 THEN 
              RETURN('*ERR_INVALID_OBJECT*');
        ELSIF sqlCODE = -44003 THEN 
              RETURN('*ERR_INVALID_sql_NAME*');
        ELSIF sqlCODE = -44004 THEN 
              RETURN('*ERR_INVALID_QALIFIED_sqlNAME*');
        end if;         
        return ('*ERR_'||sqlcode);
    END;
    /

我收到了ERR_INVALID_OBJECT

--to get the Genrated sql as Value  

    Select scott.tab_lookup('ID',1,'TEST','TEST_TABLE','TEST_DESC','sql') from dual;

-- -or-

-- to get the value returned from database field

    Select scott.tab_lookup('ID','') from dual;

我的桌子就像

    TEST_TABLE  
    ====================
    ID,TEST_DESC
    ====================
    '11','TEST 1'
    '12','TEST 5000'
    '13','TEST INPUT VALUE'
    '14','JUNK VALUE'
    '50','TEST VALUE 50'

这个表是’TEST’模式,我与SCOTT连接
并且SCOTT在TEST.TEST_TABLE上有’GRANT SELECT给斯科特’

我仍然得到错误

ERR_INVALID_OBJECT