22

There are a few ways that you can perform this data transformation. You have access to the PIVOT function then that will be the easiest, but if not then you can use an aggregate function and a CASE. Aggregate /Case version: select personid, max(case when optionid = 'A' then 1 else 0 end) OptionA, max(case when optionid = 'B' then 1 else 0 end) OptionB, ...


15

So, with thanks to @YasirArsanukaev for the time he put in, I have found a solution which works, but which I can't really explain. Riffing on the LOCAL_LISTENER thought, I was reading this other answer where it said: The database uses the LOCAL_LISTENER parameter to identify the listener it should register with. By default that is null, which according ...


13

In order to find out the users and the profile assigned you can use the commands below. DESC DBA_USERS; This will show you all the fields name for which you want to query SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS; And this command will show you the user name, profile and account status i.e. which profile is assigned to which user


12

There is an exception. When you define a before insert, row-level trigger on a table and issue a single row INSERT statement, the table is mutating error will not be raised. But if you define the same kind of trigger and issue a multi-row INSERT statement, the error will be raised. Here is an example: SQL> create table TB_TR_TEST( 2 col1 number, 3 ...


12

In general, no. A tnsnames.ora change shouldn't require a reboot but some applications will read and parse the tnsnames.ora at startup to be able to present a drop-down list of servers to the user, for example, and will cache whatever was read when the application started up rather than re-reading the file. Depending on the situation, it might be easiest ...


12

This is not an Oracle or PL/SQL issue, but a matter of implementing the proper algorithm. Here is an example: https://www.orafaq.com/wiki/Binary CREATE OR REPLACE FUNCTION dec2bin (N in number) RETURN varchar2 IS binval varchar2(64); N2 number := N; BEGIN while ( N2 > 0 ) loop binval := mod(N2, 2) || binval; N2 := trunc( N2 / 2 ); ...


10

This would be the equivalent in SQL Server syntax. Based on my reading of the Oracle docs, NULLIF and PIVOT appear to have the same format as their SQL Server kin. The challenge will be the pivot list which needs to be static unless you make the query dynamic as Itzik demonstrates but I have no idea if that can be translated to P/SQL WITH PersonOptions(...


10

A cursor is a pointer to a result set for a query. By returning a sys_refcursor you allow the client to fetch as many or few of the rows from the query as it requires. In stateful applications this could be used to page through results. A cursor can allow more flexibility than writing a PL/SQL function that returns an array as it's completely up to the ...


10

First, if you are creating a procedure in a package, the package name will need to be included when you call the procedure. begin hotel.fill_city(10000); end; / should correctly invoke your procedure. Second, you have issues with the naming of your local variables. Normally, you would not create local variables like city and postal_number that are the ...


9

If you start your instance using a server parameter file (a binary version of initialization parameter file, spfile), you can extract the initialization parameters to plain-text initialization parameter file (pfile), alter them, and then start your instance with modified memory parameters. sql> create pfile='myinit.ora' from spfile='spfileORCL.ora'; ...


9

The document that has the client/server compatibility matrix is 207303.1 Client / Server / Interoperability Support Matrix For Different Oracle Versions. (Oracle support account required) Oracle client version 12c is compatible with Oracle server version 11.2 (and below), so no your error isn't likely to come from that. Check that the listener is indeed ...


8

First, are you using "database" in the Oracle sense of the term? Or are you using it in the sense that other database vendors (such as SQL Server or MySQL) use the term? If you are using "database" in the Oracle sense, that would be the size of the SYSTEM and SYSAUX tablespaces at a minimum and would possibly include the size of the UNDO and TEMP ...


8

It depends on why you are creating the temporary tables in MySQL. Frequently, people that are creating temporary tables in other databases are doing so in order to work around limitations that don't exist in Oracle where readers don't block writers and writers don't block readers. In other databases, you commonly copy data from a permanent table to a ...


8

You need to terminate the SQL statement with a semicolon (;) or a put a slash (/) in the new line instead of hitting Enter. For example: select * from dual;. Otherwise SQLPlus will believe you have not finished your SQL statement, and it starts counting the lines. 2 is actually a line number, it is the 2nd line of your SQL statement. If you type nothing here ...


7

I don't see how this query could ever have returned the current day. ROWNUM starts with 1 so TRUNC(sysdate - rownum) will never return the current day and neither will TRUNC(sysdate + rownum). Both sides of your UNION return exactly 32 rows so the entire query should always return 64 rows. If you want 32 days before today, 32 days after today, and today ...


7

Generate the statements you need select 'DELETE FROM '||table_name||' ;' from user_tables; Even better would be using TRUNCATE instead of DELETE. That would prevent generating extra redo vectors in the redo log - in the end all you want to do is just get rid of the data. It would also avoid all actions by any DELETE triggers that the tables have. select '...


7

Short version: CREATE TABLE Relation ( stu_id INT NOT NULL REFERENCES Student, par_id INT NOT NULL REFERENCES Parent, PRIMARY KEY (stu_id, par_id) ) ; Long version: Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and ...


7

You can do this by using a save point. CREATE OR REPLACE PROCEDURE SPTest AS BEGIN -- We create a savepoint here. SAVEPOINT sp_sptest; INSERT INTO emptest(empid, empname, deptno) VALUES(1, 'ravi', 10); INSERT INTO test1(id, name, sal) VALUES(1, 'raju', 4444); UPDATE emptest SET empname = 'hari' WHERE empid = 1; -- If any ...


7

Use quotation marks: CREATE DATABASE LINK my_link CONNECT TO daniel identified by "*password" USING 'hostname:port/servicename';


6

You have the basics right. There is only one type of commit (no normal, fast...). from the concepts doc: When a transaction commits, the following actions occur: A system change number (SCN) is generated for the COMMIT. The internal transaction table for the associated undo tablespace records that the transaction has committed. The ...


6

This works fine: select regexp_substr(':123:456:', '(\d+):', 1, 2, 'i', 1) from dual; I think yours fails because the opening and closing colons won't get matched by both occurrences (because the first match is greedy).


6

That's expected, most DDL operations on partitions will invalidate the indexes affected by the DDL. The ALTER TABLE docs state that on all relevant operations. Specifically for truncate partition: For each partition or subpartition truncated, Oracle Database also truncates corresponding local index partitions and subpartitions. If those index partitions ...


6

I prefer to pivot query manually, but you may use PIVOT as well. SELECT PersonID, MAX(CASE WHEN OptionId ='A' THEN 1 END) AS OptionA, MAX(CASE WHEN OptionId ='B' THEN 1 END) AS OptionB, MAX(CASE WHEN OptionId ='C' THEN 1 END) AS OptionC FROM PersonOptions GROUP BY PersonID


6

Here is the Oracle 11.2 installation document describing the administrator user accounts after installation. Once logged in, you would change passwords with the syntax alter user system identified by mynewpassword; Note the SYS and SYSTEM accounts are assigned the DBA role. The DBA role does not have a password, the accounts do. The DBA role is created ...


6

The best account to login to the Oracle Enterprise Manager Database Control (EMDC) is the SYSMAN user account; with it, you are logging in as the proper EMDC super user. Use NORMAL mode, not AS SYSDBA. Alternatively, you can use SYSTEM (specifying mode AS SYSDBA). This is per 11.1 manual: http://docs.oracle.com/cd/B28359_01/install.111/b32002/rev_precon_db....


6

Because you are not logged in as the user system. When you execute conn system/password as sysdba, Oracle won't even check the user and password (--> from the local systeem where the db is running on). If you enter the system/password as sysdba and then execute 'show user', you'll notice you are logged in as user 'SYS'. So you are actually entering the wrong ...


6

That is the MySQL documentation, not the Oracle Database documentation. There is no such clause in Oracle Datababase. Use a trigger. Here is an example: link


6

When you use sqlplus userName/myPassword@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=asdasdasd.com)(PORT=1524))(CONNECT_DATA=(SID=AARCER1)))" then you don't need any tnsnames.ora file. Your entry in tnsnames.ora file should be like this: AARCER1 = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST= asdasdasd.com)(PORT=1524)) (CONNECT_DATA = (SID = ...


6

If you have a simple b-tree index on mycolumn, then yes, you would need to avoid calling functions on that column in order to be able to use the index to filter rows. In this case, it would seem to make much more sense to convert your numeric literals to timestamps than to do the reverse SELECT * FROM MYTABLE WHERE my_column > to_timestamp( to_char(...


6

Fullname etc. are not declared as unique in the parent table, and therefore you can't reference them from the child table. Why do you need to duplicate these columns in the child table? EDIT: Locking at you child table you say that: create table bit_2015_sep_cit4114_fyp_G_ ( ... , Full_Name VARCHAR2 (50) NOT NULL REFERENCES STUDENTINFO(Full_Name)...


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