Oracle Query to Get Database Information like Version, db size, avilable size, file location

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) - 

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"

, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"

from    (select bytes

from v$datafile

union all

select bytes

from v$tempfile

union all

select bytes

from v$log) used

, (select sum(bytes) as p

from dba_free_space) free

group by free.p;

SELECT version FROM V$INSTANCE; --11.2.0.1.0

SELECT * from PRODUCT_COMPONENT_VERSION; -- Get detailed version

SELECT * from dba_data_files ;  --Get Physical file

SELECT * from V$SESSION Where Status='KILLED'; -- Get Oracle connected sessio list

SELECT * from V$SQL ;-- Get All Executed Query details

GRPC Client and Service Service/API Creation on Asp.net, C#

GRPC service is work when user/developer don't want make customer to ideal when during long data pulling from API/Rest server. on this case ideally on websites we shows a loader or ask for wait to customer. Tippically on webservice/ API/ Rest all client services will not respond untill server side process complete. but on gRPC there is an advantage we can get the data into chunk. 

On gRPC when data is iterating on server side still we can get the row by row/ chunk of data recevied from gRPC service and that application can shows on front end.

I learned the same on you tube "https://www.youtube.com/watch?v=CcbwRBT8vnI"

Sample Code i have prepared for my testing purpose and uploaded the same on my drive. Click here to download if readmade code you want.

Oracle DB Query tips

1.  Get Table Size and Rows Details

    select * from user_tables where table_name = 'TBL_XXXXXXXX'

2.

Read Excel Data From Oracle Database

 using System.Data.OleDb;

protected DataSet ReadExcel()

        {

            // Create a DataSet which will hold the data extracted from the worksheet.

            DataSet ds = new DataSet();

            string xls = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\murdhusi\Desktop\Mails\Data_29062022.xlsx;Extended Properties=Excel 8.0";

            string xlsx = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\murdhusi\Desktop\Mails\Data_29062022.xlsx;Extended Properties=Excel 12.0";

            string connString = xlsx;

            // Create the connection object

            OleDbConnection oledbConn = new OleDbConnection(connString);

            try

            {

                // Open connection

                oledbConn.Open();

                // Create OleDbCommand object and select data from worksheet Sheet1

                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Export Worksheet$]", oledbConn);

                // Create new OleDbDataAdapter

                OleDbDataAdapter oleda = new OleDbDataAdapter();

                oleda.SelectCommand = cmd;

                // Fill the DataSet from the data extracted from the worksheet.

                oleda.Fill(ds, "Employees");

                return ds;

            }

            catch

            {

            }

            finally

            {

                // Close connection

                oledbConn.Close();

            }

            return ds;

        }

NO Data Found on Oracle OR ORA-01403:

While Insert data into another varible some case if datanot available then we get an error on Oracle so take care that case we need to write a query like this.

Sample Procedure

 create or replace

PROCEDURE "SPROC_VAULT_TEMP" 

( 

  l_RECORDSET OUT TYPES.cursor_type,

  l_ACTION in varchar2    DEFAULT  null,

  l_Vault_No_New in varchar2  DEFAULT  null,

  l_Vault_TKN_No in varchar2  DEFAULT  null

)

As 

l_PanNumber varchar2(100) default '';

l_Vault_No_Old varchar2(100) default '';

Begin

  If l_ACTION = 'SelectById' Then 

  

    BEGIN

      Select pan_number, vault_ref_aadhaar into l_PanNumber, l_Vault_No_Old from tbl_step1 

      Where vault_ref_aadhaar_old is null And uid_token = l_Vault_TKN_No;

    Exception

    WHEN TOO_MANY_ROWS  then  OPEN l_RECORDSET FOR Select 'Too Many Rows in that' from dual;

    WHEN NO_DATA_FOUND then OPEN l_RECORDSET FOR Select 'No Data Found' from dual;

    WHEN others then OPEN l_RECORDSET FOR Select 'No Other Issue' from dual;

    END;

    if l_PanNumber IS NOT NULL then

      Update tbl_step1 Set vault_ref_aadhaar = l_Vault_No_New, VAULT_REF_AADHAAR_OLD = l_Vault_No_Old 

        Where uid_token is not null And uid_token = l_Vault_TKN_No;

      Update tbl_aadhar Set UID_ID = l_Vault_No_New , UID_ID_OLD = UID_ID 

        Where pannumber = l_PanNumber;

      Commit;

      

      OPEN l_RECORDSET FOR  Select 'True' from Dual;

    Else

      OPEN l_RECORDSET FOR 

      Select l_Vault_TKN_No ,l_PanNumber from dual;  

    end if;

    

  end if;

End;

Sample Source Code (.Net)

dsMain = new DataSet();

            if (Connection.State == ConnectionState.Closed)

            {

                Connection.Open();

            }

            OracleCommand cmdMain = new OracleCommand("SPROC_VA", Connection);

            cmdMain.CommandType = CommandType.StoredProcedure;

            cmdMain.Parameters.Add("l_RECORDSET", OracleType.Cursor);

            cmdMain.Parameters["l_RECORDSET"].Direction = ParameterDirection.Output;

            cmdMain.Parameters.Add("l_ACTION", OracleType.VarChar, 100);

            cmdMain.Parameters["l_ACTION"].Value = "SelectById";

            cmdMain.Parameters.Add("l_Vault_No_New", OracleType.VarChar, 100);

            cmdMain.Parameters["l_Vault_No_New"].Value = l_Vault_No_New;

            cmdMain.Parameters.Add("l_Vault_TKN_No", OracleType.VarChar, 100);

            cmdMain.Parameters["l_Vault_TKN_No"].Value = l_Vault_TKN_No;

            OracleDataAdapter daMain = new OracleDataAdapter(cmdMain);

            daMain.Fill(dsMain);

Find a text in Database, Find string in database/all tables in SQL server

Below Query to search text in available procedures

SELECT name

FROM   sys.procedures

WHERE  Object_definition(object_id) LIKE '%MY_Search_String%'

This Procedure create a temp table and looking for searched string into all available tables

CREATE PROCEDURE usp_find_string_intables (@string AS VARCHAR(1000))
AS
BEGIN
	DECLARE @mincounter AS INT
	DECLARE @maxcounter AS INT
	DECLARE @stmtquery AS VARCHAR(1000)

	SET @stmtquery = ''

	CREATE TABLE #tmp (
		tablename VARCHAR(128)
		,columnname VARCHAR(128)
		,rowid INT identity
		)

	CREATE TABLE #tablelist (
		tablename VARCHAR(128)
		,columnname VARCHAR(128)
		)

	DECLARE @tmp TABLE (name VARCHAR(128))
	DECLARE @tablename AS VARCHAR(128)
	DECLARE @columnname AS VARCHAR(128)

	INSERT INTO #tmp (
		tablename
		,columnname
		)
	SELECT a.name
		,b.name AS columnname
	FROM sysobjects a
	INNER JOIN syscolumns b ON a.name = object_name(b.id)
	WHERE a.type = 'u'
		AND b.xtype IN (
			SELECT xtype
			FROM systypes
			WHERE name = 'text'
				OR name = 'ntext'
				OR name = 'varchar'
				OR name = 'nvarchar'
				OR name = 'char'
				OR name = 'nchar'
			)
	ORDER BY a.name

	SELECT @maxcounter = max(rowid)
		,@mincounter = min(rowid)
	FROM #tmp

	WHILE (@mincounter <= @maxcounter)
	BEGIN
		SELECT @tablename = tablename
			,@columnname = columnname
		FROM #tmp
		WHERE rowid = @mincounter

		SET @stmtquery = 'select top 1  ' + '[' + @columnname + ']' + ' from ' +

                     '[' + @tablename + ']' + ' where ' + '[' + @columnname + ']' +

                     ' like ' + '''%' + @string + '%''';

		INSERT INTO @tmp (name)
		EXEC (@stmtquery)

		IF @@rowcount > 0
			INSERT INTO #tablelist
			VALUES (
				@tablename
				,@columnname
				)

		SET @mincounter = @mincounter + 1
	END

	SELECT *
	FROM #tablelist
END

FInd String in Database 

EXEC usp_find_string_intables 'Search Sring Place here'

Create Procedure for Pagination SQL Query

--==============================

Create Table Query

CREATE TABLE [dbo].[Student]

(

    Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,

    Name varchar(50) NOT NULL,

    Address varchar(100) NULL,

    Age int NOT NULL,

    Standard varchar(10) NOT NULL,

    [Percent] decimal(5, 2) NOT NULL,

    AddedOn datetime NOT NULL DEFAULT GETDATE(),

    Status bit NOT NULL

)

--==============================

Create Procedure for Insert Data

CREATE PROCEDURE [dbo].[sp_InsertStudent]   

    @Name       VARCHAR(50), 

    @Address        VARCHAR(100),   

    @Age        INT,

    @Standard     VARCHAR(10),

    @Percent        DECIMAL(5,2),

    @Status       BIT, 

    @Result     VARCHAR(50) OUTPUT,

    @CreatedId    INT OUTPUT     

AS           

BEGIN

    INSERT INTO Student(Name,Address,Age,Standard,[Percent],Status) VALUES (@Name,@Address,@Age,@Standard,@Percent,@Status) 

    SET @Result='Insert Successful'

    SET @CreatedId=@@IDENTITY

END

--===============================

CReate Procedure to get Student by Id

CREATE PROCEDURE [dbo].[sp_GetStudentById]  

    @Id           INT

AS           

BEGIN

    SELECT * FROM Student WHERE id=@Id

END

---=======================

CReate Procedure for Pagination wise data for Performance

CREATE PROCEDURE [dbo].[sp_GetStudent] 

    @PageNo       INT,

    @PageSize       INT

AS           

BEGIN

    DECLARE @qry  NVARCHAR(1000);   

    SET @qry='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY AddedON DESC) AS ''RowNum'',* FROM Student WHERE 1=1) a WHERE a.RowNum BETWEEN ('+CAST(@pageNo AS NVARCHAR(5))+'-1)*'+CAST(@pageSize AS NVARCHAR(5))+'+1 AND ('+CAST(@pageNo AS NVARCHAR(5))+'*'+CAST(@pageSize AS NVARCHAR(5))+')'

    EXEC Sp_executesql @qry

END