I have a table with about 1 billion rows. I'm the sole user so there's no contention on locks, etc. I noticed that when I run something like this: DECLARE CURSOR cur IS SELECT col FROM table where rownum < N; BEGIN OPEN cur; LOOP dbms_output.put_line("blah") END LOOP; CLOSE cur; END; there is a lag between the time ...
I have a homegrown Oracle role that was created long ago: create role MyRole; It's been granted the ability to select, insert, update, and delete from some tables and views. grant select on sometable to MyRole; grant insert on sometable to MyRole; grant select on someothertable to MyRole; -- etc. How can I now enumerate the specifi...
I'd like to call a PL/SQL stored procedure that has an OUT parameter specified, but I don't care about the return value. I just care that the procedure executed successfully, i.e. no exceptions thrown. Do I have to define a dummy variable in my calling PL/SQL block to receive the out parameter even though I don't want it? It clutters ...
Can someone provide an example of how to use parallel table function in oracle pl/sql. We need to run massive queries for 15 years and combine the result. SELECT * FROM Table(TableFunction(cursor(SELECT * FROM year_table))) ...is what we want effectively. The innermost select will give all the years, and the table function will t...
I have been porting oracle selects, and I have been running across a lot of queries like so: SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id; ...and: SELECT last_name, d.department_id FROM employees e, departments d WHERE e.department_i...
Hi, I doing a Proof Of Concept to figure out how efficient to call a store procedure using EclipseLink. I was able to call oracle store procedure using EclispeLink with Scalar/primitive data types (link Integer, varchar etc). I wanted to understand how can I handle Oracle Store procedure from EclipseLink with collection(Oracle Types/Use...
Could anyone tell me how to bulk insert data from a ref cursor to a temporary table in PL/SQL? I have a procedure that one of its parameters stores a result set, this result set will be inserted to a temporary table in another stored procedure. This is my sample code. CREATE OR REPLACE PROCEDURE get_account_list ( type_id in account_t...
Hi, Using an Oracle temporary table does not generate much redo log as a normal table. However, the undo log is still generated. Thus, how can I write insert, update, or delete statement on a temporary table but Oracle will not generate undo log or generate as little as possible? Moreover, using /+append/ in the insert statement will g...
Have a lot of unnecessary results using contains() method in my query. Don't tell me to use like or something else. It is hardcoded and couldn't be changed. ...
Hi Business logic is coded in pl/sql packages procedures and functions. Java programs call pl/sql packages procedures and functions to do database work. pl/sql programs store exceptions into Oracle tables whenever an exception is raised. How would my java programs get the exceptions since the exception instead of being propagated from...
Hello, I'm hoping someone can help me as I've been stuck on this problem for a few days now. Basically I'm trying to pull data from 3 tables in Oracle: 1) Orders Table 2) Vendor Table and 3) Master Data Table. Here's what the 3 tables look like: Table 1: BIZ_DOC2 (Orders table) OBJECTID (Unique key) UNIQUE_DOC_NAME (Document Name i.e...
I have a stored procedure which returns a ref cursor as follows: CREATE OR REPLACE PROCEDURE AIRS.GET_LAB_REPORT (ReportCurTyp OUT sys_refcursor) AS v_report_cursor sys_refcursor; report_record v_lab_report%ROWTYPE; l_sql VARCHAR2 (2000); BEGIN l_sql := 'SELECT * FROM V_LAB_REPORT'; OPEN v_report_cursor...
I have an Oracle SQL query as part of a stored proc: DELETE FROM item i WHERE NOT EXISTS (SELECT 1 FROM item_queue q WHERE q.n=i.n) AND NOT EXISTS (SELECT 1 FROM tool_queue t WHERE t.n=i.n); A bit about the tables: item contains about 10k rows with an index on the n column item_queue contains about 1mil rows also with index on n...
Hi, I much prefer using this 'embedded' style inserts in a pl/sql block (opposed to the execute immediate style dynamic sql - where you have to delimit quotes etc). -- a contrived example PROCEDURE CreateReport( customer IN VARCHAR2, reportdate IN DATE ) BEGIN -- drop table, create table with explicit column list CreateReportTa...
I'm updating the IDs with new IDs, but I need to retain the same ID for the master record in table A and its dependents in table B. The chunk bracketed by comments is the part I can't figure out. I need to update all the records in table B that share the same ID with the current record I'm looking at for table A. DECLARE CURSOR_A CUR...
I am using Solaris. I have to log into sql plus and run some queries, which give a huge result set. I want to copy all that into a file. Is there any command for it in unix or sqlplus ? ...
Or are there any oracle data dictionary to tell me which tables are being referenced in (materalised) views? I wish to find out what tables references are used in a DML. I prefer to use an oracle package as it can be self contained in the database. But other suggestions welcome. Open source very welcome. Here is the link to the synt...
I have a hierarchical table on Oracle pl/sql. something like: create table hierarchical ( id integer primary key, parent_id references hierarchical , name varchar(100)); I need to create a procedure to alter that table so I get a new field that tells, for each node, if it has any children or not...
I am trying to export the source for a java object from an oracle database using the following code. DECLARE blob1 BLOB; BEGIN DBMS_LOB.CREATETEMPORARY(blob1, FALSE); DBMS_JAVA.EXPORT_SOURCE('OBJECTNAME', 'SCHEMANAME', blob1); END; Whenever I try to run it I get an oracle.aurora.rdbms.ModifyPermissionException exception even t...
How do I calculate number of years since product was made (rounded to 1 decimal point) for products that were made less than five years ago? Thank you. ...