You need to set oracle to listen on all ip addresses (by default, it listens only to localhost connections.) Step 1 – Edit listener.ora This file is located in: Windows: %ORACLE_HOME%\network\admin\listener.ora. Linux: $ORACLE_HOME/network/admin/listener.ora Replace localhost with 0.0.0.0 # … LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL … Read more
You can see your current session settings by querying nls_session_parameters: select value from nls_session_parameters where parameter=”NLS_NUMERIC_CHARACTERS”; VALUE —————————————- ., That may differ from the database defaults, which you can see in nls_database_parameters. In this session your query errors: select to_number(‘100,12’) from dual; Error report – SQL Error: ORA-01722: invalid number 01722. 00000 – “invalid number” … Read more
The general approach in this case would be to parse the comma-separated list into an Oracle collection and to use that collection in your SQL statement. Tom Kyte has an example of this in his discussion on variable IN lists. Assuming you create the myTableType type and the in_list function from that thread, you should … Read more
WITH Ancestor(arg1, arg2) AS ( SELECT p.arg1, p.arg2 FROM parent p WHERE arg2 NOT IN ( SELECT arg1 FROM parent ) UNION ALL SELECT p.arg1, a.arg2 FROM Ancestor a JOIN parent p ON p.arg2 = a.arg1 ) SELECT * FROM Ancestor Oracle only supports recursive CTE since 11g Release 2. In earlier versions, use CONNECT … Read more
At a minimum, you need to query ALL_TAB_COLUMNS, not ALL_TABLES DECLARE match_count integer; v_search_string varchar2(4000) := <<string you want to search for>>; BEGIN FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns) LOOP EXECUTE IMMEDIATE ‘SELECT COUNT(*) FROM ‘||t.owner || ‘.’ || t.table_name|| ‘ WHERE ‘||t.column_name||’ = :1′ INTO match_count USING v_search_string; IF match_count > … Read more
What are you using when operate with CLOB? In all events you can do it with PL/SQL DECLARE str varchar2(32767); BEGIN str := ‘Very-very-…-very-very-very-very-very-very long string value’; update t1 set col1 = str; END; / Proof link on SQLFiddle
insert into OPT (email, campaign_id) select ‘[email protected]’,100 from dual where not exists(select * from OPT where (email=”[email protected]” and campaign_id =100));
A SELECT INTO statement will throw an error if it returns anything other than 1 row. If it returns 0 rows, you’ll get a no_data_found exception. If it returns more than 1 row, you’ll get a too_many_rows exception. Unless you know that there will always be exactly 1 employee with a salary greater than 3000, … Read more
The user and password are DEFINITELY incorrect. Oracle 11g credentials are case sensitive. Try ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE; and alter password. http://oracle-base.com/articles/11g/case-sensitive-passwords-11gr1.php
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.