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.