1 Reply Latest reply: Feb 20, 2013 5:48 AM by yoonas RSS

    getting an error while executing ddl commands using dblink

    973492
      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
          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