Hi, This is a big problem since LONG is a very painful data type. You cannot search in it, use it in CTAS (create table as), pass it to a plsql object as parameter etc. Oracle strongly suggest not to use a LONG column anymore but unfortunately there are many LONG columns in data dictionary
Development
Oracle 18c New feature Private Temporary Table
Hello, 18c introduced a new object called Private Temporary Table (I will call it PTT in this post). PTT is just another version of Global Temporary Table(GTT). The data is temporary and will be stored for a time. Data in temporary tables (both PTT and GTT) can be queried by the session which inserts the
Sequential ID column without gap!
Hello, A customer is asking to you generate order numbers one by one and without gap! What an unpleasant request isn’t it? if you are not familiar to databases then you might think that this request is logical but it is not. So your customer asked for this and you decided to develop your code
How to store Yes No questions on table and BITAND function
Hi everyone, you might see BITAND function is frequently used by Oracle on source codes. Did you wonder why? What does BITAND function do? as you can understand from the name, it does a bitwise AND operation on parameters. probably you know what a bitwise and but as a small explanation it is an AND
Commit Time – Part 2 (On Commit Trigger)
Hello again, Not long ago, I mentioned about how to get approximate commit time on a table: http://mustafakalayci.me/2019/05/25/commit-time-for-an-inserted-row-in-oracle/ While I was reading some blogs and jumping from one to another, I came to Saibabu Devabhaktuni’n blog post: http://sai-oracle.blogspot.com/2013/09/how-to-setup-oracle-on-commit-trigger.html and I was literally shocked because I never heard something about this. So I did my tests
Can not Drop Materialized View on 12c and above?
Hello Everyone, I couldn’t write for a long time because of a crazy workload. During this heavy hardworking, I faced with a problem on one of my customers. They have a on commit refresh Materialized View (MV) and started to complain about they couldn’t insert into main table of MV. When I checked, I saw
Insert multi line text data using sqlldr
Hello Everyone, I just answered an OTN question about inserting multi line data using sqlldr and simply wanted to share it here (https://community.oracle.com/thread/4278952) so if have a data like this in your text file: you need to do some extra work to accomplish this problem. In my example I have 4 rows but 2
Using Temp instead of Undo for GTTs
Hello everyone, with 12c we have a very nice new option; Temp Undo for GTTs (Global Temporary Tables). as you know temporary tables are used for storing temporary data. those tables data are stored in TEMP tablespace so they are not vital. Temporary tables can generates very little redo data so they are faster than
Commit Time for an Inserted Row in Oracle
Hello everyone, This week, one of my customer have experienced a problem. They provide some Web Service to their suppliers and one of those web services creates some rows and then returns the response as successful operation but just after that, supplier queries the newly created those rows but they get “no rows”! When analyze
How to Get Your SID? USERENV vs V$MYSTAT!
Hello everyone, This is an easy question. How to find your own session SID and when you ask Google for it, you will find many answers. While reading some documents, I realized that some Gurus are using V$MYSTAT to get current SID: I always prefer USERENV(‘SID’) context to get SID and curious about which