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


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

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

There is an 8 character limit on some things in Oracle, try shorting database1, database2 and database3 to db1, db2 and db3 also the characters _ and # are allowed if I remember correctly. From Setting up the database When creating the database, ensure that the length of the Oracle System ID (SID) does not exceed eight (8) characters. For ...


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