Ciugk

Clash Royale CLAN TAG #URR8PPP compiler error while creating trigger in sql developer; I have two tables: create table superheroes('sh_name varchar2(30)); create table sh_audit(new_name varchar2(30), old_name varchar2(30), username varchar2(30), entry_date varchar2(30), operation); trigger: create or replace trigger SUPERHEROES_AUDIT before insert or update or delete on superheroes for each row enable declare v_user varchar2(30); v_date varchar2(30); begin select user, TO_CHAR(SYSDATE,' DD/MM/YYYY HH24:MI:SS') INTO v_user, v_date from dual; if inserting then insert into sh_audit(new_name, old_name, username, entry_date, operation) values(:NEW.sh_name, null, v_user, v_date,'insert'); elsif deleting then insert into sh_audit(new_name, old_name, username, entry_date, operation) values(null, :OLD.sh_name, v_user, v_date,'delete'); elsif updateing then insert into sh_audit(new_name, old_name, userna...

Clash Royale CLAN TAG #URR8PPP ORA-06502 error for casting string to number (PL/SQL) I have variable: suma NUMBER(7,2) := 0; I want to execute the following process: suma := suma+cast(substr(jmbg,i,1) as number)*7; But when I run this process it shows the following error message: ORA-06502: PL/SQL: numeric or value error: character to number conversion error. It has problem with this line, so I have tried run this process with TO_NUMBER functions, but it did the same. How can I convert the substring to number without error message? You have to know the format in which the string represents the number; for example, does the string contain leading or trailing zeros? how many decimal digits, what is the decimal separator?, ... Once you know these things, we can find the appropriate format mask to use the to_number – Aleksej 56 mins ago ...

Clash Royale CLAN TAG #URR8PPP Can I use a PL/SQL trigger to check category and concat to a incremental number based on category? I got a productID - P0001KTC and P0001DR. productID If product category is kitchen, I will assign a productID - PROD001KTC , else if the category is dining room, then the productID should be PROD001DR . productID - PROD001KTC productID PROD001DR Is it possible to write a sequence inside a trigger to check the product category and assign an id as mentioned above? if there is another living room category product inserted then the id will be PROD001LR. give at least one example on table. – Sanjay Radadiya Jul 31 '16 at 5:25 Which version of Oracle are you on? – trincot Jul 31 '16 at 5:42 ...

Clash Royale CLAN TAG #URR8PPP XML illegal characters (XMLTYPE parsing) The following are usually mentioned as illegal XML characters: < > & ' " In PLSQL, when I convert an element having a VARCHAR2 value with the above characters to XMLTYPE, only < and & are reported as causes for XML non-well-formedness. 5 XML documents below, only the first (containing the less than mark) and the last one (containing the ampersand) are being flagged down as not-well formed. Is this as expected? --------------------------------- <?xml version="1.0" encoding="utf-8"?> <Request> <param1>test<message<param1> </Request> --------------------------------- <?xml version="1.0" encoding="utf-8"?> <Request> <param1>test>message<param1> </Request> --------------------------------- <?xml version="1.0" encoding="utf-8"?> <Request> <param...

Clash Royale CLAN TAG #URR8PPP Find the length of the longest row in a column in oracle Does anybody know if there is a way to find what the length of the longest row in a column in Oracle? Basically I need to get the length of the longest row and then use that length plus 1 with SUBSTR to make the output of the column one character longer than the longest string. SUBSTR Thanks EDIT: Thanks for the advice. However, the MAX(LENGTH(column_name)) AS MAXLENGTH approach gives me the number I want but when I try to use it with SUBSTR(column_name,1, MAXLENGTH) I get an invalid identifier error. MAX(LENGTH(column_name)) AS MAXLENGTH SUBSTR(column_name,1, MAXLENGTH) SO I made a function to return the numberI wanted then used: SUBSTR(column_name,1,maxlengthfunc) This gave me the following output: SUBSTR(NAME,1,MAXLENGTHFUNC) Rather than: SUBSTR(NAME, 1, 19) And it didn't shrink the output column size like I needed. Also RTRIM(name)||' ' didn't do anything for me in SQL develop...

Clash Royale CLAN TAG #URR8PPP Check for distinct values in a row - sql Given a table with columns (col1,col2,col3,col4,....) , how do we query the table such that it returns only the rows where a specific subset of columns, for example (col2,col4,col5) have values different from each other. For example for this table, define subset of columns as (t1_id, t2_id, t3_id): The query should then return this: The subset of columns will be variable and can be very large hence using sth like where t1.id<>t2.id and t1.id<>t3.id and t2.id<>t3.id wouldn't be a handy approach. where t1.id<>t2.id and t1.id<>t3.id and t2.id<>t3.id 1 Answer 1 A simple solution could be to use NOT IN criteria for N-1 columns. Which can be shortened for each additional NOT IN. For example if there are 5 columns : WHERE t1.id NOT IN (t5.id, t4.id, t3.id, t2.id) AND t2.id NOT IN (t5.id, t4.id...

Clash Royale CLAN TAG #URR8PPP generating conditional combinations in sql I have the following table: ╔═══╦══════════════╦═════════════╗ ║ ║id ║name ║ ╠═══╬══════════════╬═════════════╣ ║ ║ 1 ║a1 ║ ║ ║ 1 ║b1 ║ ║ ║ 2 ║b2 ║ ║ ║ 3 ║c1 ║ ║ ║ 2 ║c2 ║ ║ ║ 4 ║a2 ║ ╚═══╩══════════════╩═════════════╝ I have the below query which does the following: For input (a,b,c) it returns all possible combinations of the form (aX,bX,cX) where X is anything present after "a/b/c" in the records. (a,b) generates (a1,b1) , (a1,b2) according to my table. Running this query select t1.id as t1_id, t1.name as t1_name, t2.id as t2_id, t2.name as t2_name, t3.id as t3_id, t3.name as t3_name, from (select * from table where name like 'a%') as t1 cross join (select * from table where name like 'b%') as t2 cross join (select * from table ...

Clash Royale CLAN TAG #URR8PPP SQL Query Or Pl/SQL program output in Different format I have a requirement to get the emp no and salary in different format. EX: EMP_NO,FIRST_NAME,LAST_NAME,START_DATE,GENDER,JOB_ID,DEPT_NO,DES,SALARY,CREATION_DATE INSERT INTO emp VALUES (2,'Jay','Red','21-MAR-01','M',100,10,'',12500','13-APR-17'); INSERT INTO emp VALUES (3,'Neel','Sai','11-JAN-00','M',100,10,'user',12500','13-APR-17'); INSERT INTO emp VALUES (5,'Ravi','','','M',100,10,'',10000','13-APR-17'); INSERT INTO emp VALUES (6,'Maya','V','23-JUL-18','F',101,20,'Tester',23000','23-JUL-18'); INSERT INTO emp VALUES (7,'Rush','Shig','23-JUL-18','M',101,20,'Ast Manager',25000','23-JUL-18'); INSERT INTO emp VALUES (8,'Mark','Philip...

Clash Royale CLAN TAG #URR8PPP Oracle pl/sql: executing dynamic delete within a transaction I need to delete one or more row from list of tables stored in a table, and commit only if all deletion succeed. So I wrote something like this (as part of a bigger procedure): BEGIN SAVEPOINT sp; FOR cur_table IN (SELECT * FROM TABLE_OF_TABLES) LOOP EXECUTE IMMEDIATE 'DELETE FROM ' || cur_table.TABNAME || ' WHERE ID = :id_bind' USING id; END LOOP; EXCEPTION WHEN OTHERS THEN ROLLBACK TO SAVEPOINT sp; END; I know this couldn't work, because of the "execute immediate". So, what is the correct way to do that? what is the issue? are you having an error, a wrong result, ... ? – Aleksej 1 hour ago I haven't tried yet. I guess "execute immediate...

How to generate timeseries data into table in Oracle PL/SQL between two date using interval 1 minute? I am new in this topic. In my setup Oracle 12c Standart Edition database and PL/SQL Developer. I have a table which has the following structure: int id; timestamp time_mon; double price; I need to insert data into a table which generate with some period such as 02.01.2018 00:00 - 02.01.2019 00:00 with an interval of 5 minutes. I cannot cope with this task. Please help me and show the SQL code example. 1 Answer 1 Assuming you mean 02.01.2018 = 2 January, this could be a way: select date '2018-01-02' + interval '5' minute * (level -1) from dual connect by date '2018-01-02' + interval '5' minute * (level -1) <= date '2019-01-02' By clicking "Post Your Answer", you acknowledge that you have read our updated ...