Accessing dblink using Dynamic SQL

As I had mentioned in my previous post. After I install oracle 8i machine on my XP Professional succesfully, I should create database link in my 8i and create store procedure using dynamic SQL. I need dynamic SQL because I need to build a database gateway that collect data from many resource of database machine. So, technically the database link is represented as variable i/o in the store procedure.

Here, the example code that contain dynamic sql to query specific data from table x in database link name y:

ds_numrows NUMBER; --as global variable
ds_cursor_id NUMBER; --as global variable
queryString := 'SELECT x.attr1, x.attr2 from x@y';
ds_cursor_id := DBMS_SQL.open_cursor;
DBMS_SQL.parse (ds_cursor_id, queryString, DBMS_SQL.v7);
DBMS_SQL.define_column (ds_cursor_id, 1, queryString, 1024);
ds_numrows := DBMS_SQL.EXECUTE (ds_cursor_id);
LOOP
IF DBMS_SQL.fetch_rows (ds_cursor_id) = 0
THEN
EXIT;
END IF;
DBMS_SQL.column_value (ds_cursor_id, 1, queryString );
END LOOP;
DBMS_SQL.close_cursor (ds_cursor_id);
COMMIT;


Advertisement

Filed under: Uncategorized