Ciugk

Clash Royale CLAN TAG #URR8PPP How to count unique rows in Oracle I have an oracle database table with a lot of columns. I'd like to count the number of fully unique rows. The only thing I could find is: SELECT COUNT(DISTINCT col_name) FROM table; This however would require me listing all the columns and I haven't been able to come up with syntax that will do that for me. I'm guessing the reason for that is that this query would be very low performance? Is there a recommended way of doing this? Possible duplicate of SQL - how to count unique combination of columns – Organic Advocate Feb 15 at 22:40 5 Answers 5 How about SELECT COUNT(*) FROM (SELECT DISTINCT * FROM Table) Please format code to aid readability. This ...

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 how to fetch 1 row only without using loop for php oracle $stid = oci_parse($conn, ' SELECT def_usr,status,subject,type,site,def_date,VER_DATE,VER_USR,closed_date,problem,hd_no,solution,attachment FROM hdr_web where hd_no=:num2'); oci_bind_by_name($stid, ":num2",$no); oci_execute($stid); $count = 0; while (($row = oci_fetch_row($stid)) != false) { $def_usr=$row[0]; $status=$row[1]; $subject=$row[2]; $type=$row[3]; $site=$row[4]; $def_date=$row[5]; $ver_date=$row[6]; $VER_USR=$row[7]; $closed_date=$row[8]; $problem=$row[9]; $hd_no=$row[10]; $soluti...

Clash Royale CLAN TAG #URR8PPP Altova MapForce adding tables from multiple Oracle database schemas How to add tables from multiple Oracle database schemas in Altova MapForce application? I have inserted database on Mapping workspace and selected one table from schema user "AAA" which I used for connection. When I right click on database object and select Add/Remove Tables it opens new window "Add/Remove Tables" and it is showing tables only for schema user "AAA" which I used for connection. In that window "Add/Remove Tables" I can't find the way to select new database schema, for example to select schema user "BBB", and add table from that schema. There is some green user icon when clicked it is showing all database users but when I select one, for example "BBB" schema user, nothing happens, the window is still showing tables from schema user "AAA". 1 Answer ...

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 Separate values of one column into multiple columns based on other column I have a table like this with only two columns: Table: Employees +----------+-------------+ |Employee | Designation | +----------+-------------+ | Ron | Manager | | James | HR | | Toby | Clerk | | Amanda | Clerk | | Jenny | Manager | | Quentin | HR | | Roger | Manager | | Harry | Clerk | | Sunny | Clerk | | Rachael | Manager | +----------+-------------+ I need to write a query to output results like this: +-----------+--------- +------------+ | Manager | HR | Clerk | +-----------+----------+------------+ | Jenny | James | Amanda | | Rachael | Quentin | Harry | | Roger | null | Sunny | | Ron | null | Toby | +-----------+----------+------------+ The employees must be separated into different column based on their designation and ordered al...

Clash Royale CLAN TAG #URR8PPP How can I apply condition to Insert data in Oracle Forms 6i I have table DOC_CUST_PRODUCT (DOC_CODE, CUST_CODE, P_CODE) DOC_CUST_PRODUCT (DOC_CODE, CUST_CODE, P_CODE) I want to restrict insertion and show message when DOC_CODE has more than 5 different CUST_CODE DOC_CODE CUST_CODE SELECT COUNT(DISTINCT CUST_CODE) INTO Y FROM BP_DOC_CUST_PRODUCT WHERE DOC_CODE = :DOC_CODE; IF NVL(Y,0) > 4 THEN MESSAGE('Sorry, Can Not Entry More Than 5 Chemist...'); MESSAGE('Sorry, Can Not Entry More Than 5 Chemist...'); but it doesn't work. Start by fixing your keyboard. Its shift key seems to be blocked. – JB Nizet 3 hours ago Writing all uppercase is considered SHOUTING and rude. But more importantly your question lacks a lot of information. Please read: stackoverflo...

Clash Royale CLAN TAG #URR8PPP Escaping New Line character within SQL query I am reading a table using my java code and creating a csv file out of it. So the 4 rows that I have get converted similar to below - sam , 18 , banker , He likes to play football jam , 28 , hacker , he likes nothing However in certain cases when the last varchar2 column contains n it becomes like this sam , 18 , banker , He likes to play football jam , 28 , hacker , he likes nothing When I try to read the file , each line is read one at a time and I'm not able parse the data due to few words being pushed to subsequent lines. Is there a way to escape the new line character within the column in my query to make it into a single line? My sql query select name , age , job , hobbies from person_details I am using csvwriter to generate the csv file - CSVWriter csvWriter = new CSVWriter(new FileWriter(results), DELIMITER, Character.M...

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 Oracle session timezone: Can Oracle DB session convert java.sql.Date to correct timezone? We have an audit table( Columns/Types: ID/Number,.. Audited_Date/Date) which logs audit entries using prepared statements. Until now, for different contexts we set the database session timezone for the connection, after which we were using the CURRENT_DATE attribute for the audited_date column. THIS MEANT THAT THE DATE INSERTED INTO THE COLUMN IS IN THE TIMEZONE OF THE CONNECTION WHICH IS IMPORTANT. Now, we have a new requirement to add different dates based on the supplied timestamps for the audit logs. Similar to the previous approach where the auditing engine didn't have to worry about the timezone, is there a way to set the date for the column, WITHOUT having to do something like this: TimeZone timeZone = TimeZone.getTimeZone(timezone); calendar.setTimeZone(timeZone); preparedStatement.setDate(4, new java.sql.Date(userTimestampMillis), calendar); I would real...

Clash Royale CLAN TAG #URR8PPP No connection available in pool, netstat shows non-zero values in Recv-Q I have an old Java application working on java 1.6 on Tomcat 6. Due to way how it is set up in the environment, it is hard to do any inner diagnostics - basically I can't touch it so - it is a blackbox. The app is crashing due to lack of free connections. The limits are set high (max 255 parallel connections) but, even if the number of open connections is like 60, it is still crashing. Netstat shows that there are a lot of data in recvQ (just an example): tcp 1464 0 localhost:7076 remote-host1:3120 ESTABLISHED tcp 2512 0 localhost:7611 remote-host2:3120 ESTABLISHED tcp 6184 0 localhost:4825 remote-host3:3120 ESTABLISHED I couldn't find any useful hints about the case (similar issue is here: https://serverfault.com/questions/672730/no-connection-available-in-pool-netstat-recvq-shows-high-number). The questions: 1) Why the application is not reading all ...

Clash Royale CLAN TAG #URR8PPP Passing an ArrayList of Java Objects While Calling a Stored Procedure Using Spring JDBC What is the Right approach for passing an arraylist of java objects to a stored procedure. Internally the Stored procedure expects a collection of a particular data type . Currently i am trying the following but it is not working example : //Setting the JdbcCall Object SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate) .withProcedureName("name") .withCatalogName("catalog") .withoutProcedureColumnMetaDataAccess() .declareParameters(new SqlParameter( "input_parameter1", Types.ARRAY, "SAMPLELIST"), new SqlOutParameter("P_ERROR_MESSAGE" , Types.VARCHAR)); //Creating the list of objects to be sent to proc List<SampleObject> list = new ArrayList<>...

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

Clash Royale CLAN TAG #URR8PPP 12.1.3. Oracle OAF. Get selected rows from Master-Detail tables I have two advanced tables with Master-Detail relationship. These tables connected by ViewLink. Also I have a Singe selector on Master level. I need to get selected record from Master and Details tables. enter image description here I know how to get the Master row in particular method XxOcmMatchHeaderVOImpl voHeader = getXxOcmMatchHeaderVO1(); Row rowSelect = voHeader.getFilteredRows("SelectFlag", "Y"); But how can I get rows from Detail table? By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Clash Royale CLAN TAG #URR8PPP retrieve row from multiple row of table in oracle I want to retrieve data from three table for example Table_1 : NAME_A Table_2 : NAME_B Primary_key PD_ID,EV_N Table_3 : NAME_C Primary key PD_ID Required Output Hi, it’s an example - you should add the logic to the question... too hard to understand what you are trying to do... it seems that you need to join the tables while taking the rownum into account? – cohenjo 17 mins ago So what determines the order of records from Table_3 ? It's not controlled by any of the posted columns. – APC 14 mins ago Table_3 What is your attempt so f...