New answers tagged

1

Callbacks are totally against the idea behind SQL. It's designed as declarative language which queries describe the task and query optimizer builds composition of sorts, index seeks, merges, etc to meet the query. No any row-by-row processing by callbacks. Oracle supports overloading, with restrictions http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/...


3

WHERE PARENT_ID IN my_array; This will not work. First, as the error message states, you are not allowed to use local collection types in SQL statements, you need to define them in the database. Second, that syntax does not exist. So first you define the type: CREATE TYPE arr_type is TABLE of VARCHAR2(11 BYTE); And after that, you can use the ...


2

It should be a simple boolean. This should work and be more efficient. The SQL%ROWCOUNT attribute will be populated with the number of rows deleted. If you didn't delete any then just exit. FOR i IN 1..100 LOOP DELETE FROM mytable WHERE date <= (SYSDATE - 30) AND ROWNUM < 5000; EXIT WHEN sql%rowcount = 0; COMMIT; END LOOP; If you aren'...


0

How about raise no_data_found;


0

This: EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA =B') Does not do what you think it does. ;) That statement does not change who YOU are. It changes your default Schema for purposes of searching/finding an object. For example, try this: 1) Create a table in User TESTUSERB. Let's call it TEST 2) Login as User TESTUSERA. 3) desc TEST (...


1

Many ways, but as you suggested, you can use DUAL: CREATE PROCEDURE NODATAFOUND AS nowt VARCHAR(10); BEGIN SELECT * INTO nowt FROM DUAL WHERE 1=0; END; / It'll produce a ORA-01403: no data found when executed.


0

I would use an existing table from project structure with an impossible where clause. For example a negative number on PK when PK i defined as positive, etc. Strange question in an interview....I never got that kind.


3

Consider below case: +------------+--------------+-----------+ | date | services | downloads | +------------+--------------+-----------+ | 2016-05-31 | Apps | 1 | | 2016-05-31 | Applications | 1 | | 2016-05-31 | Applications | 1 | | 2016-05-31 | Apps | 1 | | 2016-05-31 | Videos | 1 | | ...



Top 50 recent answers are included