Code Tester for Oracle Community

Code Tester for Oracle Forum

Ranatosh Das
Follow / 5 Apr 2018 at 3:13pm / Code Tester for Oracle
Latest post by on 25 Apr 2018 at 2:38pm

Need Code Coverage data for Complete Package

Expand content
Hi All, I have created some test cases for the procedures and functions of a package. I have enabled the "Gather Code Coverage data" option for the package and also for the sub programs(procedures...

Hi All,

I have created some test cases for the procedures and functions of a package. I have enabled the "Gather Code Coverage data" option for the package and also for the sub programs(procedures and functions). On execution of the test cases, I can see the code coverage for each and every procedures and functions of the package. But the data is specific to the procedure or function.

Is there any way I can get the complete code coverage data for the whole package based on all the test cases for the member procedures and functions.

Regards,

Ranatosh

Unanswered 1611 3 / Create an account to join the discussion.
sjainull
Follow / 30 Mar 2018 at 12:57pm / Code Tester for Oracle
Latest post by on 8 Apr 2018 at 8:13am

Facing Performance issue with code tester tool

Expand content
Hi All, I am using code tester 3.2.0.19 version. Oracle data base version 11g. For creating Test case we are using toad 12.12 it takes less than a minute or two. But thing is we did not find any option...

Hi All,

I am using code tester 3.2.0.19 version.  Oracle data base version 11g. 

For creating Test case we are using toad 12.12 it takes less than a minute or two. But thing is we did not find any option to  enable the code coverage for the test cases , create the suite and copy the test case to Suite which we creates in toad. So we have to use code tester tool.

We are facing  huge performance issue with tool. PFB details
a) For connection it tooks 15-30 mins.
b) To Edit created Test Case it takes 15-30 mins 
c) When we run test case it tooks 20- 30 mins to complete.
d) TO copy the Test case  to Suite it will take  15-30 mins
if u consider above max times to execute 1 Test case  it tooks 1 hr to 1.5 hr.Some times the tool hangs in this case we need to repeat the process again.

Could you please let us know is this the usual behaviour or we are missing some thing which results in this issue.

Thanks in Advance.

Regards,

Jainul

1768 5 / Create an account to join the discussion.
Sarfraz_Ramay
Follow / 21 Mar 2018 at 5:26pm / Code Tester for Oracle
Latest post by on 27 Mar 2018 at 1:09pm

schema.schema causing compile time error when code tester and application...

Expand content
application code - APPS schema custom code - XXG schema code tester - XXG_CODE_TESTER schema I am testing a packaged procedure in APPS schema. One of the arguments is pi_claim_rec IN xxg_interface_stg...

application code - APPS schema

custom code - XXG schema

code tester - XXG_CODE_TESTER schema

I am testing a packaged procedure in APPS schema. One of the arguments is pi_claim_rec    IN xxg_interface_stg%ROWTYPE

xxg_interface_stg table is created under the XXG schema and being referenced with a synonym in APPS schema where the actual packaged procedure being tested resides. I do NOT want to add xxg. prefix to the argument in procedure nor do i want to prefix it with APPS

XXG_CODE_TESTER has all the privileges on xxg_interface_stg table and execute priv on APPS. other tests are running fine.

When i create a test there is a compile time error 

PLS-00201: identifier 'XXG.APPS' must be declared
PL/SQL: Item ignored
PLS-00313: 'S_PI_CLAIM_REC$1' not declared in this scope

If i copy the body of generated test code to TOAD, remove the duplicate schema.schema reference the code compiles fine but it doesn't help

1. With every new test case added the test code keeps getting overwritten with latest test code being generated

2.  When the test is run it says "No test results available at this level" under the results tab

3. Under Properties tab test code compilation status keeps going invalid after the run even though i just compiled successfully after removing XXG.APPS.

Is this a known issue ? How can i get around this please ? I am using trial version code tester 3.2.0.19 part of Toad Oracle Expert 11.6 2017

1951 4 / Create an account to join the discussion.
sjayanna
Follow / 13 Oct 2017 at 2:32pm / Code Tester for Oracle
Latest post by on 26 Mar 2018 at 12:35pm

Code tester : Error in Install or Upgrade repository

Expand content
Code Tester encountered one or more problems attempting to install, upgrade or remove the backend. The details are listed below. If you do not think that the problem is caused by some aspect of your own...

Code Tester encountered one or more problems attempting to install,
upgrade or remove the backend. The details are listed below.
If you do not think that the problem is caused by some aspect
of your own environment (no privileges on tablespace, would
be one such cause), please click on the button above to send
support the error report.

<b>Invalid objects</b>

NONE

<b>Problems with installation of necessary data</b>

QU_VERIFY.VERIFY_INSTALL failed
ORA-04045: errors during recompilation/revalidation of APPS.QU_CONFIG
ORA-01775: looping chain of synonyms
ORA-06508: PL/SQL: could not find program unit being called: "APPS.QU_CONFIG"
ORA-06512: at "APPS.QU_VERIFY", line 3723
ORA-06512: at "APPS.QU_VERIFY", line 3740
ORA-06512: at "APPS.QU_VERIFY", line 3952
ORA-06512: at line 4


<b>Install environment</b>

Oracle Version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Windows Version:
Windows 10 (Version 10.0, Build 0, 64-bit Edition)

<b>Task body</b>

@C:\Program Files\Quest Software\Toad for Oracle 2017 R2 Edition\Code Tester for Oracle 3.2\\Code\qu_uninstall
@C:\Program Files\Quest Software\Toad for Oracle 2017 R2 Edition\Code Tester for Oracle 3.2\\Code\qu_install
@C:\Program Files\Quest Software\Toad for Oracle 2017 R2 Edition\Code Tester for Oracle 3.2\\Code\qu_uninstall_synonyms
@C:\Program Files\Quest Software\Toad for Oracle 2017 R2 Edition\Code Tester for Oracle 3.2\\Code\qu_access
REM &lt;guiaction&gt;qu_verify_synonyms&lt;/guiaction&gt;&lt;guimessage&gt;Non existing public synonyms&lt;/guimessage&gt;

Unanswered 2900 3 / Create an account to join the discussion.
Sarfraz_Ramay
Follow / 14 Mar 2018 at 4:04pm / Code Tester for Oracle
Latest post by on 15 Mar 2018 at 7:05am

how to test local procedures/functions inside package body

Expand content
I am using a separate schema for each application. XXG_CODE_TESTER is the test schema and APPS is the code schema. I have granted execute priv on the package i am changing to xxg_Code_tester. The test...

I am using a separate schema for each application. XXG_CODE_TESTER is the test schema and APPS is the code schema. I have granted execute priv on the package i am changing to xxg_Code_tester. The test seems to be working fine but I am only able to select the public procedure in package spec for creating and running the test.

Is it possible to test local procedures/functions in the package body ?

1758 1 / Create an account to join the discussion.
kparadise
Follow / 2 Nov 2017 at 6:48pm / Code Tester for Oracle
Latest post by on 14 Nov 2017 at 6:27pm

Creating Temporary Tables

Expand content
Hello, I am new to TOAD for the most part; as I mostly work within Teradata SQL Assistant. However I need to work in TOAD for this Oracle database so my question is; can you create temporary tables in...

Hello,

I am new to TOAD for the most part; as I mostly work within Teradata SQL Assistant.  However I need to work in TOAD for this Oracle database so my question is; can you create temporary tables in TOAD. 

For instance, I have a list of 30,000 unique ID's, and I want to upload those IDs into TOAD somehow (temp table) so I can extract data for those 30,000 accounts only using other tables. 

This is possible in Teradata SQL Assistant by using;

CREATE VOLATILE TABLE TABLE_NAME     

(FIELD_NAME INT/VARCHAR(50)/DECIMAL)

ON COMMIT PRESERVE ROWS;

then

INSERT INTO TABLE_NAME VALUES (?)

then you select your text file which holds the unique IDs, and it imports those into the table.

3260 1 / Create an account to join the discussion.
tpetchel
Follow / 2 Oct 2017 at 3:00pm / Code Tester for Oracle
Latest post by on 17 Oct 2017 at 3:49pm

CODE TESTER

Expand content
I'm getting "Unit tesing is not supported due to unsupported data types" when attempting to create a test definition for a function that takes a RAW inpu parameter. Is there a work around...
I'm getting "Unit tesing is not supported due to unsupported data types" when attempting to create a test definition for a function that takes a RAW inpu parameter. Is there a work around for this?
Unanswered 3430 3 / Create an account to join the discussion.
eric.seesemann
Follow / 6 Sep 2017 at 9:59am / Code Tester for Oracle
Latest post by on 11 Oct 2017 at 2:42pm

qu_test.run_suite_by_Name get NEVER-RUN-Description: No test results...

Expand content
Hi, i'll run a Test-Suite in a PL/SQL-Job automaticly. My Test-Suite "FE" works in the front end well, but when i use the API, i get the result " NEVER-RUN-Description: No test results...

Hi,

i'll run a Test-Suite in a PL/SQL-Job automaticly.

My Test-Suite "FE" works in the front end well, but when i use the API, i get the result " NEVER-RUN-Description: No test results available at this level.".

I use the folowing code to starte the Suite:

DECLARE
   l_result       VARCHAR2(100);
   l_result_row   qu_result_xp.last_run_results_suite_cur%ROWTYPE;
   my_results     qu_result_xp.last_run_results_suite_rc;
BEGIN
   qu_test.run_suite_by_name (suite_name_in                    => 'FE'
                        , result_out                  => l_result
                        , results_out                 => my_results
                        , unit_test_guid_list_in      => NULL
                        , test_case_guid_list_in      => NULL
                        , delimiter_in                => NULL
                        , code_coverage_goal_in       => NULL
                         );
   DBMS_OUTPUT.put_line ('Overall result of test: ' || l_result);

   LOOP
      FETCH my_results
       INTO l_result_row;

      EXIT WHEN my_results%NOTFOUND;
      DBMS_OUTPUT.put_line (   RPAD (' ', l_result_row.h_level * 2)
                            || ' '
                            || l_result_row.NAME
                            || '-Status: '
                            || l_result_row.result_status
                            || '-Description: '
                            || l_result_row.description
                           );
   END LOOP;
END;
/

When i run the test inside the Suite over the API-Call "qu_test.run_test_for2", it works also fine.

Does anyone have an idea?

Eric

3542 4 / Create an account to join the discussion.
sjayanna
Follow / 29 Sep 2017 at 8:21am / Code Tester for Oracle

Error while connecting to database in Code Tester 2.7

Expand content
ORA-04045: errors during recompilation/revalidation of APPS.QU_CONFIG ORA-01775: looping chain of synonyms ORA-06508: PL/SQL: could not find program unit being called: "APPS.QU_CONFIG" ...

ORA-04045: errors during recompilation/revalidation of APPS.QU_CONFIG

ORA-01775: looping chain of synonyms

ORA-06508: PL/SQL: could not find program unit being called: "APPS.QU_CONFIG"

Checked in database in the instance, the package QU_CONFIG is invalid.

Unanswered 1803 0 / Create an account to join the discussion.
DirkPeter
Follow / 21 Mar 2011 at 6:03pm / Code Tester for Oracle
Latest post by on 3 Sep 2017 at 6:21am

code coverage allways 0,00

Expand content
I'm trying to get results with de code coverage feature for my unit tests. Unfortanally the results are always 0. When creating a report I allway get the messages: 'No code coverage/profile data...

I'm trying to get results with de code coverage feature for my unit tests.
Unfortanally the results are always 0.
When creating a report I allway get the messages:

'No code coverage/profile data exists for <name>

Code coverage data is gathered only when requested explicitly by checking
the "Gather code coverage data" checkbox found in the Test Editor Properties
page for your test definition. You can also set this attribute at the unit
test and test case levels if you want to gather code coverage information
for a subset of all of your tests.'


The profiler tables with public synonyms are created and I granted ALL on the table to public.

I  checked the 'Gather Code Coverage data' option in the 'test definition'.
I  checked the 'Gather Code Coverage data' option the Unittest definition and set the percentage to 75.
On the unittest tab Customization in the section -- Initialization for this unit test, I put the statement 'qu_result_xp.profile_this_unit_test ;'


After running the unit test the 'Last actual code coverage (%)' is still 0,00
What did I mis to get the % of code coverage ??

Dirk

qct_test_definition.JPG

qct_unit_test_1.JPG

qct_unit_test_2.JPG

9856 5 / Create an account to join the discussion.
Gregory Liss
Follow / 4 Nov 2015 at 11:50am / Code Tester for Oracle
Latest post by on 2 Sep 2017 at 5:30am

Code Tester for Oracle 2.7 has been released.

Expand content
We just got word that v2.7 is now available on the support portal. Get it while it's hot!!! support.software.dell.com/.../download-new-releases

We just got word that v2.7 is now available on the support portal.    Get it while it's hot!!!

support.software.dell.com/.../download-new-releases

4305 1 / Create an account to join the discussion.
duncan.mein
Follow / 18 Aug 2017 at 1:49pm / Code Tester for Oracle
Latest post by on 21 Aug 2017 at 8:41am

Schema % not found in global cache

Expand content
I have just upgrade to 3.1.0.71 and when I log into one environment and click 'Tests', I get the following error: Any thoughts on how I can solve this? Cheers Duncan

I have just upgrade to 3.1.0.71 and when I log into one environment and click 'Tests', I get the following error:

Any thoughts on how I can solve this?

Cheers

Duncan

3198 1 / Create an account to join the discussion.
indi7
Follow / 8 Aug 2017 at 11:23am / Code Tester for Oracle

Code Tester: Export and Import tests by command line

Expand content
Hi, can you help meu? I am trying to export and import tests and suites by command line but I am always getting erros. When I try to export tests I get Test Definition not found. Exit code -2010. ...

Hi, can you help meu?

I am trying to export and import tests and suites by command line but I am always getting erros.

When I try to export tests I get Test Definition not found. Exit code -2010.

When I try to import tests I get ORA-01114: IO error writing block to file  (block # ). Exit code -2019.

Can you help me? Is there a way of export and import all tests and suites by command line?

Thanks.

1697 0 / Create an account to join the discussion.
Gregory Liss
Follow / 29 Sep 2016 at 8:13pm / Code Tester for Oracle
Latest post by on 14 Jun 2017 at 7:35pm

Code Tester for Oracle v3.0 is out in the wild...

Expand content
All, We've been working hard for quite some time now and we're finally able to release the latest version of Code Tester for Oracle. You can get it as part of the Toad for Oracle developers Suite...

All,

  We've been working hard for quite some time now and we're finally able to release the latest version of Code Tester for Oracle.  You can get it as part of the Toad for Oracle developers Suite at https://support.software.dell.com/toad-for-oracle/12.10.  Aside from lots of bug fixes, the key change in this release is we now have a true 64 bit version (along with the 32 bit version). 

Lots of new stuff is on the way so stay tuned as we continue to work hard to make Code Tester even better.

4731 3 / Create an account to join the discussion.
n_praveen_kumar
Follow / 6 Jan 2016 at 2:59pm / Code Tester for Oracle
Latest post by on 30 May 2017 at 1:31pm

Getting Error when running Dynamic Sql

Expand content
Hi All, I am getting error message as below, when creating test case for procedure which has result set query build dynamically, took the dynamically created query and tried to equate it will the result...

Hi All,

I am getting error message as below, when creating test case for procedure which has result set query build dynamically, took the dynamically created query and tried to equate it will the result set  in

outcomes tab using the Query option, please let me know the correct solution for this problem

Type        Description

ERROR  Code Tester cannot determine the list of elements in the select list for this test. You must either specify a valid table, view or query in the Properties Window of the cursor variable or you must                             compare the cursor variable to a static table, view or query  (not an expression).

4477 1 / Create an account to join the discussion.
hrishi14
Follow / 26 May 2017 at 6:57am / Code Tester for Oracle
Latest post by on 30 May 2017 at 7:43am

Creating a procedure but not getting executed using toad

Expand content
hello everyone i am using toad for creating a procedure............ i am trying to create a procedure but it is not getting executed.......... the procedure is getting created but error is coming in...

hello everyone i am using toad for creating a procedure............ i am trying to create a procedure but it is not getting executed..........

the procedure is getting created but error is coming in the execution time...

create or replace procedure utv.sampl_procedure
is
startdate DATE;
begin
select tick_date into startdate from utv.tick where tick_date= to_date('2017/05/16 09:00:09' , 'yyyy/mm/dd hh24:mi:ss');
dbms_output.put_line(startdate);
end;

exec utv.sampl_proc

this is the error:

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "UTV.SAMPL_PROC", line 5
ORA-06512: at line 1

3372 1 / Create an account to join the discussion.
gene.l.bradley
Follow / 23 May 2017 at 9:39pm / Code Tester for Oracle

Re: [Code Tester for Oracle - Discussion Forum] How to declare local...

Expand content
Run the script with (F5) On Tue, May 23, 2017 at 3:10 PM, haifaG < bounce-haifaG@toadworld.com > wrote: How to declare local and global variables and execute a script with toad Thread...
Run the script with (F5)

On Tue, May 23, 2017 at 3:10 PM, haifaG <bounce-haifaG@toadworld.com> wrote:
Thread created by haifaG

Good evening everybody, I'm a new toad's user. Actually I'm doing a BI solution for commercial activity of a company.

I already construct my datawarehouse with 3dimensions (customers, products and structure) now all I have to do is to add a dimension for date to make my events organised and to add a calendar in my qlikview sheets. I started with creating a table DIMTIME :

CREATE TABLE DimDate

(DateKey NUMBER(10) primary key, Datt date, FullDateTN CHAR(10), DayOfMonth VARCHAR2(2), DaySuffix VARCHAR2(4), DayName VARCHAR2(9),  DayOfWeekTN CHAR(1), DayOfWeekInMonth VARCHAR2(3), DayOfWeekInYear VARCHAR2(2), DayOfQuarter VARCHAR2(3), DayOfYear VARCHAR2(3), WeekOfMonth VARCHAR2(1), WeekOfQuarter VARCHAR2(2), WeekOfYear VARCHAR2(2), Month VARCHAR2(2), MonthName VARCHAR2(9), MonthOfQuarter VARCHAR2(2), Quarter CHAR(1), QuarterName VARCHAR2(9), Year CHAR(4), YearName CHAR(7), MonthYear CHAR(10), MMYYYY CHAR(6), FirstDayOfMonth DATE, LastDayOfMonth DATE,

FirstDayOfQuarter DATE,

LastDayOfQuarter DATE,

FirstDayOfYear DATE,

LastDayOfYear DATE,

IsHolidayTN NUMBER(1),

IsWeekday NUMBER(1),

HolidayTN VARCHAR2(50));

Now I have to declare a new local variable and table to generate my table

My boss said that he wants the years in the calendar generated automatically, I found this script in t-sql (to populate my time dimension) I already converted it to plsql but when I execute it on toad it didn't stop showing errors messages:

--Specify Start Date and End date here

--Value of Start Date Must be Less than Your End Date

v_StartDate TIMESTAMP(3) := '01/01/2013' --Starting value of Date Range

v_EndDate TIMESTAMP(3) := '01/01/2015' --End Value of Date Range

--Temporary Variables To Hold the Values During Processing of Each Date of Year

v_DayOfWeekInMonth NUMBER(10);

v_DayOfWeekInYear NUMBER(10);

v_DayOfQuarter NUMBER(10);

v_WeekOfMonth NUMBER(10);

v_CurrentYear NUMBER(10);

v_CurrentMonth NUMBER(10);

v_CurrentQuarter NUMBER(10)

/*Table Data type to store the day of week count for the month and year*/

DECLARE @DayOfWeek TABLE (DOW NUMBER(10), MonthCount NUMBER(10), QuarterCount NUMBER(10), YearCount NUMBER(10))

INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)

INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)

INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)

INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)

INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)

INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)

INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)

--Extract and assign various parts of Values from Current Date to Variable

v_CurrentDate TIMESTAMP(3) := v_StartDate

v_CurrentMonth := TO_NUMBER(TO_CHAR(v_CurrentDate, 'MM'))

v_CurrentYear := TO_NUMBER(TO_CHAR(v_CurrentDate, 'YY'))

v_CurrentQuarter := TO_NUMBER(TO_CHAR(v_CurrentDate, 'QQ'))

/********************************************************************************************/

--Proceed only if Start Date(Current date ) is less than End date you specified above

WHILE v_CurrentDate < v_EndDate

LOOP

/*Begin day of week logic*/

        /*Check for Change in Month of the Current date if Month changed then

         Change variable value*/

IF v_CurrentMonth != TO_NUMBER(TO_CHAR(v_CurrentDate, 'MM'))

THEN

UPDATE @DayOfWeek

SET MonthCount = 0

v_CurrentMonth := TO_NUMBER(TO_CHAR(v_CurrentDate, 'MM'));

END IF;

       /* Check for Change in Quarter of the Current date if Quarter changed then change

        Variable value*/

IF v_CurrentQuarter != TO_NUMBER(TO_CHAR(v_CurrentDate, 'QQ'))

THEN

UPDATE @DayOfWeek

SET QuarterCount = 0

v_CurrentQuarter := TO_NUMBER(TO_CHAR(v_CurrentDate, 'QQ'));

END IF;

       /* Check for Change in Year of the Current date if Year changed then change

        Variable value*/

IF v_CurrentYear != TO_NUMBER(TO_CHAR(v_CurrentDate, 'YY'))

THEN

UPDATE @DayOfWeek

SET YearCount = 0

v_CurrentYear := TO_NUMBER(TO_CHAR(v_CurrentDate, 'YY'));

END IF;

       -- Set values in table data type created above from variables

UPDATE @DayOfWeek

SET

MonthCount = MonthCount + 1,

QuarterCount = QuarterCount + 1,

YearCount = YearCount + 1

WHERE DOW = TO_NUMBER(TO_CHAR(v_CurrentDate, 'DW'))

SELECT

MonthCount,

QuarterCount,

YearCount INTO v_DayOfWeekInMonth, v_DayOfQuarter, v_DayOfWeekInYear

FROM @DayOfWeek

WHERE DOW = TO_NUMBER(TO_CHAR(v_CurrentDate, 'DW'));

/*End day of week logic*/

/* Populate Your Dimension Table with values*/

INSERT INTO DimDate

SELECT

TO_CHAR (v_CurrentDate,112) as DateKey,

v_CurrentDate AS Date,

TO_CHAR (v_CurrentDate,103) as FullDateUK,

TO_CHAR (v_CurrentDate,101) as FullDateUSA,

TO_NUMBER(TO_CHAR(v_CurrentDate, 'DD')) AS DayOfMonth,

--Apply Suffix values like 1st, 2nd 3rd etc..

CASE

WHEN TO_NUMBER(TO_CHAR(v_CurrentDate, 'DD')) IN (11,12,13) _

THEN FROM dual TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, 'DD'))(1)) || 'th'

WHEN SUBSTR(TO_NUMBER(TO_CHAR(v_CurrentDate, 'DD')), GREATEST(-LENGTH(TO_NUMBER(TO_CHAR(v_CurrentDate, 'DD',v_CurrentDate))),-1)) = 1 _

THEN TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, 'DD'))(1)) || 'st'

WHEN SUBSTR(TO_NUMBER(TO_CHAR(v_CurrentDate, 'DD')), GREATEST(-LENGTH(TO_NUMBER(TO_CHAR(v_CurrentDate, 'DD',v_CurrentDate))),-1)) = 2 _

THEN TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, 'DD'))(1)) || 'nd'

WHEN SUBSTR(TO_NUMBER(TO_CHAR(v_CurrentDate, 'DD')), GREATEST(-LENGTH(TO_NUMBER(TO_CHAR(v_CurrentDate, 'DD',v_CurrentDate))),-1)) = 3 _

THEN TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, 'DD'))(1)) || 'rd'

ELSE TO_CHAR(TO_NUMBER(TO_CHAR(v_CurrentDate, 'DD'))(1)) || 'th'

END LOOP; AS DaySuffix,

TO_CHAR(v_CurrentDate, 'DW') AS DayName,

TO_NUMBER(TO_CHAR(v_CurrentDate, 'DW')) AS DayOfWeekUSA,

-- check for day of week as Per US and change it as per UK format

CASE TO_NUMBER(TO_CHAR(v_CurrentDate, 'DW'))

WHEN 1 THEN 7

WHEN 2 THEN 1

WHEN 3 THEN 2

WHEN 4 THEN 3

WHEN 5 THEN 4

WHEN 6 THEN 5

WHEN 7 THEN 6

END

AS DayOfWeekUK,

v_DayOfWeekInMonth AS DayOfWeekInMonth,

v_DayOfWeekInYear AS DayOfWeekInYear,

v_DayOfQuarter AS DayOfQuarter,

TO_NUMBER(TO_CHAR(v_CurrentDate, 'DY')) AS DayOfYear,

TO_NUMBER(TO_CHAR(v_CurrentDate, 'WW')) + 1 - TO_NUMBER(TO_CHAR(TO_CHAR, 'WW' (_

TO_NUMBER(TO_CHAR(v_CurrentDate, 'MM'))) + '/1/' || TO_CHAR (_

TO_NUMBER(TO_CHAR(v_CurrentDate, 'YY')))) AS WeekOfMonth,

(_ - DATEDIFF * INTERVAL '1' QQ(QQ, 0, v_CurrentDate) + 0 _

v_CurrentDate) / 7) + 1 AS WeekOfQuarter,

TO_NUMBER(TO_CHAR(v_CurrentDate, 'WW')) AS WeekOfYear,

TO_NUMBER(TO_CHAR(v_CurrentDate, 'MM')) AS Month,

TO_CHAR(v_CurrentDate, 'MM') AS MonthName,

CASE

WHEN TO_NUMBER(TO_CHAR(v_CurrentDate, 'MM')) IN (1, 4, 7, 10) THEN 1

WHEN TO_NUMBER(TO_CHAR(v_CurrentDate, 'MM')) IN (2, 5, 8, 11) THEN 2

WHEN TO_NUMBER(TO_CHAR(v_CurrentDate, 'MM')) IN (3, 6, 9, 12) THEN 3

END AS MonthOfQuarter,

TO_NUMBER(TO_CHAR(v_CurrentDate, 'QQ')) AS Quarter,

CASE TO_NUMBER(TO_CHAR(v_CurrentDate, 'QQ'))

WHEN 1 THEN 'First'

WHEN 2 THEN 'Second'

WHEN 3 THEN 'Third'

WHEN 4 THEN 'Fourth'

END AS QuarterName,

TO_NUMBER(TO_CHAR(v_CurrentDate, 'YEAR')) AS Year,

'CY ' || TO_CHAR (TO_NUMBER(TO_CHAR(v_CurrentDate, 'YEAR'))) AS YearName,

SUBSTR(TO_CHAR(v_CurrentDate, 'MM'), 1, 3) + '-' || TO_CHAR (_

TO_NUMBER(TO_CHAR(v_CurrentDate, 'YY'))) AS MonthYear,

SUBSTR('0' || TO_CHAR (TO_NUMBER(TO_CHAR(v_CurrentDate, 'MM'))), GREATEST(-LENGTH('0' || TO_CHAR(VARCHAR, (TO_NUMBER(TO_CHAR(v_CurrentDate, 'MM', v_CurrentDate)))),-2)) + _

TO_CHAR (TO_NUMBER(TO_CHAR(v_CurrentDate, 'YY'))) AS MMYYYY,

CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (TO_NUMBER(TO_CHAR(_, 'DD'

v_CurrentDate) - 1), v_CurrentDate))) AS FirstDayOfMonth,

CONVERT(DATETIME, CONVERT(DATE, -  * INTERVAL '1' DD(5)(TO_NUMBER(TO_CHAR(_, 'DD'

(INTERVAL '1' MM + v_CurrentDate)))) + INTERVAL '1' MM + _

v_CurrentDate)))) AS LastDayOfMonth,

DATEDIFF * INTERVAL '1' QQ(QQ, 0, v_CurrentDate) + 0 AS FirstDayOfQuarter,

DATEDIFF * INTERVAL '1' QQ(QQ, -1, v_CurrentDate) + -1 AS LastDayOfQuarter,

CONVERT(DATETIME, '01/01/' || TO_CHAR (TO_NUMBER(TO_CHAR(_, 'YY'

v_CurrentDate))) AS FirstDayOfYear,

CONVERT(DATETIME, '12/31/' || TO_CHAR (TO_NUMBER(TO_CHAR(_, 'YY'

v_CurrentDate))) AS LastDayOfYear,

NULL AS IsHolidayUSA,

CASE TO_NUMBER(TO_CHAR(v_CurrentDate, 'DW'))

WHEN 1 THEN 0

WHEN 2 THEN 1

WHEN 3 THEN 1

WHEN 4 THEN 1

WHEN 5 THEN 1

WHEN 6 THEN 1

WHEN 7 THEN 0

END AS IsWeekday,

NULL AS HolidayUSA, Null, Null

v_CurrentDate := INTERVAL '1' DD(5) + v_CurrentDate;

END

/********************************************************************************************/

Step 3.

Update Values of Holiday as per UK Government Declaration for National Holiday.

/*Update HOLIDAY fields of UK as per Govt. Declaration of National Holiday*/

-- Good Friday  April 18

UPDATE DimDate

SET HolidayUK = 'Good Friday'

WHERE Month = 4 AND DayOfMonth  = 18

-- Easter Monday  April 21

UPDATE DimDate

SET HolidayUK = 'Easter Monday'

WHERE Month = 4 AND DayOfMonth  = 21

-- Early May Bank Holiday   May 5

  UPDATE DimDate

SET HolidayUK = 'Early May Bank Holiday'

WHERE Month = 5 AND DayOfMonth  = 5

-- Spring Bank Holiday  May 26

UPDATE DimDate

SET HolidayUK = 'Spring Bank Holiday'

WHERE Month = 5 AND DayOfMonth  = 26

-- Summer Bank Holiday  August 25

   UPDATE DimDate

SET HolidayUK = 'Summer Bank Holiday'

WHERE Month = 8 AND DayOfMonth  = 25

-- Boxing Day  December 26  

   UPDATE DimDate

SET HolidayUK = 'Boxing Day'

WHERE Month = 12 AND DayOfMonth  = 26

--CHRISTMAS

UPDATE DimDate

SET HolidayUK = 'Christmas Day'

WHERE Month = 12 AND DayOfMonth  = 25

--New Years Day

UPDATE DimDate

SET HolidayUK  = 'New Year''s Day'

WHERE Month = 1 AND DayOfMonth = 1

--Update flag for UK Holidays 1= Holiday, 0=No Holiday

UPDATE DimDate

SET IsHolidayUK  = CASE WHEN HolidayUK   IS NULL _

THEN 0 WHEN HolidayUK   IS NOT NULL THEN 1 END

Step 4.

Update Values of Holiday as per USA Govt. Declaration for National Holiday.

/*Update HOLIDAY Field of USA In dimension*/

/*THANKSGIVING - Fourth THURSDAY in November*/

UPDATE DimDate

SET HolidayUSA = 'Thanksgiving Day'

WHERE

Month = 11

AND DayOfWeekUSA = 'Thursday'

AND DayOfWeekInMonth = 4

/*CHRISTMAS*/

UPDATE DimDate

SET HolidayUSA = 'Christmas Day'

WHERE Month = 12 AND DayOfMonth  = 25

/*4th of July*/

UPDATE DimDate

SET HolidayUSA = 'Independance Day'

WHERE Month = 7 AND DayOfMonth = 4

/*New Years Day*/

UPDATE DimDate

SET HolidayUSA = 'New Year''s Day'

WHERE Month = 1 AND DayOfMonth = 1

/*Memorial Day - Last Monday in May*/

UPDATE DimDate

SET HolidayUSA = 'Memorial Day'

FROM dbo.DimDate

WHERE DateKey IN

(

SELECT

MAX(DateKey)

FROM dbo.DimDate

WHERE

MonthName = 'May'

AND DayOfWeekUSA  = 'Monday'

GROUP BY

Year,

Month

)

/*Labor Day - First Monday in September*/

UPDATE DimDate

SET HolidayUSA = 'Labor Day'

FROM dbo.DimDate

WHERE DateKey IN

(

SELECT

MIN(DateKey)

FROM dbo.DimDate

WHERE

MonthName = 'September'

AND DayOfWeekUSA = 'Monday'

GROUP BY

Year,

Month

)

/*Valentine's Day*/

UPDATE DimDate

SET HolidayUSA = 'Valentine''s Day'

WHERE

Month = 2

AND DayOfMonth = 14

/*Saint Patrick's Day*/

UPDATE DimDate

SET HolidayUSA = 'Saint Patrick''s Day'

WHERE

Month = 3

AND DayOfMonth = 17

/*Martin Luthor King Day - Third Monday in January starting in 1983*/

UPDATE DimDate

SET HolidayUSA = 'Martin Luthor King Jr Day'

WHERE

Month = 1

AND DayOfWeekUSA  = 'Monday'

AND Year >= 1983

AND DayOfWeekInMonth = 3

/*President's Day - Third Monday in February*/

UPDATE DimDate

SET HolidayUSA = 'President''s Day'

WHERE

Month = 2

AND DayOfWeekUSA = 'Monday'

AND DayOfWeekInMonth = 3

/*Mother's Day - Second Sunday of May*/

UPDATE DimDate

SET HolidayUSA = 'Mother''s Day'

WHERE

Month = 5

AND DayOfWeekUSA = 'Sunday'

AND DayOfWeekInMonth = 2

/*Father's Day - Third Sunday of June*/

UPDATE DimDate

SET HolidayUSA = 'Father''s Day'

WHERE

Month = 6

AND DayOfWeekUSA = 'Sunday'

AND DayOfWeekInMonth = 3

/*Halloween 10/31*/

UPDATE DimDate

SET HolidayUSA = 'Halloween'

WHERE

Month = 10

AND DayOfMonth = 31

/*Election Day - The first Tuesday after the first Monday in November*/

BEGIN

DECLARE @Holidays TABLE (ID NUMBER(10) , _

DateID int, Week TINYINT, YEAR CHR(4), DAY CHR(2))

INSERT INTO @Holidays(DateID, Year,Day)

SELECT

DateKey,

Year,

DayOfMonth

FROM DimDate

WHERE

Month = 11

AND DayOfWeekUSA = 'Monday'

ORDER BY

YEAR,

DayOfMonth

v_CNTR NUMBER(10); v_POS NUMBER(10); v_STARTYEAR NUMBER(10); v_ENDYEAR NUMBER(10); v_MINDAY NUMBER(10);

SELECT

MIN(Year)

, MIN(Year)

, MAX(Year) INTO v_CurrentYear, v_STARTYEAR, v_ENDYEAR

FROM @Holidays

WHILE; v_CurrentYear <= v_ENDYEAR

BEGIN

SELECT COUNT([Year]) INTO v_CNTR

FROM @Holidays

WHERE Year = v_CurrentYear;

v_POS := 1;

WHILE v_POS <= v_CNTR

LOOP

SELECT MIN(DAY) INTO v_MINDAY

FROM @Holidays

WHERE

Year = v_CurrentYear

AND Week IS NULL;

UPDATE @Holidays

SET Week = v_POS

WHERE

Year = v_CurrentYear

AND Day = v_MINDAY

v_POS := v_POS + 1 FROM dual;

END LOOP;

v_CurrentYear := v_CurrentYear + 1 FROM dual;

END

UPDATE DimDate

SET HolidayUSA  = 'Election Day'

FROM dbo.DimDate DT

JOIN @Holidays HL ON (HL.DateID + 1) = DT.DateKey

WHERE

Week = 1

END

--set flag for USA holidays in Dimension

UPDATE DimDate

SET IsHolidayUSA = CASE WHEN HolidayUSA  IS NULL THEN 0 WHEN HolidayUSA  IS NOT NULL THEN 1 END

/*****************************************************************************************

Does anyone know how to :

1. create and run a script file on toad

2. The syntax of creating a new local and global variables and tables

Thanks

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Code Tester for Oracle Forum notifications altogether.
Code Tester for Oracle - Discussion Forum

Flag this post as spam/abuse.




--
Gene L. Bradley Jr.
Systems Analyst
Office of Information Technology
Jackson State University
1400 J R Lynch Street
P.O. Box 17750
Jackson, MS 39217
 
ph 601.979.1042
fax 601.371.9146

In God we trust; all others bring data.  ~W.E. Deming



CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.  If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

 
1910 0 / Create an account to join the discussion.
j.vaessen
Follow / 15 Feb 2017 at 12:43pm / Code Tester for Oracle
Latest post by on 17 Apr 2017 at 7:57pm

Code Tester Freeware license expired

Expand content
Hi, I have downloaded recently Code Tester Freeware v2.7. After installation it appears I can't use the program, because I get the message that the edition is expired since 19-July-2016. Does anyone...

Hi, I have downloaded recently Code Tester Freeware v2.7. After installation it appears I can't use the program, because I get the message that the edition is expired since 19-July-2016. Does anyone know a solution? Thanks in advance.

3765 1 / Create an account to join the discussion.
Rajthilak
Follow / 23 Mar 2017 at 7:56am / Code Tester for Oracle
Latest post by on 23 Mar 2017 at 9:58am

Best way to integrate Sonar to Toad for Oracle Professional 12.0.0...

Expand content
Hi All, Trying to identify bestway to integrate sonar tool into toad to analyse existing code and packages. There are standard quality profile setup in Sonar website across our organisation for each...

Hi All,

Trying to identify bestway to integrate sonar tool into toad to analyse existing code and packages.

There are standard quality profile setup in Sonar website across our organisation for each technology.

Finding way to use sonar tool for existing code.

Toad version: Toad for Oracle Professional 12.0.0.61

Oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Thanks

Unanswered 3759 2 / Create an account to join the discussion.
Yashar
Follow / 9 Mar 2017 at 12:11pm / Code Tester for Oracle
Latest post by on 14 Mar 2017 at 7:40am

overloaded subprograms not listed

Expand content
Overloaded subprograms are not listed. seems like Overload feature is not supported. https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#i12352 do you have the same problem?

Overloaded subprograms are not listed.

seems like Overload feature is not supported.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#i12352 

do you have the same problem?

Unanswered 3542 1 / Create an account to join the discussion.