SQL
Oracle SQL
SQL = Structured Query Language All relational databases, such as Oracle and SQL Server, use their own version of Structured Query Language (SQL) to allow users to interact with the data in the database. This could be adding data, deleting, updating or just viewing it. In general the SQL used in Oracle and by other […]
Oracle Implicit Commit
The idea behind this test came from a script which was rolled into a production environment. The script had been tested but the incorrect data was only on the production database and we decided that we would leave the commit out of the script and see what the results were before committing it. Within the script […]
ORA-00997: illegal use of LONG datatype
I had a requirement to keep track of the LOBs which were in the DB, their partition names and high values. We wanted to keep this information in a small lookup table so I first attempted to run a Create Table As Select (CTAS) statement, to which I received the following error: ERROR at line […]
Create SQL from SQL
In this article I would like to demonstrate the power of generating SQL statements from a SQL another SQL statement. This kind of tool is invaluable for any Oracle DBA or Oracle developer. I say Oracle because the dynamic SQL statements which follow are all written for the Oracle database. However, the same principles apply […]
ORA-13761: invalid filter
The Problem You are trying to populate a SQL Tuning Set (STS) from the cursor cache using the DBMS_SQLTUNE Oracle supplied package, as below: user@DB> EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( – > sqlset_name => ’10g_TEST’, – > time_limit => 60, – > repeat_interval => 10, – > capture_option => ‘MERGE’, – > basic_filter => ‘PARSING_SCHEMA_NAME = ”TEST”’, – […]
ORA-38171 Insufficient privileges for SQL management object operation
The Problem You are trying to load plans into a SQL Management Baseline (SMB) in your 11g DB and you receive the following error: SQL> DECLARE 2 my_plans PLS_INTEGER; 3 BEGIN 4 my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => ‘TEST’); 5 END; 6 / DECLARE * ERROR at line 1: ORA-38171: Insufficient privileges for SQL management object […]
ORA-01489 result of string concatenation is too long
The Problem The result of string concatenation cannot be more than 4000 characters long. If the string concatenation is too long, you will receive the following error message: ORA-01489: result of string concatenation is too long The Solution Example I If you are using dynamic SQL and the resultant string is greater than 4000 characters […]
ORA-01704: string literal too long
While attempting to update a column in a table of data type CLOB, one of the business analysts received this error message: ORA-01704: string literal too long They were trying to update the table with a SQL statement and with a SQL statement you can only specify up to 4000 bytes. The way to get […]
