Oracle Indexes – SQL Query to Identify Missing Hinted Indexes

It might be possible if you consider overloading a procedure. Suppose you want the default of 5 for parameter a and 7 for parameter b and want also to check if the parameters were passed. In this case you have to overload the procedure 4 times for each combination of (with and without parameter). Finally, the real thing happens in the private procedure p_:

set serveroutput on size 100000 format wrapped

create or replace package tq84_test_pck as

   procedure p;
   procedure p(a number          );
   procedure p(          b number);
   procedure p(a number, b number);

end tq84_test_pck;
/

create or replace package body tq84_test_pck as

    procedure p_(a number := 5, a_default boolean,
                 b number := 7, b_default boolean) as
    begin

        dbms_output.put_line(
             'a: ' || a || 
             case when a_default then ' [default]'
                                 else ' [passed ]' end);

        dbms_output.put_line(
             'b: ' || b || 
             case when b_default then ' [default]'
                                 else ' [passed ]' end);

        dbms_output.new_line;

    end p_;

    procedure p is
    begin
        p_(a_default => true, 
           b_default => true);
    end p;

    procedure p (a number) is
    begin
        p_(a         => a, 
           a_default => false, 
           b_default => true);
    end p;

    procedure p (b number) is
    begin
        p_(b         => b, 
           a_default => true, 
           b_default => false);
    end p;

    procedure p (a number, b number) is
    begin
        p_(a         => a,
           b         => b, 
           a_default => false, 
           b_default => false);
    end p;

end tq84_test_pck;
/

show errors

declare

begin

   tq84_test_pck.p;
   tq84_test_pck.p( b => 7);
   tq84_test_pck.p( a => 7 );
   tq84_test_pck.p( a => null);
   tq84_test_pck.p( a => 5, b => 6);

end;
/

It's part of a distributed query. The string after the @ is a database link.

You can see where the database link is pointing by querying the all_db_links view, or the user_ or dba_ equivalents depending on the owner and your privileges. It might refer to a TNS alias, in which case you could get further details from the tnsnames.ora file.

When you query table you're (usually) looking at a table in your own schema. When you query table@dblink you're looking at a table in a remote database. They are different objects so you'd expect different results. Your local table is empty, but the remote one has some data.

I say 'usually' because table could be a synonym pointing at another schema, or even to a schema on a remote database itself. And dblink could be pointing back to your own database - it would be unusual but I've seen it done as a way of accessing another schema (not something I'd recommend). Without seeing if there is a synonym or where the dblink is going it's hard to be definitive, but these are less common scenarios. In this case though you're clearly hitting two different tables, probably on different databases. (Well, unless there's a VPD and connecting to the same table as a different user gives a different view... but starting to go down the rabbit hole now...)