Home » Applications » Other Application Suites » Delphi (doa) generates ora-06502 error
Delphi (doa) generates ora-06502 error [message #475484] Wed, 15 September 2010 05:13
bibber
Messages: 38
Registered: August 2006
Member
Hi,

I'm using a delphi with direct-oracle-access components to communicate with a oracle 10g database. In an oracle package I have created a function that builds up a select-statement and returns it as a varchar2.

Since, the return value can be longer than 4000 characters, I have changed it to a clob. If I call the function directly in SQL*PLUS, I succesfully get the return string. However, when I call the function in my delphi TOraclepackage component, I get the following error: "ORA-06502: PL/SQL: numeric or value error: character string buffer too small".

To my opinion, the delphi variables are the problem, since in SQL*PLUS no problems occurs.

How can I solve my delphi-oracle problem?
thnx, Rob

--ORACLE FUNCTION IN PACKAGE
function geef_sql
  (        p_owner            varchar2
  ,        p_table_name       varchar2
  ,        p_srid             varchar2
  ,        p_export_geometrie varchar2) return clob
  is
    --
    cursor c1
    is
    select decode(data_type, 'VARCHAR2', '||''|$$'' || gvi_exp2sqlldr.strip(' || column_name ||  ') || ''$$'' '
                           , 'CHAR',     '||''|$$'' || gvi_exp2sqlldr.strip(' || column_name ||  ') || ''$$'' '
                           , 'NUMBER',   '||''|'' || to_char(' || column_name || ') '
                           , 'DATE',     '||''|'' || to_char(' || column_name || ', ''YYYY-MM-DD HH24:MI:SS'' ) '
                  ) as attribuut
    from   all_tab_columns
    where  table_name = upper(p_table_name)
    and    owner = upper(p_owner)
    and    data_type not in ('SDO_GEOMETRY', 'CLOB')
    order by column_id;
    --
    cursor c2
    is
    select ', ' || column_name as attribuut
    from   all_tab_columns
    where  table_name = upper(p_table_name)
    and    owner = upper(p_owner)
    and    data_type = 'CLOB'
    order by column_id;
    --
    cursor c3
    is
    select ', gvi_exp2sqlldr.geef_geometrie_clob(' || nvl(p_srid, 'NULL') || ',' || column_name || ')  ' as attribuut
    from   all_tab_columns
    where  table_name = upper(p_table_name)
    and    owner = upper(p_owner)
    and    data_type = 'SDO_GEOMETRY'
    order by column_id;
    --
    cursor c4
    is
    select ', gvi_exp2sqlldr.geef_geometrie_wkt(' || nvl(p_srid, 'NULL') || ',' || column_name || ')  ' as attribuut
    from   all_tab_columns
    where  table_name = upper(p_table_name)
    and    owner = upper(p_owner)
    and    data_type = 'SDO_GEOMETRY'
    order by column_id;
    --
    l_sql  clob;
    --
  begin
    --

    for r1 in c1 loop
      l_sql := l_sql || r1.attribuut;
    end loop;
    --
    for r2 in c2 loop
      l_sql := l_sql || r2.attribuut || ' as CLOB_' || c2%rowcount;
    end loop;
    --

    if (upper(p_export_geometrie) = 'WKT') then

    -- exporteren als wkt
    for r4 in c4 loop
      l_sql := l_sql || r4.attribuut || ' as GEOMETRIE_' || c4%rowcount;
    end loop;

    else

    -- exporteren als sqlldr
    for r3 in c3 loop
      l_sql := l_sql || r3.attribuut || ' as GEOMETRIE_' || c3%rowcount;
    end loop;

    end if;
    --
    l_sql := 'select null ' || l_sql || ' from ' || p_owner || '.' || p_table_name;
    --
    return(l_sql);
    --
  end;

// delphi code
var
  l_sql: String;
begin
......
l_sql := OraclePackage1.CallStringFunction('geef_sql',  ['p_owner', g_owner, 'p_table_name', p_tabelnaam, 'p_srid', g_srid, 'p_export_geometrie', g_export_geometrie ]);
Previous Topic: database error number 17003 has orrcured
Next Topic: plz help
Goto Forum:
  


Current Time: Mon Jul 28 22:23:21 CDT 2014

Total time taken to generate the page: 0.08934 seconds