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
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.
the & is the default value for DEFINE, which allows you to use substitution variables. I like to turn it off using SET DEFINE OFF then you won’t have to worry about escaping or CHR(38).
No. There is no such option available from Oracle SQL Developer. You have to execute a query by hand or use other tool (For instance PLSQL Developer has such option). The following SQL is that one used by PLSQL Developer: select table_name, constraint_name, status, owner from all_constraints where r_owner = :r_owner and constraint_type=”R” and r_constraint_name … Read more
Version I am using Update 5th May 2012 Jeff Smith has blogged showing, what I believe is the superior method to get CSV output from SQL Developer. Jeff’s method is shown as Method 1 below: Method 1 Add the comment /*csv*/ to your SQL query and run the query as a script (using F5 or … Read more
You need to turn on dbms_output. In Oracle SQL Developer: Show the DBMS Output window (View->DBMS Output). Press the “+” button at the top of the Dbms Output window and then select an open database connection in the dialog that opens. In SQL*Plus: SET SERVEROUTPUT ON
You can change this in preferences: From Oracle SQL Developer’s menu go to: Tools > Preferences. From the Preferences dialog, select Database > NLS from the left panel. From the list of NLS parameters, enter DD-MON-RR HH24:MI:SS into the Date Format field. Save and close the dialog, done! Here is a screenshot:
Use a collection VARIABLE cursor REFCURSOR; DECLARE your_collection SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(); BEGIN your_collection.EXTEND( 10000 ); FOR i IN 1 .. 10000 LOOP — Populate the collection. your_collection(i) := DBMS_RANDOM.STRING( ‘x’, 20 ); END LOOP; OPEN :cursor FOR SELECT t.* FROM your_table t INNER JOIN TABLE( your_collection ) c ON t.id = c.COLUMN_VALUE; END; / PRINT … Read more
SELECT L.*, T.* FROM (SELECT Supplier_Code, Local_Commodity_Code, SUBSTR(LOCAL_COMMODITY_CODE, 1, INSTR(LOCAL_COMMODITY_CODE,’~’)-1) LOCAL_COM_CODE FROM OYSTER_WEB3.TRANSACTION ) T JOIN Local_Feed_Commodity_Map L ON L.Local_Commodity_Code = T.Local_Com_Code Oracle has an aversion to the SQL standard ‘AS’ keyword in some locations, so I’ve not used it anywhere to maximize the chances of the code working. However, as I noted in a … Read more
select order_number, line_number, isnull(max(case when linenum=1 then name else null end),”) name1 , isnull(max(case when linenum=1 then value else null end),”) value1, isnull(max(case when linenum=2 then name else null end),”) name2, isnull(max(case when linenum=2 then value else null end),”) value2, isnull(max(case when linenum=3 then name else null end),”) name3, isnull(max(case when linenum=3 then value else … Read more