Which are the differences between DBMS_UTILITY.EXEC_DDL_STATEMENT and EXECUTE IMMEDIATE? Answer Fundamentally they do the same thing, which is to provide a mechanism to execute DDL statements in PL/SQL, which isn’t supported natively. If memory serves me well, the EXEC_DDL_STATEMENT was available in the Oracle 7 version of the DBMS_UTILITY package, whereas Native Dynamic SQL (EXECUTE IMMEDIATE) was only introduced in
Tag: oracle
What is the difference between CLOB and NCLOB?
Can you state any difference between the CLOB and NCLOB? Answer A CLOB stores character data encoded in the database character set. A NCLOB stores character data encoded in the national character set will show you the database and national character sets of your database.
Query to get all those names of employees,who have ‘A’ as their middle character in their name
E.g:- BRAKE,CRANE etc. In my employees table , I have ENAME,ENO,JOB,SALARY. Here, I want to extract out those enames that have an ‘A’ as the center character in their name. If length of ename is …
Update Query with select and row lock
I got this query as part of a previous answer to my question. Now how to lock the rows in the select query to make sure no other thread over writes my update. Answer It’s not clear to me that you have the problem you seem to think you have. Updating a row in the table inherently places a row-level
How to use “Partition By” or “Max”?
I’ve the following table (my_data): what is the best / smallest SQL statement to retrieve only the data related to the highest year and grouped by ‘X’ , like this: Note that this result table will be used in a join. Answer
What does the following Oracle error mean: invalid column index
I got the following error while testing some code: SQLException: Invalid column index What exactly does that mean? Is there an online document explaining what all the Oracle error codes and statements? Answer If that’s a SQLException thrown by Java, it’s most likely because you are trying to get or set a value from a ResultSet, but the index you
Inserting into Oracle and retrieving the generated sequence ID
I have a handful of raw SQL queries for SQL Server which use SCOPE_IDENTITY to retrieve the generated ID for a specific INSERT immediately after that INSERT occurs all in one execution… The question is: What’s the best way to do that for an Oracle database? Can this be done on Oracle through standard SQL or do I have to
How to insert a timestamp in Oracle?
I have an Oracle DB with a timestamp field in it. What is the correct SQL code to insert a timestamp into this field?
Case insensitive searching in Oracle
The default behaviour of LIKE and the other comparison operators, = etc is case-sensitive. Is it possible make them case-insensitive? Answer Since 10gR2, Oracle allows to fine-tune the behaviour of string comparisons by setting the NLS_COMP and NLS_SORT session parameters: You can also create case insensitive indexes: This information was taken from Oracle case insensitive searches. The article mentions REGEXP_LIKE
Update statement using with clause
I have a script that uses a stack of with clauses to come up with some result, and then I want to write that result in a table. I just can’t get my head around it, could someone point me in the right direction? Here’s a simplified example that indicates what i want to do: The real thing has quite