Re: SQLl _at_ command
Date: Mon, 31 Aug 2020 23:47:34 -0400
Message-ID: <c9f637f2-c1e1-8b5c-d40a-cc31927a50bd_at_gmail.com>
Yes, you're right. I haven't noticed so far because I use my own homegrown Perl script to dump queries to CSV.
SQL> spool /tmp/emp.csv
SQL> select * from emp;
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,19801217,800,,20 7499,"ALLEN","SALESMAN",7698,19810220,1600,300,30 7521,"WARD","SALESMAN",7698,19810222,1250,500,30 7566,"JONES","MANAGER",7839,19810402,2975,,20 7654,"MARTIN","SALESMAN",7698,19810928,1250,1400,30 7698,"BLAKE","MANAGER",7839,19810501,2850,,30 7782,"CLARK","MANAGER",7839,19810609,2450,,10 7788,"SCOTT","ANALYST",7566,19870419,3000,,20 7839,"KING","PRESIDENT",,19811117,5000,,10 7844,"TURNER","SALESMAN",7698,19810908,1500,0,30 7876,"ADAMS","CLERK",7788,19870523,1100,,20 7900,"JAMES","CLERK",7698,19811203,950,,30 7902,"FORD","ANALYST",7566,19811203,3000,,20 7934,"MILLER","CLERK",7782,19820123,1300,,10
14 rows selected.
Elapsed: 00:00:00.015
SQL> spool off
SQL> When I open the file in Excel, it looks like this:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 19801217 800 20 7499 ALLEN SALESMAN 7698 19810220 1600 300 30 7521 WARD SALESMAN 7698 19810222 1250 500 30 7566 JONES MANAGER 7839 19810402 2975 20 7654 MARTIN SALESMAN 7698 19810928 1250 1400 30 7698 BLAKE MANAGER 7839 19810501 2850 30 7782 CLARK MANAGER 7839 19810609 2450 10 7788 SCOTT ANALYST 7566 19870419 3000 20 7839 KING PRESIDENT 19811117 5000 10 7844 TURNER SALESMAN 7698 19810908 1500 0 30 7876 ADAMS CLERK 7788 19870523 1100 20 7900 JAMES CLERK 7698 19811203 950 30 7902 FORD ANALYST 7566 19811203 3000 20 7934 MILLER CLERK 7782 19820123 1300 10
14 rows selected.
Elapsed: 00:00:00.015
There is a whole bunch of empty lines both in the beginning and at the
end of files.
Regards
On 8/31/20 10:59 PM, Jack Applewhite wrote:
> Now we're hitting the problem with extra blank lines in spooled files
> from 20.2 sql / sqlcl. We use sqlcl a LOT to produce CSV files for
> import into other systems and the blank lines are now a Big problem Is
> there a sqlcl version that both respects $PWD AND doesn't inject extra
> blank lines in spooled output?
>
> Thanks.
>
> ------------------------------------------------------------------------
> *From:* Jack Applewhite <jack.applewhite_at_austinisd.org>
> *Sent:* Thursday, August 27, 2020, 15:19
> *To:* Jeff Smith; gogala.mladen_at_gmail.com; Martin Berger; Jack Applewhite
> *Cc:* franck_at_pachot.net; Oracle-L oracle-l
> *Subject:* Re: SQLl _at_ command
>
> Never mind. All it took was downloading a later version and now SD
> 20.2 likes this for java.home.
>
> /usr/java/jdk1.8.0_261-amd64/jre
>
> Thanks.
> --
> Jack C. Applewhite - Database Administrator
> Austin I.S.D. - MIS Department
> 512.414.9250 (wk)
>
> I cannot help but notice that there is no problem between us that
> cannot be solved by your departure. -- Mark Twain
> ------------------------------------------------------------------------
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>
> on behalf of Jack Applewhite <jack.applewhite_at_austinisd.org>
> *Sent:* Thursday, August 27, 2020 13:57
> *To:* Jeff Smith <jeff.d.smith_at_oracle.com>; gogala.mladen_at_gmail.com
> <gogala.mladen_at_gmail.com>; Martin Berger <martin.a.berger_at_gmail.com>
> *Cc:* franck_at_pachot.net <franck_at_pachot.net>; Oracle-L oracle-l
> <oracle-l_at_freelists.org>
> *Subject:* Re: SQLl _at_ command
> Yes, 20.2 did the trick for sql. It sees $PWD just fine.
>
> Now I need to figure out what SQL Dev 20.2 wants for the path to JDK home.
> Looks to me like it's /usr/java/jdk1.8.0_231-amd64/jre, but I get this
> error and kicked off.
> -bash: /usr/java/jdk1.8.0_231-amd64/jre: is a directory
>
> I'm trying to run it in an X window from my Ubuntu VM. Would that mess
> things up? I can do so with the 17.3 version in $ORACLE_HOME. What
> that one shows as java.home is
> /u01/app/oracle/product/18.0.0.0/dbhome_1/jdk/jre
> but that doesnt' work for 20.2.
>
> Am I missing something?
> Thanks.
> --
> Jack C. Applewhite - Database Administrator
> Austin I.S.D. - MIS Department
> 512.414.9250 (wk)
>
> I cannot help but notice that there is no problem between us that
> cannot be solved by your departure. -- Mark Twain
> ------------------------------------------------------------------------
> *From:* Jeff Smith <jeff.d.smith_at_oracle.com>
> *Sent:* Wednesday, August 26, 2020 15:37
> *To:* gogala.mladen_at_gmail.com <gogala.mladen_at_gmail.com>; Martin Berger
> <martin.a.berger_at_gmail.com>; Jack Applewhite
> <jack.applewhite_at_austinisd.org>
> *Cc:* franck_at_pachot.net <franck_at_pachot.net>; Oracle-L oracle-l
> <oracle-l_at_freelists.org>
> *Subject:* RE: SQLl _at_ command
>
> End of day, I’m happy to hear that sqlcl is being used and helping
> people do good things.
>
> I’m testing the fix now reported from some folks online about
> excessive line breaks/blank lines in output, and we’re working on a
> FASTER, native compiled sqlcl that will eliminate any ‘java tax’ when
> it comes to startups.
>
> Keep up the feedback, we’re gonna keep building.
>
> *From:*Mladen Gogala <gogala.mladen_at_gmail.com>
> *Sent:* Wednesday, August 26, 2020 4:27 PM
> *To:* Martin Berger <martin.a.berger_at_gmail.com>; Jack Applewhite
> <jack.applewhite_at_austinisd.org>
> *Cc:* Jeff Smith <jeff.d.smith_at_oracle.com>; franck_at_pachot.net;
> Oracle-L oracle-l <oracle-l_at_freelists.org>
> *Subject:* Re: SQLl _at_ command
>
> I agree. I misreported the problem. I am using Oracle 12.2 and have
> downloaded SQLCl 20.2 but forgot to put $HOME/sqlcl/bin into the PATH.
> The problem was with Oracle 12.2 version of SQLCl, not with 20.2. I
> humbly apologize for the confusion.
>
> On Wed, 2020-08-26 at 22:11 +0200, Martin Berger wrote:
>
> Jack,
>
> please download & use late
> <https://linkprotect.cudasvc.com/url?a=https%3a%2f%2fwww.oracle.com%2ftools%2fdownloads%2fsqlcl-downloads.html%23license-lightbox&c=E,1,bkLfEjZQMQOgA2XMaeD5ry0fdiBMRmAtvQynBa0BM_rm4aRXU8snBLyRnZiYtDMEIjSEb6bnudEHISm2uONKr3cM9f2FbqA0au07XaB7KwwD&typo=1>st
> 20.x SQLcl.
>
> I saw similar issues with pre 20.x Versions, but my
> observation with 20.x is fine.
>
> hth,
>
> Martin
>
> Am Mi., 26. Aug. 2020 um 22:05 Uhr schrieb Jack Applewhite
> <jack.applewhite_at_austinisd.org
> <mailto:jack.applewhite_at_austinisd.org>>:
>
> This is on an X8-M ODA at 18.8 with 18c DBs. Not sure why 18c
> shipped with SQLDev and sql 17.3.
>
> [oracle_at_falcon admin]$ echo $PWD
>
> /u01/app/oracle/admin
>
> oracle_at_falcon admin]$ sql /nolog
>
> SQLcl: Release 17.3.0 Production on Wed Aug 26 15:03:08 2020
>
> Copyright (c) 1982, 2020, Oracle. All rights reserved.
>
> _at_ > show sqlpath
>
> SQLPATH :
> /u01/app/oracle/product/18.0.0.0/dbhome_1/sqldeveloper/sqldeveloper/bin/
> <https://linkprotect.cudasvc.com/url?a=https%3a%2f%2furldefense.com%2fv3%2f__http%3a%2f18.0.0.0%2fdbhome_1%2fsqldeveloper%2fsqldeveloper%2fbin%2f__%3b%21%21GqivPVa7Brio%21Kt-gbWmc5Cv0pyCOKkVP1OCvur9mnlVVbDVSUlg9EAKo_kJYEZbS4NALfkzvNG0n4b4%24&c=E,1,UBG6VFaoVE10fp29Tnn8KL7QRN6wHQM147ZN7-iQFTx-HlORJRjrRBKfRr_pfCyIOt2UlpqQbzDQSBxkepx-1msmsjYfyM-IJzoqdDNmVaEjO8vMJcc,&typo=1>:.
>
> _at_ >
>
> _at_ > exit
>
> [oracle_at_falcon admin]$
>
> What's it supposed to be?
>
> Thanks.
>
> --
> Jack C. Applewhite - Database Administrator
> Austin I.S.D. - MIS Department
> 512.414.9250 (wk)
>
> I cannot help but notice that there is no problem between us
> that cannot be solved by your departure. -- Mark Twain
>
> ------------------------------------------------------------------------
>
> *From:*Jeff Smith <jeff.d.smith_at_oracle.com
> <mailto:jeff.d.smith_at_oracle.com>>
> *Sent:* Wednesday, August 26, 2020 14:51
> *To:* Jack Applewhite <jack.applewhite_at_austinisd.org
> <mailto:jack.applewhite_at_austinisd.org>>; Martin Berger
> <martin.a.berger_at_gmail.com
> <mailto:martin.a.berger_at_gmail.com>>; franck_at_pachot.net
> <mailto:franck_at_pachot.net> <franck_at_pachot.net
> <mailto:franck_at_pachot.net>>
> *Cc:* Mladen Gogala <gogala.mladen_at_gmail.com
> <mailto:gogala.mladen_at_gmail.com>>; Oracle-L oracle-l
> <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>>
> *Subject:* RE: SQLl _at_ command
>
> Jumping in late, so apologies
>
> But if I echo $PWD
>
> And then start sqlcl
>
> And show sqlpath…
>
> I see $PWD
>
> So can someone explain to me what we’re doing wrong? Happy to
> file a bug and drive to get it fixed for you.
>
> Jeff
>
> *From:* Jack Applewhite <jack.applewhite_at_austinisd.org
> <mailto:jack.applewhite_at_austinisd.org>>
> *Sent:* Wednesday, August 26, 2020 3:20 PM
> *To:* Martin Berger <martin.a.berger_at_gmail.com
> <mailto:martin.a.berger_at_gmail.com>>; franck_at_pachot.net
> <mailto:franck_at_pachot.net>
> *Cc:* Mladen Gogala <gogala.mladen_at_gmail.com
> <mailto:gogala.mladen_at_gmail.com>>; Oracle-L oracle-l
> <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>>
> *Subject:* Re: SQLl _at_ command
>
> Yes, this has been bugging us since we migrated to 18c. I LOVE
> sql or sqlcl, but having it not respect $PWD is Very annoying.
>
> I just execute sql, which launches the sql script in
> $ORACLE_HOME/bin, which executes the line below
>
> cd "`dirname $0`"/../sqldeveloper/sqldeveloper/bin &&
> bash sql $*
>
> So, not being an ace shell scripter, is there a
> straightforward way to get this to inherit the $PWD of the
> original invocation of sql? Or to edit the
> .../sqldeveloper/bin/sql big ol' script to inherit $PWD?
>
> Thanks.
>
> --
> Jack C. Applewhite - Database Administrator
> Austin I.S.D. - MIS Department
> 512.414.9250 (wk)
>
> I cannot help but notice that there is no problem between us
> that cannot be solved by your departure. -- Mark Twain
>
> ------------------------------------------------------------------------
>
> *From:*oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> <oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>> on behalf of Franck
> Pachot <franck_at_pachot.net <mailto:franck_at_pachot.net>>
> *Sent:* Wednesday, August 26, 2020 11:26
> *To:* Martin Berger <martin.a.berger_at_gmail.com
> <mailto:martin.a.berger_at_gmail.com>>
> *Cc:* Mladen Gogala <gogala.mladen_at_gmail.com
> <mailto:gogala.mladen_at_gmail.com>>; Oracle-L oracle-l
> <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>>
> *Subject:* Re: SQLl _at_ command
>
> Hi all,
>
> It is not SQLcl that changes the directory but the wrapping
> script that you find in $ORACLE_HOME/bin/sql
>
> Franck.
>
> On Wed, Aug 26, 2020 at 5:02 PM Martin Berger
> <martin.a.berger_at_gmail.com <mailto:martin.a.berger_at_gmail.com>>
> wrote:
>
> Hi Mladen,
>
> I had similar issues with versions prior to 20.2: SQLcl
> changed it's working directory.
> can you please do a
>
> !pwd
>
> (or !cd )
> to see in which directory your SQLcl really is?
>
> hth
>
> berx
>
> Am Mi., 26. Aug. 2020 um 16:40 Uhr schrieb Mladen Gogala
> <gogala.mladen_at_gmail.com <mailto:gogala.mladen_at_gmail.com>>:
>
> Hi!
>
> I am having problems with the _at_ command from SQLCl.
> For some reason, SQLCl 20.2 doesn't find the file,
> even if it is in the current directory. Does anyone
> have the same experience?
>
> Regards
>
> --
>
> Mladen Gogala
> /Database Consultant
> Tel: (347) 321-1217/
>
>
> --
>
> Martin Berger Oracle ♠
>
> martin.a.berger_at_gmail.com
> <mailto:martin.a.berger_at_gmail.com> _at_martinberx
> <https://linkprotect.cudasvc.com/url?a=https%3a%2f%2furldefense.com%2fv3%2f__https%3a%2flinkprotect.cudasvc.com%2furl%3fa%3dhttps%2a3a%2a2f%2a2furldefense.com%2a2fv3%2a2f__https%2a3a%2a2ftwitter.com%2a2fmartinberx__%2a3b%2a21%2a21GqivPVa7Brio%2a21I-SDO1_GF_ewc9zuJDvm6QZLWxKG67V_pPren3w8nI3xegiy0X5Y-QP5f32GnOkvwQA%2a24%26c%3dE%2c1%2cLdMdl5KnEq1_rAu_YNSUchYkDSkhM7ivXZDh-PwloVTtYUIWmJiU9qki3xj0UfG-_1FgUkTDskO6CgDpNgqfrAk7zPNbl1ZodZblv6xsd97z2C517M2m-CgM%26typo%3d1__%3bJSUlJSUlJSUlJSUlJQ%21%21GqivPVa7Brio%21Kt-gbWmc5Cv0pyCOKkVP1OCvur9mnlVVbDVSUlg9EAKo_kJYEZbS4NALfkzvLS3pZE8%24&c=E,1,XvuI8aUFKCBS-IdjK1W94Sufk_bg6fokY7Fud9ss6hlRT16n2NZhX0bnrXkk0d3I7YuLlm6StkdXed7PBO7iX3nuVK6GYj5DVrCrPhPJup0JQDkJLuOF&typo=1>
> ^∆xhttp://berxblog.blogspot.com
> <https://linkprotect.cudasvc.com/url?a=https%3a%2f%2furldefense.com%2fv3%2f__https%3a%2flinkprotect.cudasvc.com%2furl%3fa%3dhttps%2a3a%2a2f%2a2furldefense.com%2a2fv3%2a2f__https%2a3a%2a2flinkprotect.cudasvc.com%2a2furl%2a3fa%2a3dhttp%2a2a3a%2a2a2f%2a2a2fberxblog.blogspot.com%2a26c%2a3dE%2a2c1%2a2ccxgSkjNgBA1gIHAFZHIDBLYXPyQZAtwX3LEljGqqFG2ioOMWHGBPQWeL8d2Snw-W1k1yUTpOzlFK007z7L00CRP6ZBxVzjTSXtvXKUb8M3-ojEGWuxaxUpw%2a2c%2a26typo%2a3d1__%2a3bJSUl%2a21%2a21GqivPVa7Brio%2a21I-SDO1_GF_ewc9zuJDvm6QZLWxKG67V_pPren3w8nI3xegiy0X5Y-QP5f32GKfHsmB4%2a24%26c%3dE%2c1%2cD4QJAOObQb82VC3jF_C_Ad9vzVx7-Snqc284P9zy9yOAy2CnmaP4vBco0L9iXPsVB5ygF4Y2BgoIzfm7tqxlU44TJpetBwYFu2vS4fLMSk6vTYFA26A%2c%26typo%3d1__%3bJSUlJSUlJSUlJSUlJSUlJSUlJSUlJSUlJQ%21%21GqivPVa7Brio%21Kt-gbWmc5Cv0pyCOKkVP1OCvur9mnlVVbDVSUlg9EAKo_kJYEZbS4NALfkzveGFzLj0%24&c=E,1,3SiYLzJaG9H7Hlyjiql9e-uMlGHFS15oYVOq5I-Dnyj6ooc2zqt5G_3yIyyERhZgAPKoR8OysE3wRAawLq-_kMYkxAG7R9pH2QhiogSE1HHBTAThJe-I&typo=1>
>
> Confidentiality Notice: This email message, including all
> attachments, is for the sole use of the intended recipient(s)
> and may contain confidential student and/or employee
> information. Unauthorized use of disclosure is prohibited
> under the federal Family Educational Rights & Privacy Act (20
> U.S.C. §1232g, 34 CFR Part 99, 19 TAC 247.2, Gov’t Code
> 552.023, Educ. Code 21.355, 29 CFR 1630.14(b)(c)). If you are
> not the intended recipient, you may not use, disclose, copy or
> disseminate this information. Please call the sender
> immediately or reply by email and destroy all copies of the
> original message, including attachments.
>
> Confidentiality Notice: This email message, including all
> attachments, is for the sole use of the intended recipient(s)
> and may contain confidential student and/or employee
> information. Unauthorized use of disclosure is prohibited
> under the federal Family Educational Rights & Privacy Act (20
> U.S.C. §1232g, 34 CFR Part 99, 19 TAC 247.2, Gov’t Code
> 552.023, Educ. Code 21.355, 29 CFR 1630.14(b)(c)). If you are
> not the intended recipient, you may not use, disclose, copy or
> disseminate this information. Please call the sender
> immediately or reply by email and destroy all copies of the
> original message, including attachments.
>
>
> --
>
> Mladen Gogala
> /Database Consultant
> Tel: (347) 321-1217/
>
> Confidentiality Notice: This email message, including all attachments,
> is for the sole use of the intended recipient(s) and may contain
> confidential student and/or employee information. Unauthorized use of
> disclosure is prohibited under the federal Family Educational Rights &
> Privacy Act (20 U.S.C. §1232g, 34 CFR Part 99, 19 TAC 247.2, Gov’t
> Code 552.023, Educ. Code 21.355, 29 CFR 1630.14(b)(c)). If you are not
> the intended recipient, you may not use, disclose, copy or disseminate
> this information. Please call the sender immediately or reply by email
> and destroy all copies of the original message, including attachments.
>
> Confidentiality Notice: This email message, including all attachments,
> is for the sole use of the intended recipient(s) and may contain
> confidential student and/or employee information. Unauthorized use of
> disclosure is prohibited under the federal Family Educational Rights &
> Privacy Act (20 U.S.C. §1232g, 34 CFR Part 99, 19 TAC 247.2, Gov’t
> Code 552.023, Educ. Code 21.355, 29 CFR 1630.14(b)(c)). If you are not
> the intended recipient, you may not use, disclose, copy or disseminate
> this information. Please call the sender immediately or reply by email
> and destroy all copies of the original message, including attachments.
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 -- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 01 2020 - 05:47:34 CEST