Home » SQL & PL/SQL » SQL & PL/SQL » Skipping code in a procedure? (SQL Developer 1.5.5)
Skipping code in a procedure? [message #658479] Thu, 15 December 2016 09:31 Go to next message
MyLastGamble
Messages: 5
Registered: December 2016
Junior Member
So, I'm not sure how easy this question is to answer but I will post it anyway.

We have a procedure that runs during our monthly process. The whole process itself takes about 8 hours to complete, this specific procedure takes a good hour to run. What this procedure does is load sales data into a production table for various lines of businesses. There are approximately 30 insert statements that run during this procedure.

Here is the problem we're encountering. This procedure has not been changed since may of this year and its ran fine all year since it was updated. However the past 2 months when its ran its loaded 0 in sales for 1 line of business, everything else loads fine. When we take that LOB's query out of the procedure and run it stand alone, it runs fine and loads sales information. It just when it runs during the monthly process that it pulls in 0's. Here's the query, with object names changed for anonymity:

INSERT
  INTO production_data
    (
      AGENT_KEY,
      OFFICE_KEY,
      LINE_OF_BUSINESS,
      TIME,
      Month_To_Date,
      M_GOAL,
      Year_To_Date,
      Y_GOAL,
      Counts
    )
  SELECT b.agent_key,
    NULL,
    Line_Of_Bus_Variable,
    b.time,
    (SELECT NVL(SUM(NVL(total_net_new,0) + NVL(total_net_renewed,0)),0)
    FROM SCA.stg_monthly_sales p
    JOIN SCA.stg_option_codes o
    ON o.option_id    = p.period__id
    WHERE o.option_cd = 'MTH'
    AND p.office_cd      = b.office_cd
    AND p.period      = b.period
    AND p.year        = b.year
    ) AS Month_To_Date,
    0 AS m_goal,
    (SELECT NVL(SUM(NVL(total_net_new,0) + NVL(total_net_renewed,0)),0)
    FROM SCA.stg_monthly_sales p
    JOIN SCA.stg_option_codes o
    ON o.option_id    = p.period__id
    WHERE o.option_cd = 'MTH'
    AND p.office_cd      = b.office_cd
    AND p.period      <= b.period
    AND p.year        = b.year
    )    AS  Year_To_Date,
    0    AS y_goal,
    NULL AS Counts
  FROM SCA.v_Agent_Hier b
  WHERE b.period_descr = 'MTH'
  AND b.time                  = VAR_TIME;

A sample record output after the procedure runs would look like this:

AGENT_KEY  OFFICE_KEY  LINE_OF_BUSINESS     TIME        Month_To_Date  M_GOAL    Year_To_Date Y_GOAL   Count
14698	   NULL	       64          	    12/1/2016	 0	        0  	  0	       0	NULL


As you can see, for all agent keys it pulls 0's. However, if I were to run the insert statement against the database right after or during the monthly process WITHOUT ALTERING ANYTHING, it actually pulls sales data:

AGENT_KEY  OFFICE_KEY  LINE_OF_BUSINESS     TIME        Month_To_Date  M_GOAL    Year_To_Date Y_GOAL   Count
14698	   NULL	       64          	    12/1/2016	 138104.36	 0  	  1423156.21   0	NULL


Any idea of what could cause a subquery to just randomly not pull any information during a procedure run? The table that the subquery pulls from is populated hours before this process starts, this process and all processes before are dependent on the subquery's table population finishing. Its odd that it happened last month but the query ran fine outside of the procedure with no alterings. The whole process ran fine in test and then it loaded 0 sales again this month, with the insert statement running fine again after the procedure was done, again with no alterings to any data sources. I am at a loss as all my tests show that it should be running fine. Any help would be appreciated!
Re: Skipping code in a procedure? [message #658484 is a reply to message #658479] Thu, 15 December 2016 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Is there any WHEN OTHERS somewhere
2/ Check for any implicit conversions
3/ If both above are false and a query returns random results for the same data that's an Oracle bug.

Re: Skipping code in a procedure? [message #658488 is a reply to message #658484] Thu, 15 December 2016 10:34 Go to previous messageGo to next message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
An oracle error ought to mean no rows inserted, not wrong values. If the insert/select had a function call and that function had when others then 1 might make sense.
2 seems more likely.
So check the datatypes of the columns being compared in the where clause of the sub-queries. Make sure they're the same. If they're not then the oracle will have to do implicit conversion which may rely on session settings that are different between the environment where it works and the one where it doesn't.
If that is the case, add the appropriate function calls to do explicit conversions (to_number, to_date for example) with the appropriate format mask.
Re: Skipping code in a procedure? [message #658490 is a reply to message #658488] Thu, 15 December 2016 10:44 Go to previous messageGo to next message
MyLastGamble
Messages: 5
Registered: December 2016
Junior Member
cookiemonster wrote on Thu, 15 December 2016 10:34

So check the datatypes of the columns being compared in the where clause of the sub-queries. Make sure they're the same. If they're not then the oracle will have to do implicit conversion which may rely on session settings that are different between the environment where it works and the one where it doesn't.
This makes sense. I checked the data types and the column the subquery pulls from is a number(15,2) data type. The destination table is number(22,2). I'm not sure a conversion is needed here, but I could definitely be wrong.
Re: Skipping code in a procedure? [message #658492 is a reply to message #658490] Thu, 15 December 2016 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If there are any conversion the execution plan will show it.
So execute the query then:
select * from table(dbms_xplan.DISPLAY_CURSOR(null,null,'TYPICAL +PREDICATE'));
Re: Skipping code in a procedure? [message #658497 is a reply to message #658479] Thu, 15 December 2016 13:11 Go to previous messageGo to next message
joy_division
Messages: 4817
Registered: February 2005
Location: East Coast USA
Senior Member
That VAR_TIME is a little suspicious to me.
Looks like your "TIME" column is a date. Are they TRUNCed in the table?
How does the procedure set VAR_TIME vs. how are you setting VAR_TIME when you run it stand-alone?
Re: Skipping code in a procedure? [message #658498 is a reply to message #658497] Thu, 15 December 2016 13:27 Go to previous messageGo to next message
MyLastGamble
Messages: 5
Registered: December 2016
Junior Member
joy_division wrote on Thu, 15 December 2016 13:11
That VAR_TIME is a little suspicious to me.
Looks like your "TIME" column is a date. Are they TRUNCed in the table?
How does the procedure set VAR_TIME vs. how are you setting VAR_TIME when you run it stand-alone?
The procedure sets this at the beginning based on a few factors. This variable is used for every query in this procedure, not just this query.
Re: Skipping code in a procedure? [message #658499 is a reply to message #658498] Thu, 15 December 2016 14:30 Go to previous messageGo to next message
joy_division
Messages: 4817
Registered: February 2005
Location: East Coast USA
Senior Member
So, what is it set to in the procedure and what are you setting it to when you run it manually?
Re: Skipping code in a procedure? [message #658500 is a reply to message #658499] Thu, 15 December 2016 14:48 Go to previous messageGo to next message
MyLastGamble
Messages: 5
Registered: December 2016
Junior Member
joy_division wrote on Thu, 15 December 2016 14:30
So, what is it set to in the procedure and what are you setting it to when you run it manually?
The same. The procedure sets it as 3149 (the time_id code for 11/31/2016) and I use the same when I run it manually. That definitely isn't the issue, I always use procedure code to populate variables when I'm running the procedure code manually.
Re: Skipping code in a procedure? [message #658501 is a reply to message #658500] Thu, 15 December 2016 15:05 Go to previous messageGo to next message
joy_division
Messages: 4817
Registered: February 2005
Location: East Coast USA
Senior Member
I'm lost here. First, 11/31/2016 is not a valid date. 3149 is a number, yet you are displaying a date. My head hurts.
Re: Skipping code in a procedure? [message #658502 is a reply to message #658501] Thu, 15 December 2016 16:07 Go to previous messageGo to next message
MyLastGamble
Messages: 5
Registered: December 2016
Junior Member
joy_division wrote on Thu, 15 December 2016 15:05
I'm lost here. First, 11/31/2016 is not a valid date. 3149 is a number, yet you are displaying a date. My head hurts.
Sorry. 11/31 was a mistype, should be 11/30. I put a date in that field to make it easier to read the output but we actually use number codes, like 3149 or 4433, to reference dates as 11/30/2016 could be either a day, a month end, or a week end, and 3149 lets me know its 11/30/2016, end of the month.
Re: Skipping code in a procedure? [message #658514 is a reply to message #658502] Fri, 16 December 2016 03:12 Go to previous message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
Considering the likely nature of your problem substituting data for a different type of data isn't helpful.

It'd help if you gave us table definitions.
Previous Topic: Hierarchy Query Help
Next Topic: Extract directory path from a datafile name
Goto Forum:
  


Current Time: Sat Jan 20 14:20:38 CST 2018

Total time taken to generate the page: 0.01427 seconds