Home » Infrastructure » Windows » temporary table
temporary table [message #681846] Fri, 04 September 2020 00:13 Go to next message
kamuran.canakli
Messages: 7
Registered: September 2020
Junior Member
Hi,
This query working on pl/sql developer tools. But I cant work in c# with OracleManagedDataAccess control. What can I do ?

create global temporary table IBRLotlar on commit preserve rows
AS
SELECT h.part_no, h.lot_batch_no, h.location_no, r.warehouse_id, h.quantity, h.qty_reversed,
h.direction, h.date_created,
case when h.direction = '+' then h.quantity else 0 end Giren,
case when h.direction = '-' then h.quantity else 0 end Cikan
FROM INVENTORY_TRANSACTION_HIST H, WAREHOUSE_BAY_BIN r
WHERE h.location_no = r.location_no
AND h.contract = r.contract
AND INVENTORY_PART_API.Get_Type_Code_Db(H.contract, H.part_no) IN
('1', '2')
AND H.quantity - H.qty_reversed > 0
AND r.warehouse_id = ('&AMBAR')
and inventory_part_api.Get_Part_Product_Family(h.contract, h.part_no) LIKE
NVL('&URUN_AILESI', '%')
AND r.location_no LIKE NVL('&LOKASYON', '%')
AND H.date_created BETWEEN to_date('&BASLANGIC_TARIH', 'DD.MM.YYYY') AND NVL(to_date('&BITIS_TARIH', 'DD.MM.YYYY'), SYSDATE)
order by 1,2;
Re: temporary table [message #681847 is a reply to message #681846] Fri, 04 September 2020 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 67487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

What does "I cant work" mean? Any error message?

Re: temporary table [message #681848 is a reply to message #681846] Fri, 04 September 2020 01:26 Go to previous messageGo to next message
John Watson
Messages: 8402
Registered: January 2010
Location: Global Village
Senior Member
Is the problem (which you do need to describe) related to your use of & substitution variables?
Re: temporary table [message #681864 is a reply to message #681847] Fri, 04 September 2020 11:13 Go to previous messageGo to next message
kamuran.canakli
Messages: 7
Registered: September 2020
Junior Member
Hi again,

I apologize for not explaining the problem.
query works without problem on pl/sql developer tools.

query dont works in c# I get Ora-933

string qry = string.Format(@"
create global temporary table IFSAPP.IBRLotlar on commit preserve rows
AS
SELECT h.part_no, h.lot_batch_no, h.location_no, r.warehouse_id, h.quantity, h.qty_reversed,
h.direction, h.date_created,
case when h.direction = '+' then h.quantity else 0 end Giren,
case when h.direction = '-' then h.quantity else 0 end Cikan
FROM IFSAPP.INVENTORY_TRANSACTION_HIST H, IFSAPP.WAREHOUSE_BAY_BIN r
WHERE h.location_no = r.location_no
AND h.contract = r.contract
AND IFSAPP.INVENTORY_PART_API.Get_Type_Code_Db(H.contract, H.part_no) IN ('1', '2')
AND H.quantity - H.qty_reversed > 0
AND r.warehouse_id = ('H')
and IFSAPP.inventory_part_api.Get_Part_Product_Family(h.contract, h.part_no) LIKE NVL('HORTM', '%')
AND r.location_no LIKE NVL('', '%')
AND H.date_created BETWEEN to_date('01.08.2020', 'DD.MM.YYYY') AND NVL(to_date('31.08.2020', 'DD.MM.YYYY'), SYSDATE);

select * from IFSAPP.IBRLotlar;

");
DataTable dtTemp = new DataTable();
if (LIB.et.ERPConn.State

ConnectionState.Broken || LIB.et.ERPConn.State

ConnectionState.Closed) LIB.et.ERPConn.Open();
OracleCommand cmd = new OracleCommand(qry, LIB.et.ERPConn);
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 300;
cmd.Parameters.Add(new OracleParameter("ambar", ambar.ToString()));
cmd.Parameters.Add(new OracleParameter("lokasyon", lokasyon.ToString()));
cmd.Parameters.Add(new OracleParameter("urunailesi", urunailesi.ToString()));
cmd.Parameters.Add(new OracleParameter("bastar", Convert.ToDateTime(bastar)));
cmd.Parameters.Add(new OracleParameter("bittar", Convert.ToDateTime(bittar)));
dtTemp.Load(cmd.ExecuteReader());
cmd.Dispose();
LIB.et.ERPConn.Close();
return dtTemp;

Re: temporary table [message #681865 is a reply to message #681864] Fri, 04 September 2020 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle does not support several SQL commands at once.

Re: temporary table [message #681868 is a reply to message #681864] Fri, 04 September 2020 13:03 Go to previous messageGo to next message
EdStevens
Messages: 1269
Registered: September 2013
Senior Member
One more time. With emphasis. Please format your code and preserve that formattng by enclosing it in 'code' tags. Took me more time to write this sentence that it did you reformat your code for you.

CREATE global TEMPORARY TABLE ifsapp.ibrlotlar
ON COMMIT preserve ROWS AS
  SELECT h.part_no,
         h.lot_batch_no,
         h.location_no,
         r.warehouse_id,
         h.quantity,
         h.qty_reversed,
         h.direction,
         h.date_created,
         CASE
           WHEN h.direction = '+' THEN h.quantity
           ELSE 0
         END Giren,
         CASE
           WHEN h.direction = '-' THEN h.quantity
           ELSE 0
         END Cikan
  FROM   ifsapp.inventory_transaction_hist H,
         ifsapp.warehouse_bay_bin r
  WHERE  h.location_no = r.location_no
         AND h.contract = r.contract
         AND ifsapp.inventory_part_api.Get_type_code_db(H.contract, H.part_no)
             IN
             ( '1', '2' )
         AND H.quantity - H.qty_reversed > 0
         AND r.warehouse_id = ( 'H' )
         AND ifsapp.inventory_part_api.Get_part_product_family(h.contract,
             h.part_no)
             LIKE
             Nvl('HORTM', '%')
         AND r.location_no LIKE Nvl('', '%')
         AND H.date_created BETWEEN To_date('01.08.2020', 'DD.MM.YYYY') AND
                                    Nvl(To_date('31.08.2020', 'DD.MM.YYYY'),
                                        SYSDATE);

SELECT *
FROM   ifsapp.ibrlotlar;  
Now, my question is why are you trying to populate a GTT when you define it? Defining it should be a one-time operation, part of creating the application. And the very purpose of a GTT is so that each user session gets it own private data, which exists only for the duration of the session.

Re: temporary table [message #681871 is a reply to message #681868] Sat, 05 September 2020 00:28 Go to previous messageGo to next message
kamuran.canakli
Messages: 7
Registered: September 2020
Junior Member
Hi, GTT is very very faster and easy for complex query. This query is only small part .
Re: temporary table [message #681872 is a reply to message #681871] Sat, 05 September 2020 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is not correct.
Oracle, unlike MS SQL Server, does not need GTT even for very complex queries.

Re: temporary table [message #681874 is a reply to message #681872] Sat, 05 September 2020 00:40 Go to previous messageGo to next message
kamuran.canakli
Messages: 7
Registered: September 2020
Junior Member
Hi,

I am not an expert on this subject. It has been 3 months since I started Oracle. We use IFS. One of our reports was giving results in 61 minutes. I tried to rewrite it with GTT and now it has decreased to 5 minutes. Perhaps the first report technique was different.
Re: temporary table [message #681876 is a reply to message #681874] Sat, 05 September 2020 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And perhaps the query was not correctly written.

Re: temporary table [message #681877 is a reply to message #681876] Sat, 05 September 2020 01:45 Go to previous messageGo to next message
kamuran.canakli
Messages: 7
Registered: September 2020
Junior Member
exactly
Re: temporary table [message #681878 is a reply to message #681877] Sat, 05 September 2020 01:46 Go to previous messageGo to next message
kamuran.canakli
Messages: 7
Registered: September 2020
Junior Member

Is there a document containing correct query writing techniques? (Book or website)
Re: temporary table [message #681879 is a reply to message #681878] Sat, 05 September 2020 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 67487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I like Stephane Faroult's books and videos specifically written to develop and improve SQL applications.
You have Oracle documentation (not only about SQL): Database Performance Tuning Guide

You can also read:
- OraFAQ Forum Guide, Detailed OraFAQ Forum Guide section, Performance Tuning paragraph
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Tuning High-Volume SQL Wiki page



[Updated on: Sat, 05 September 2020 02:04]

Report message to a moderator

Re: temporary table [message #681883 is a reply to message #681874] Sat, 05 September 2020 08:33 Go to previous messageGo to next message
EdStevens
Messages: 1269
Registered: September 2013
Senior Member
kamuran.canakli wrote on Sat, 05 September 2020 00:40
Hi,

I am not an expert on this subject. It has been 3 months since I started Oracle. We use IFS. One of our reports was giving results in 61 minutes. I tried to rewrite it with GTT and now it has decreased to 5 minutes. Perhaps the first report technique was different.
Indeed it was.
I suspect that what you really need, instead of a GTT, is technique known as 'sub-query factoring'. Using your information, something like this:
with temp_data as
  (SELECT h.part_no,
         h.lot_batch_no,
         h.location_no,
         r.warehouse_id,
         h.quantity,
         h.qty_reversed,
         h.direction,
         h.date_created,
         CASE
           WHEN h.direction = '+' THEN h.quantity
           ELSE 0
         END Giren,
         CASE
           WHEN h.direction = '-' THEN h.quantity
           ELSE 0
         END Cikan
  FROM   ifsapp.inventory_transaction_hist H,
         ifsapp.warehouse_bay_bin r
  WHERE  h.location_no = r.location_no
         AND h.contract = r.contract
         AND ifsapp.inventory_part_api.Get_type_code_db(H.contract, H.part_no)
             IN
             ( '1', '2' )
         AND H.quantity - H.qty_reversed > 0
         AND r.warehouse_id = ( 'H' )
         AND ifsapp.inventory_part_api.Get_part_product_family(h.contract,
             h.part_no)
             LIKE
             Nvl('HORTM', '%')
         AND r.location_no LIKE Nvl('', '%')
         AND H.date_created BETWEEN To_date('01.08.2020', 'DD.MM.YYYY') AND
                                    Nvl(To_date('31.08.2020', 'DD.MM.YYYY'),
                                        SYSDATE
  )

SELECT *
FROM   temp_data
;
You can read more about the technique and its variations, with good examples here.

One problem with your approach is that once you create the GTT for one particular session, you'd need to drop it when you are finished with that session. And that negates the entire purpose and advantage of GTTs.

One thing you need to keep in mind is that every database product (Oracle, MS Sql Server, MySQL, etc) are architecturally very different. They are not just the same thing with different names. In addition to fundamental differences in design and architecture, they each have their own variations in implementation of SQL, and they each have their own unique procedural languages (PL/SQL for Oracle, T-sql for MSSQL, etc). The bottom line of this is that standard/best practices in one can very well be worst practices in others - and the implementation of "temporary" tables is a textbook example of that. I like to say that the similarities begin AND END with 'select * from emp_table'. Do not assume that what you learned in one product is good in another.
Re: temporary table [message #681895 is a reply to message #681883] Mon, 07 September 2020 04:45 Go to previous message
kamuran.canakli
Messages: 7
Registered: September 2020
Junior Member
Thanks for all
Previous Topic: High memory utilisation from a Oracle Process
Goto Forum:
  


Current Time: Fri Oct 30 07:37:57 CDT 2020