| Global Temporary Tables [message #655822] |
Tue, 13 September 2016 16:19  |
 |
derrellgore
Messages: 20
Registered: July 2016
|
Junior Member |
|
|
So I just wrote by first script using Global Temporary Table. Got it to work but I have a couple of questions.
I will be getting a new system which uses Oracle. I want to be able to use Visual Studio to write programs with data from the Oracle database. Pretty sure I will only have READ-ONLY access permissions on the database so might be an issue if I had to create GLOBAL TEMPORARY TABLES. As you can see from the C# code at the end I am saving my scripts in an SQL database which works great. I think I can get by with everything except possibly GTT's once I get the new system.
1. What would you do in the place of Global Temporary Table if you did not have CREATE TABLE PERMISSION on database? Using Global Temporary Tables is the only thing I can see hindering me to use Visual Studio to write programs using the data. It is my understanding you cannot use GTT unless you create the tables first. I created a GTT called HWL_Variance_Temp.
2. What exactly ends a session? I found that if I ran this from my program using different parameters it kept the data from the first time. I am using ON COMMIT PRESERVE ROWS. I couldn't get TRUNCATE to work with GTT so as you can see in my last line I just DELETED everything to clear the table. This works but is this the best way to do it?
ORACLE SCRIPT
DECLARE
v_YearTemp NUMBER;
v_MonthTemp NUMBER;
v_YearMonthTemp VARCHAR2(50);
v_Count NUMBER;
CURSOR Usage_Cursor IS
SELECT TotUsage, LastMonth, TwoMonthAgo, ThreeMonthAgo, Name, Year, Month
FROM HWL_Variance
Where AcctNo = :p_AcctNo AND Route = :p_Route AND HasAuxMeter = :p_HasAuxMeter AND Srv = :p_Srv
Order By YearMonth;
Usage_Row HWL_Variance%ROWTYPE;
BEGIN
v_YearTemp := 0;
v_MonthTemp := 0;
FOR Usage_Row IN Usage_Cursor LOOP
IF v_YearTemp = 0 THEN
v_YearTemp := Usage_Row.Year;
v_MonthTemp := Usage_Row.Month;
IF LENGTH(CAST(v_MonthTemp AS VARCHAR2)) = 1 THEN
v_YearMonthTemp := CAST(v_YearTemp as VARCHAR2) || '/0' || CAST(v_MonthTemp as VARCHAR2);
ELSE
v_YearMonthTemp := CAST(v_YearTemp as VARCHAR2) || '/' || CAST(v_MonthTemp as VARCHAR2);
END IF;
END IF;
SELECT COALESCE(COUNT(*),0) INTO v_Count FROM HWL_Variance_Temp WHERE YearMonth = v_YearMonthTemp;
IF(v_Count = 0) THEN
INSERT INTO HWL_VARIANCE_TEMP(YearMonth, TotUsage, Name)
VALUES(v_YearMonthTemp, Usage_Row.TotUsage, 'TOTAL USAGE - ' || Usage_Row.Name);
END IF;
v_MonthTemp := v_MonthTemp -1;
IF v_MonthTemp = 0 THEN
v_MonthTemp := 12;
v_YearTemp := v_YearTemp - 1;
END IF;
IF LENGTH(CAST(v_MonthTemp AS VARCHAR2)) = 1 THEN
v_YearMonthTemp := CAST(v_YearTemp as VARCHAR2) || '/0' || CAST(v_MonthTemp as VARCHAR2);
ELSE
v_YearMonthTemp := CAST(v_YearTemp as VARCHAR2) || '/' || CAST(v_MonthTemp as VARCHAR2);
END IF;
SELECT COALESCE(COUNT(*),0) INTO v_Count FROM HWL_Variance_Temp WHERE YearMonth = v_YearMonthTemp;
IF(v_Count = 0) THEN
INSERT INTO HWL_VARIANCE_TEMP(YearMonth, TotUsage, Name)
VALUES(v_YearMonthTemp, Usage_Row.LastMonth, 'TOTAL USAGE - ' || Usage_Row.Name);
END IF;
v_MonthTemp := v_MonthTemp -1;
IF v_MonthTemp = 0 THEN
v_MonthTemp := 12;
v_YearTemp := v_YearTemp - 1;
END IF;
IF LENGTH(CAST(v_MonthTemp AS VARCHAR2)) = 1 THEN
v_YearMonthTemp := CAST(v_YearTemp as VARCHAR2) || '/0' || CAST(v_MonthTemp as VARCHAR2);
ELSE
v_YearMonthTemp := CAST(v_YearTemp as VARCHAR2) || '/' || CAST(v_MonthTemp as VARCHAR2);
END IF;
SELECT COALESCE(COUNT(*),0) INTO v_Count FROM HWL_Variance_Temp WHERE YearMonth = v_YearMonthTemp;
IF(v_Count = 0) THEN
INSERT INTO HWL_VARIANCE_TEMP(YearMonth, TotUsage, Name)
VALUES(v_YearMonthTemp, Usage_Row.TwoMonthAgo, 'TOTAL USAGE - ' || Usage_Row.Name);
END IF;
v_MonthTemp := v_MonthTemp -1;
IF v_MonthTemp = 0 THEN
v_MonthTemp := 12;
v_YearTemp := v_YearTemp - 1;
END IF;
IF LENGTH(CAST(v_MonthTemp AS VARCHAR2)) = 1 THEN
v_YearMonthTemp := CAST(v_YearTemp as VARCHAR2) || '/0' || CAST(v_MonthTemp as VARCHAR2);
ELSE
v_YearMonthTemp := CAST(v_YearTemp as VARCHAR2) || '/' || CAST(v_MonthTemp as VARCHAR2);
END IF;
SELECT COALESCE(COUNT(*),0) INTO v_Count FROM HWL_Variance_Temp WHERE YearMonth = v_YearMonthTemp;
IF(v_Count = 0) THEN
INSERT INTO HWL_VARIANCE_TEMP(YearMonth, TotUsage, Name)
VALUES(v_YearMonthTemp, Usage_Row.ThreeMonthAgo, 'TOTAL USAGE - ' || Usage_Row.Name);
END IF;
v_YearTemp := 0;
END Loop;
OPEN :p_RefCursor FOR
SELECT YearMonth, TotUsage, Name
FROM HWL_Variance_Temp
Order By YearMonth;
DELETE FROM HWL_Variance_Temp;
END;
C# PROCEDURE
public static List<HWL_Variance> GetUsageChart(string acctNo, int route, bool hasAuxMeter, string srv)
{
List<HWL_Variance> usageList = new List<HWL_Variance>();
string script = "SPVARIANCE_GETUSAGECHART";
OracleConnection connection = HWLOracleDB.GetConnection();
string sql = HWL_OracleScriptDB.GetScriptByName(script);
OracleCommand selectCommand = new OracleCommand(sql, connection);
selectCommand.BindByName = true;
selectCommand.CommandType = CommandType.Text;
selectCommand.Parameters.Add(":p_AcctNo", OracleDbType.Varchar2).Value = acctNo;
selectCommand.Parameters.Add(":p_Route", OracleDbType.Int32).Value = route;
selectCommand.Parameters.Add(":p_HasAuxMeter", OracleDbType.Int32).Value = Convert.ToString(Convert.ToInt32(hasAuxMeter));
selectCommand.Parameters.Add(":p_Srv", OracleDbType.Varchar2).Value = srv;
selectCommand.Parameters.Add(":p_RefCursor", OracleDbType.RefCursor);
selectCommand.Parameters[":p_RefCursor"].Direction = ParameterDirection.Output;
try
{
connection.Open();
OracleDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
HWL_Variance usage = new HWL_Variance();
usage.YearMonth = reader["YearMonth"].ToString();
usage.TotUsage = Convert.ToInt32(reader["TotUsage"]);
usage.Name = reader["Name"].ToString();
usageList.Add(usage);
}
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
connection.Close();
}
return usageList;
}
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Global Temporary Tables [message #655832 is a reply to message #655822] |
Wed, 14 September 2016 01:59  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
You could ask your DBA to grant you the CREATE TABLE privilege, but without quota on any tablespace. That means that you will be able to create and use global temporary tables but will be unable to take up any permanent storage.
|
|
|
|
|
|
|
|
| Re: Global Temporary Tables [message #655860 is a reply to message #655851] |
Wed, 14 September 2016 14:49 |
|
derrellgore
Messages: 20
Registered: July 2016
|
Junior Member |
|
|
So I rewrote the code...it is now much smaller and no Temp Table. I wrote a function to handle the YearMonth. I wanted to create function...run the code...and drop the function...but was unable to get that to work. Tried using Execute Immediate and that would NOT work. I could run the CREATE FUNCTION in a script by itself but not with the rest of the code following it. Is there a way to do that?
BEGIN
OPEN :p_RefCursor FOR
SELECT distinct YearMonth, TotUsage, Name
FROM
(SELECT GetYearMonth(Year, Month) as YearMonth, TotUsage, Name FROM HWL_Variance Where AcctNo = :p_AcctNo AND Route = :p_Route AND HasAuxMeter = :p_HasAuxMeter AND Srv = :p_Srv
UNION
SELECT GetYearMonth(Year, Month - 1) as YearMonth, LastMonth AS TotUsage, Name FROM HWL_Variance Where AcctNo = :p_AcctNo AND Route = :p_Route AND HasAuxMeter = :p_HasAuxMeter AND Srv = :p_Srv
UNION
SELECT GetYearMonth(Year, Month - 2) as YearMonth, TwoMonthAgo AS TotUsage, Name FROM HWL_Variance Where AcctNo = :p_AcctNo AND Route = :p_Route AND HasAuxMeter = :p_HasAuxMeter AND Srv = :p_Srv
UNION
SELECT GetYearMonth(Year, Month - 3) as YearMonth, ThreeMonthAgo AS TotUsage, Name FROM HWL_Variance Where AcctNo = :p_AcctNo AND Route = :p_Route AND HasAuxMeter = :p_HasAuxMeter AND Srv = :p_Srv)
ORDER BY YearMonth;
END;
FUNCTION
create or replace FUNCTION GetYearMonth
(
p_Year NUMBER,
p_Month NUMBER
)
RETURN VARCHAR2
AS
v_YearMonth VARCHAR2(50);
v_YearTemp NUMBER;
v_MonthTemp NUMBER;
BEGIN
v_YearTemp := p_Year;
v_MonthTemp := p_Month;
IF v_MonthTemp = 0 THEN
v_MonthTemp := 12;
v_YearTemp := p_Year - 1;
END IF;
IF v_MonthTemp = -1 THEN
v_MonthTemp := 11;
v_YearTemp := p_Year - 1;
END IF;
IF v_MonthTemp = -2 THEN
v_MonthTemp := 10;
v_YearTemp := p_Year - 1;
END IF;
IF LENGTH(CAST(v_MonthTemp AS VARCHAR2)) = 1 THEN
v_YearMonth := CAST(v_YearTemp AS VARCHAR2) || '/0' || CAST(v_MonthTemp as VARCHAR2);
ELSE
v_YearMonth := CAST(v_YearTemp AS VARCHAR2) || '/' || CAST(v_MonthTemp as VARCHAR2);
END IF;
RETURN v_YearMonth;
END;
[Updated on: Wed, 14 September 2016 14:53] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Global Temporary Tables [message #655881 is a reply to message #655864] |
Thu, 15 September 2016 03:19 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You can do what you're doing with existing oracle functions, no need for a custom one:
(case when month <= 0 then year else year -1 end)
||'/'||
lpad(case when month = 0 then 12
when month = -1 then 11
when month = -2 then 10
else month
end
, 2, '0')
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
