Forum Stats

  • 3,750,250 Users
  • 2,250,140 Discussions
  • 7,866,859 Comments

Discussions

SQLcl stops processing after a while

User609481-OC
User609481-OC Member Posts: 6
edited Dec 18, 2017 6:29PM in SQLcl

Hello teams,

I have a PowerShell script that loops through an array of CountryNames and generates a SQL file based on the Where clause of the CountryName.

I use latest production SQLcl version to execute the SQL file.

Sometimes it processes the First iteration only then hangs/stops and sometimes it stops in the 4th iteration.

When I the script run manually, it normally goes fine.

Why it hangs after a while? What could be happening? how to troubleshoot it and find errors? (I have a try/catch block and no exceptions are catched)

Please let me know if you need more details.

User609481-OC

Best Answer

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Dec 16, 2017 7:46PM Accepted Answer

    One example is when using Tools > Database Export to export just the DDL for a schema, but change the  Save As value from Single File to Type Files.  SQL Developer will write out one SQL file per object type to a user specified output directory, and also create a master script which can be used to run all those SQL files.  This master script is placed into an unconnected Worksheet so you can select a connection and create a clone in another schema, as here...

    DatabaseExportTypeFiles.jpg

    Hope this helps.

    User609481-OC

Answers

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Dec 15, 2017 4:26PM
    Please let me know if you need more details

    Yes, please provide more details.  For example, your script, and a sample of a generated SQL file, and which version of Windows you have.

    It is not clear from your description whether each iteration is a distinct SQLcl session, whether you are spooling the output, and so on.  Providing a test case we can use to try to reproduce the behavior is the best way to get help.

  • User609481-OC
    User609481-OC Member Posts: 6
    edited Dec 15, 2017 6:06PM

    Hi Gary, Thanks for replying

    Below is the main code, I have removed some unnecessary code (like logging handling)

    -------------------------------------------- begin loop --------------------

    foreach($country in $Countries)

    {

    #Prepare sql file name for the country

    $Country_sql_file = "$script_dir\Sqls\Get_" + $country[1] + ".sql"

    #Prepare the spool file name

    $CountryResultFileName = "$script_dir\Extracts\SIEBELCUSTRECONAll_" + $country[1] + ".csv"

    #Change spool file name in the sql file

    $output = Get-Content "$script_dir\Extract_Customer_Data_CSV.sql" | Foreach-Object {$_ -replace "strCustomer_Report.csv", $CountryResultFileName }

    #Change Where clause

    $output = $output -replace "strCountry", $country[0]

    #Write the SQL file

    Set-Content $Country_sql_file -Value $output -Force -Encoding ASCII

    #Wait 3 seconds for the file to be written and released

    Start-Sleep -s 3

    #Execute the generated SQL file

    $output = Invoke-Expression  "D:\Apps\sqlcl\bin\sql.exe $($db_user + '/' + $db_password + '@' + $db_sid + ' `@' + $Country_sql_file) 2>&1"

    }  

    -------------------------------------------- end loop --------------------

    Below is a sample Generated SQL file that is executed using the Invoke-expression command

    ------------------------------------- begin SQL -------------------------

    SET SQLFORMAT CSV

    SPOOL S:\Scripts\008_Customer_Report\Extracts\SIEBELCUSTRECONAll_NL.csv;

    select A.OU_NUM as Account_Number,

          A.NAME as name1,

          A.CUST_STAT_CD as Account_Status,

          A.X_OS_CC_FISCALCODE as FiscalCode,

          A.VAT_REGN_NUM as Vat#,

          e.X_OS_CC_NAME as OperatingUnit,

          A.X_OS_CC_SALES_CHANNEL as SalesChannel,

          B.BRLOC_ATTRIB06 as CLMClassification,

          B.BRLOC_ATTRIB07 AS STCClassification,

          A.X_OS_CC_SECTOR_LIST as Sector,

          A.DUNS_NUM AS DUNSNumber,

          d.ADDR_PER_ID as AddressId,

          C.name as SiteName,C.CUST_STAT_CD  as SiteStatus,

          f.ADDR_LINE_4 as AdditionalInfo,

          f.country as country,

          f.ADDR as Address,

          f.ADDR_LINE_4 as usageAdditionalInfo,

          f.ADDR_LINE_2 as BuildingName,

          f.State as State,

          f.city as city,

          f.zipcode as PostalCode,

          d.RELATION_TYPE_CD as Usagetype,

          d.X_OS_CC_ADDR_STATUS as UsageStatus,

          C.OU_NUM as Site, d.RELATION_TYPE_CD

          from S_ORG_EXT A, S_ORG_EXT_FNX B, S_ORG_EXT c, S_CON_ADDR D, siebel.CX_ACCount_NSO e, S_ADDR_PER f  

    Where A.row_id = B.par_row_id and a.row_id = c.par_ou_id and d.ADDR_PER_ID = f.row_id

    and c.row_id = d.ACCNT_ID  and e.row_id = a.X_OS_CC_PRI_NSO_ID

    and B.BRLOC_ATTRIB06 in('Customer','Site') and f.country = 'Netherlands';

    SPOOL OFF

    EXIT

    /

    -------------- end of SQL ------------------------------------------

    I use Windows server 2008 R2 64 bit

    Let me know if I miss anything

  • User609481-OC
    User609481-OC Member Posts: 6
    edited Dec 16, 2017 7:07AM

    I added | Out-Null at the end of the Invoke as below to force wait for the command to complete, and it really waited, but it hanged in the third iteration again

    $output = Invoke-Expression  "D:\Apps\sqlcl\bin\sql.exe $($db_user + '/' + $db_password + '@' + $db_sid + ' `@' + $Country_sql_file) 2>&1" | Out-Null

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Dec 16, 2017 2:02PM

    Let's assume your PowerShell script and SQLcl scripts are fine.  Some ideas...

    1. As the hang occurs after multiple SQLcl sessions, you might consider building a master SQL script to execute the individual country SQL scripts in succession, then execute that master SQL script in a single SQLcl session.

    2. It seems the spooled output may be going to a networked drive.  Is "S" local or networked?.  Possibly that may be causing problems.

    3. Do any of the queried tables or views reside on other databases that are accessed via database links?  Possibly a ROLLBACK prior to EXIT would help in that case.

  • User609481-OC
    User609481-OC Member Posts: 6
    edited Dec 16, 2017 7:18PM

    Number 2 and 3 are invalid where S: is a physical drive on the server and all tables are in the same DB.

    I dont understand number 1, it seems good idea to have one session for all countries, could you please clarify more and/or give an example.

    Please take in consideration that I have 20 countries in the loop.

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Dec 16, 2017 7:46PM Accepted Answer

    One example is when using Tools > Database Export to export just the DDL for a schema, but change the  Save As value from Single File to Type Files.  SQL Developer will write out one SQL file per object type to a user specified output directory, and also create a master script which can be used to run all those SQL files.  This master script is placed into an unconnected Worksheet so you can select a connection and create a clone in another schema, as here...

    DatabaseExportTypeFiles.jpg

    Hope this helps.

    User609481-OC
  • User609481-OC
    User609481-OC Member Posts: 6
    edited Dec 16, 2017 8:24PM

    Thanks Gary for the quick response, really appreciated.

    This deserves a try

    I will try it and update about the result.

  • User609481-OC
    User609481-OC Member Posts: 6
    edited Dec 17, 2017 1:43PM

    This was a great idea, it worked very fine with no hangs.

    Thank you Gary

    Just one more question, how can I get information from inside the master SQL? for example getting time stamps when each of the SQLs is started/finished for logging info..etc 

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Dec 18, 2017 5:27PM

    As long as you do not mind the Elapsed time listed in your spool file output along with the SQL query results, you can use...

    SET TIMING ON
  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Dec 18, 2017 6:29PM

    And if you really want timestamps or to calculate your own elapsed times that do not appear in the individual country spooled  results, you can roll your own wrapper SQL script to spool those to a separate output.  Here is a discussion from Ask TOM on how to use dbms_utility.get_time to calculate elapsed time:

    https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4030201115642

    If you only wish to display time stamps, the simplest is "select systimestamp from dual;".

    User609481-OCUser609481-OC