:

RSSSQL

Oracle 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 […]

Continue Reading

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 […]

Continue Reading

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 […]

Continue Reading

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 […]

Continue Reading

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”’, – […]

Continue Reading

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 […]

Continue Reading

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 […]

Continue Reading

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 […]

Continue Reading