x002548's Blog

Site Sections

• Home
• Contact
• Syndication
• Login

Posts

83

Comments

600

Trackbacks

40

May 2005 Entries

How to post a question for a database

Since, I'm tired of retyping this...If you want to get an answer fast about a database question (doesn't matter the platform) and it's sql related....Do this

Please state your problem in the context of a business requirement. Please do not force a narrowly focused technical solution, which may or may not be of any value.  It may also be a distraction to what the actual solution would be. To aid in the solution please do the following if possible

1. State the question

"How do I find the earliest row entered"

2. Please post the DDL of your tables (Including Indexes, and constraints)

Like

CREATE TABLE myTable99(Col1 int, Col2...

3. Post some sample data in the form of DML

Like

INSERT INTO myTable99(Col1, Col2, ect)
SELECT 1, 'x', ect UNION ALL
SELECT 1, 'x', ect UNION ALL
SELECT 1, 'x', ect UNION ALL

4.  Post whatever DML that you have attempted already...

SELECT * FROM myTable99 CROSS JOIN myTable99 [:D]

5. Post the expected results

Thank you, and come again.

 EDIT:  Please post your question in the appropriate category here at SQLTeam

And don't forget to use [ code] [ /code] tags when posting code, just eliminate the spaces I have used here.


EDIT2: A FAQ Section

Q: How do I create DDL as requested in step 2?

A:

1. Go to Enterprise Manager.

2. Open the database folder to display all of the tables.

3. Right Click on the table you want.

4. Choose Menu options All Tasks>Generate SQL Scripts

5. Look at the dialog, there are three tabs. Make sure you pick all the correct options (indexes, keys, ect)

6. Click Preview.

7. Copy and paste the code.

posted @ Wednesday, May 25, 2005 4:37 PM | Feedback (28)

SQL Server MVP's

No Kidding...I should have guessed that Bill was one.  There's a boat load of MVPs.  Let's see who we know. OK so 85 ain't a boat load.

I know or have spoken with

Mr. Bill, Frank Kalis, Adam Machanic (I believe this is our Adam), Brad McGehee, Nigel Rivett, Jasper Smith, Rob Volk (And why isn't his bio there?)

Don't know, but I'd like to.

Kalen Delaney (Still have to get her internals book), Brian Knight  (I love Brians Books), Valeria Rodriguez Z. (Tell me again why all dba's don't look like this?)

 

posted @ Monday, May 23, 2005 4:31 PM | Feedback (15)

Remove a User From All Databases on a server

I'm sure Nigel or Tara alread have blogged this, but it was asked for and I scratched one up.Anyone have any horror stories with something like this?

EDIT: If you look in that thread, you'll see Pat Phelan's use of sp_MSForEachDb.  Very Clever.

USE Northwind
GO

CREATE PROC isp_dropuser_ALL @user sysname
AS
SET NOCOUNT ON
DECLARE @MAX_name sysname, @name sysname, @sql nvarchar(4000), @check int

SELECT @sql = 'SELECT @Check=1 FROM master..syslogins WHERE [name] = '''+@user+'''', @check = NULL
 EXECUTE sp_executesql @sql, N'@Check int OUT', @Check OUT
 IF @Check IS NOT NULL
   BEGIN
  SELECT @MAX_name = MAX([name]), @name = MIN([name]) FROM master..sysdatabases
  
  WHILE @name <= @MAX_name
    BEGIN
   PRINT 'Interogatting Database ' + @name
   SELECT @sql = 'SELECT @Check=1 FROM ' + @name + '..sysusers WHERE [name] = '''+@user+'''', @check = NULL
   EXECUTE sp_executesql @sql, N'@Check int OUT', @Check OUT
   IF @Check IS NOT NULL
     BEGIN 
    SELECT @sql = 'EXEC '+@name+'..sp_dropuser ''' +@user+''''
    EXEC(@sql)
     END
   SELECT @name = MIN([name]) FROM master..sysdatabases WHERE [name] > @name
    END
  PRINT 'Removing Login ' + @user + ' From Server ' + @@SERVERNAME
  SELECT @sql = 'EXEC master..sp_droplogin ''' +@user+''''
  EXEC(@sql)
 END
   ELSE
  PRINT 'User ' + @User + ' does not have a Login to this Server'
SET NOCOUNT OFF
GO

EXEC sp_addlogin 'myUser99', 'myPassword99', 'Northwind'
EXEC sp_adduser 'myUser99'
select [name] from master..syslogins WHERE [name] Like 'my%'
select [name] from sysusers WHERE [name] Like 'my%'
GO

EXEC isp_dropUser_ALL 'myUser99'
select [name] from master..syslogins WHERE [name] Like 'my%'
select [name] from sysusers WHERE [name] Like 'my%'
GO

EXEC isp_dropUser_ALL 'xxx'
GO

DROP PROC isp_dropuser_ALL
GO

 

posted @ Monday, May 23, 2005 2:57 PM | Feedback (1)

Weekends, Holidays and other reasons to Party

OK, so I really never need a reason.  The question ofter arises.

“How can I count the number of days between 2 dates, but exclude weekends and holidays”

EDIT: In this thread, Dr. Cross Join(aka Jeff Smith) posts a method where you don't need to add weekends.  I gotta test it out yet, but I pretty sure Jeff's right.

Here's How

CREATE TABLE WeekEndsAndHolidays (DayOfWeekDate datetime, DayName char(3))
GO

SET NOCOUNT ON
DECLARE @FirstSat datetime, @x int
SELECT @FirstSat = '1/3/2004', @x = 1

--Add WeekEnds
WHILE @x < 52
BEGIN
 INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName)
 SELECT DATEADD(ww,@x,@FirstSat),   'SAT' UNION ALL
 SELECT DATEADD(ww,@x,@FirstSat+1), 'SUN'
 SELECT @x = @x + 1
END
SET NOCOUNT OFF
GO

SELECT * FROM WeekEndsAndHolidays Order by DayOfWeekDate
GO

-- Add US Holidaze
INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName)
SELECT '1/1/2004',   'THU' UNION ALL
SELECT '2/16/2004',  'MON' UNION ALL
SELECT '5/31/2004',  'MON' UNION ALL
SELECT '7/5/2004',   'MON' UNION ALL
SELECT '9/6/2004',   'MON' UNION ALL
SELECT '11/25/2004', 'THU' UNION ALL
SELECT '11/26/2004', 'FRI' UNION ALL
SELECT '12/24/2004', 'FRI' UNION ALL
SELECT '12/31/2004', 'FRI'
GO

SELECT * FROM WeekEndsAndHolidays Order by DayOfWeekDate
GO

-- WeekDays in May

DECLARE @Start datetime, @End datetime
SELECT @Start = '5/1/2004', @End = '6/1/2004'

SELECT  DATEDIFF(dd,@Start,@End)- COUNT(*) AS WeekDays
  FROM WeekEndsAndHolidays
 WHERE DayOfWeekDate BETWEEN @Start AND @End
GO

 

posted @ Thursday, May 12, 2005 2:36 PM | Feedback (11)

Find a word in a string

By doing this with an identity Column on the row, you have a pseudo array for sql server.  This user defined function will grab the nth occurance of a word in a string.  By marrying that with the IDENTITY, it can be assumed to be an array.  The next parts of these would be to be able to perform functions like DELETE and UPDATE of the word in that location (which may be more trouble than it's worth.)  Pluse in Ken Henderson's Book, “The Guru's Guide to Stored Procedures, XML, and HTML”, has an entire chapter dedicated to “creating” arrays in sql server.  You have to be extremely careful about memory management and object management.  Suffice it to say, I don't have a box I can thouroughly destroy, so I never played with it.

But this concept is similar.  At the very minium, this is an extension of all the “How do I parse words from a string” questions.

Anyway, here's the udf.

CREATE FUNCTION udf_GetWord (
   @str nvarchar(4000)
 , @Word int
 , @Delim char(1)
)
RETURNS nvarchar(4000)
AS
BEGIN
 DECLARE @LastPosition int, @DelimFound int, @Start int, @End int, @WordFound nvarchar(4000)

 SELECT @LastPosition = 0, @DelimFound = 0
 WHILE (@DelimFound < @Word-1)
   BEGIN
  IF (CHARINDEX(@Delim, @str, @LastPosition + 1) = 0)
   BREAK
 
  ELSE
    BEGIN
   SET @LastPosition = CHARINDEX(@Delim, @str, @LastPosition + 1)
   SET @DelimFound = @DelimFound + 1
    END
  
   END
 
 SET @Start = @LastPosition + 1
 SET @End = CHARINDEX(@Delim, @str, @LastPosition + 1) - @Start
 IF (@End = 0) SET @End = LEN(@str)
 SELECT @WordFound = SUBSTRING(@str,@start,@end)
 RETURN @WordFound
END

 

posted @ Monday, May 09, 2005 3:51 PM | Feedback (4)

bcp out for Oracle

As of Oracle 8i (which I believe is no longer supported) there was no utility to unload data.  I guess Oracle felt that once the got the data, there was no reason to relinquesh it.  In any case, for myself, and my rusty Oracle, here a sample of code on how to code in PLSQL as well as how to perform an extract.  I know it'll be hard from a sql server perspective, but this actually flies.

-- @Q:\Packages\Ben_Extract_Package.pkg;
-- EXEC Ben_Extract_Package.Ex_ENR_PARTIC_sp;
-- EXEC Ben_Extract_Package.Ex_ENR_PARTIC_PLAN_sp;
-- EXEC Ben_Extract_Package.Ex_ENR_PARTIC_DPND_sp;
-- EXEC Ben_Extract_Package.Ex_ENR_DPND_BENEF_sp;

-- ***********************************************************************
-- *         Prudential Benefits Annual Enrollment System                *
-- *          *
-- * Description: Extract Feed to PeopleSoft                             *
-- *              Specifications used:                                   *
-- *              Web-IVR_To_PeopleSoft_Files_Layout.doc                 *
-- * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - *
-- * Modifications Log:                                                  *
-- *          *
-- * User     Date       Description                                     *
-- * -------- ---------- ------------------------------------------------*
-- * x002548  07/19/2000 Initial Version                                 *
-- * x002548  10/12/2000 Modify PESP Extract to include format changes,  *
-- *                     Change the amount fields to 2 byte integers     *
-- * x002548  09/25/2000 Modify PESP Extract to include format changes,  *
-- *                     truncate the National_Id to 9 bytes from 15, and*
-- *                     add a trailer record                            *
-- * - - - - - - - - - - - - - 2002 Changes - - - - - - - - - - - - - - -*
-- * x091589  08/22/2001 Add SCHED_ID to Ex_ENR_PARTIC_sp   * 
-- *      Ex_ENR_PARTIC_PLAN_sp   *
-- *      Ex_ENR_PARTIC_DPND_sp   *
-- * x091589  08/22/2001 Add STUDENT_CERTIFY to Ex_ENR_DPND_BENEF_sp  *
-- * x091589  08/22/2001 Remove Ex_PESP_sp     *
-- * x091589  10/03/2001 Increase the size of EMPL_CONTRBUTN_AMT from 8-9*
-- *    When Null      *
-- *          *
-- ***********************************************************************
-- ********** S T A R T - G L O B A L - D E C L A R A T I O N S **********
-- ***********************************************************************

CREATE OR REPLACE PACKAGE Ben_Extract_Package AS

I_FileType utl_file.file_type;
I_FilePath VarChar2(200);
I_FileName VarChar2(50);
O_SqlCode Number;
O_SqlErrM VarChar2(254);
O_RowCount Number;

-- ***********************************************************************
-- * Procedure Calls                                                    *
-- ***********************************************************************

    PROCEDURE Ex_ENR_PARTIC_sp;
    PROCEDURE Ex_ENR_PARTIC_PLAN_sp;
    PROCEDURE Ex_ENR_PARTIC_DPND_sp;
    PROCEDURE Ex_ENR_DPND_BENEF_sp;

END Ben_Extract_Package;

/

CREATE OR REPLACE PACKAGE BODY Ben_Extract_Package
AS

-- **********************************************************************
-- *** P R O C E D U R E (Ex_ENR_PARTIC_sp) D E C L A R A T I O N S *****
-- **********************************************************************
  PROCEDURE Ex_ENR_PARTIC_sp AS
  v_row_count number := 0;

Begin

-- Declare Cursor to extract the ENR_PARTIC table as a 1 Column fixed width 124 bytes
-- called Unload_String

Declare Cursor
  CSR_ENR_PARTIC IS
    SELECT
    RPAD(A.EMPLID,11)
  ||NVL((RPAD(A.BENEFIT_PROGRAM,3)), '   ')  
  ||NVL((RPAD(A.EVENT_CLASS,3)), '   ')  
  ||NVL((RPAD(A.EVENT_STATUS,1)), ' ')  
  ||NVL((RPAD(A.BAS_PROCESS_STATUS,2)), '  ')  
  ||NVL((TO_CHAR(A.EVENT_DT, 'YYYY-MM-DD')),'          ')
  ||NVL((RPAD(A.ELECT_SUMM,1)), ' ') 
  ||NVL((TO_CHAR(A.ELECT_SUMM_DT, 'YYYY-MM-DD')),'          ')
  ||NVL((RPAD(A.SURVEY_COMPLETE,1)), ' ') 
  ||NVL((TO_CHAR(A.SURVEY_COMPLETE_DT, 'YYYY-MM-DD')),'          ')
  ||NVL((TO_CHAR(A.DATAASOF_DT, 'YYYY-MM-DD')),'          ')   
  ||NVL((TO_CHAR(A.DATAIN_DT, 'YYYY-MM-DD')),'          ')
  ||NVL((TO_CHAR(A.DATAOUT_DT, 'YYYY-MM-DD')),'          ')
  ||TO_CHAR(A.LASTUPDDTTM, 'yyyy-mm-dd-hh24.mi.ss')||'.000000'
  ||NVL((RPAD(A.LASTUPDUSER,15)),'               ') 
  ||NVL((RPAD(A.LASTUPDVEH,1)), ' ')
  ||RPAD(NVL(C.SCHED_ID,' '),6) AS Unload_String
      FROM   ENR_PARTIC A
     ,ENR_EMPLOY_DATA B
     ,ENR_USER_SCRTY C
     WHERE  A.EMPLID    = B.EMPLID
       AND  B.NATIONAL_ID = C.NATIONAL_ID; 

csr_Columns CSR_ENR_PARTIC%ROWTYPE;

Begin

  Ben_Extract_Package.O_SqlCode  := 0;
  Ben_Extract_Package.O_SqlErrM  := 'Normal Completion';
 Ben_Extract_Package.I_FileName := 'ENR_PARTIC.txt';
 Ben_Extract_Package.I_FilePath := 'D:\Benefits\Extracts\';

      I_FileType := utl_file.fopen (I_FilePath,I_FileName,'W');
 
 Ben_Extract_Package.O_RowCount := 0;

-- Fetch rows and write records.  The Cursor Open, Fetch and Close is dynamic
 For csr_Columns IN CSR_ENR_PARTIC
  Loop
                  v_row_count := v_row_count + 1;
   utl_file.put_line(Ben_Extract_Package.I_FileType, csr_Columns.Unload_String);
   Ben_Extract_Package.O_RowCount := Ben_Extract_Package.O_RowCount + 1;
  End Loop;

      utl_file.fclose  (Ben_Extract_Package.I_FileType);

 
     dbms_output.put_line('ENR_PARTIC EXTRACT COUNT:'||to_char(v_row_count));
   EXCEPTION
 WHEN utl_file.invalid_path THEN
  dbms_output.put_line(' FAILS Extract -  Invalid path');
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid File Path';
 WHEN utl_file.invalid_operation THEN
  dbms_output.put_line(' FAILS Extract -  Invalid operation');
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid Operation';
 WHEN utl_file.invalid_mode THEN
  dbms_output.put_line(' FAILS Extract -  Invalid mode');
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid Mode';
 WHEN OTHERS THEN
  dbms_output.put_line('SqlError:='||SQLERRM||' SQLCODE:='||SQLCODE);
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := SqlErrM(SqlCode);
End;
   END Ex_ENR_PARTIC_sp;

-- ***************************************************************************
-- *** P R O C E D U R E (Ex_ENR_PARTIC_PLAN_sp) D E C L A R A T I O N S *****
-- ***************************************************************************
  PROCEDURE Ex_ENR_PARTIC_PLAN_sp AS
  v_row_count number := 0;

Begin
-- Declare Cursor to extract the ENR_PARTIC_PLAN table as fixed width 124 bytes

Declare Cursor
  CSR_ENR_PARTIC_PLAN IS

    SELECT
     RPAD(A.EMPLID,11)
  ||RPAD(A.PLAN_TYPE,2)
  ||RPAD(NVL(A.BENEFIT_PROGRAM,' '),3)
  ||RPAD(NVL(TO_CHAR(A.EVENT_DT, 'YYYY-MM-DD'), ' '),10)
  ||RPAD(NVL(A.DISPLAY_PLN_SEQ,' '),2)
  ||RPAD(NVL(A.ELECTION_MADE,' '),1)
  ||RPAD(NVL(A.PROOF_RCVD,' '),1)
  ||RPAD(NVL(A.OPTION_CD,' '),3)
  ||RPAD(NVL(A.ALT_OPTION_CD,' '),3)
  ||NVL(LPAD(TO_CHAR(A.PCT_GROSS, +990.999),8,0),'   0.000')
  ||NVL(LPAD(TO_CHAR(A.PCT_GROSS_ATAX, +990.999),8,0),'   0.000')
  ||NVL(LPAD(TO_CHAR(A.CUR_PCT_GROSS, +990.999),8,0),'   0.000')
  ||NVL(LPAD(TO_CHAR(A.CUR_PCT_GROSS_ATAX, +990.999),8,0),'   0.000')
  ||NVL(LPAD(TO_CHAR(A.EMPL_CONTRBUTN_AMT, +99990.99),9,0),'     0.00')
  ||NVL(LPAD(TO_CHAR(A.ANNUAL_PLEDGE, +99990.99),9,0),'     0.00')
  ||RPAD(NVL(A.CUR_HLTH_PROVIDER,' '),1)
  ||RPAD(NVL(A.HLTH_PROVIDER_ID1,' '),30)
  ||RPAD(NVL(A.HLTH_PROVIDER_ID2,' '),30)
  ||RPAD(NVL(A.HIGHLY_COMP_EE,' '),1)
  ||RPAD(NVL((TO_CHAR(A.LASTUPDDTTM, 'yyyy-mm-dd-hh24.mi.ss')||'.000000'),' '),26)
  ||RPAD(NVL(A.LASTUPDUSER,' '),15)
  ||RPAD(NVL(A.LASTUPDVEH,' '),1)
  ||RPAD(NVL(C.SCHED_ID,' '),6) AS Unload_String
      FROM   ENR_PARTIC_PLAN A
     ,ENR_EMPLOY_DATA B
     ,ENR_USER_SCRTY C
     WHERE  A.EMPLID    = B.EMPLID
       AND  B.NATIONAL_ID = C.NATIONAL_ID; 

csr_Columns CSR_ENR_PARTIC_PLAN%ROWTYPE;

Begin

 Ben_Extract_Package.O_SqlCode := 0;
 Ben_Extract_Package.O_SqlErrM := 'Normal Completion';
 Ben_Extract_Package.I_FileName := 'ENR_PARTIC_PLAN.txt';
 Ben_Extract_Package.I_FilePath := 'D:\Benefits\Extracts\';

-- Open File
Ben_Extract_Package.I_FileType := utl_file.fopen (Ben_Extract_Package.I_FilePath,Ben_Extract_Package.I_FileName,'w');

Ben_Extract_Package.O_RowCount := 0;

-- Fetch rows and write records.  The Cursor Open, Fetch and Close is dynamic

 For csr_Columns IN CSR_ENR_PARTIC_PLAN
  Loop
                  v_row_count := v_row_count + 1;
   utl_file.put_line(Ben_Extract_Package.I_FileType, csr_Columns.Unload_String);
   Ben_Extract_Package.O_RowCount := Ben_Extract_Package.O_RowCount + 1;
  End Loop;

utl_file.fclose  (Ben_Extract_Package.I_FileType);

 
      dbms_output.put_line('ENR_PARTIC_PLAN EXTRACT COUNT:'||to_char(v_row_count));

   EXCEPTION
 WHEN utl_file.invalid_path THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid File Path';
 WHEN utl_file.invalid_operation THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid Operation';
 WHEN utl_file.invalid_mode THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid Mode';
 WHEN OTHERS THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := SqlErrM(SqlCode);
End;
   END Ex_ENR_PARTIC_PLAN_sp;

-- ***************************************************************************
-- *** P R O C E D U R E (Ex_ENR_PARTIC_DPND_sp) D E C L A R A T I O N S *****
-- ***************************************************************************
  PROCEDURE Ex_ENR_PARTIC_DPND_sp AS
   v_row_count number := 0;

Begin
-- Declare Cursor to extract the ENR_PARTIC_DPND table as fixed width n bytes

Declare Cursor
  CSR_ENR_PARTIC_DPND IS

  SELECT
     RPAD(A.EMPLID,11)
  ||RPAD(A.PLAN_TYPE,2)
  ||RPAD(NVL(A.DEPENDENT_BENEF,' '),2)
  ||RPAD(NVL(A.BENEFIT_PROGRAM,' '),3)
  ||RPAD(NVL(TO_CHAR(A.EVENT_DT, 'YYYY-MM-DD'), ' '),10)
  ||RPAD(NVL(A.DISPLAY_PLN_SEQ,' '),2)
  ||LPAD(TO_CHAR(NVL(A.BENEF_PCT,0)),3,'0')
  ||RPAD(NVL(A.CONTINGENT,' '),1)
  ||RPAD(NVL(A.HLTH_PROVIDER_ID1,' '),30)
  ||RPAD(NVL(A.HLTH_PROVIDER_ID2,' '),30)
  ||RPAD(NVL(A.CUR_HLTH_PROVIDER,' '),1)
  ||RPAD(NVL(C.SCHED_ID,' '),6) AS Unload_String
      FROM   ENR_PARTIC_DPND A
     ,ENR_EMPLOY_DATA B
     ,ENR_USER_SCRTY C
     WHERE  A.EMPLID    = B.EMPLID
       AND  B.NATIONAL_ID = C.NATIONAL_ID; 

csr_Columns CSR_ENR_PARTIC_DPND%ROWTYPE;

Begin

 Ben_Extract_Package.O_SqlCode := 0;
 Ben_Extract_Package.O_SqlErrM := 'Normal Completion';
 Ben_Extract_Package.I_FileName := 'ENR_PARTIC_DPND.txt';
 Ben_Extract_Package.I_FilePath := 'D:\Benefits\Extracts\';

-- Open File
Ben_Extract_Package.I_FileType := utl_file.fopen (Ben_Extract_Package.I_FilePath,Ben_Extract_Package.I_FileName,'w');

Ben_Extract_Package.O_RowCount := 0;

-- Fetch rows and write records.  The Cursor Open, Fetch and Close is dynamic

 For csr_Columns IN CSR_ENR_PARTIC_DPND
  Loop
                  v_row_count := v_row_count + 1;
   utl_file.put_line(Ben_Extract_Package.I_FileType, csr_Columns.Unload_String);
   Ben_Extract_Package.O_RowCount := Ben_Extract_Package.O_RowCount + 1;
  End Loop;

utl_file.fclose  (Ben_Extract_Package.I_FileType);

 
      dbms_output.put_line('ENR_PARTIC_DPND EXTRACT COUNT:'||to_char(v_row_count));

   EXCEPTION
 WHEN utl_file.invalid_path THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid File Path';
 WHEN utl_file.invalid_operation THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid Operation';
 WHEN utl_file.invalid_mode THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid Mode';
 WHEN OTHERS THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := SqlErrM(SqlCode);
End;
   END Ex_ENR_PARTIC_DPND_sp;

-- **************************************************************************
-- *** P R O C E D U R E (Ex_ENR_DPND_BENEF_sp) D E C L A R A T I O N S *****
-- **************************************************************************
  PROCEDURE Ex_ENR_DPND_BENEF_sp AS
   v_row_count number := 0;

Begin
-- Declare Cursor to extract the ENR_DPND_BENEF table as fixed width n bytes

Declare Cursor
  CSR_ENR_DPND_BENEF IS
        SELECT
     RPAD(A.EMPLID,11)
  ||RPAD(A.DEPENDENT_BENEF,2)
  ||RPAD(NVL(A.NAME,' '),50)
  ||RPAD(NVL(A.SAME_ADDRESS_EMPL,' '),1)
  ||RPAD(NVL(A.ADDRESS1,' '),35)
  ||RPAD(NVL(A.ADDRESS2,' '),35)
  ||RPAD(NVL(A.ADDRESS3,' '),35)
  ||RPAD(NVL(A.ADDRESS4,' '),35)
  ||RPAD(NVL(A.CITY,' '),30)
  ||RPAD(NVL(A.STATE,' '),6)
  ||RPAD(NVL(A.POSTAL,' '),12)
  ||RPAD(NVL(A.RELATIONSHIP,' '),2)
  ||RPAD(NVL(A.DEP_BENEF_TYPE,' '),1)
  ||RPAD(NVL(A.MAR_STATUS,' '),1)
  ||RPAD(NVL(TO_CHAR(A.MAR_STATUS_DT, 'YYYY-MM-DD'), ' '),10)
  ||RPAD(NVL(A.SEX,' '),1)
  ||RPAD(NVL(TO_CHAR(A.BIRTHDATE, 'YYYY-MM-DD'), ' '),10)
  ||RPAD(NVL(A.STUDENT,' '),1)
  ||RPAD(NVL(A.DISABLED,' '),1)
  ||RPAD(NVL(A.NATIONAL_ID,' '),15)
  ||RPAD(NVL(A.DPND_CERTIFY,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_DP1,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_DP2,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_DP3,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_DP4,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_DP5,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_DP6,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_DP7,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_EF1,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_EF2,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_EF3,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_EF4,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_AR1,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_AR2,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_AR3,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_AR4,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_AR5,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_AR6,' '),1)
  ||RPAD(NVL(A.QA_CERTIFY_AR7,' '),1)
  ||RPAD(NVL(A.NEW_RCD,' '),1)
  ||RPAD(NVL((TO_CHAR(A.LASTUPDDTTM, 'yyyy-mm-dd-hh24.mi.ss')||'.000000'),' '),26)
  ||RPAD(NVL(A.LASTUPDUSER,' '),15)
  ||RPAD(NVL(A.LASTUPDVEH,' '),1)
  ||RPAD(NVL(A.STUDENT_CERTIFY,' '),1) AS Unload_String
      FROM   ENR_DPND_BENEF A;

csr_Columns CSR_ENR_DPND_BENEF%ROWTYPE;

Begin

 Ben_Extract_Package.O_SqlCode := 0;
 Ben_Extract_Package.O_SqlErrM := 'Normal Completion';
 Ben_Extract_Package.I_FileName := 'ENR_DPND_BENEF.txt';
 Ben_Extract_Package.I_FilePath := 'D:\Benefits\Extracts\';

-- Open File
Ben_Extract_Package.I_FileType := utl_file.fopen (Ben_Extract_Package.I_FilePath,Ben_Extract_Package.I_FileName,'w');

Ben_Extract_Package.O_RowCount := 0;

-- Fetch rows and write records.  The Cursor Open, Fetch and Close is dynamic

 For csr_Columns IN CSR_ENR_DPND_BENEF
  Loop
                  v_row_count := v_row_count + 1;
   utl_file.put_line(Ben_Extract_Package.I_FileType, csr_Columns.Unload_String);
   Ben_Extract_Package.O_RowCount := Ben_Extract_Package.O_RowCount + 1;
  End Loop;

utl_file.fclose  (Ben_Extract_Package.I_FileType);

 
      dbms_output.put_line('ENR_DPND_BENEF EXTRACT COUNT:'||to_char(v_row_count));

   EXCEPTION
 WHEN utl_file.invalid_path THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid File Path';
 WHEN utl_file.invalid_operation THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid Operation';
 WHEN utl_file.invalid_mode THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := 'Invalid Mode';
 WHEN OTHERS THEN
  Ben_Extract_Package.O_SqlCode := SqlCode;
  Ben_Extract_Package.O_SqlErrM := SqlErrM(SqlCode);
End;
   END Ex_ENR_DPND_BENEF_sp;

END Ben_Extract_Package;

/
   

 

posted @ Thursday, May 05, 2005 12:33 PM | Feedback (3)

Article Categories

• DB2
• West Orange Ridership Community

Archives

• April, 2008 (1)
• July, 2007 (1)
• March, 2007 (2)
• February, 2007 (4)
• December, 2006 (1)
• November, 2006 (2)
• September, 2006 (4)
• August, 2006 (2)
• July, 2006 (2)
• June, 2006 (3)
• May, 2006 (2)
• November, 2005 (1)
• October, 2005 (1)
• September, 2005 (1)
• August, 2005 (2)
• June, 2005 (8)
• May, 2005 (6)
• April, 2005 (5)
• February, 2005 (4)
• January, 2005 (5)
• December, 2004 (2)
• October, 2004 (2)
• September, 2004 (2)
• August, 2004 (1)
• June, 2004 (6)
• May, 2004 (2)
• April, 2004 (2)
• March, 2004 (2)
• February, 2004 (3)
• January, 2004 (2)
• October, 2003 (1)
• September, 2003 (1)

Post Categories

• Code Generataion
• DB2
• Disconnected Ramblings
• Oracle
• SQL Gimmicks
• SQL Server

Image Galleries

• Directions
• Lost Again?
• Misc
• Mountains
• SQLSki Team

Misc

• It's an Enigma Wrapped up in a riddle
• Little Feat
• Radio 'Rita
• Ski Resort Cam List

SQL Server

• Daily WTF (Doesn't mean World Tennis Federation)
• dbForums Member List
• SQL Express Team's BLOG
• SQL Server 2000 Books Online
• SQLTeam
• SQLTeam Member List