I am creating a temporary table with one column and then inserting records into the temp table using a subquery.
I am using ADO 2.7, the problem is it creates the temp table but when i do a insert, it is not inserting records into the temp table.
If I do this from oracle GUI it works fine( i am using PL/SQL Developer from allround automations)
The syntax is
CREATE GLOBAL TEMPORARY TABLE SEC_FILTER_USERNAME(PROP_NO CHAR(10))
INSERT INTO SEC_FILTER_GRAJPUT SELECT PROP_NO FROM CT_MAIN WHERE INST_CODE='BIGBUCKS'
Any help is appreciated.
When you insert a record into a GTT, then the records are removed after the session ends. You may even delete the rows at the end of the transaction with "on commite delete rows;". In your case, probably ADO 2.7 creates a separate session and you are trying to view the records from another session.
GTT created without "on commit preserve rows" deletes all rows on commit.
If ADO generated auto commit, inserting records would not have any effect.
Try to find if ADO commits every single statement and turn this property off.