Hot answers tagged

4

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; ...


3

You cannot use backups from before the switch to ARCHIVELOG mode to restore and recover the database to a point in time after the switch. Looking at this quote it makes sense. If you enable archivelog on a database where it was not enabled, then any backup that you have before archive log was enabled can't be used to do a point in time recover, since ...


3

It's not really a requirement. The database itself will work perfectly without backup after switching between ARCHIVE / NOARCHIVE mode. But if something ever goes wrong (or database needs to be copied/cloned to other machine), you'll need a backup. You may or may not use incremental backups feature ; if you do want to use them you must take 0-level ...


2

Customers come and go. Sometimes they have the right to take their data with them. To extract a subset of data from one schema where the data could be anywhere and the only marker might be a tenant number is a lot harder than putting each customer's data in one schema. One solution I have seen used is to have a master schema with the metadata concerning ...


1

This Error can happen with PL/SQL and came up with ORA-600 [kqlidchg0],ORA-00001 and/or ORA-01578 see MOS Doc ID 17210525.8. For 12.1 and 11.2 install latest Patch (April 2016) ORA-600 [kqlidchg0] with PL/SQL ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated Try: ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';


1

In Oracle a schema and a user is the same thing. In Postgres it isn't. So there is no direct "mapping" on what you did in Oracle in "Postgres land". A table is always owned by the user who created it initially there is no way to change that. If you do not want to give the user ink the privilege to create tables, the "Postgres" way would be to give that ...


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 ...


1

Ok, so you have no backup. You made sure it is not a permission issue. But you have Data Pump dumps, at least that is something. First try to recover the datafile without data loss: startup mount alter database datafile 'V:\DB\CST001.DBF' online; recover datafile 'V:\DB\CST001.DBF'; Answer the prompts accordingly, if any. If this succeeds, you can open ...


1

ORA-00913 too many values Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT. Action: ...


1

This should work: with w(id, line#, line, rest) as ( select id, 1, SUBSTR(regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'), 1, 132), substr(to_char(bar), LEAST(132, regexp_instr(bar, '$', 1, 1, 1, 'm'))+1) from foo union all select id, line#+1, SUBSTR(regexp_substr(rest, '^.*?$', 1, 1, 'm'), 1, 132), substr(rest, LEAST(132, ...



Only top voted, non community-wiki answers of a minimum length are eligible