我是 oracle pl-sql 的新手，ORA-0091:Invalid character在下面執行此查詢時遇到錯誤。但是當我註釋Execute Immediate語句並使用dbms_output.put_line變數的結果時qry很好，並且變數內部的查詢沒有問題qry。

declare
qry                  varchar2(5000);
effdate              date := to_date('5/19/2020', 'mm/dd/yyyy');
user_id              varchar2(1000) := 'pantea';
search_number        varchar2(50)   := '8080';
deposit_filter_query varchar2(5000) := '  deposit_group_code =  -1
                                   and deposit_group_desc =  -1                                      
                                   and currency_desc      = ''-1''
                                   group by customer_num
                                   having sum(dep_avg_balance) > 1;';

begin

 qry := 'insert into dep_filter_cust
          select distinct customer_num ,''' || search_number || '''
             from vmi_customer_deposit_detail v
               inner join branchlf_user b
             on b.userid = ''' || user_id || '''
               and dep_branch_cod = v.dep_branch_cod
          where effective_date = to_date(''' ||
            to_char(effdate, 'mm/dd/yyyy') || ''','' mm/dd/yyyy'') and ' ||
             deposit_filter_query;

 --dbms_output.put_line(qry);
 execute immediate to_char(qry);
        commit;

end;

我想知道您是否可以幫助我在這裡找到問題。提前致謝。

如評論中所述，您最初的問題是不必要的分號 ( ;)。

程式碼審查

通過按照您的方式建構查詢，您將引入 SQL 注入漏洞。（強制性XKCD參考）。要防止 SQL 注入，請使用 BIND 變數。

大多數 RDBMS 允許您?用作佔位符。Oracle 支持這一點和命名佔位符（例如:SEARCH_NUM）。通過使用 Bind 變數，DATE 數據類型將被綁定為 DATE 等。這消除了TO_CHAR/的必要性TO_NUMBER。

由於您的程式碼確實是動態的，因此我強烈建議您使用該DBMS_SQL包來解析您的 SQL 語句並綁定變數。如果 Binds 的使用也是動態的，則尤其如此。（例如，一次使用兩個變數，另一個使用 3 個，等等）

DBMS_SQL可以在此處查看文件。

範常式式碼：

declare
 qry                  varchar2(5000);
 effdate              date := to_date('5/19/2020', 'mm/dd/yyyy');
 user_id              varchar2(1000) := 'pantea';
 search_number        varchar2(50)   := '8080';
 deposit_filter_query varchar2(5000) := '  deposit_group_code =  -1
                                     and deposit_group_desc =  -1                                      
                                     and currency_desc      = ''-1''
                                     group by customer_num
                                     having sum(dep_avg_balance) > 1'; -- removed semi-colon
 initial_dml           varchar2(5000) := 'insert into dep_filter_cust (col1, col2)' || chr(10) -- alway define the columns in INSERT
            || 'select distinct customer_num , :SEARCH_NUM
               from vmi_customer_deposit_detail v
                 inner join branchlf_user b
               on b.userid = :USER_ID
                 and dep_branch_cod = v.dep_branch_cod
            where effective_date = :EFFDATE' || CHR(10); -- chr(10) === \n; NEWLINE character for visualisation

actual_sql  varchar2(32767);

c   pls_integer;
ret PLS_INTEGER;

begin

 -- build your SQL (I do this in a separate function)
 actual_sql := initial_dml || ' and ' || deposit_filter_query;

 -- debug results
 dbms_output.put_line( actual_sql );

 -- get your cursor
 c := dbms_sql.open_cursor();

 -- parse the SQL
 dbms_sql.parse( c, actual_sql, DBMS_SQL.NATIVE );

 -- bind variables (this can be dynamic if needed)
 -- Oracle can use NAMED Binds
 dbms_sql.bind_variable( c, ':SEARCH_NUM', search_number );
 dbms_sql.bind_variable( c, ':USER_ID', user_id );
 dbms_sql.bind_variable( c, ':EFFDATE', effdate );

 -- execute
 ret := dbms_sql.execute( c );

 -- close the cursor
 dbms_sql.close_cursor( c );
end;
/

如果你不這樣做，至少，USING使用EXECUTE IMMEDIATE.

例如

execute immediate actual_sql using search_number, user_id, effdate;

引用自：https://dba.stackexchange.com/questions/267444