Home » SQL & PL/SQL » SQL & PL/SQL » procedure error
procedure error [message #409530] Tue, 23 June 2009 01:13 Go to next message
arjunyadav
Messages: 15
Registered: September 2008
Junior Member
Dear all,
I have created one procedure as follows:
=========================================
create or replace procedure link_testing is
v_database_link varchar2(500);
begin
select trim(p.text_value)
into v_database_link
from parameter_table p
where upper(p.param_desc) = 'DATABASELINK';
begin
SELECT * FROM XXBL_CHARGES_DETAILS@v_database_link;
exception
when others then
null;
end;

end;
========================================
SELECT * FROM XXBL_CHARGES_DETAILS@v_database_link; where v_database_link is variable having database link name 'MBIL_TO_MGVCL'.


when I pass database link name through variable and
compile the procedure, oracle give a warning table or view does not exists.

when I pass database link name directly then it compiles successfully.
SELECT * FROM XXBL_CHARGES_DETAILS@MBIL_TO_MGVCL;
Is there any way to pass database link name through variable.


Thanks and Regards
Arjun
Re: procedure error [message #409538 is a reply to message #409530] Tue, 23 June 2009 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use dynamic SQL.

Once again please read OraFAQ Forum Guide, especially "How to format your post?" section.

Regards
Michel
Re: procedure error [message #410047 is a reply to message #409530] Wed, 24 June 2009 23:31 Go to previous messageGo to next message
stuti.raj
Messages: 6
Registered: March 2009
Location: India
Junior Member
create or replace procedure link_testing is

v_database_link  varchar2(500);
qry1	         VARCHAR2(1000);

begin

select trim(p.text_value)
into v_database_link
from parameter_table p
where upper(p.param_desc) = 'DATABASELINK';

begin

qry1:= 'SELECT * FROM XXBL_CHARGES_DETAILS@' || v_database_link;
execute immediate qry1;
COMMIT;

exception
when others then
null;
end;

end;


Try this way. It should work.
Re: procedure error [message #410053 is a reply to message #410047] Wed, 24 June 2009 23:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Try this way. It should work.

WRONG!
1/ SELECT returns some values, where in your code?
2/ SELECT may return several rows, how is this handle in your code?
3/ COMMIT is silly after SELECT
4/ COMMIT is silly in the procedure, are you sure the caller want YOU commit his previous changes?
5/ "when others then null;" is stupid, as always. Now you don't know when an error occurs.
...

Regards
Michel

[Updated on: Wed, 24 June 2009 23:48]

Report message to a moderator

Re: procedure error [message #410143 is a reply to message #410053] Thu, 25 June 2009 08:05 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Given problems 1 & 2, problem 5 will at least mean the code does nothing tidily.....
Previous Topic: IN Clause with 100 or more items
Next Topic: Select Statement
Goto Forum:
  


Current Time: Mon Dec 05 09:11:47 CST 2016

Total time taken to generate the page: 0.05098 seconds