New answers tagged

0

Recently I had to rewrite a bunch of queries from an implicit FOR loop into explicit cursors. The reason was that the queries fetched data from an external database via link and this database had a different encoding than our local database. When transferring data from the implicit cursor into a locally defined record type misterious intermittent errors ...


0

As other have asked, it would be helpful to know what business requirements need this functionality. For example, if you need to know when a certain row started appearing in query results for other sessions (which would only occur on the commit, not the insert), then you could rename your table and add a column that is an effective date. When you insert ...


0

In PL/SQL I'd collect ROWID for each line updated get the systimestamp just before the "real" commit update the record by collected rowid with the previous timestamp commit one again But as stated in comments What is the business requirement for this? It has a big defect, it's commiting twice for the same records, so generating almost double ...


3

You can calculate a RANK/ROW_NUMBER based on this logic: ROW_NUMBER() OVER (PARTITION BY IMMS_ID ORDER BY CASE WHEN status='DI' THEN 1 WHEN status='OI' THEN 2 WHEN status='FI' THEN 3 WHEN status='OV' THEN 4 WHEN status='FV' THEN 5 END)


0

To get an approximate value (+/- 3sec or so) you may use ORA_ROWSCN You must define the table with ROWDEPENDENCIES to have the SCN stored for each row. Here an simple example: create table t1 (c1 number, c2 date) ROWDEPENDENCIES; insert into t1 values(1,sysdate); -- wait 5 sec commit; insert into t1 values(2,sysdate); -- wait 10 sec commit; SELECT ...


2

You can use the EXCEPT keyword: CREATE TABLE #T1(c1 char(1), c2 int) go INSERT INTO #T1 Values('A',1) INSERT INTO #T1 Values('A',2) INSERT INTO #T1 Values('A',3) INSERT INTO #T1 Values('A',4) INSERT INTO #T1 Values('B',1) INSERT INTO #T1 Values('B',2) INSERT INTO #T1 Values('C',1) INSERT INTO #T1 Values('C',3) INSERT INTO #T1 Values('C',4) INSERT INTO #T1 ...


0

SQL is a query language that allows you to issue a single query or execute a single insert/update/delete. PL-SQL is Oracle's "Procedural Language" SQL, which allows you to write a full program (loops, variables, etc.) to accomplish multiple selects/inserts/updates/deletes. You should learn SQL first from https://www.youtube.com/watch?v=7Vtl2WggqOg, then move ...


0

The reason you are not seeing any results is that the FOR loop is not returning any rows as there is no owner called 'O'. To select all the table for a user who is not the logged in user you could use; FOR t IN (SELECT t.table_name, t.owner FROM all_tables t where owner <> USER) However you will get a failure almost immediately as MAX(DATE) ...


3

You are only printing the last row, not every row: You need to move the dbms_output inside the loop: create or replace procedure Display is cursor ABC is select empno, ename, sal from emp where deptno=10; emp_rec ABC%rowtype; Begin Open ABC; Loop fetch ABC into emp_rec; exit when ABC%notfound; --- the output needs to be ...


0

create another procedure with passing paramaters in it schedule the above procedure with creare job CREATE OR REPLACE PROCEDURE XX_COPY_SALES_ORDER IS RETCD NUMBER; RETBUFF VARCHAR2(100); BEGIN XX_PROCESS_PKG.MAIN (RETCD,RETBUFF,NULL); END; BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name ...


1

You don't need loops, but one CASE per searched value: select ... from ( select ... case when 'Bach' IN (col1,col2,col3,col4,col5) then 1 else 0 end + case when 'Joan' IN (col1,col2,col3,col4,col5) then 1 else 0 end + case when 'Mike' IN (col1,col2,col3,col4,col5) then 1 else 0 end as matches from tab ) dt where matches > 0 ...


5

NUM is not a reserved word. The best way is not to use reserved words. If you insist on using them, you can put them between quotation marks. This does not work: SET SERVEROUTPUT ON declare begin number; begin begin := 10; DBMS_OUTPUT.put_line('The value of begin ' || begin); end; / This does: SET SERVEROUTPUT ON declare "begin" number; ...


0

Closest thing that I know is to overload a function... create or replace package test_pkg as function variableParams ( a in varchar2, z in integer, y in integer ) return integer; function variableParams ( a in varchar2, z in integer, y in integer, x in integer ) return integer; end; / create or replace package body test_pkg as function ...


1

DECODE is an SQL function, not PL/SQL. You can not use it in plain PL/SQL, only as part of SQL statements. There is no nice and easy way for this. There is the method with collections, for example: create or replace type t_numbers as table of number; / create or replace function sum_plsql (p_numbers t_numbers) return number as rv number := 0; begin ...



Top 50 recent answers are included