1 2 Previous Next 16 Replies Latest reply on Oct 23, 2017 7:47 PM by Christoph

    Build with SQLcl and spooling

    Christoph

      Hi all,

      i have a build step that runs SQLcl and spools out put to a file. I would like to save the resulting file in my associated repository. How can I do that?

       

      Thanks,

      C

        • 1. Re: Build with SQLcl and spooling
          Timo Hahn

          You copy or move the file to the folder where the artifacts are build.The content of this folder (war, ear, ...) if normally zipped and saved.

          If you want to put the file into the source code repository, you move the file in a folder inside the workspace and add it after a successful build to the repository.

           

          Timo

          • 2. Re: Build with SQLcl and spooling
            Christoph

            Thanks for the info, Timo. I tried the "Copy Artifacts" step but got this error: Unable to find a build for artifact copy from:...

            Here are my build steps:

            • 3. Re: Build with SQLcl and spooling
              Shay Shmeltzer-Oracle

              Try to configure the "archive the artifacts" part in the post build steps of your build.

              See for example how I do it here:

              https://youtu.be/zFtUmAe61mg?t=376

              • 4. Re: Build with SQLcl and spooling
                Christoph

                Thanks Shay.

                Unfortunately still no luck.

                I'm getting this error in the console:

                 

                Unable to find a build for artifact copy from: developer85862-a424635_cmr-test_20924.Apex Export with SqlCl

                Archiving artifacts

                Finished: FAILURE

                 

                 

                I have tried several different ways:

                spool to !ls /home/c2c/hudson/workspace/developer85862-a424635_cmr-test_20924.spooltest/emp.txt

                and copy the artifact from

                emp.txt

                 

                or

                spool to !ls /home/c2c/hudson/workspace/developer85862-a424635_cmr-test_20924.spooltest/emp.txt

                 

                Either case I get the same error. Not sure what I'm doing wrong.

                 

                Thanks

                • 5. Re: Build with SQLcl and spooling
                  Shay Shmeltzer-Oracle

                  Can you share your console log from the build?

                  • 6. Re: Build with SQLcl and spooling
                    Christoph

                    Here you go:

                     

                     

                    Started by user ***Moderator action (Timo): removed private data.***

                    Building remotely on Builder 36

                    Checkout:developer85862-a424635_cmr-test_20924.spooltest / /home/c2c/hudson/workspace/developer85862-a424635_cmr-test_20924.spooltest - hudson.remoting.Channel@349b0f48:Builder 36

                    Using strategy: Default

                    Last Built Revision: Revision 23c0f07920feb7e2b4874368a1efb474a03a9344 (origin/master)

                    Checkout:developer85862-a424635_cmr-test_20924.spooltest / /home/c2c/hudson/workspace/developer85862-a424635_cmr-test_20924.spooltest - hudson.remoting.LocalChannel@7a7bd561

                    Cloning the remote Git repository

                    Cloning repository origin

                    Fetching upstream changes from https://developer.us2.oraclecloud.com/developer85862-a424635/s/developer85862-a424635_cmr-test_20924/scm/cmr-test.git

                    Commencing build of Revision 23c0f07920feb7e2b4874368a1efb474a03a9344 (origin/master)

                    Checking out Revision 23c0f07920feb7e2b4874368a1efb474a03a9344 (origin/master)

                    Connected.

                    Run Script !pwd

                    !ls -la /home/c2c/hudson/workspace/developer85862-a424635_cmr-test_20924.spooltest

                    spool /home/c2c/hudson/workspace/developer85862-a424635_cmr-test_20924.spooltest/emp.txt

                    select * from emp;

                    spool off

                    !ls -altr

                    !ls -altr /home/c2c/hudson/workspace/developer85862-a424635_cmr-test_20924.spooltest

                    /home/builder

                     

                    total 28

                    drwx------. 6 builder builder 4096 Oct 19 16:28 .

                    drwx------. 3 builder builder 4096 Oct 19 16:28 ..

                    drwx------. 7 builder builder 4096 Oct 19 16:28 .git

                    drwx------. 2 builder builder 4096 Oct 19 16:28 install

                    drwx------. 2 builder builder 4096 Oct 19 16:28 plsql

                    -rw-------. 1 builder builder 14 Oct 19 16:28 README.md

                    drwx------. 2 builder builder 4096 Oct 19 16:28 .vscode

                     

                     

                      EMPNO ENAME JOB MGR HIREDATE SAL COMM

                    ---------- ---------- --------- ---------- --------- ---------- ----------

                      DEPTNO

                    ----------

                      7839 KINGX PRESIDENT 17-NOV-81 5000 

                      10

                     

                      7698 BLAKE MANAGER 7839 01-MAY-81 2850 

                      30

                     

                      7782 CLARK MANAGER 7839 09-JUN-81 2450 

                      10

                     

                     

                      EMPNO ENAME JOB MGR HIREDATE SAL COMM

                    ---------- ---------- --------- ---------- --------- ---------- ----------

                      DEPTNO

                    ----------

                      7566 JONES MANAGER 7839 02-APR-81 2975 

                      20

                     

                      7788 SCOTT ANALYST 7566 09-DEC-82 3000 

                      20

                     

                      7902 FORD ANALYST 7566 03-DEC-81 3000 

                      20

                     

                     

                      EMPNO ENAME JOB MGR HIREDATE SAL COMM

                    ---------- ---------- --------- ---------- --------- ---------- ----------

                      DEPTNO

                    ----------

                      7369 SMITH CLERK 7902 17-DEC-80 800 

                      20

                     

                      7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

                      30

                     

                      7521 WARD SALESMAN 7698 22-FEB-81 1250 500

                      30

                     

                     

                      EMPNO ENAME JOB MGR HIREDATE SAL COMM

                    ---------- ---------- --------- ---------- --------- ---------- ----------

                      DEPTNO

                    ----------

                      7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

                      30

                     

                      7844 TURNER SALESMAN 7698 08-SEP-81 1500 0

                      30

                     

                      7876 ADAMS CLERK 7788 12-JAN-83 1100 

                      20

                     

                     

                      EMPNO ENAME JOB MGR HIREDATE SAL COMM

                    ---------- ---------- --------- ---------- --------- ---------- ----------

                      DEPTNO

                    ----------

                      7900 JAMES CLERK 7698 03-DEC-81 950 

                      30

                     

                      7934 MILLER CLERK 7782 23-JAN-82 1300 

                      10

                     

                     

                    14 rows selected.

                     

                    total 216

                    -rw-r--r--. 1 builder builder 172 Sep 11 2013 .bash_profile

                    -rw-r--r--. 1 builder builder 500 May 7 2015 .emacs

                    -rw-r--r--. 1 builder builder 124 Mar 22 2016 .bashrc

                    -rw-r--r--. 1 builder builder 18 Mar 22 2016 .bash_logout

                    drwxr-xr-x. 4 builder builder 4096 Mar 30 2016 atg11.1.0.1

                    -rw-r-----. 1 builder builder 87 Sep 30 00:41 .gitconfig

                    drwxr-xr-x. 9 root root 4096 Sep 30 00:55 ..

                    -rwxr-xr-x. 1 root root 58871 Sep 30 01:24 psmcli-1.1.14.zip

                    drwx------. 2 builder builder 4096 Sep 30 05:52 .oracle_jre_usage

                    drwx------. 3 builder builder 4096 Sep 30 07:42 .config

                    drwx------. 3 builder builder 4096 Sep 30 07:42 .cache

                    drwx------. 3 builder builder 4096 Sep 30 07:42 .local

                    drwx------. 3 builder builder 4096 Sep 30 07:42 .pki

                    -rw-------. 1 builder builder 9468 Sep 30 07:42 .v8flags.5.1.281.88.c87a8ca60f0891b79d192fa86f019916.json

                    drwx------. 3 builder builder 4096 Oct 1 19:31 .node-gyp

                    drwx------. 2 builder builder 4096 Oct 3 03:53 .ssh

                    -rw-------. 1 builder builder 9468 Oct 4 08:58 .v8flags.5.1.281.93.c87a8ca60f0891b79d192fa86f019916.json

                    drwxr-xr-x. 6 builder builder 4096 Oct 4 22:56 .gradle

                    drwx------. 2 builder builder 4096 Oct 8 17:49 tasks

                    drwx------. 3 builder builder 4096 Oct 12 13:31 .sonar

                    drwx------. 8 builder builder 4096 Oct 12 23:16 .jdeveloper

                    drwx------. 3 builder builder 4096 Oct 16 18:28 .soapuios

                    drwx------. 882 builder builder 28672 Oct 17 22:44 .npm

                    drwx------. 3 builder builder 4096 Oct 19 03:03 .psm

                    drwx------. 2 builder builder 4096 Oct 19 14:39 .sqldeveloper

                    drwxr-x---. 2 builder builder 4096 Oct 19 16:10 .m2

                    drwx------. 21 builder builder 4096 Oct 19 16:28 .

                    drwx------. 4 builder builder 4096 Oct 19 16:28 hudson

                    drwxr-x---. 31 builder builder 4096 Oct 19 16:30 tmp

                     

                    total 32

                    drwx------. 3 builder builder 4096 Oct 19 16:28 ..

                    drwx------. 2 builder builder 4096 Oct 19 16:28 .vscode

                    -rw-------. 1 builder builder 14 Oct 19 16:28 README.md

                    drwx------. 2 builder builder 4096 Oct 19 16:28 plsql

                    drwx------. 2 builder builder 4096 Oct 19 16:28 install

                    drwx------. 7 builder builder 4096 Oct 19 16:28 .git

                    drwx------. 6 builder builder 4096 Oct 19 16:29 .

                    -rw-------. 1 builder builder 2118 Oct 19 16:30 emp.txt

                     

                    Unable to find a build for artifact copy from: developer85862-a424635_cmr-test_20924.spooltest

                    Archiving artifacts

                    Finished: FAILURE

                    • 7. Re: Build with SQLcl and spooling
                      Timo Hahn

                      ***Moderator note (Timo): Christoph, please be very careful when you post private data here in this public forum. This kind of data should be removed or obfuscated before posting here. I removed the obvious private data from your post.

                      ***

                      • 8. Re: Build with SQLcl and spooling
                        Shay Shmeltzer-Oracle

                        So here is how I configure this:

                        The first step of my build was a shell command doing "pwd" this then showed me the directory the build was running in in the console log.

                        In the log file I see:

                        + pwd

                        /home/builder/hudson/workspace/developer-oracletemplates_sqldev_13778.spooltest

                         

                        I then used that path in the SQLcl spool command:

                        Screen Shot 2017-10-19 at 2.03.25 PM.png

                        then I indicated to archive all the txt files in the post build like this:

                        Screen Shot 2017-10-19 at 2.04.49 PM.png

                        Now when a build finishes I get this:

                        Screen Shot 2017-10-19 at 2.06.21 PM.png

                        • 9. Re: Build with SQLcl and spooling
                          Christoph

                          Thanks Timo!

                          • 10. Re: Build with SQLcl and spooling
                            Christoph

                            Thanks Shay,

                            Th curious thing is, that when I do pwd I get /home/builder.

                            I tried it similar to your example. But still no luck:

                             

                             

                             

                            Fetching upstream changes from https://developer.us2.oraclecloud.com/developer85862-a424635/s/developer85862-a424635_cmr-test_20924/scm/cmr-test.git

                            Commencing build of Revision 23c0f07920feb7e2b4874368a1efb474a03a9344 (origin/master)

                            Checking out Revision 23c0f07920feb7e2b4874368a1efb474a03a9344 (origin/master)

                            Connected.

                            Run Script !pwd

                            !ls -la /home/c2c/hudson/workspace/developer85862-a424635_cmr-test_20924.spooltest

                            spool /home/c2c/hudson/workspace/developer85862-a424635_cmr-test_20924.spooltest/a.txt

                            select * from dual;

                            spool off

                            !ls -altr /home/c2c/hudson/workspace/developer85862-a424635_cmr-test_20924.spooltest

                            /home/builder

                             

                            total 28

                            drwx------. 6 builder builder 4096 Oct 20 16:17 .

                            drwx------. 3 builder builder 4096 Oct 20 16:16 ..

                            drwx------. 7 builder builder 4096 Oct 20 16:17 .git

                            drwx------. 2 builder builder 4096 Oct 20 16:17 install

                            drwx------. 2 builder builder 4096 Oct 20 16:17 plsql

                            -rw-------. 1 builder builder 14 Oct 20 16:17 README.md

                            drwx------. 2 builder builder 4096 Oct 20 16:17 .vscode

                             

                             

                            D

                            -

                            X

                             

                            total 32

                            drwx------. 3 builder builder 4096 Oct 20 16:16 ..

                            drwx------. 2 builder builder 4096 Oct 20 16:17 .vscode

                            -rw-------. 1 builder builder 14 Oct 20 16:17 README.md

                            drwx------. 2 builder builder 4096 Oct 20 16:17 plsql

                            drwx------. 2 builder builder 4096 Oct 20 16:17 install

                            drwx------. 7 builder builder 4096 Oct 20 16:17 .git

                            drwx------. 6 builder builder 4096 Oct 20 16:17 .

                            -rw-------. 1 builder builder 8 Oct 20 16:18 a.txt

                             

                            Unable to find a build for artifact copy from: developer85862-a424635_cmr-test_20924.spooltest

                            Archiving artifacts

                            Finished: FAILURE

                             

                            My Build step:

                            My Post Build step:

                             

                            • 11. Re: Build with SQLcl and spooling
                              Shay Shmeltzer-Oracle

                              This is because you are issuing your pwd from inside SQLcl.

                              Instead use a separate build step of type "execute shell":

                              Screen Shot 2017-10-20 at 10.21.01 AM.png

                              • 12. Re: Build with SQLcl and spooling
                                Christoph

                                Thanks Shay. So I ran pwd as an Execute Shell and got indeed a different path:

                                /home/builder/hudson/workspace/developer85862-a424635_cmr-test_20924.spooltest

                                 

                                 

                                 

                                 

                                 

                                the console output is:

                                 

                                [developer85862-a424635_cmr-test_20924.spooltest] $ /bin/sh -xe /home/builder/tmp/hudson3320904591823392888.sh

                                + pwd

                                /home/builder/hudson/workspace/developer85862-a424635_cmr-test_20924.spooltest

                                Connected.

                                Run Script spool /home/builder/hudson/workspace/developer85862-a424635_cmr-test_20924.spooltest/a.txt

                                select * from dual;

                                spool off

                                 

                                D

                                -

                                X

                                 

                                Unable to find a build for artifact copy from: developer85862-a424635_cmr-test_20924.spooltest

                                Archiving artifacts

                                Finished: FAILURE

                                • 13. Re: Build with SQLcl and spooling
                                  Shay Shmeltzer-Oracle

                                  Can you include an image of your post build tab?

                                  What did you specify in the archive options?

                                  • 14. Re: Build with SQLcl and spooling
                                    Christoph

                                    Sorry. Forgot to paste that:

                                     

                                    1 2 Previous Next