Code Tester for Oracle Community

Code Tester for Oracle Forum

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.

1200 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 1363 3 / Create an account to join the discussion.
sjayanna
Follow / 13 Oct 2017 at 2:32pm / Code Tester for Oracle

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 765 0 / 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

1416 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 737 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

7707 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

2303 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

1232 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.

653 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.

2641 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).

2458 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

1388 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.

 
883 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.

1786 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 1679 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 1570 1 / Create an account to join the discussion.
soden
Follow / 14 Feb 2017 at 1:29pm / Code Tester for Oracle
Latest post by on 14 Mar 2017 at 7:34am

Customizing Test Definition in Code Tester fails

Expand content
We removed an Argument from the list of parameters for a function and tried to update the Test Definition. In order to remove the argument I: - Open up the Test Editor for the specific Program being...

We removed an Argument from the list of parameters for a function and tried to update the Test Definition. In order to remove the argument I:

- Open up the Test Editor for the specific Program being tested
- Expand list of Test Elements
- Right-click on the removed test element
- Select Remove test element. 

I immediately get the "Unexpected Error Occurred" dialog with the following Message:

Internal error. Test element {...long GUID...} not found in cache collection.

Then whether I cancel or continue to create the support bundle, the dialog repeatedly comes up. I have to End Task on Code Tester to continue working. Unfortunately we are developing on a separate network so I cannot send the support bundle. We are using version 2.0.0.582 of Code Tester.

Anyone know of a solution or workaround?

Thanks,
Mitch

Unanswered 1644 1 / Create an account to join the discussion.
Damir Vadas
Follow / 18 Jan 2017 at 11:32am / Code Tester for Oracle

table rebuild with Mat View

Expand content
Hi, When you choose for some table "Rebuild" option, Toad creates script. Unfortunately it does not works if a table has materialized view. Is there any way to fix that in next releases ...drop...

Hi,
When you choose for some table "Rebuild" option, Toad creates script.
Unfortunately it does not works if a table has materialized view.
Is there any way to fix that in next releases ...drop and create mat view script part for that table.
Brg
Damir

1079 0 / Create an account to join the discussion.
karthik.rajashekaran@gmail.com
Follow / 29 Dec 2016 at 12:45pm / Code Tester for Oracle
Latest post by on 29 Dec 2016 at 1:35pm

Case Statement in Where clause

Expand content
I am having an issue in the where clause case statement of this select. please help me out it says 00907. 00000 - "missing right parenthesis" and e.entype = 'PRT' and (case ...

I am having an issue in the where clause case statement of this select. please help me out 

it says 00907. 00000 -  "missing right parenthesis"

                   and e.entype      = 'PRT'

                   and

                      (case

                           when (t.trans_code not in ('ABDC'))

                      then

                        (

                         (smd.user_group_desc3 = '12' and sm.process_sec_type <> 'OPOPSW' and nvl(sw.pay_receive_ind, '0') = 'C') or

                         (smd.user_group_desc3 = '12' and sm.process_sec_type = 'OPOPSW') or

                         (smd.user_group_desc3 not in ('3', '25', '12')) or

                         ((smd.user_group_desc3 = '3' and (trim(sm.ticker) = td.transaction_notes) or (t.user_char3 = 'FXO_EXERCISE' and sm.ticker <> 'USD') and e.user_field3 = 'CASH')

                           or

                          (smd.user_group_desc3 = '3' and trim(sm.ticker) = td.transaction_notes and e.user_field3 = 'FUT'

                             and exists

                              (select distinct ec.user_float10

                                   from pace_masterdbo.codes       c,

                                        pace_masterdbo.code_values cv,

                                        rulesdbo.entity_extension  ec

                                  where c.short_desc = 'ILFUTURESFXF'

                                    and c.instance = cv.code_inst

                                    and ec.user_float10 = rtrim(cv.short_desc)

                                    and trim(ec.entity_id) = trim(ee.user_char1)))

                           or

                          (smd.user_group_desc3 = '25' and t.trade_currency <> 'USD')

                         )

                       )

                    end)=1

                   and e.user_float5 is not null

1906 2 / Create an account to join the discussion.
christophe.wirtz.external
Follow / 16 Dec 2016 at 7:50am / Code Tester for Oracle
Latest post by on 16 Dec 2016 at 7:59am

Editor : right click for collapse all (+)

Expand content
Hello, there a was an useful feature, collapse all folds, in TOAD for Oracle editor (v12.6 ?), and I cannot see it anymore, in beta releases too. Is this feature planned yet, in a future version...
Hello,
there a was an useful feature, collapse all folds, in TOAD for Oracle editor  (v12.6 ?), and
I cannot see it anymore, in beta releases too.

Is this feature planned yet, in a future version  ?

Thank you.

Best regards,

Christophe WIRTZ
Oracle Developper


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