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


15

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


13

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

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

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


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

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

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


7

Oracle SQL Developer ships with a report to find these unindexed columns. Here's the SQL behind this report: select a.owner "Owner", a.table_name "Table_Name", a.constraint_name "Constraint_Name", a.columns "Foreign_Key_Column_1", ...


7

There are different ways to achieve the goal. SQL> select to_char(trunc(add_months(sysdate,-12*5),'YEAR'),'YYYYMM') from dual; OR SQL> select to_char(trunc(SYSDATE - interval '5' year,'YEAR'),'YYYYMM') from dual; Regarding the second one, what happens if the SYSDATE or the current date supplied happens to be a leap day? SQL> select to_char(trunc(...


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

When you add a column that is nullable and don't give a default value, the database doesn't have to do anything on the table data itself. The rows are unchanged, only the table metadata is altered. (This is due to the on-disk row format used. Null columns at the end of a row are not stored in the row pieces, in general/under normal circumstances/for ...


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


6

The error has occurred because you are referencing a column in another table which is not unique. The good answers are already given by Lennart and Balazs Papp. I would like to explain why do we need a unique column in the parent table. As you said you want to keep duplicate values in the column used for the foreign key which is not possible while creating ...


6

Use grouping sets. You can try it here. select cat1, cat2, cat3, sum(val) as val from t1 join t2 on t1.f_id = t2.f_id group by grouping sets ((cat1),(cat1, cat2),(cat1, cat2, cat3)) order by cat1, cat2 nulls first, cat3 nulls first OUTPUT cat1 cat2 cat3 val A 15 A a 10 A a aa 4 A a ab 6 A b 5 A b ba 4 A b bb ...


6

SQL> create table t1 (c1 date); Table created. SQL> insert into t1 values (date'0000-01-05'); 1 row created. SQL> commit; Commit complete. SQL> select c1, to_char(c1, 'dd/MM/yyyy'), dump(c1) from t1; C1 TO_CHAR(C1 ------------------- ---------- DUMP(C1) -----------------------------------------------------------------------...


5

When you create a program with CREATE_PROGRAM procedure, you can specify how many arguments it expects with number_of_arguments parameter. If, for some reason, you want to modify some attribute (number_of_arguments in our case) for an existing Scheduler object (program in our case), you don't need to drop the object and re-create it with new attribute value ...


5

I would suggest you to make use of External Tables. You can create an external table on your CSV file using ORACLE_LOADER driver and then update your existing table with data in your external table using DML (MERGE for example). Consult Oracle Utilities Guide for detailed info. What follows is my sample of how you can update tables from flat files. First ...


5

I believe you should be aiming for a plan that avoid any actual sort operation, and "stops short" as soon as possible. To avoid the sort (and "materializing" the inner view), your sort order must match exactly the index columns, or your where clauses must be strict equals only on all the leading columns. Otherwise there will be a need to sort subsets, and ...


5

Best practice is to avoid Non-SQL function calls inside SQL queries as the function may not be universally available -- in my system there is no numtodsinterval function -- also because of the additional overhead to switch context from SQL to PL/SQL and then back to SQL for every row returned or tested if the function is called in the where clause. Here is ...


5

It is possible that the number of rows exceed the value of PLS_INTEGER. Here is the quote from Oracle on %ROWCOUNT. %ROWCOUNT Attribute: How Many Rows Were Fetched? %ROWCOUNT returns: Zero after the explicit cursor is opened but before the first fetch Otherwise, the number of rows fetched (a PLS_INTEGER) Note: If the number of rows ...


5

A new subpartition can be added to the existing partitioned table using the following command: ALTER TABLE PART_TEST modify partition OCT19 add subpartition OCT19AXCS values ('AXCS');


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