The PL/SQL window is executable, and an error is reported when the execution in the stored procedure is executed.

The PL/SQL window is executable, and an error is reported when the execution in the stored procedure is executed.

Recently, the test department encountered environment problems. We developed and run a program on our development side, but it was not enough to run it in the test department. According to their dba requirements, the past inspection. The program section is as follows:

I use vsql variables to view the dynamic SQL statement execution as follows:

Insert into tb_bil_acct_his_to
(Acct_id,
Acct_name,
Acct_nbr_97,
Cust_id,
Pay_method,
Branch_id,
Bank_acct,
Addr_id,
Crt_date,
Eff_date,
Exp_date,
Mod_date,
Eff_state,
Latn_id,
Addr_desc,
Post_code,
Mailing_flag,
Bank_acct_name,
Post_target_addr,
Empee_id,
Exch_id,
Month_id,
Serv_id,
OPER_TYPE_ID,
ACTION_DATE,
ACTION_TYPE,
HIS_ID,
HAND_FLAG)
Select a. acct_id,
A. acct_name,
A. acct_nbr_97,
A. cust_id,
A. pay_method,
A. branch_id,
A. bank_acct,
A. addr_id,
A. crt_date,
A. eff_date,
A. exp_date,
A. mod_date,
A. eff_state,
A. latn_id,
A. addr_desc,
A. post_code,
A. mailing_flag,
A. bank_acct_name,
A. post_target_addr,
A. empee_id,
A. exch_id,
A. month_id,
A. serv_id,
28,
Sysdate,
1,
Seq_bil_acct_his_hisid_566.nextval,
0
From tb_bil_acct_566
Where a. acct_id = 5021006 and a. serv_id is null

This section of SQL can be executed smoothly in a separate SQL window, but the stored procedure once executed, the ora-00942 table or view doesn't exist is reported

I started to wonder for a long time. Later I found out that if a user has a dba role, the permissions contained in the role will not be inherited in the stored procedure. So I separately added the permissions to tb_bil_acct_566, seq_bil_acct_his_hisid_566.nextval and so on are explicitly authorized to solve the problem.

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)