Amit- I don’t believe you can add a column anywhere but at the end of the table once the table is created. One solution might be to try this: CREATE TABLE MY_TEMP_TABLE AS SELECT * FROM TABLE_TO_CHANGE; Drop the table you want to add columns to: DROP TABLE TABLE_TO_CHANGE; It’s at the point you could … Read more
The difference is- A function must return a value (of any type) by default definition of it, whereas in case of a procedure you need to use parameters like OUT or IN OUT parameters to get the results. You can use a function in a normal SQL where as you cannot use a procedure in … Read more
If you want to be really robust about this then you ought to force the NLS setting to English and apply the “fill mode” format model FM for trimming leading and trailing spaces. If To_Char(DateC,’fmDAY’, ‘nls_date_language=english’) = ‘FRIDAY’ Then …
Well, Just worked it out. While having both installations we have two ORACLE_HOME directories and both have SQAORA32.dll files. While looking up for ORACLE_HOMe my app was getting confused..I just removed the Client oracle home entry as oracle client is by default present in oracle DB Now its working…Thanks!!
A check constraint follows the same syntax rules as conditions for a WHERE clause: alter table foo add constraint check_email check (REGEXP_LIKE(email,’your_regex_goes_here’,’I’)); More details in the manual: for Oracle 11 – http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions007.htm#SQLRF52141 for Oracle 12 – https://docs.oracle.com/database/121/SQLRF/conditions007.htm#SQLRF52141 Edit: There are however some restrictions on what you can actually use in a check constraint: Oracle 11 … Read more
You’re looking for USER_TAB_COLUMNS – all the columns, and their descriptions in the schema the query is executed in – or ALL_TAB_COLUMNS – the same except for all tables that user has permission to view. A typical query might be: select * from user_tab_columns where table_name=”MY_TABLE” order by column_id column_id is the “order” of the … Read more
On 11g and prior, create a sequence to increment the column via trigger. See Auto-increment primary key in Pre 12c releases (Identity functionality) For example, TABLE SQL> CREATE TABLE t ( 2 ID NUMBER(10) NOT NULL, 3 text VARCHAR2(50) NOT NULL); Table created. PRIMARY KEY to be populated by the sequence SQL> ALTER TABLE t … Read more
You need to query the data dictionary, specifically the USER_CONS_COLUMNS view to see the table columns and corresponding constraints: SELECT * FROM user_cons_columns WHERE table_name=”<your table name>”; FYI, unless you specifically created your table with a lower case name (using double quotes) then the table name will be defaulted to upper case so ensure it … Read more
You are exceeding the SQL limit of 4000 bytes which applies to LISTAGG as well. SQL> SELECT listagg(text, ‘,’) WITHIN GROUP ( 2 ORDER BY NULL) 3 FROM 4 (SELECT to_char(to_date(level,’j’), ‘jsp’) text FROM dual CONNECT BY LEVEL < 250 5 ) 6 / SELECT listagg(text, ‘,’) WITHIN GROUP ( * ERROR at line 1: … Read more
Many-to-many conditions should not be enforced using a trigger. Many-to-many conditions are enforced by creating a junction table containing the keys in question, which are then foreign-keyed back to the respective parent tables. If your intention is to allow many employees to be in a department, and to allow an employee to be a member … Read more