Let’s answer the second question first: “why go away from the GTTs? are they really that bad.” A couple of days ago I was knocking up a proof of concept which loaded a largish XML file (~18MB) into an XMLType. Because I didn’t want to store the XMLType permanently I tried loading it into a … Read more
If you’re already using analytics (ROW_NUMBER() OVER …) then adding another analytic function on the same partitioning will add a negligible cost to the query. On the other hand, there are many other ways to do pagination, one of them using rownum: SELECT * FROM (SELECT A.*, rownum rn FROM (SELECT * FROM your_table ORDER … Read more
Say your table is called t1 and your primary-key is called id First, create the sequence: create sequence t1_seq start with 1 increment by 1 nomaxvalue; Then create a trigger that increments upon insert: create trigger t1_trigger before insert on t1 for each row begin select t1_seq.nextval into :new.id from dual; end;
you can check if your session has a row in V$TRANSACTION (obviously that requires read privilege on this view): SQL> SELECT COUNT(*) FROM v$transaction t, v$session s, v$mystat m WHERE t.ses_addr = s.saddr AND s.sid = m.sid AND ROWNUM = 1; COUNT(*) ———- 0 SQL> insert into a values (1); 1 row inserted SQL> SELECT … Read more
Josh- Thank you very much for taking the time to answer. Your instructions helped a whole lot, and are very close to what I have found on my own. Interestingly enough, I found it can be slimmed a little more. For those in my situation who Do not want their users to have to install … Read more
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
This answer summarizes (hopefully) all the steps required, many of which documented in various places online and might save someone hours of Googling. A. How to deploy and configure Oracle.DataAccess.Client. A.1. Download ODAC112030Xcopy_64bit.zip or ODAC112030Xcopy_32bit.zip. A.1.1. Extract the content of the following folders within the zip file into your application/host’s bin/setup folder: A.1.1.1. instantclient_11_2 A.1.1.2. … Read more
: Please Use yyyy-MM-dd date format !!! That is no way related to an Oracle error. I have a date field in a table that contains date in dd-MMM-yy format . No, you are confused. Oracle does not store dates in the format you see. It stores it internally in 7 bytes with each byte … Read more
If you want stmt.setString(1, “a”); // This won’t return any records to return a record, try conn.prepareStatement(“select * from x where c = cast(? as char(4))”)
In SQL Plus: SQL> create procedure myproc (prc out sys_refcursor) 2 is 3 begin 4 open prc for select * from emp; 5 end; 6 / Procedure created. SQL> var rc refcursor SQL> execute myproc(:rc) PL/SQL procedure successfully completed. SQL> print rc EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ———- ———- ——— ———- ———– … Read more