Recent content by rtefft

I need to require DoD CAC card authentication for a ColdFusion-Oracle application. While we can make the server require the CAC certs, does anyone have examples of mapping the CAC cert to an Oracle ID? We currently use encrypted session veriables for username/password and have a requirement to...

Sem & Dima: When the lockup/freezing occurs, all jobs currently executing freeze and all pending jobs never start. I've been working with the DBA and sysadmin and they are puzzled as well. Let me clarify more of the background. We have a single entry in the job queue called RPTMGR that...

Dima, JOB_QUEUE_INTERVAL was deprecated in Oracle 9i and is no longer set in our INIT.ORA files. Since the problem happens erattically (anywhere from 1-5 days between problems with 50-100 jobs/day), this would seem to indicate the Oracle defined interval is working fine. At the time of the...

I have a report manager package that executes via DBMS_JOB queue every 5 minutes. It simply looks for report submissions, and launches a new DBMS_JOB for each one as needed. It has been running fine for months, but lately the job will simply hang in the queue for no apparent reason. If other...

I could use direct-path inserts, but would still pay the REDO price when deleting them after use. We tried CREATE GLOBASL TEMPORARY TABLE but it still hit the redo logs. ____________________________ Rich Tefft PL/SQL Programmer

While running a production DB in ARCHIVELOG mode, does anyone know if there is a way to have 1 table that is NOT logged? We use a temp table for transferring files to ColdFusion (we load them in as strings or CLOBs and it queries them out). We end up with excessive redo log usage and fill up...

We were using that, but the various JDBC, ODBC, and thin drivers used between CF and Oracle have limits on string and LOB length (4k on average). I need to send more data than that without using Oracle tables (to keep overhead to a minimum). I was hoping an array datatype might do the trick...

Truth, I mean an array of multi-byte character strings. For example, an array of 12 elements, each containing a month ('JAN','FEB', 'MAR'...). Thanks, Rich ____________________________ Rich Tefft PL/SQL Programmer

Can anyone show me how to use CFSTOREDPROC where one of the parameters of the procedure is a simple string array? We call procedures all the time, but cannot figure out how to return a simple 1-dimensional string array. ____________________________ Rich Tefft PL/SQL Programmer

Also look at SET LINESIZE 200 (or SET LINES 200). This widens the display so it won't wrap so much. ____________________________ Rich Tefft PL/SQL Programmer

Exie, Glad it helps. One thing I forgot to mention: because it is a function, you can use it in SQL statements directly. If I understood your columns right, INACTIVITY_1_START_DTTM is the start of blackout period 1, and INACTIVITY_1_INTERVAL_DTTM is the end of the blackout period. If so, this...

Exie, I built a function that seems to work as you dewscribed: -- DURING_BLACKOUT -- -- this function returns a varchar TRUE or FALSE if the specified I_CHECK_DATE falls between -- the I_BLACK_START_DATE and I_BLACK_END_DATE periods for the current week. The blsckout -- dates are used...

You can "fast-forward" the sequence using a single SQL loop if you need to. Dropping and recreating the sequence means rebuilding grants and synonyms, and possible recompiling PL/SQL as well. When we use a sequence to generate a sequential number for a table's primary key, we include this...

I think we found just what we were looking for: CREATE GLOBAL TEMPORARY TABLE TEST_TABLE ... ON COMMIT DELETE ROWS; This creates a table (should go in your Temporary space) which keeps it's data only through the current transaction. None of the table I/O is put into the RB segs or redo...

Santa, Thanks for the clarification on the cost of a rollback. (yikes!) As for the TRUNCATE, we cannot as the table may have data in it from other users at the time (see first part of my 2nd message). Perhaps we can leave the temp data in there and use an after-hours TRUNCATE (though that...