sql - Executing anonymous block PlSQL fail -
i want execute pl/sql script via terminal can't manage work. first checks whether user exists , if copies data table of user.
problem arises when there no user - script doesn't work because says table or view not exist, , means somehow precompiles it, while want execute line line.
here is:
declare v_count integer := 0; begin select count (1) v_count sys.dba_users username = upper ('b'); if v_count = 0 dbms_output.put_line ('fail'); else insert a.some_table (some_column) select some_column b.some_table some_column = "x"; end if; end; /
it throws error table not exist @ line select some_column b.some_table
because while indeed not exist (the user not) script wouldn't go there.
you need use dynamic pl/sql insert, not validated @ compile time @ runtime:
declare v_count integer := 0; begin select count (1) v_count sys.dba_users username = upper ('b'); if v_count = 0 dbms_output.put_line ('fail'); else execute immediate 'insert a.some_table (some_column) select some_column b.some_table some_column = ''x'''; end if; end; /
Comments
Post a Comment