PL/SQL getting values from procedures into a acceptval in a prompt

[hedub]

I need to fetch values from a procedure. The procedures name is Finn and its located in a package called Seter. Its no problem getting the OUT values in Finn like:

declare
l_rad NUMBER;
l_sete NUMBER;
begin
l_rad:='&&s_rad';
l_sete:='&&s_sete';

if l_sete is null then
Seter.Finn('&&s_film','&&s_sted', '&&s_kino',l_rad, l_sete);



end if;
if l_sete > 0 then
DBMS_OUTPUT.PUT_LINE('---------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Bekrefter valg - Sete: '||l_sete||' Rad: '||l_rad);

end if;

end;
/

But I need to put l_sete and l_row back into &&s_sete and &&s_row after executing Seter.Finn. How do I do that? Thanks in advance.

 

[SantaMufasa]

Hedub,

Instead of using "&&..." named literals, which are not variable, I recommend that you use bind variables. Here is an example using your earlier code.

[b]var s_rad number
var s_sete number[/b]
declare
    l_rad NUMBER;
    l_sete NUMBER;
begin
    l_rad:='[b]:s_rad[/b]';
    l_sete:='[b]:s_sete[/b]';
    
    if l_sete is null then
    Seter.Finn('&&s_film','&&s_sted', '&&s_kino',l_rad, l_sete);
    [b]:s_sete := l_sete;
    :s_rad := l_row;[/b]
end if;
    if l_sete > 0 then
    DBMS_OUTPUT.PUT_LINE('---------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Bekrefter valg -          Sete: '||l_sete||'  Rad: '||l_rad);

end if;

end;
/

Let us know if this code resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[hedub]

Yes, but you do need && named literals for using it in a prompt. Example:

accept s_sete prompt 'Choose seat:'
prompt
accept s_rad prompt 'Choose row:'
prompt

??

what i need is the outvariable from procedure Finn and put them in s_sete... etc.

 

[SantaMufasa]

Sorry, I should have added:

var s_rad number
var s_sete number
accept s_sete prompt 'Choose seat: '
prompt
accept s_rad prompt 'Choose row: '
[b]exec :s_rad := '&s_rad'
exec :s_sete := '&s_sete'[/b]
...

Additionally, the "accept" statement has the same effect as "&&", therefore, you can simply use "&".

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[hedub]

Thanks a lot!! Great forum.