We recently installed an Oracle Cloud DBaaS, version 12.1.0.2.0 on Linux.
The following SQL runs perfectly in either SQL Developer or SQL*Plus:
drop table BA_STG_SUM_9;
create table BA_STG_SUM_9 as select
a.*,
b.SUBSCRIBER_ID, b.INSURANCE_ID as INSUR_ID, b.PAYER_ID, b.POLICY_INFO_ID, b.GROUP_NAME, b.GROUP_NO, c.PATIENT_ACCOUNT_NO
from BA_STG_SUM_8 A
left outer join V_BA_PATIENT_POLICY_INFO B
on a.PATIENT_ID = b.SUBSCRIBER_ID
and a.INSURANCE_ID = b.PAYER_ID
and a.PHYS_SERVICE_DATE >= b.START_DATE
and a.PHYS_SERVICE_DATE <= B.END_DATE
left outer join V_HV_D_MT_MEMBERS C
on a.PATIENT_ID = c.MEMBER_ID;
I know that the table is created perfectly because I can go to the other tool and run a select statement. That is, if I am executing the SQL in SQL*Plus, I can go into SQL Developer and select from BA_STG_SUM_9. Likewise, if I am executing the SQL in SQL Developer, I can go over to SQL Plus and select from BA_STG_SUM_9. The CREATE TABLE command has finished successfully.
But here's the trouble: When I run the CREATE TABLE from either tool, it never "appears" to finish. It's like it never gets a message back from the database saying "All done." Again, I know for sure that it HAS finished, because I can go to the other tool and run the select statement. The problem does not occur on short-running commands (i.e. a few seconds).
The CREATE TABLE statement above usually runs for about 4 minutes. And, in fact, after about 4 minutes I can see results in the "other" environment, while the executing environment just sits there without ever returning a cursor.
This "no cursor returned" situation occurs whether I am attached to the database via SQL Developer or SQL*Plus remotely (i.e. from those two tools running directly on my own PC), and I have even seen it when using the native SQL*Plus that's right on the database server.
The biggest problem with this: Scripts with multiple SQL commands cannot run completely, because any SQL commands that occur after the "no cursor returned" command are never reached.
Your thoughts and ideas are welcome. Thanks!