| Embedded PlSql blocks from Pro*C to retrieve data from Multiple databases without DB Links [message #173235] |
Sat, 20 May 2006 12:06 |
josh_snehal
Messages: 15
Registered: May 2006
Location: BANGALORE
|
Junior Member |
|
|
Hi Buddy,
I am relatively new to Pro*C .And generally operate in PL/SQL only.
I have a customer requirement to monitor tables in different databases.And do some calculations on this data , and store it in local application database.
We are not allowed to have any DB Links between databases.
Different databases are in Oracle 8i.
Due to technical constraints it is also not possible to use Java.Next best option I come to know is Pro*C , which can allow fetch accross various databases.
Basically my requirement is to pass a cursor from Pro C ,which contains table_name,column_name and condition on which selection to happen , which will fetch sum & count from remote database for that table & columns combination ,
Here I am passing table_name , column_name , condition etc. in a variable ,
which at run time will be formed into a select string .String will be passed to execute immediate statement ( dynamic sql) to return SUM of that column.
e.g.
Local Database table say is T1
T1(
Applicaion_Name, --Remote application to monitor internal purpose
Table_name, -- Remote table to monitor
Column_Name, --Remote table's column to monitor
Parameter -- Sum or Count on that column of remote table
Condition --Contains string after WHERE clause of select stmt
)
applicaion to monitor is to be passed from ProC code
Cursor c1(v_appli_name) is
select application_name,table_name,column_name,parameter,condition
from <T1 it is local applition table structure as above)
where application_name = v_appli_name and
parameter='SUM' in local table;
This will retrieve rows from local table containing , tables and columns to be monitor from a remote database.
In next For Loop block for this values , Execute Immediate String will be formed as follows
for v_c1 in C1
v_String='Select '||sum(v_C1.column_name)||'from'||v_C1.table_name||'where'||v_C1.condition
EXECUTE IMMEDIATE v_String into v_sumcol; /* i.e. summation of that remote database table column for given condtion is taken in one variable */
This value of v_SumCol will be returned to calling program , and there I will do the Application Logic related Stuff.
Make Sense ?
This code & Logic everything is tested in PL/SQL blocks,( See in PL/SQL Experts forums on orafaq.com , Question : Dynamic Cursor in Dynamic Sql )
Only hurdle I am facing is How to do this in Pro*C ?
Is it possible to pass Dynamic Sql statements,Cursors in Pro*C blocks ?
Can some one give link to some such sample code , or Online Documents or tutorials , Any help in Pro*C is welcome ,
A helping hand in implementing this would be a great Welcome.
Finally , A warm regards & thanks to all , who take their valuable interest in this problem .
Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
