Skip to Main Content

SQLcl

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQlcl changes SQL statement and adds Bind variables automatically, How need to disable this.

1380988Oct 16 2017 — edited Oct 16 2017

Hello all,

Sometimes, I do want to code without using bind variables.  See here as an example.  I want to simply execute the following code in session 1 using SQLcl:

SQL> exec DBMS_LOCK.SLEEP(60);

Now, When I want to monitor my query, see here what I have:

SQL> running

USERNAME        SID MACHINE                   STMT                                                               RUNT

GFRONT (45)      45 MTL-OSX-DBA-SBI-6.local   DECLARE SqlDevBind1Z_1 VARCHAR2(32767):=:SqlDevBind1ZInit1;  BEG   00:00:04

GFRONT (45)      45 MTL-OSX-DBA-SBI-6.local   IN BEGIN DBMS_LOCK.SLEEP(TO_NUMBER( SqlDevBind1Z_1)); END; :AUXS   00:00:04

GFRONT (45)      45 MTL-OSX-DBA-SBI-6.local   QLDBIND1:=SqlDevBind1Z_1;  END;                                    00:00:04

But when I execute the same code in another client like PL/SQL Developer or SQLPLUS, here is what I see:

SQL> running

USERNAME        SID MACHINE                      STMT                                                   RUNT

GFRONT (44)      44 MTL-TOUCHTUNES\IT-TOOLS-03   begin sys.DBMS_LOCK.SLEEP(60); end;                    00:00:08

This can become quite difficult when the code being executed is longer, as in this example:

USERNAME         SID MACHINE                   STMT                                                               RUNT

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   DECLARE SqlDevBind1Z_1 VARCHAR2(32767):=:SqlDevBind1ZInit1; SqlD   00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   evBind1Z_2 VARCHAR2(32767):=:SqlDevBind1ZInit2; SqlDevBind1Z_3 V   00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   ARCHAR2(32767):=:SqlDevBind1ZInit3; SqlDevBind1Z_4 VARCHAR2(3276   00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   7):=:SqlDevBind1ZInit4; SqlDevBind1Z_5 VARCHAR2(32767):=:SqlDevB   00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   ind1ZInit5; SqlDevBind1Z_6 VARCHAR2(32767):=:SqlDevBind1ZInit6;    00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   SqlDevBind1Z_7 VARCHAR2(32767):=:SqlDevBind1ZInit7; SqlDevBind1Z   00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   _8 VARCHAR2(32767):=:SqlDevBind1ZInit8; SqlDevBind1Z_9 VARCHAR2(   00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   32767):=:SqlDevBind1ZInit9;  BEGIN begindbms_mview.refresh(TO_C    00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   HAR( SqlDevBind1Z_9));dbms_mview.refresh(TO_CHAR( SqlDevBind1Z_    00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   8));dbms_mview.refresh(TO_CHAR( SqlDevBind1Z_7));dbms_mview.re     00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   fresh(TO_CHAR( SqlDevBind1Z_6));dbms_mview.refresh(TO_CHAR( Sql    00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   DevBind1Z_5));dbms_mview.refresh(TO_CHAR( SqlDevBind1Z_4));dbm     00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   s_mview.refresh(TO_CHAR( SqlDevBind1Z_3));dbms_mview.refresh(TO    00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   _CHAR( SqlDevBind1Z_2));dbms_mview.refresh(TO_CHAR( SqlDevBind1    00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   Z_1));end; :AUXSQLDBIND9:=SqlDevBind1Z_1; :AUXSQLDBIND8:=SqlDe     00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   vBind1Z_2; :AUXSQLDBIND7:=SqlDevBind1Z_3; :AUXSQLDBIND6:=SqlDevB   00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   ind1Z_4; :AUXSQLDBIND5:=SqlDevBind1Z_5; :AUXSQLDBIND4:=SqlDevBin   00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   d1Z_6; :AUXSQLDBIND3:=SqlDevBind1Z_7; :AUXSQLDBIND2:=SqlDevBind1   00:19:01

GFRONT (652)     652 MTL-OSX-DBA-SBI-6.local   Z_8; :AUXSQLDBIND1:=SqlDevBind1Z_9;  END;                          00:19:01

Anything that can be disable/OR set to make sure to have a "what you execute is what you get" kind of behaviour in SQLcl? 

Thanks,

Steeve

Comments

Post Details

Added on Oct 16 2017
1 comment
873 views