My Tables are not Updating in Toad

Toad for Oracle & DevOps Toolkit
1

Hello,
I am having problem with updating my Table in toad. I just installed a new toad professional edition. The old version I had runs well with this same method of work.
I was given a script but in other to simplify the complexity I created a table using the script.

CREATE TABLE TABLE_NAME AS
MY SCRIPT --------------------------- This is the script.

When I use the table TABLE_NAME to create a report it does not update with new data. the script updates well but the created table does not update.

below is a sample of my query using the newly created table. Is there anything I need to do in toad?


-------------------RECAPTURE IN PROGRESS
SELECT 'Selected Solution Error Ind' AS IND_DESCRIPTION, SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Selected Solution Error Ind"='Yes' THEN 1 ELSE 0
END) AS count_of_ind  ------COUNT HEADER
FROM DISCREPANCY_TABLE
-------------------------------------------------------------------
UNION ALL
SELECT 'Pending Bucket Error', SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Pending Bucket Error"='Yes' THEN 1 ELSE 0
END) AS count_of_ind
FROM DISCREPANCY_TABLE
-------------------------------------------------------------------
UNION ALL
SELECT 'Disbursed Not Obligated Ind', SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Disbursed Not Obligated Ind"='Yes' THEN 1 ELSE 0
END) AS count_of_ind
FROM DISCREPANCY_TABLE
------------------------------------------------------------------------------------------------------------------
UNION ALL
SELECT 'Complete Not Disbursed Ind', SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Complete Not Disbursed Ind"='Yes' THEN 1 ELSE 0
END) AS count_of_ind
FROM DISCREPANCY_TABLE
-----------------------------------------------------------------------------
UNION ALL
----Complete Not Fully Disbursed Ind
SELECT 'Complete Not Fully Disbursed Ind', SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Complete Not Fully Disbursed Ind"='Yes' THEN 1 ELSE 0
END) AS count_of_ind
FROM DISCREPANCY_TABLE
------------------------------------------------------------------------------------------------
UNION ALL
----Closing Solution Disbursed Solution Error Ind
SELECT 'Closing Solution Disbursed Solution Error Ind', SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Closing Solution Disbursed Solution Error Ind"='Yes' THEN 1 ELSE 0
END) AS count_of_ind
FROM RESTORE20_DISCREPANCY_TABLE
----------------------------------------------------------------------------------------------------------
UNION ALL
--Complete But Not Obligated Ind
SELECT 'Complete But Not Obligated Ind', SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Complete But Not Obligated Ind"='Yes' THEN 1 ELSE 0
END) AS count_of_ind
FROM DISCREPANCY_TABLE
--------------------------------------------------------------------------------------------------
UNION ALL
--Variance Vs Calculation Ind
SELECT 'Variance Vs Calculation Ind', SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Variance Vs Calculation Ind"='Yes' THEN 1 ELSE 0
END) AS count_of_ind
FROM DISCREPANCY_TABLE
------------------------------------------------------
UNION ALL
SELECT 'Unresolved Discrepancy Total' ,
        SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Selected Solution Error Ind"='Yes' THEN 1 ELSE 0 END)+
        SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Pending Bucket Error"='Yes' THEN 1 ELSE 0 END)+
        SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Disbursed Not Obligated Ind"='Yes' THEN 1 ELSE 0 END) +
        SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Complete Not Disbursed Ind"='Yes' THEN 1 ELSE 0 END) +
        SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Complete Not Fully Disbursed Ind"='Yes' THEN 1 ELSE 0 END) +
        SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Closing Solution Disbursed Solution Error Ind"='Yes' THEN 1 ELSE 0 END) +
        SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Complete But Not Obligated Ind"='Yes' THEN 1 ELSE 0 END) +
        SUM(CASE WHEN "Curr App Status" != 'Recapture In Progress' AND "Variance Vs Calculation Ind"='Yes' THEN 1 ELSE 0 END) 
 FROM DISCREPANCY_TABLE  
 ---------------------------------------------------
 UNION ALL
 SELECT 'Total Unique Accounts' AS IND_DESCRIPTION, COUNT(DISTINCT ACCT_ID) AS "Total Unique Accounts"
  FROM DISCREPANCY_TABLE
  WHERE "Curr App Status" !='Recapture In Progress' AND ACCT_ID IS NOT NULL
    -----------------------------------------------------
 UNION ALL---------------RECAPTURE IN PROGRESS
 SELECT 'Recapture in Progress', SUM(CASE WHEN "Curr App Status" = 'Recapture In Progress' THEN 1 ELSE 0 END) AS count_of_ind
FROM DISCREPANCY_TABLE
-------------------------------------------------------------------------------------------------------------------------------
--RESOLVED
UNION ALL
SELECT 'Resolved', SUM(CASE WHEN "RESOLUTION" = 'Resolved' THEN 1 ELSE 0 END) AS count_of_ind
FROM DISCREPANCY_TABLE

@JohnDorlon

2

Are you sure that rows were inserted into the table when it was created?

A couple ways to tell are:

  • Run the Select by itself, without the "create table" part before it
  • Try a sql like select count(*) from TABLE_NAME

If the table does not have rows, then this has nothing to do with Toad.

If the table does have rows, then I need more info about how you look at the table in Toad but don't see any rows. I am thinking that you have a filter set somewhere but I don't want to guess.

3

There are records when I run the CTE script. But when I use the table I created with CTE it doesn't return the updated data. Below is a sample of the data.

image
image817×408 5.66 KB

4

Where do you go in Toad and not see data that you expect to see data? Post a screen shot please of the entire relevant window in Toad.

6

OK, I can see that there is no filter on the grid table in Toad. So what you are looking at is really all that's in the table, but I don't think this is a Toad issue.

Some thoughts:

  1. There is no need to commit after "Create table .... as select ...." statements. Because CREATE TABLE is DDL, a commit happens automatically.
  2. If you drop and create the DDL again, and then you see the data, and then later is is gone.....then either some other process must be deleting it, or later, when it seems the data is gone, maybe you aren't really selecting from the same table.
7

You can see the table on the left highlighted. is there a way I can automate the dropping and rerunning of the DDL at certain times?

8

You can see the table on the left highlighted

Yes. So you used the object palette to get the object name into the editor? Is that schema the same as the one you are logging in as?

Because when you do "CREATE TABLE TABLE_NAME AS ..." then TABLE_NAME is created in your login schema. If the object palette is showing a different schema, that could explain all of this.

You aren't giving me a complete picture of what's happening here so I have to guess and give you possible explanations.

is there a way I can automate the dropping and rerunning of the DDL at certain times?

Sure, you could schedule a script to run using the Automation Designer, but it might be better to figure out what's really happening here.

10

Ok, but that doesn't show me which schema you are logged in as. Are you logged in as EGRANTSRPTUSER?

The reason I ask is:

If you are logged in as EGRANTSRPTUSER, then "create table RESTORE20_SENATE_TABLE as select" creates the table EGRANTSRPTUSER.RESTORE_20_SENATE_TABLE, and we know that you are looking at the right table.

If you are logged in as a different user, then "create table RESTORE20_SENATE_TABLE as select..." creates a table in the schema that you are logged in as.

If you are running "create table EGRANTSRPTUSER.RESTORE20_SENATE_TABLE as select ..." then it doesn't matter which user you are logged in as.

12

Ok, so in that case:

  1. Run your create table EGRANTSRPTUSER.RESTORE20_SENATE_TABLE as select....
  2. Run Select * from EGRANTSRPTUSER.RESTORE20_SENATE_TABLE. This will return the same number as rows returned by your select from step 1.
  3. If Select * from EGRANTSRPTUSER.RESTORE20_SENATE_TABLE later returns fewer rows, then they were deleted by some other process.
13

It returns the same number of rows but does not return updated total if I run it tomorrow without recreating the table

14

I created this script to run the update separately using fn+f5. Is there a way this can be run in toad automatically without my intervention.

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE EGRANTSRPTUSER.RESTORE20_SENATE_TABLE';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
(The script that created the Table).

COMMIT;

15
  • if you "CREATE TABLE AS SELECT...", it should create the table with the right number of rows.
  • If you do that again (with no drop), the statement will fail because the table already exists. Oracle does not update the table as you have found in this case. This is not a Toad bug. This is standard Oracle behavior.

You can, as you have found "drop table ...." then "create table as select", to get around this.

To schedule that in Toad, you could go to either of these places

  • Schema browser -> Scheduler Jobs. Click "Create scheduler job". This is a little more complicated to set up, but would schedule it on the Oracle server, so it wouldn't matter if your PC is running
  • Automation Designer. Create a Script action, then right-click to schedule it. This is simpler, but would schedule it on your PC.
16

I got this resolved. Instead of using the given script to create a table
Create Table Tabel_Name as
select * from Jobs

I created a view instead
Create View Vw_Table_Name as
Select * from jobs. The report now updates

17

Yes, a view will stay current with table data all the time because it is basically just a saved query. Glad you found something that works for you.