Re: Dynamic SQL and Database Links

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 14 Jul 1999 19:48:56 GMT
Message-ID: <378de62f.16455091@inet16.us.oracle.com>

On 14 Jul 1999 19:03:48 GMT, farnham_at_spot.Colorado.EDU (Jenny Farnham) wrote:

>Scenario:
>
>We need to insert data into a table via a database
>link.
>
>Questions:
>
>1.) Has anyone used dynamic sql to create
>the insert statement so that depending on what
>database you are logged into - the correct
>database link is used? and if so, how did you
>get around the 2000 character limit?
>
>We have this huge insert statement with lots
>of data.
>
>Any ideas?

First, what 2000 character limit? In pl/sql you can create variables up to 32k in length.

Something like this might do the trick.

procedure do_insert( p_db_link varchar2 ) is

  l_cursor number;
  l_status number;
  l_insert varchar2(32767);

begin

  l_cursor := dbms_sql.open_cursor;
  l_insert := 'insert into T@' || p_db_link || ' ( c1, c2, c3 ) ';
  l_insert := l_insert || ' values ( 1, 2, 3 )';

  dbms_sql.parse( l_cursor, l_insert, dbms_sql.native );   l_status := dbms_sql.execute( l_cursor );   dbms_sql.close_cursor( l_cursor );
end;
/

If your insert statement breaks the 32k limit then use

procedure do_insert( p_db_link varchar2 ) is

  l_cursor number;
  l_status number;
  l_insert dbms_sql.varchar2s;  -- pl/sql table of varchar2(256)

begin

  l_cursor := dbms_sql.open_cursor;
  l_insert(1) := 'insert into T@' || p_db_link; 
  l_insert(2) := ' ( c1, c2, c3 ) ';
  l_insert(3) := ' values ( 1, 2, 3 )';

  dbms_sql.parse( l_cursor, l_insert, 1, 3, true, dbms_sql.native );   l_status := dbms_sql.execute( l_cursor );   dbms_sql.close_cursor( l_cursor );
end;
/

hope this helps.

chris.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jul 14 1999 - 14:48:56 CDT