CREATE OR REPLACE function PackageCodeForTable(
    pOwner in varchar2
    , pTableName in varchar2
    , pKeyFieldName in varchar2
) return clob is

GENERATION_INFO_TEMPLATE constant clob :=
'
/*******************************************************************************
    Сервисный пакет для таблицы ' || upper(pOwner) || '.' || upper(pTableName) || '
    Код пакета сгенерирован функцией PackageCodeForTable
    Дата: ' || to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') || '  
*******************************************************************************/
';

ROW_FUNCTION_SPEC_TEMPLATE constant clob := 
'
function AllColumns(pKey in {$OWNER}.{$TABLE_NAME}.{$KEY_FIELD_NAME}%type)
    return {$OWNER}.{$TABLE_NAME}%rowtype;
';

FUNCTION_SPEC_TEMPLATE constant clob := 
'
function {$FIELD_NAME}(pKey in {$OWNER}.{$TABLE_NAME}.{$KEY_FIELD_NAME}%type)
    return {$OWNER}.{$TABLE_NAME}.{$FIELD_NAME}%type;
';

ROW_FUNCTION_IMPL_TEMPLATE constant clob := 
'
function AllColumns(pKey in {$OWNER}.{$TABLE_NAME}.{$KEY_FIELD_NAME}%type)
    return {$OWNER}.{$TABLE_NAME}%rowtype is
vResult {$OWNER}.{$TABLE_NAME}%rowtype;
begin
    select  t.*
    into    vResult
    from    {$OWNER}.{$TABLE_NAME} t
    where   t.{$KEY_FIELD_NAME} = AllColumns.pKey
    ;
    
    return vResult; 
end AllColumns;
';

FUNCTION_IMPL_TEMPLATE constant clob := 
'
function {$FIELD_NAME}(pKey in {$OWNER}.{$TABLE_NAME}.{$KEY_FIELD_NAME}%type)
    return {$OWNER}.{$TABLE_NAME}.{$FIELD_NAME}%type is
vResult {$OWNER}.{$TABLE_NAME}.{$FIELD_NAME}%type;
begin
    select  t.{$FIELD_NAME}
    into    vResult
    from    {$OWNER}.{$TABLE_NAME} t
    where   t.{$KEY_FIELD_NAME} = {$FIELD_NAME}.pKey
    ;
    
    return vResult; 
end {$FIELD_NAME};
';

vResult clob;
vFunctionSpecificationCode clob;
vFunctionImplementationCode clob;
vPackageSpecificationCode clob;
vPackageImplementationCode clob;
vQueryTableColumns clob;
vTableColumn TStringArray; -- DDL создания типа: CREATE OR REPLACE TYPE TStringArray is varray(200) of varchar2(500);

begin
    vQueryTableColumns := 'select column_name from dba_tab_columns where owner = :pOwner and table_name = :pTableName order by column_id';
    execute immediate vQueryTableColumns bulk collect into vTableColumn using upper(pOwner), upper(pTableName);
    
    vPackageSpecificationCode := 'create package ' || pOwner || '.p#' || pTableName || ' as' || utl_tcp.crlf || GENERATION_INFO_TEMPLATE;

    vFunctionSpecificationCode := ROW_FUNCTION_SPEC_TEMPLATE;
    vFunctionSpecificationCode := replace(vFunctionSpecificationCode, '{$OWNER}', pOwner);
    vFunctionSpecificationCode := replace(vFunctionSpecificationCode, '{$TABLE_NAME}', pTableName);
    vFunctionSpecificationCode := replace(vFunctionSpecificationCode, '{$KEY_FIELD_NAME}', pKeyFieldName);
    vPackageSpecificationCode := vPackageSpecificationCode || vFunctionSpecificationCode;

    for i in 1..vTableColumn.count loop
        vFunctionSpecificationCode := FUNCTION_SPEC_TEMPLATE;
        vFunctionSpecificationCode := replace(vFunctionSpecificationCode, '{$OWNER}', pOwner);
        vFunctionSpecificationCode := replace(vFunctionSpecificationCode, '{$TABLE_NAME}', pTableName);
        vFunctionSpecificationCode := replace(vFunctionSpecificationCode, '{$KEY_FIELD_NAME}', pKeyFieldName);
        vFunctionSpecificationCode := replace(vFunctionSpecificationCode, '{$FIELD_NAME}', lower(vTableColumn(i)));
        
        vPackageSpecificationCode := vPackageSpecificationCode || vFunctionSpecificationCode;
    end loop;
    
    vPackageSpecificationCode := vPackageSpecificationCode || utl_tcp.crlf || 'end p#' || pTableName || ';' || utl_tcp.crlf;


    vPackageImplementationCode := 'create package body ' || pOwner || '.p#' || pTableName || ' as' || utl_tcp.crlf || GENERATION_INFO_TEMPLATE;    

    vFunctionImplementationCode := ROW_FUNCTION_IMPL_TEMPLATE;
    vFunctionImplementationCode := replace(vFunctionImplementationCode, '{$OWNER}', pOwner);
    vFunctionImplementationCode := replace(vFunctionImplementationCode, '{$TABLE_NAME}', pTableName);
    vFunctionImplementationCode := replace(vFunctionImplementationCode, '{$KEY_FIELD_NAME}', pKeyFieldName);
    vPackageImplementationCode := vPackageImplementationCode || vFunctionImplementationCode;

    for i in 1..vTableColumn.count loop
        vFunctionImplementationCode := FUNCTION_IMPL_TEMPLATE;
        vFunctionImplementationCode := replace(vFunctionImplementationCode, '{$OWNER}', pOwner);
        vFunctionImplementationCode := replace(vFunctionImplementationCode, '{$TABLE_NAME}', pTableName);
        vFunctionImplementationCode := replace(vFunctionImplementationCode, '{$KEY_FIELD_NAME}', pKeyFieldName);
        vFunctionImplementationCode := replace(vFunctionImplementationCode, '{$FIELD_NAME}', lower(vTableColumn(i)));
        
        vPackageImplementationCode := vPackageImplementationCode || vFunctionImplementationCode;
    end loop;
    
    vPackageImplementationCode := vPackageImplementationCode || utl_tcp.crlf || 'end p#' || pTableName || ';' || utl_tcp.crlf;
    
    
    vResult := vPackageSpecificationCode || '/' || utl_tcp.crlf || utl_tcp.crlf || utl_tcp.crlf || vPackageImplementationCode || '/'; 
    
    return vResult;
end PackageCodeForTable;
/