py0019 : Color code in idel Python Shell

When you write a code in IDLE python shell, you may observe 4 different colors, yes these colors have some meaning:

• Purple: It shoe that you have typed a command in python shell
• Green: Some content sent to command
• Blue: Any output from command will be BLUE in color
• Black: not a command

What are commands: Print/quit are commands in python. Command generally written with round parenthesis i.e. print(), quit()

Function to Find Valid Invalid Date Format Oracle

Some times we need to check data inside the column if it is in proper shape or proper format. Like some of database saving date data in side of varchar field. which is wrong, if could have invalid date format or can have bad data in terms of date, month, year.

Here i am going to provide you the example of function to check date is properly saved in varchar column, regardless of any date format. Function will check for all combination of date type format and will return valid or invalid i.e. atleast one format is correctly saved in column.

create function datetype1(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'DD-MON-YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype2(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'DD-MON-YY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype3(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'DD-MM-YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype4(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'MM-DD-YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype5(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'YYYY-MM-DD') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype6(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'DD/MM/YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype7(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'MM/DD/YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype8(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'YYYY/MM/DD') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/



CREATE OR REPLACE Function DateFieldValidInvalid
   ( field_v in varchar2)
   RETURN varchar2
IS
   cnumber varchar2(2000);
v_query1 varchar2(4000);

BEGIN
v_query1 := 'select a from (select datetype1(''' || field_v||''') a from dual union ' ||
'select datetype2(''' || field_v||''') from dual union ' ||
'select datetype3(''' || field_v||''') from dual union ' ||
'select datetype4(''' || field_v||''') from dual union ' ||
'select datetype5(''' || field_v||''') from dual union ' ||
'select datetype6(''' || field_v||''') from dual union ' ||
'select datetype7(''' || field_v||''') from dual union ' ||
'select datetype8(''' || field_v||''') from dual) where a = ''Valid''' ;
 
execute immediate v_query1 into cnumber;

RETURN cnumber;
END;


select DateFieldValidInvalid(created_ON) from t_contact ;

Row Count of Each Table

Is it easier way to find row count of each table in SQLSERVER 

Yes, earlier i have added post which is custom dynamic tsql to get row count of each table in sqlserver. Even SQLServer system tables itself store the result of each row count of all tables of all databases.

It can be easily identify using sys.tables and partition table. This is not a innovative but it is just a smartness at your work

SELECT 

SCHEMA_NAME(schema_id),

t.name AS Table_Name,

SUM(p.[rows]) AS [TotalRowCount]

FROM 

sys.tables t

JOIN sys.partitions p ON t.object_id = p.object_id

AND p.index_id IN ( 0, 1 )

GROUP BY SCHEMA_NAME(schema_id), t.name

having 

SUM(p.[rows]) > 1

to get all tables remove having clause from above query you will be able to see the result of each table. Right now it is returning the only those tables whose result is more than 1.

print row count in begin try on delete

I was executing below section of code on sqlserver

SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN

select count(*) from my_table

delete from my_table where key = 1

COMMIT
PRINT 'Success!'
END TRY
BEGIN CATCH
ROLLBACK
PRINT 'Fail'+ ERROR_MESSAGE()
END CATCH
SET NOCOUNT OFF

Here my code is able to show the count of Select query but not able to show the result of delete query in result window.

Requirement:
To show the result of delete in query window.

Solution :
Remove SET NOCOUNT ON and SET NOCOUNT OFF from your block, you will be able to see the delete result.

Count of Each Table Oracle

small and simple plsql script is created to find count of each table in oracle database for one schema.
This PLSQL can execute either on SQL Developer or SQLPlus editor. It's a small plsql used cursor and for loop and concept of dynamic query execution in Oracle.

See below code

set serveroutput on
declare
    
    cursor c2 is select * from user_tables;
    v_sql1 varchar(4000);
    v_count number;
begin
   for ii in c2 loop
    v_sql1 := 'select count(*) from '|| ii.table_name;
    
    execute immediate v_sql1 into v_count;
    dbms_output.put_line(ii.table_name ||'-'|| v_count);
   end loop;
end;
/