Call this before the query: set define off; Alternatively, hacky: update t set country = ‘Trinidad and Tobago’ where country = ‘trinidad &’ || ‘ tobago’; From Tuning SQL*Plus: SET DEFINE OFF disables the parsing of commands to replace substitution variables with their values.
You might be interested in this query. It tells you how much space is allocated for each table taking into account the indexes and any LOBs on the table. Often you are interested to know “How much spaces the the Purchase Order table take, including any indexes” rather than just the table itself. You can … Read more
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name=”TABLE_NAME” AND cons.constraint_type=”P” AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner ORDER BY cols.table_name, cols.position; Make sure that ‘TABLE_NAME’ is in upper case since Oracle stores table names in upper case.
You can use VARRAY for a fixed-size array: declare type array_t is varray(3) of varchar2(10); array array_t := array_t(‘Matt’, ‘Joanne’, ‘Robert’); begin for i in 1..array.count loop dbms_output.put_line(array(i)); end loop; end; Or TABLE for an unbounded array: … type array_t is table of varchar2(10); … The word “table” here has nothing to do with database … Read more
The correct syntax for updating the password using SQL Developer is: alter user user_name identified by new_password replace old_password ; You can check more options for this command here: ALTER USER-Oracle DOCS
I found this link useful. Here is the paragraph highlighting some of the pros/cons of each approach. The most commonly seen design is to imitate the many Boolean-like flags that Oracle’s data dictionary views use, selecting ‘Y’ for true and ‘N’ for false. However, to interact correctly with host environments, such as JDBC, OCCI, and … Read more
You can use literal quoting: stmt := q'[insert into MY_TBL (Col) values(‘ER0002′)]’; Documentation for literals can be found here. Alternatively, you can use two quotes to denote a single quote: stmt := ‘insert into MY_TBL (Col) values(”ER0002”)’; The literal quoting mechanism with the Q syntax is more flexible and readable, IMO.
Section 3 of RFC4122 provides the formal definition of UUID string representations. It’s 36 characters (32 hex digits + 4 dashes). Sounds like you need to figure out where the invalid 60-char IDs are coming from and decide 1) if you want to accept them, and 2) what the max length of those IDs might … Read more
From a quick search, Oracle appears to use double quotes (“, eg “table”) and apparently requires the correct case—whereas, for anyone interested, MySQL defaults to using backticks (`) except when set to use double quotes for compatibility.
This is usually caused by duplicates in the query specified in USING clause. This probably means that TABLE_A is a parent table and the same ROWID is returned several times. You could quickly solve the problem by using a DISTINCT in your query (in fact, if ‘Y’ is a constant value you don’t even need … Read more