| urgent [message #38515] |
Wed, 24 April 2002 10:43  |
Sam
Messages: 255
Registered: April 2000
|
Senior Member |
|
|
Iam having problem executing this procedure.
procedure(
emp1 char,
emp2 char,
Rdb_link char)/****Rdb_link is a database link to go to a remote server*****/
is
begin
select * from emp@Rdb_link where emp1 = 'unknown'
can i execute a sql statement with like this.
thanx
|
|
|
|
|
|
| Re: urgent [message #38520 is a reply to message #38516] |
Wed, 24 April 2002 11:42  |
oraboy
Messages: 97
Registered: October 2001
|
Member |
|
|
well ..u gotta use dynamic sql
try this and it shud work fr you
Procedure p1(c1 varchar2,c2 varchar2,dblink varchar2) as
lv_str varchar2(4000);
begin
lv_str := 'Select * from emp@'||dblink||
' where '||c1 ||' = ''unknown''';
Open res for lv_str;
end;
if you want the resultset back ..use Open for statement..
or if its going to be scalar values, then you can try using
Execute immediate lv_str into lv_rec;
dbms_output.put_line(lv_rec);
HTH
oraboy
|
|
|
|
|
|
| Re: urgent [message #38554 is a reply to message #38520] |
Fri, 26 April 2002 06:10 |
oraboy
Messages: 97
Registered: October 2001
|
Member |
|
|
Yes..if you have the object name (to be queried/manipulated) known only at runtime..u dont have any option other than DYNAMIC SQL
(as far as my knowledge is concerned)
(In your case, the dblink value is known only at runtime )
Regards
Oraboy
|
|
|
|
| Re: urgent [message #38560 is a reply to message #38520] |
Fri, 26 April 2002 12:52 |
Sam
Messages: 255
Registered: April 2000
|
Senior Member |
|
|
iam trying to use dynamic sql to call a remote a stored proc
this is something like this and i want to get back all the results from the remote database.
sql_stmt:= 'begin master.child1proc@'||db_link||'; end;'
open rem_cur for sql_stmt
execute immediate sql_stmt into out_cur;
will this work. appreciate help
|
|
|
|
| Re: urgent [message #38578 is a reply to message #38520] |
Sun, 28 April 2002 11:28 |
oraboy
Messages: 97
Registered: October 2001
|
Member |
|
|
No..This wont work becoz OPEN FOR STATEMENT is meant for SELECT statement.
but I think you can omit that part and use EXECUTE IMMEDIATE directly to make it work
declare
lv_res resultset ; -- to get back the resultset
begin
sql_stmt 'begin master.child1proc('||lv_res||')'
||'@'||dblink||'; end;'
Exec immediate sql_stmt
-- access ur resultset here (available in lv_res)
End;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
