Tag Info

New answers tagged

0

One ad hoc improvement: poor: for root in all_fitting_roots LOOP FITTING := FITTING + 1; END LOOP; better: SELECT COUNT(DISTINCT ROOT_ID) into FITTING FROM ( SELECT UNIQUE_ID, ROOT_ID, FIRST_NAME, LAST_NAME, POSTAL_CODE, FSA FROM (SELECT p.*, CONNECT_BY_ROOT UNIQUE_ID AS ROOT_ID FROM UNIQUE_PHYSICIAN p START WITH PARENT_PHYS_ID IS ...


1

You are trying to output avg_sal.salary which is not defined. Use alias in the cursor body : .... cursor c1 is select avg(e.salary) AS salary ... Side notes. It seems that parameter passed (ddept_id ) is not used anywhere inside the procedure except printing. Also, it's been long time since JOIN syntax was introduced and it's much more readable ...


0

I think you're going about this the wrong way. First off, I don't think you need a procedure/function to solve your problem. Second, I definitely don't see how a cursor should come in to it. Try the following window query using the OVER() clause: SELECT avg(e.salary) OVER(PARTITION BY d.dname) FROM emp e, works w, dept d WHERE e.eid=w.eid and d.did = ...


1

It may probably no longer be a problem, but in the code it seems you forgot that the assignment operator is :=, not =, so typeEngine would be null and the query returns no rows.


2

Not to the current session, no. You can use the dbms_application_info package to instrument the code so that tools running in other sessions can query v$session_longops and see how far along the process is. This is the same tool that Oracle internally writes to during long-running operations so lots of front-ends like Enterprise Manager and Toad will ...


0

I think these answers are all getting a) way too complicated, and b) off-track relative to what user2023861 wants to accomplish: "I am trying to declare a variable, then select that variable." means two different things in either PL/SQL or in SQLplus, and perhaps two more things in DBArtisan. To accomplish the objective in SQLplus, use "Substitution ...


2

My proposal would be this one: CREATE TABLE UNIQUE_PHYSICIAN ( PARENT_PHYS_ID NUMBER, PHYS_ID NUMBER CONSTRAINT PHYSICIAN_PK PRIMARY KEY); ALTER TABLE DPISYS.UNIQUE_PHYSICIAN ADD CONSTRAINT UNIQUE_PHYSICIAN_PARENT_FK FOREIGN KEY (PARENT_PHYS_ID) REFERENCES DPISYS.UNIQUE_PHYSICIAN (PHYS_ID); CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG ...


0

If I understand your question correctly, if the trigger on YourTable inserts a row into YourTable as well, then that insertion will fire another instance of the same trigger. See previous answers at: http://stackoverflow.com/questions/16391081/how-to-fix-oracle-trigger-causing-recursion-issue Recursion has issues. Yes, I have written recursive code in the ...


0

Yes, if this was to work, it would generate an infinite loop. But more than likely, it will throw a mutating table exception first. What is the problem that you are trying to solve? A trigger that updates other rows in a table when one row changes is unlikely to be a reasonable solution. If you have cross-row dependencies, that almost always indicates ...


0

When I learned PL/SQL I started with Steven Feuerstein's PL/SQL Best Practices. Several more edition have come out, but it gives you a good perspective on how to think about code.


0

I'm successfully able to create a scheduler for my MV to refresh every minutes if the status stallness is not FRESH. Here's my solutions : I created a schedules to be used in my scheduler: BEGIN sys.dbms_scheduler.create_schedule( repeat_interval => 'FREQ=MINUTELY', start_date => to_timestamp_tz('2015-06-09 01:41:55 Asia/Jakarta', ...



Top 50 recent answers are included