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
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
try this select * from ( select sal ,dense_rank() over (order by sal desc) ranking from table ) where ranking = 4 — Replace 4 with any value of N
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 …
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
If you want to add N days to your days. You can use the plus operator as follows – SELECT ( SYSDATE + N ) FROM DUAL;
It can also happen with varchar2 columns. This is pretty reproducible with PreparedStatements through JDBC by simply creating a table with a column of varchar2 (20 or any arbitrary length) and inserting into the above table with a row containing more than 20 characters So as above said it can be wrong with types, or … Read more
A stored function(or procedure) might be created in order to create a SQL for Dynamic Pivoting, and the result set is loaded into a variable of type SYS_REFCURSOR : CREATE OR REPLACE FUNCTION Get_Categories_RS RETURN SYS_REFCURSOR IS v_recordset SYS_REFCURSOR; v_sql VARCHAR2(32767); v_cols_1 VARCHAR2(32767); v_cols_2 VARCHAR2(32767); BEGIN SELECT LISTAGG( ””||”level”||”’ AS “‘||”level”||'”‘ , ‘,’ ) WITHIN … Read more
PIVOT should work fine – SQL Fiddle demo (schema borrowed from bluefeets answer) SELECT * FROM source PIVOT ( MIN(org) AS org, MIN(position) AS position FOR lang IN(‘EN’ AS en, ‘FI’ AS fi, ‘SV’ AS sv) );