What is the effect of placing the commit like this in program? The Oracle Documentation describes COMMIT as: Purpose Use the COMMIT statement to end your current transaction and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle Database treats as a single unit. This statement … Read more
If you’re on 11g you can use the hint IGNORE_ROW_ON_DUPKEY_INDEX: SQL> create table my_table(a number, constraint my_table_pk primary key (a)); Table created. SQL> insert /*+ ignore_row_on_dupkey_index(my_table, my_table_pk) */ 2 into my_table 3 select 1 from dual 4 union all 5 select 1 from dual; 1 row created.
This is one way to do it: SQL> set serveroutput on SQL> CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF VARCHAR2(50); 2 / Type created SQL> CREATE OR REPLACE PROCEDURE testing (t_in MyType) IS 2 BEGIN 3 FOR i IN 1..t_in.count LOOP 4 dbms_output.put_line(t_in(i)); 5 END LOOP; 6 END; 7 / Procedure created SQL> DECLARE … Read more
From Oracle Database 12c Release 2 you could use TO_NUMBER with DEFAULT … ON CONVERSION ERROR: SELECT TO_NUMBER(‘*’ DEFAULT 0 ON CONVERSION ERROR) AS “Value” FROM DUAL; Or CAST: SELECT CAST(‘*’ AS NUMBER DEFAULT 0 ON CONVERSION ERROR) AS “Value” FROM DUAL; db<>fiddle demo
You need to add the SID entry for XE in order to register the instance with the listener. After installation of Oracle XE, everything looks good, but when you issue C:\>sqlplus / as sysdba SQL>shutdown immediate SQL>startup TNS-12505: TNS:listener does not currently know of SID given in connect descriptor the instance will not register with … Read more
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
Joyce, Here are three examples: 1) Using dbms_utility.comma_to_table. This is not a general purpose routine, because the elements should be valid identifiers. With some dirty tricks we can make it work more universal: SQL> declare 2 cn_non_occuring_prefix constant varchar2(4) := ‘zzzz’; 3 mystring varchar2(2000):=’a:sd:dfg:31456:dasd: :sdfsdf’; — just an example 4 l_tablen binary_integer; 5 l_tab dbms_utility.uncl_array; … Read more
You can’t access PLSQL objects (cases 2 & 5 = package-level objects) from java, see “java – passing array in oracle stored procedure”. You can however access SQL types (case 1 and 4). To get OUT parameters from PL/SQL to java, you can use the method described in one of Tom Kyte’s thread using OracleCallableStatement. … Read more
Yes. You just have to use the RAISE_APPLICATION_ERROR function. If you also want to name your exception, you’ll need to use the EXCEPTION_INIT pragma in order to associate the error number to the named exception. Something like SQL> ed Wrote file afiedt.buf 1 declare 2 ex_custom EXCEPTION; 3 PRAGMA EXCEPTION_INIT( ex_custom, -20001 ); 4 begin … Read more
Create the SQL type like this: CREATE TYPE MyListOfValuesType AS TABLE OF VARCHAR2(4000); And then use it in a SQL statement DECLARE MyListOfValues MyListOfValuesType; BEGIN MyListOfValues := MyListOfValuesType(‘MyValue1’, ‘MyValue2’); FOR rec IN ( SELECT * FROM DatabaseTable WHERE DatabaseTable.Field in ( SELECT * FROM TABLE(MyListOfValues) ) ) LOOP … END LOOP; END; Up until Oracle … Read more