This discussion is archived
1 Reply Latest reply: Feb 20, 2013 3:48 AM by yoonas RSS

getting an error while executing ddl commands using dblink

973492 Newbie
Currently Being Moderated
Hi,

i am using Oracle9iR2 Version.

i have created a procedure like below to execute ddl commands on remote database through dblink using dbms_sql.

CREATE OR REPLACE PROCEDURE run_remote_ddl (p_dblink VARCHAR2, qry VARCHAR2)
AS
c_handle NUMBER;
feedback INTEGER;
stat VARCHAR2 (2000);
BEGIN
stat := 'select DBMS_SQL.open_cursor' || p_dblink || ' from dual';

EXECUTE IMMEDIATE stat
INTO c_handle;

stat :=
'begin DBMS_SQL.parse'
|| p_dblink
|| ' ('
|| c_handle
|| ','''
|| qry
|| ''', DBMS_SQL.v7); end;';

EXECUTE IMMEDIATE stat;

stat :=
' select DBMS_SQL.EXECUTE' || p_dblink || '(' || c_handle
|| ') from dual';

EXECUTE IMMEDIATE stat
INTO feedback;

stat :=
'declare x integer; begin x:= :1; DBMS_SQL.close_cursor'
|| p_dblink
|| '(x); end;';

EXECUTE IMMEDIATE stat
USING c_handle;
END;
/

when i run this procedure like below

begin
run_remote_ddl ('@dblink', 'create table scott.ttt(num number)');
end;


got an error:

ORA-06553: PLS-103: Encountered the symbol ".2" when expecting one of the following:

. ( * @ & = - + ; < / > at in is mod not rem

<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
The symbol ". was inserted before ".2" to continue.
ORA-06512: at RUN_REMOTE_DDL", line 9
ORA-06512: at line 2

Please tell me how to resolve this.

Thanks in advance.
  • 1. Re: getting an error while executing ddl commands using dblink
    yoonas Expert
    Currently Being Moderated
    Hi,
    >

    ORA-06553: PLS-103: Encountered the symbol ".2" when expecting one of the following:

    . ( * @ & = - + ; < / > at in is mod not rem

    <an exponent (**)> or != or ~= >= <= <> and or like
    between

    >

    Hope you are not typing 2 instead of @ as both are on the same key


    Can you run the following and see what is happening
    CREATE OR REPLACE PROCEDURE run_remote_ddl (p_dblink VARCHAR2, qry VARCHAR2)
    AS
    c_handle NUMBER;
    feedback INTEGER;
    stat VARCHAR2 (2000);
    BEGIN
    
    dbms_output.put_line(p_dblink);
    
    stat := 'select DBMS_SQL.open_cursor@dblink from dual';
    
    --stat := 'select DBMS_SQL.open_cursor from dual';
    
    EXECUTE IMMEDIATE stat
    INTO c_handle;
    
    END;
    /
    
    
    exec run_remote_ddl('@dblink', 'create table scott.ttt(num number)');
    Regards

    Edited by: yoonus on Feb 20, 2013 3:47 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points