Just started accessing Oracle data with WebFOCUS.

Needed to do:

ON TABLE HOLD FORMAT SQLORA

The DBA has issues with DROP TABLE...

He wants me to do TRUNCATE TABLE...

DROP TABLE works without error, but TRUNCATE TABLE produces:

(FOC1400) SQLCODE IS 955 (HEX: 000003BB)
(FOC1421) TABLE EXISTS ALREADY. DROP IT OR USE ANOTHER TABLENAME
: ORA-00955: name is already used by an existing object
(FOC1414) EXECUTE IMMEDIATE ERROR.

The DBA wants to know if TRUNCATE TABLE is supported or not.

Thanks in advance.

RELEASE 7.1.8

Show us the code.

Hi Harry,

Are you sure you want to Truncate?

Hope the Link helps...

Tom

P.S. Hi Francis, THANKS!! for an earlier response to an earlier thread, appreciate it...

Very simple code:

SQL SQLORA
DROP TABLE WEBFOCUS1;
END
TABLE FILE CAR
PRINT *
ON TABLE HOLD AS WEBFOCUS1 FORMAT SQLORA
END

The DBA strongly requests TRUNCATE over DROP. The link that was kindly provided note:

Removing rows with the TRUNCATE TABLE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates dependent objects of the table, requires you to regrant object privileges on the table, and requires you to re-create the indexes, integrity constraints, and triggers on the table and respecify its storage parameters. Truncating has none of these effects.

You may need to include the schema name, otherwise the SQLORA TRUNCATE request may not know which schema to work with:

SQL SQLORA
TRUNCATE TABLE BMLIBI.IB_HIERARCHY
END
-RUN

Sorry Francis,

That generates an error:

(FOC1400) SQLCODE IS 955 (HEX: 000003BB)
(FOC1421) TABLE EXISTS ALREADY. DROP IT OR USE ANOTHER TABLENAME
: ORA-00955: name is already used by an existing object
(FOC1414) EXECUTE IMMEDIATE ERROR.

You get this error for the TRUNCATE statement? Weird. That example I gave was from working code in my archive.

My IBI Oracle interface manual is packed away in my attic somewhere. Is the TRUNCATE TABLE command valid? I did not find references to it when searching the IBI site.

Sorry to say, neither methos worked:

SQL SQLORA
TRUNCATE TABLE WEBFOCUS1 ;
END
-RUN
SQL SQLORA
COMMIT ;
END
-RUN
TABLE FILE CAR
PRINT MODEL
ON TABLE HOLD AS WEBFOCUS1 FORMAT SQLORA
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 18 LINES= 18
0 HOLDING SQLORA FILE...
(FOC1400) SQLCODE IS 955 (HEX: 000003BB)
(FOC1421) TABLE EXISTS ALREADY. DROP IT OR USE ANOTHER TABLENAME
: ORA-00955: name is already used by an existing object
(FOC1414) EXECUTE IMMEDIATE ERROR.

Can it be an issue of my using 7.1.8?

Thanks for the continued assistance.

Harry, as Tom mentioned, "Are you sure you want to Truncate?"

"Use the TRUNCATE TABLE statement to remove all rows from a table." - it does not drop the table - the HOLD command fails because the table still exists.

I'm not sure why the DBA suggests TRUNCATE, but try TRUNCATE and DROP, then HOLD.

Thanks Francis,

The DBA mentioned that TRUNCATE was more efficient and was kinder/gentler to the system. Actually, my eyes glassed over when he went into a lenthy explanation.

I always make it a point NOT to argue with a DBA, so TRUNCATE was the way I had to go.

I tried the following without an error; however, not knowing the internals, I can only assume that this works and the DBA will be happy.

SQL SQLORA
TRUNCATE TABLE WEBFOCUS1 ;
END
-RUN
SQL SQLORA
DROP TABLE FOCUS.WEBFOCUS1 ;
END
-RUN
SQL SQLORA
COMMIT ;
END

If there are no technical objection to this approach, I guess I am sailing on smooth waters.

Yes, this works, because the table was dropped.

You may not wish to argue with the DBA, but if I were you, I would point out that TRUNCATE does not delete the table - it's an important fact that an Oracle DBA should know, specially since most of them think they're worth their weight in gold.

Harry,

Firstly, in your last example, if you are going to do a DROP TABLE, then there is no point in first doing a TRUNCATE.

Next, as you pointed out earlier in the thread, there may be good reasons not to DROP the table; however in making this decision (TRUNCATE vs. DROP) you need to take the bigger picture. Is your WEBFOCUS1 Oracle table a permanent part of your system? E.g., are there other reports or processes that require this WEBFOCUS1 table? Or is this table a temporary holding area for just this one process.

If it is a temporary holding area that is not used elsewhere, then I'd recommend using the DROP (although if it is temporary you might not even need it to be Oracle, a plain HOLD file might work just as well depending on how it is used down stream).

If the table is a permanent part of your system, then I'd recommend doing the TRUNCATE, but then instead of doing HOLD AS WEBFOCUS1 FORMAT SQLORA (which doesn't work), you add one more step:

 
SQL SQLORA
TRUNCATE TABLE WEBFOCUS1 ;
END
-RUN

TABLE FILE CAR
PRINT MODEL
ON TABLE HOLD AS TEMP1
END
-RUN

MODIFY FILE WEBFOCUS1
FIXFORM FROM TEMP1
MATCH * KEYS
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA ON TEMP1
END
-RUN
etc 
etc
 

EricH

I agree with EricH. You cannot truncate then hold to SQLORA format. I think WebFOCUS is trying to create the table, which is why you get the error. Also, you lose all your indexes. TRUNCATE, then MODIFY is the way I found works the best.

EricH, you are probably on to something there...

I did have to change the code around a bit. The MATCH * KEYS threw a lot of errors - probably because I used the original webfocus1.mas which has FOCLIST as a key. Only the first record was loaded.

These are the errors that were thrown:
(FOC1400) SQLCODE IS 1 (HEX: 00000001)
(FOC1417) INSERT WOULD CREATE DUPLICATES FOR UNIQUE INDEX. NO ACTION TAKEN
: ORA-00001: unique constraint (FOCUS.WEBFOCUS1IX) violated
(FOC1416) EXECUTE ERROR : SEG01

Here's the .acx and .mas:

SEGNAME=SEG01, TABLENAME=WEBFOCUS1, KEYS=01, WRITE=YES, $

FILENAME=WEBFOCUS1, SUFFIX=SQLORA , $
SEGMENT=SEG01, SEGTYPE=S0, $
FIELDNAME=FOCLIST , ALIAS=FOCLIST, USAGE=I5, ACTUAL=I4, $
FIELDNAME='MODEL', ALIAS='MODEL', USAGE=A24, ACTUAL=A24, $

I changed the code to the following and it worked:

SQL SQLORA
TRUNCATE TABLE WEBFOCUS1 ;
END
-RUN
TABLE FILE CAR
PRINT MODEL
COMPUTE FOCLIST/I4 = 1 + FOCLIST; NOPRINT
BY TOTAL FOCLIST
ON TABLE HOLD AS TEMP1
END
-RUN
MODIFY FILE WEBFOCUS1
FIXFORM FROM TEMP1
MATCH FOCLIST
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA ON TEMP1
END

It's not too pretty but the DBA is happy to see the TRUNCATE working.

Too bad you didn't mention the other errors in your original post.

(FOC1400) SQLCODE IS 1 (HEX: 00000001)
(FOC1417) INSERT WOULD CREATE DUPLICATES FOR UNIQUE INDEX. NO ACTION TAKEN
: ORA-00001: unique constraint (FOCUS.WEBFOCUS1IX) violated
(FOC1416) EXECUTE ERROR : SEG01\

Francis,

They did not exist until I put the MODIFY code in.

I put all of the errors in my post as they appeared.

Harry - Something along these lines should eliminate the need for the FOCLIST:

 

TABLE FILE CAR
BY MODEL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS FOO
END
 

EricH

I did try the PRINTONLY setting; however, whenever I review the .mas, FOCLIST is there. Nothing I did seemed to get rid of it so I gave up. I do not know if it's an issue with 7.1.8.

Thanks

FOCLIST is created when you PRINT and HOLD FORMAT FOCUS because FOCUS cannot ensure the uniqueness of the records when the query uses a PRINT statement. FOCLIST is created to ensure uniqueness. Try changing your PRINT to a SUM (with the necessary BYs) and FOCLIST should go away.

quote:

Originally posted by Francis Mariani:
You may not wish to argue with the DBA, but if I were you, I would point out that TRUNCATE does not delete the table - it's an important fact that an Oracle DBA should know, specially since most of them think they're worth their weight in gold.

I'm pretty sure that's exactly the intention of his DBA. If you TRUNCATE a table, you keep any referential integrity constraints that were defined (foreign keys to other tables in the database, for example) and any user access rights on the database level.

If you DROP the table, you lose those.

And it remains to be seen that the user that WebFOCUS uses to connect to the database has enough privileges to DROP or CREATE a table.

If I were a DBA, I wouldn't be eager to allow a reporting application access rights on that level.


To the OP: An alternative solution could be to use a FOCUS file to store your results in the file system, instead of in the Oracle database.

quote:

ON TABLE HOLD FORMAT SQLORA

The original post never suggested there was a pre-existing table.

Francis,

You ever just run a procedure once and once only?

The DBA gave me the option of outputting to only to one table. Anything I do has to go to that table. So, it will be reused repeatedly.

Currently, the historical data resides in wonderfully optimized FOCUS databases, however, management wants all data to be moved to Oracle.

I had to show a proof of concept that I can use WebFOCUS to migrate data rather easily (which it can; however, the DBA took exception with the lack of TRUNCATE).

The full story is, management decided to go to Cognos. My 18 years of FOCUS/WebFOCUS development is going to be shut down. I have to move all of the FOCUS databases over Oracle.

Got asprin?

Harry,

Hopefully you will get training; if you do, make sure you take Framework Manager. COGNOS is all about staging the data for the users, so, it's analytical and SQL based.

Best wishes...

Tom

quote:

My 18 years of FOCUS/WebFOCUS development is going to be shut down

Harry, get another job and let some other sucker do it.