oracle

[Solved] How to upload any type of and any size of file in table of Oracle using C#? [closed]

by

//Here First you need to upload file on application server then convert into File Stream. string auditReportUploadDocLocation = ConfigurationManager.AppSettings[“AuditReportUploadDocLocation”].ToString(); string filelocation = Path.Combine(Server.MapPath(auditReportUploadDocLocation), fuUploadDoc.FileName); fuUploadDoc.PostedFile.SaveAs(filelocation); fileName = Path.GetFileName(fuUploadDoc.PostedFile.FileName); using (FileStream fs = new FileStream(filelocation, FileMode.Open, FileAccess.Read)) { objAudAuditReportMaster = objAudAuditReportBAL.UploadAuditReportDoc(fileName, fs, Session[“AudLoginID”].ToString(), audProcessName); } //Delete the file from application server. if (File.Exists(filelocation)) File.Delete(filelocation); //After that … Read more

[Solved] SELECT from table with three foregin keys to one table [closed]

by

Okay, it’s Saturday night and I’m feeling mellow enough to tackle this without a data model. You have given us the names of the three lookup tables (subjects, opinions, users) but not the actual structures and columns. So I’m making some guesses. select subjects.name as subject_name , opinions.value , o_users.name as opinion_guy , p_users.name as … Read more

[Solved] Using distinct for specific column in oracle

by

In your example the query returns distinct values for the combination of COLA and COLB. Examine the syntax: Note, that DISTINCT/UNIQUE/ALL can be only placed after SELECT and before of the first expression in the select list. The documentation says that:https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm DISTINCT | UNIQUE Specify DISTINCT or UNIQUE if you want the database to return … Read more

[Solved] SQL Join with unique rows

by

Ok, let’s see if this answer suits your request. SELECT a.EMPLID,a.DEDCD, to_char(a.EFFDT,’YYYY-MM-DD’) EFFDT, b.DEDCD as DEDCD2,GTN FROM ( select EFFDT,GTN,EMPLID,DEDCD, row_number() over (partition by EMPLID order by DEDCD) rn from table1 ) A LEFT OUTER JOIN ( select EFFDT,EMPLID,DEDCD, row_number() over (partition by EMPLID order by DEDCD) rn from table2 ) B ON ( A.EMPLID=B.EMPLID … Read more

[Solved] Filter out records that are not in this date format oracle

by

You can use an inline function to check if the date is valid. Like this: WITH FUNCTION is_valid_date (date_str_i VARCHAR2, format_i VARCHAR2) RETURN VARCHAR2 /* check if date is valid */ AS l_dummy_dt DATE; BEGIN SELECT TO_DATE(date_str_i,format_i) INTO l_dummy_dt FROM DUAL; RETURN ‘Y’; EXCEPTION WHEN OTHERS THEN RETURN ‘N’; END; dates AS ( SELECT ‘0201-05-31 … Read more

[Solved] SQL grouping on time interval

by

WITH changes AS ( SELECT “DATE”, CASE WHEN LAG( “DATE” ) OVER ( ORDER BY “DATE” ) + INTERVAL ‘5’ MINUTE = “DATE” THEN 0 ELSE 1 END AS has_changed_group FROM TEST ), grps AS ( SELECT “DATE”, SUM( has_changed_group ) OVER ( ORDER BY “DATE” ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS … Read more

[Solved] Oracle database multiple row -> c# [closed]

by

Let’s brush up your code: // Extract methods, don’t cram everything in OnClick private void FeedFriendsListBox() { string oracleDb = @”connection string”; //DONE: wrap IDisposable into using using (OracleConnection conn = new OracleConnection(oracleDb)) { conn.Open(); //DONE: Make Sql readable – format it out and use names for the parameter(s) string sql = @”SELECT NAME, ADDRESS … Read more

[Solved] Sql If statement error

by

I think you need: CREATE OR REPLACE TRIGGER CREAT_SERVIÇO BEFORE INSERT ON SERVIÇO FOR EACH ROW BEGIN if(:new.MORADA_RUA is NULL and :NEW.LOCAIS_ID_LOCAL is NULL) THEN RAISE_APPLICATION_ERROR(-20000, ‘YOU HAVE TO HAVE AN ADRESS OR AN LOCAL’); END IF; END; If your trigger is going do disallow inserting when both address and local are null. But to … Read more

[Solved] Format a number 000..999 [closed]

by

It really isn’t clear where your numbers are coming from. if you just want to generate a list on the fly to you can use the connect-by syntax: select to_char(level, ‘FM00’) from dual connect by level <= 10; 01 02 03 04 05 06 07 08 09 10 For 0 to 999 just change the … Read more

[Solved] IF COL_LENGTH(‘EMP_NUM’,’EMPLOYEE’) IS NOT NULL – EQUIVALENT IN ORACLE [closed]

by

In Oracle, you have to use dynamic SQL as you can’t execute DDL in PL/SQL as is. Why PL/SQL? Because IF-THEN-ELSE is PL/SQL, not SQL. Presuming this is part of your PL/SQL procedure, you’d then if col_length(‘EMP_NUM’, ‘EMPLOYEE’) is not null then execute immediate ‘alter table employee modify emp_num not null’; end if; That’s for … Read more