As part of the automatic deployment of a project, we deploy various Oracle scripts (packages, tables, data, etc...) using qsr - Quest Script Runner that is part of Toad . The script executes multiple sql files using the following syntax: qsr -c "user/pass@server" -f "file.sql" -o "log.txt" -e The problem is that we have around 100 sql files that we need to deploy on 8 environments. And each sql file execution takes approx. 1-2 seconds, even if it's a simple file or a simple check (if table exists, skip, else create it). And because of this sometimes the deployment can take 30 minutes or more. I checked the only documentation I could find in the Help section of Toad and tried the -i parameter and also removing the -e parameter: I see some improvement, mostly because of the -i parameter, but I am just worried what removing -e parameter really does. Are the scripts stacked then in QSR? When is QSR closed on the deployment agent (the running machine)? Also, I am a bit puzzled that I cannot find any documentation online about this tool, so any help on how to speed this deployment up would be appreciate (I have already tried sqlplus as an alternative, but it seems our scripts are not 100% compatible with it - some of them are simply not executed).

**Question:** In PL/SQL for Oracle running in Toad, can I suppress script output except for certain lines I want to see? e.g. I don't want create table, insert record, drop table. I do want my own dbms_output.put_line messages. **Example:** Here's my current output file. (This is simplified but it shows the kinds of messages I'm getting.) PL/SQL procedure successfully completed. Commit complete. Table created. 1 row created. 1 row created. 1 row created. Dropping table: ER_TEST001S_RES Checking results for test001s PASS: All records on _res expected results table have matches on actual results table for test001s PASS: All records on _res actual results table have matches on expected results table for test001s PL/SQL procedure successfully completed. Commit complete. My preferred output would look like this. Checking results for test001s PASS: All records on _res expected results table have matches on actual results table for test001s PASS: All records on _res actual results table have matches on expected results table for test001s Checking results for test002s PASS: All records on _res expected results table have matches on actual results table for test002s PASS: All records on _res actual results table have matches on expected results table for test002s **Context:** I'm using PL/SQL to test a calculation program that runs as a database procedure. My script inserts test records, runs the calculation procedure on them, creates tables of expected results, and compares expected to actual results. (I drop/create the test objects and expected results tables on each test run to make it quick and easy to run all tests at once when moving from dev to test environments.) Currently I have an action in Toad's Automation Designer to run the scripts for all tests, with 'set serveroutput on' and the output going to a file where I check whether any tests failed. **Goal:** Groom the script output to show only lines sent via dbms_output relating to pass/fail status of the test steps. I can't find the right search terms to get a lead on this. Is it possible?

Coming from a SQL SSMS environment, I would like to duplicate their Print Command which allows you to print commands or variables while executing a query. I am looking for this feature to help identify particular sections of the script during the execution. For instance we have a .sql script that runs in the evening that checks for discrepancies on particular inventory numbers. These inventory numbers have lets say a store_id of 1, 2, 3, 4, 5. The script has a select statement attached to each of these store_id. I would like to be able to place a PRINT statement for the select statement of Store_ID 1 so I can see the output.txt file and see the heading 'Store_id 1" then the inventory numbers, then see heading "Store_id 2" etc... in MSSQL SSMS it would like something like this PRINT 'Searching Inventory Discrepancies at Store 1' Select * from table1 where store_id = '1' etc... PRINT 'Searching Inventory Discrepancies at Store 2' Select * from table1 where store_id = '2' etc... If I need to clarify please advise, thank you!

How to check the Oracle database connection encryption type?(between Oracle database and other client applications)How do I assure between Oracle database and Toad the database connection is encrypted? (My Toad version is 9.5 and Oracle database version is 11g)

I have created a procedure in Toad for Oracle 12. When I execute the procedure it does not show the dataset in datagrid pane, while the procedure is being executed successfully. What I am doing wrong? CREATE OR REPLACE PROCEDURE FEEDERS_TO_MAKERS_MCB(OpNo CTS_OPERATIONS.OPERATIONNUM%Type ,BName CTS_OPERATIONS.BLENDNAME%Type) AS p_recordset Types.cursor_type; BEGIN OPEN p_recordset FOR SELECT FILLED_MCB.BARCODE,TILTED_MCB.TILTINGTIME,MAKERS.FEEDERNUM,MAKERS.MAKERNUM FROM FILLED_MCB INNER JOIN TILTED_MCB ON FILLED_MCB.FM_ID = TILTED_MCB.FM_ID INNER JOIN MAKERS ON MAKERS.M_ID = TILTED_MCB.M_ID INNER JOIN CTS_OPERATIONS ON CTS_OPERATIONS.CTS_ID = FILLED_MCB.CTS_ID --WHERE CTS_OPERATIONS.OPERATIONNUM=OpNo AND CTS_OPERATIONS.BLENDNAME=BName; WHERE CTS_OPERATIONS.OPERATIONNUM=3004 AND CTS_OPERATIONS.BLENDNAME='PG'; END; ---- Execute Procedure --------------- exec FEEDERS_TO_MAKERS_MCB(3004,'PG'); -------------------------------------

How do you turn off this annoying feature? I don't want my DB manager to croak at me as loud as it can.

select
  a.*,
  b.Start_Date,
  b.End_date,
  sum(b.cost) as total_Cost
from Ess.TZeewrld a
left join Ess.Wrldest b
  on a.account_number, = b.account_number,
group by
  b.Start_Date,
  b.End_date;

I installed Oracle SQL Developer from Oracle's web page. It seems to work without a Oracle client installed on my computer. How is that possible? I also installed Toad for Oracle and it seems to require Oracle client.

I have a small problem at work. Our DBA went on a journey of self-discovery ... I am very happy for him and I hope he finds what he is looking for. But he left us without any information about how the database he created works, the triggers he has, the connections between tables and, frankly, no documentation. My question is whether it is possible to export all the information from the database (connections between tables, schema, triggers, fields with data type, etc .. :) in a file from TOAD or another application to mount it in some application that would reverse engineer the database to indicate that same information that we need. I thank you very much for the help you can give me!

I'm not a DBA, so bear with me. I've scheduled tasks through Toad's automation designer, but this uses Windows task scheduler. It's using Oracle 12c. All I'm doing is updating (creating/dropping) tables with the most recent data. Then I use an ODBC to report on that data in another application. How can I ensure my scripts execute on time without running a local machine with scheduled tasks? Can this be done without additional software? Thanks in advance.

If I have about 10 queries which contain subqueries, would it be a best practice to contain all of them in one procedure? This script creates a small fraction of the data required for a report. DROP TABLE TEST1; CREATE TABLE TEST1 AS ( SELECT C.DATE, C.ITEM, (SELECT name FROM name_codes WHERE code = C.DEPARTMENT) AS DEPARTMENT, C.BUSINESS FROM LOGS C)); That's a fraction of it but the code more or less creates tables, filters fields, and combines outputs until the desired result. I am looking to automate data input for other scripts as well. Each script calculates a KPI and would be its own procedure. Then all of those procedures would be wrapped into a package. That package will be scheduled as a job through DBMS_SCHEDULER. Does this sound like the correct course of action? If so, can I call an existing script to create a procedure or would I need to paste the PLSQL into the procedure script? For the moment, I just want to test the first script as a procedure which is scheduled as a job through DBMS_SCHEDULER.

I have a table that has 4 e-mail fields. A single contact can have anywhere from 1-4 e-mail addresses on file. I'm having trouble writing a query that displays all duplicates across columns. For my example, I'll only discuss 2 email fields. Example: - John Smith, **Personal Email** fake@fakejohn.com **Work** fake@fakejohn.com - Jane Smith, **Personal Email** jane@fake678.com **Work** NULL - Jane Smith, **Personal Email** NULL **Work** jane@fake678.com In the above example, I can't find a way to say "I have 2 Jane Smiths that are duplicates" because they have the same email, but in different columns *while also* excluding John Smith, because while his email appears twice, I don't want to self-reference, as this is not a duplicate of itself. Iterations of my attempt to find a cross-column duplication check have been unsuccessful but include the following:

Select First, Last, Personal
From Contacts C
where C.Personal = C.Work
  and c.ID  c.ID -- my attempt to prevent self reference

This doesn't work, I receive 0 results, but should have thousands. Without the c.ID c.ID bit, I receive too many results, as I retrieve results for non-duplicate records that simply have the same email address in both fields. I've also added "Group By" clauses based on First & Last name and filter by greater than 1, but when checking our database, I'm still finding that I have contacts with the same name and no e-mail addresses in common *with each other* but have the same scenario as John Smith above. Is there any way to achieve this? I haven't found any queries to get me started.

I am trying to insert string in LONGTEXT column. Length of string is 75402 and byte count is 150804 (about 147 KB) Throws an exception Data too long for column On inserting record. MySQL document state that LONGTEXT can contains maximum 4GB of data Any suggestion will be welcome **UPDATE:** Exception occurs when characters length is greater than 65535 But 65536 is a max length of TEXT datatype and I am using LONGTEXT here.

In toad, whenever I create an object such as a table, If I name columns in PascalCase when it created if I open the table, then toad displays all the column names in uppercase. Is there any option in toad which prevents this default behavior and let us see the object names in the exact case which we create them?

I'm using a script (Toad, Oracle) so that I can use variables and enter two values that get repeated in multiple SQL statements. That's working great, but the SQL statements echo into the console output. I don't expect grid output, but I would like the SQL statement echo to be suppressed (I'm glad it's an option, but once things are working it's just in the way). Is there a way to get rid of that?

I have a serious problem with toad oracle client. I got the error message as following when I open toad client (installed and then opened it): > Error: No valid Oracle clients found. You need at least one 64-bit > client properly configured Please do me a favor to give me how to deal with this issue or give me any slim clue. Download from toad official website with 64-bit like as following picture

I am trying to connect to a database using Toad on windows 7. But I am not able to connect because I dont have *SQLNET Editor*, *TNSNames Editor* and *LDAP Editor* installed. Please help me with this. I am not able to find out in which package these 3 come in. I have *Oracle network client*, *SQL Client 2008* and *SQLite Expert Professional* installed. What extra I need to install?

I finally figured out the answer but it might be useful for someone looking up the same question so here is the whole write up to hopefully save you the frustration. Problem I had: Toad's query builder has 3 tabs - Diagram, Query and Results. I adjusted my query to what I wanted in Query tab. Ran it and confirmed that Results tab looked good. Then, I wanted to sync Query to Diagram. After syncing I noticed that my LEFT OUTER JOIN was turned into a RIGHT OUTER JOIN. Why? Won't the results be wrong? I wanted all records from INVOICES table and only matching records from INVOICE_ITEMS table. My original query's FROM statement was: FROM INVOICES LEFT OUTER JOIN INVOICE_ITEMS ON (INVOICE_ITEMS.INVOICE_GKEY = INVOICES.GKEY) And Toad kept turning it into: FROM INVOICES RIGHT OUTER JOIN INVOICE_ITEMS ON (INVOICE_ITEMS.INVOICE_GKEY = INVOICES.GKEY) It was driving me crazy. Why was it doing that? Well, the solution was simple. I apparently **reversed my tables** and Toad was smart enough to fix it for me. *(Facepalm)* I'm still not sure if it's a good feature or not considering my confusion but it's definitely there. So, the correct syntax for LEFT OUTER JOIN was supposed to be: FROM INVOICES LEFT OUTER JOIN INVOICE_ITEMS ON (INVOICES.INVOICE_GKEY = INVOICE_ITEMS.GKEY)

I want to format my string in my select statement but I cant seem to find the syntax for this pattern. I have a column of card_num with 16 digits and i want to have a dash(-) after the first four digits. Original card_num: 1234567891234567 Desired output: 1234-567891234567 I think regex can do this but cant find elsewhere the right code for this pattern. Any idea how to do this? Thanks.

I have a column with 82 rows(from table1) and I want to update just the 76 rows in it with different values from another table(table2). Avoiding 6 specific rows(1, 10, 38, 39, 46, 77). Leaving these rows with null values. I used cursor to save all the 76 rows from my table2 Cursor my_cur Is Select * from table2 Then I tried to update my table1 Begin For x in my_cur loop Update table1 Set my_col = x.acct_num Where rnum not in ('1', '10', '38', '39', '46', '77'); end loop; End; But I ended up updating these rows with same value(only 1 acct_num to all 76 rows). It seems that the last value(acct_num) from my cursor was the only one im always getting. I want to enter different acct_num to 76 rows and in the same order as in my table2 I added rownum in both of them to have at least be sure what rows to skip. Any ideas how to do it? Thanks.