Oracle heterogeneous system. My Oracle HS agaar short easy to remember. What's that? Oracle HS is a system that allows for manipulation of other databases outside the system databse being contacted. Other systems can be an Oracle database, or a non-Oracle databases, such as SQL Server or MySQL.
With Oracle HS system settings, we can do SQL query on other databases on our Oracle session. For example we want to fetch the data in a table in another database, then query it will be like this:
SELECT * FROM rfq@hsmsql
There are additional @ hsmsql which shows HS Oracle database link that has been set-up before (HS Oracle setup is not discussed in this post).
Well, the problem occurs when the Oracle HS in the script used in PL / SQL. Oracle HS can not read or fill in the variables PL / SQL. Consider queries PL / SQL the following:
SELECT Count( * ) INTO v_count FROM rfq@hsmsql
SELECT * FROM rfq@hsmsql WHERE rfqnum = v_rfqnum
In the normal query with no Oracle HS, the query above can work well. v_count row will contain the number obtained from table RFQ, whereas the second query will be filtered for rows that have the same rfqnum with v_rfqnum. But the Oracle did not HS. Oracle HS can not enter the Count (*) into a variable v_count and can not filter the content rfqnum record variabel v_rfqnum.
The solution, we can use a mechanism called-SQL Passthrough. There are built-in package from Oracle named special DBMS_HS_PASSTHROUGH used to obtain data and / or vice versa from / to Oracle HS. Here is how to get results from Oracle HS and stored on variables PL / SQL
con := DBMS_HS_PASSTHROUGH.open_cursor@hsmsql.world;
DBMS_HS_PASSTHROUGH.parse@hsmsql.world (con,
‘SELECT COUNT( * ) FROM rfq_mxm
WHERE rfqnum = ”’
|| vrfq.rfqnum
|| ””
);
rs := DBMS_HS_PASSTHROUGH.fetch_row@hsmsql.world (con);
DBMS_HS_PASSTHROUGH.get_value@hsmsql.world (con, 1, vcount);
DBMS_OUTPUT.put_line (’count: ‘ || vcount);
If the code is described conceptually above, then the steps are as follows:
1. Open the cursor HS (meaning here is different cursor with the understanding PL / SQL cursor)
2. Parse the query with the function
3. Pull the results of each line and enter into a variable and PL / SQL function get_value
It is much more complicated, but this is the safest way to manipulate the variables PL / SQL with heterogeneous systems (Oracle HS).
0 comments:
Post a Comment