Tagged Questions
0
votes
0answers
45 views
PL/SQL extremely slow FETCH performance
I have a stored procedure that includes (among many other things) a dynamic query with a LOOP and a FETCH. The fetch has to potential retrieve a large amount of data as one column contains spatial ...
0
votes
0answers
28 views
Passing parameters to a procedure executed by DBMS_SCHEDULER
I have a scheduler program that runs a stored procedure that requires input parameters.
BEGIN
DBMS_SCHEDULER.DROP_PROGRAM
(program_name => 'MYSCHEMA.EXPORT_SCHEMA_STARTING');
END;
...
0
votes
1answer
30 views
Error in trigger: table is mutating
CREATE OR REPLACE TRIGGER "TRANSACTION"
before
update of totalunitsbought on SIPHOLDER referencing new AS NEW old AS OLD
for each row
begin
declare
mportfolioname varchar2(20);
...
2
votes
1answer
35 views
Testing AFTER INSERT Trigger
I have the following table:
CREATE TABLE Train(
Train_No integer PRIMARY KEY,
Loco_No integer REFERENCES Locomotive(Loco_No),
Back_Loco_No float REFERENCES Locomotive(Loco_No),
...
0
votes
1answer
40 views
Combine multiple queries into single query
I am trying to run the following query:
SELECT Freight.Freight_No,
Freight.Available,
Locomotive.Loco_No,
Locomotive.Available,
Driver.Driver_No,
Driver.Name,
...
0
votes
1answer
30 views
Is the data fetched by an explicit cursor immutable in PL/SQL?
This seems like an obvious question, but Googling has so far failed me...
Say I have a view which delineates updates that I need to apply and, to this end, I have an SP that opens the view as a ...
0
votes
0answers
26 views
Error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
i've problem here. i wanna create a Stored procedure like below, but i encountered error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records.
create or ...
0
votes
1answer
44 views
Oracle's UTL_FILE
I am learning Oracle pl/sql by myself.
I need to create a procedure that exports a table to a csv file. For that I am thinking of UTL_FILE.Is there any good book or a site to get me started? I did ...
2
votes
1answer
121 views
Reset every Oracle sequence to the MAX value of its target table PK?
I finally got around to migrating from MySQL to Oracle and was pleasantly surprised just how well of a job the SQLDeveloper Migration tool did. It even took my AUTOINCREMENT fields and created ...
0
votes
1answer
53 views
PL/SQL for Updating 1 Column in Table A Where PK = a row in table B
Using an oracle DB, 11g r2, I need to write a pl/sql which updates one column in table A where the PK exists in table B, with commits ever ~50,00 rows.
3200000 rows are involved in the update, and ...
0
votes
1answer
52 views
Invalid rowid error
I'm trying to see, how UPDATE Lock helps to minimize error while dml (delete/update) operations.
declare
cursor update_lock is select empno from emp where deptno=&no for update of sal;
num ...
2
votes
2answers
187 views
Why can't we write ddl statement directly into the PL/SQL block
Why can't we write ddl statements directly in PL/SQL block, for example when i write
CREATE OR REPLACE PROCEDURE test IS
BEGIN
truncate table table_name; // error
END test;
/
But,
CREATE OR ...
1
vote
2answers
111 views
Oracle insert max from two date
I want to create an insert statement in a PL/SQL Script that inserts the larger of two different values. Conceptually, what I want to do is something like
Insert into MYTABLE (DATE_INSERT)
VALUE ...
0
votes
0answers
178 views
12154 error when using PL/SQL Developer
I have installed a few versions of Oracle on my machine. I have tried 64 bit and 32 bit versions. I have installed 11G. I need Oracle 10G 32 bit.
Using the following guide I removed all versions of ...
2
votes
1answer
83 views
Oracle Packages, Procedures and Functions Not Respecting Roles
When you create a package, sproc or function in one schema (say, SCHEMA1) that accesses objects in another schema (SCHEMA2), even when SCHEMA1 has appropriate permissions to the respective SCHEMA2 ...
6
votes
1answer
219 views
Package state not re-intialized on raise_application_error
When a package has state and the header is changed, the first call gets an error stack something like this:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of ...
3
votes
2answers
80 views
Is there a bug with PL/Scope in combination with associative arrays?
I believe I stumbled upon a bug with PL/Scope in combination with associative arrays, but perhaps I overlook something.
I have the following package
create or replace package tq84_pkg_c as
...
3
votes
1answer
377 views
halt on error in a sqlplus script
I am deploying pl/sql code using several sql files that are called with @@file.
If a package got a compilation error the script continues to the end.
Is there a way to stop on every compilation ...
2
votes
1answer
168 views
How to speed up a query with a lot of subqueries
I have a query which uses a series of functions to return the status of each document for a loan.
SELECT loan_number,
borrower_name,
get_application_status(loan_number, ...
1
vote
1answer
157 views
DB stored procedures: how do they help increase security in web systems?
The usage of Oracle PL/SQL procedures for controlling data access is often emphasized in PL/SQL books and other sources as being more secure approach. I'v seen several systems where all business logic ...
1
vote
2answers
260 views
How to isolate general Oracle 10g issues through SQL
Background:
I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in ...
2
votes
2answers
1k views
How to store single result in variable and reuse it in a query (Oracle)?
I have a query like
SELECT id FROM xyz WHERE ...;
which has a more or less complex WHERE clause and returns exactly one row with one column (ID). I need this ID for several later queries in a ...
0
votes
1answer
281 views
Find ID columns with null value in oracle database
I need a way to iterate through all tables in tablespace "T_ECOS" and find all ID with null value in oracle database
Any suggestion?
2
votes
1answer
75 views
PLSQL Package - Catching Errors in procedure (Oracle)
I have a problem with my package (and procedure) not catching exceptions.
I have the below code:
CREATE OR REPLACE PACKAGE process_orders
IS
PROCEDURE add_order (
order_num NUMBER,
...
3
votes
3answers
574 views
Using the same sequence twice in a PL/SQL statement
I wrote the snippet of PL/SQL today:
declare
first_id number;
second_id number;
begin
insert into table (sort_nr, text_id, unit_id) values(...,
table_seq.nextval, table_seq.nextval) returning ...
5
votes
3answers
1k views
Why use explicit cursors instead of regular loops?
I've been writing basic web apps for a year (for an Oracle db), and since the functions are pretty simple, most of us stick with regular FOR loops to get our data:
for i in (select * from STUDENTS) ...
2
votes
0answers
264 views
Where can I find a syntax reference for Oracle SQL Developer's “Generate DB Doc” function?
Oracle SQL Developer (I'm using v3.2) has a feature called "DB Doc", which generates documentation for database objects. I mostly want to use it to generate documentation for my stored procedures, ...
0
votes
3answers
256 views
ORA-01562: failed to extend rollback segment number 11
I´m trying to execute this query into a SP
DELETE FROM table SC
WHERE SC.evaluationMonth= 'Some month';
COMMIT;
But for this month there are something like 10 million records. So I get the ...
0
votes
1answer
489 views
Oracle: Output not shown even if SERVEROUTPUT variable set to ON
I was wondering why the output is not being shown even if I set the SERVEROUPUT variable to ON.
I have the following annonymous block code:
DECLARE
v_rec_count number:=0;
BEGIN
for rec in ...
1
vote
0answers
241 views
How can one reliably update a LOB column with data from another LOB column in Oracle?
I have tried:
UPDATE "SCHEMA".TABLE SET LOB_COLUMN = (SELECT LOB_COLUMN FROM "SCHEMA".TABLE)
I have tried:
CREATE OR REPLACE PROCEDURE COPY_LOB_DATA IS
OLD_BLOB BLOB;
NEW_BLOB BLOB;
...
6
votes
3answers
267 views
How can I ensure that only one copy of a procedure is running in Oracle?
We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error.
Whats the best method ...
3
votes
1answer
563 views
How far should bind variables go in Oracle?
I am currently examining all database users's SQL code in our department for not using bind variables. We do experience an ORA-04031 error within a constant period of time.
Now, I have a quite firm ...
3
votes
2answers
299 views
FORALL on Nested Collections
Suppose I have the following two tables in my Oracle database:
create table a (num number);
create table b (val varchar2(30);
and in my PL/SQL program I have declared a nested collection as ...
5
votes
1answer
81 views
Retrieve Indices of Associative Array as Collection
In PL/SQL, suppose I have some associative array defined as follows:
declare
type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER;
a_arr a_arr_t;
I then, sparsely, populate the ...
0
votes
1answer
1k views
Why does dbms_scheduler max_run_duration not raise event JOB_OVER_MAX_DUR?
Starting from this question and this example I've tried to create an event based dbms_scheduler job, which waits for event JOB_OVER_MAX_DUR raised from jobs, which exceeded their max_run_duration.
...
4
votes
2answers
2k views
Oracle 11G - Index Rebuild VS GATHER_TABLE_STATS
Quick question, after populating my table, I noticed through my explain plan that indexes, in most cases, only seems to take effect after I perform GATHER_TABLE_STATS:
Exec ...
1
vote
1answer
193 views
Help with getters & setters in PL/SQL
I am not a db guy, and I have a very beginner's knowledge bout PL/SQL. That being clear now the question:
I have 2 tables one file level & another batch level. In a day, only 1 file_id is ...
2
votes
2answers
853 views
Return many rows on a plsql Oracle10g
I'm doing a little project and I need to do a plsql to return many rows.
I have someting like:
Table(id, data1, data2, from)
And the user gives me the value of from and I have to return all that.
...
1
vote
1answer
356 views
Improve performance for my plsql block
First of all let me make kind of a disclaimer here:
1) i'm a developer, not an administrator;
2) and i dont have admin rights on my database.
3) table specs :
-- oulp_path_attendance_base_t - ...
0
votes
2answers
507 views
How do I manage column order when using PL/SQL's %ROWTYPE records?
I have a table with a large number of columns, which has data gathered from several other tables. I want to write some PL/SQL like:
DECLARE
TYPE t_toptab_list IS TABLE OF toptab%ROWTYPE;
l_data ...
1
vote
1answer
191 views
Error with Oracle trigger, invalid identifier
I have the following DML statement that is working perfectly, i have tried to make it into a trigger but it isn't working.
DML statement
UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL =
NVL((SELECT ...
1
vote
1answer
146 views
Update statement errors, parenthesis, failed validation ORACLE
i am writing an update query and it is giving me errors, what i am trying to do is to take a value multiply it added to another value.
Original statement
The statement below works flawlessly,
...
4
votes
2answers
312 views
Return a fully dynamic table from an Oracle function
I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of ...
3
votes
1answer
225 views
How to qualify variable inside trigger body (PL/SQL )?
I can explicitly qualify variable in the body of stored procedure or function :
create or replace
PROCEDURE ptest AS
int_val INT;
BEGIN
ptest.int_val:=0;
END;
/
How to do the same inside trigger ...
2
votes
2answers
122 views
Overhead of DBMS_Profiler
If I use DBMS_Profiler to profile a long running (5 hour) PL/SQL script, how much longer should I expect the script to run with profiling? I realize this will be dependent on what the script is ...
1
vote
2answers
2k views
ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes
I got that error when I tried to execute this query:
select *
from lc_history2 lc
where lc.view_name <> 'LC Main'
and lc.modifiedtime = (select max(modifiedtime) from lc_history2 l where lc.lc ...
2
votes
1answer
310 views
Alter in PL SQL Loop
I just started learning PL/SQL an hour ago... I'm trying to set profile settings dynamically... I select distinct profile names from dba_users and I'm trying to run an alter profile command for each ...
5
votes
3answers
833 views
PL/SQL: best way to count elements in an array?
Given this:
DECLARE
TYPE T_ARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
MY_ARRAY T_ARRAY;
V_COUNT INTEGER;
I would like to do:
BEGIN
-- ... some code filling the MY_ARRAY ...
2
votes
1answer
337 views
PL/SQL : query numeric data type by scale?
Wish to select rows with number fields having a certain amount of data after the decimal
eg
select number from table where number.scale > 4;
Is this possible to do directly, or must I cast to ...
4
votes
1answer
786 views
How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats
Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings:
FOR COLUMNS SIZE 5 some_column
and
FOR COLUMNS some_column SIZE 5
both will create a ...
