In order to find out the users and the profile assigned you can use the commands below.
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
This is not an Oracle or PL/SQL issue, but a matter of implementing the proper algorithm.
Here is an example:
CREATE OR REPLACE FUNCTION dec2bin (N in number) RETURN varchar2 IS
N2 number := N;
while ( N2 > 0 ) loop
binval := mod(N2, 2) || binval;
N2 := trunc( N2 / 2 );
First, if you are creating a procedure in a package, the package name will need to be included when you call the procedure.
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 ...
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 ...
You can do this by using a save point.
CREATE OR REPLACE PROCEDURE SPTest
-- We create a savepoint here.
INSERT INTO emptest(empid, empname, deptno)
VALUES(1, 'ravi', 10);
INSERT INTO test1(id, name, sal)
VALUES(1, 'raju', 4444);
SET empname = 'hari'
WHERE empid = 1;
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 ...
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.
CREATE TABLE Relation
( stu_id INT NOT NULL REFERENCES Student,
par_id INT NOT NULL REFERENCES Parent,
PRIMARY KEY (stu_id, par_id)
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 ...
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=
(SID = ...
Oracle SQL Developer ships with a report to find these unindexed columns.
Here's the SQL behind this report:
select a.owner "Owner",
There are different ways to achieve the goal.
SQL> select to_char(trunc(add_months(sysdate,-12*5),'YEAR'),'YYYYMM') from dual;
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(...
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 ...
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 ...
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(...
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?
Locking at you child table you say that:
create table bit_2015_sep_cit4114_fyp_G_
, Full_Name VARCHAR2 (50) NOT NULL
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 ...
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
cat1 cat2 cat3 val
A a 10
A a aa 4
A a ab 6
A b 5
A b ba 4
A b bb ...
For where you are looking, you can only see the tables that exist in the schema that belongs to the user you supplied in your connection details.
If you want to see 'the tables' - you need to expand the 'Other Users' node, and explore the users there, and go to their respective tables list.
If you can query these objects in a worksheet w/o adding a SCHEMA. ...
This error indicates that a mismatch has been detected between redo records and rollback (undo) records.
Arg [a] - Maximum Undo record number in Undo block
Arg [b] - Undo record number from Redo block
Since we are adding a new undo record to our undo block, we would expect that the new record number ...
I just wanted to chime in and share what got me past this error. In my case (Ora 22.214.171.124 on Win 7) I found I needed the trailing '\' in my directory path.
CREATE OR REPLACE DIRECTORY dpump_dir AS 'C:\Data\SID'
CREATE OR REPLACE DIRECTORY dpump_dir AS 'C:\Data\SID\'
As the documentation describes:
The return data type is RAW if the measure column is RAW; otherwise
the return value is VARCHAR2.
So unless you pass the column as RAW, the result will be VARCHAR2, and that is not good enough, because that means data loss during conversion.
LISTAGG works with VARCHAR2 or RAW, so to simply put it, you need to convert ...
The standard approach to get rows N through M is to do something like
FROM (SELECT a.*, rownum rnum
FROM (SELECT emp_id, last_name
WHERE positionID in (1,3)
ORDER BY <<something>>) a
WHERE rownum <= 60) b
WHERE rnum > 50
Note that you need to ...
The answer is right there, in the data. (I assume that the time part is 00:00:00 in all of them):
The order by has ties. There is not one "first row" but 3 of them.
So, when the 2nd query restricts the result to one row, one of the 3 is - arbitrarily - chosen. It may not be the same row chosen the next time you run the same query!
Alter the session:
declare a number;
EXECUTE IMMEDIATE 'ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE';
select count(*) into a from item w where w.Item_num='MOH601' ;
From Oracle documentation-
When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.
SQL> conn system
SQL> select * from session_roles;
SQL> show user;
USER is "SYSTEM"
SQL> grant execute on dbms_lock to jay;
grant execute ...
The reason why your trigger does not work for users like SYS or SYSTEM is because they have the ADMINISTER DATABASE TRIGGER privilege.
The ADMINISTER DATABASE TRIGGER privilege allows you to create database-level triggers (server error, login, and logout triggers). It also allows you to log in regardless of errors thrown by a login trigger as a failsafe.