accessing a stored procedure via dblink does not return set(oracle)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: accessing a stored procedure via dblink does not return set(oracle)

  1. #1
    Join Date
    Mar 2006
    Location
    Buenos Aires, Argentina
    Posts
    25

    accessing a stored procedure via dblink does not return set(oracle)

    Hello guys,

    i need a little help with a dblink issue. I have database A that has an dblink to a database B. In database B resides an stored procedure that returns a REF CURSOR AND also sends output via DBMS_OUTPUT.PUT_LINE from a select made in that stored procedure. When i try to access this stored procedure via dblink, i dont get the results set, nor the DBMS_OUTPUT shows me nothing (using SQL*Plus). I use set serveroutput on but nothing gets printed. Anyone can help me with this? what could be wrong?

    thanks in advance,

    dgc

  2. #2
    Join Date
    Feb 2005
    Posts
    158
    DBMS_OUTPUT.PUT_LINE will store stuff in memory for the process running the procedure (in your case the session is in database B).
    DBMS_OUTPUT.GET_LINES extracts the stuff from the memory into a local PL/SQL variables. Here's the kicker. You need to run the DBMS_OUTPUT.GET_LINES from the session in Database B. With a SET SERVEROUTPUT ON, SQL*Plus automatically does the GET_LINE stuff for the connected session (in your case on database A).
    You are going to have to do a quick anonymous PL/SQL to handle GET_LINEs from database B :

    begin
    dbms_output.enable@link(10000);
    dbms_output.put_line@link('test122');
    end;
    .
    /

    variable test char(200)

    set serveroutput on size unlimited

    declare
    v_num number;
    begin
    dbms_output.get_line@link(:test,v_num);
    dbms_output.put_line(v_num);
    end;
    .
    /

    print test

  3. #3
    Join Date
    Mar 2006
    Location
    Buenos Aires, Argentina
    Posts
    25
    Quote Originally Posted by gamyers
    begin
    dbms_output.enable@link(10000);
    dbms_output.put_line@link('test122');
    end;
    .
    /

    variable test char(200)

    set serveroutput on size unlimited

    declare
    v_num number;
    begin
    dbms_output.get_line@link(:test,v_num);
    dbms_output.put_line(v_num);
    end;
    .
    /

    print test

    Wow. I didnīt try that one. Thank fou for that. But what about the remote package? Because this may solve mi testing via SQL*Plus, but the remote package does not return SET. I am not sure this is possible, i am reading documentation about it, and it seems it is not supported remote packages. Can someone clarify this matter????

    Thank you very much for your help.

    regards,

    DGCampos

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width