Learn PL SQL, Data Science and Automation

Below are some important code samples for those who are willing to learn Selenium automation. Driver Initialization WebDriver driver ; driver = new ChromeDriver() ; driver = new FirefoxDriver() ; driver = new SafariDriver() ; driver = new InternetExplorerDriver() ; driver = new EdgeDriver() ; // Driver Profiling //Firefox //Load Firefox System. setProperty ( "webdriver.firefox.bin" , "path/to/driver" ) ; FirefoxProfile profile = new FirefoxProfile() ; //load with add-on File file = new File( "path/to/add-on" ) ; profile.addExtension(file) ; //profile settings (setting up desired capabilities) DesiredCapabilities capabilities= new DesiredCapabilities() ; capabilities.setCapability(FirefoxDriver. PROFILE , profile) ; WebDriver driver = new RemoteWebDriver(capabilities) ; //Chrome Options System. setProperty ( "webdriver.chrome.driver" , "/path/to/chrome/driver" ) ; ChromeOptions options = new ChromeOption...

Logical Storage Blocks Data Block is the smallest unit of logical storage for a database object. Extents Extends is the next level of logical grouping in the database. An extent contains one or more database blocks. Segments The next level of logical grouping in a database in the segments. A segment is a group of extents from a database object that Oracle treats as a unit. Data Segments: Every table in the database resides in a single data segment, consisting of one or more extents. Index Segments:  Each index is stored in its own index segment. As with partitioned tables, each partition of a partitioned index is stored in its own segment. Temporary Segments:  When a user's SQL statement needs disk space to complete an operation such as a sorting operation that cannot fit in memory, Oracle allocates a temporary segment . Temporary segments exist only for the duration of SQL execution . Rollback Segments:   Rollback Segments only exists in the SYSTEM ...

Behavior Driven Development (BDD) is a development process that originally associated with Test-Driven Development (TDD).  BDD is written in a readable format in an understandable language for anyone involved in software development.  BDD Features Providing better readability and visibility.  Verifying the software against customer requirements.  Assure the implementation of the system is correct. Derives examples of different expected behaviors of the system. Uses examples as acceptance tests. Focus on customer requirements throughout the development. BDD Practice There are two practices in BDD:-  Specification by Example (SbE). Test-Driven Development (TDD). Specification by example (SbE) uses examples in conversation to illustrate the business rules and the behavior of the software.  This uses to have a better understanding for Business Analyst, Product Owners, Testers and the Developers to reduce the misunderstanding abou...

To Scrape Data using python we are using BeautifulSoup python Package !pip install beautifulsoup4 As a first step we have to import the packages and html page that we need to scrape. In here I have used some static HTML content which was customized to scrape the data. #imports import requests from bs4 import BeautifulSoup #html HTML Sample Doing Data Science with Python Doing Data Science with Python Author: Eranda Kodagoda This will help to perform various data science activitied using python Modules Title Duration in minutes Getting Started 20 Setting Up Environment 40 Extracting Data 30 Exploring and Processing Data 45 Building Productive Model 45 To View the HTML using beautifulsoup we can use below code-lines and execute on python executor from IPython.core.display import display, HTML display(HTML(html_string)) To Print the HTML using beautifulsoup we can use below code-lines and execute on python executor ps=Be...

Extracting data in a database using python. Using Python to extract data in a MySQL table first we need to pip install pymysql Create table along with connection to the mysql server using python import pymysql connection=pymysql.connect(host='host',port=port,user='user',password='password',db='schema') #creating cursor cursor= connection.cursor() #query for create Table TABLES={} TABLES['employees'] = ( "CREATE TABLE employees (" "PersonID int," "LastName varchar(255)," "FirstName varchar(255)," "Address varchar(255)," "City varchar(255)" ")") for name, ddl in TABLES.items(): cursor.execute(ddl) connection.commit() connection.close() Insert Data into the MySQL Table using Python import pymysql # Data set to insert insert_people=[("8","Perera","L.D","123",...

About Data Science with Python From this module I will post some interesting posts about Data science and how to gather information and process them using Python and for that I will be using below softwares  Python 3.6 Anaconda Jupyter Notebook PyCharm by JetBrains In the posts I will include fundamentals of Python  programming techniques such as lambdas, reading and manipulating csv files, and the numpy library. And In the posts I will be doing a simple Data Challange which is available on Kaggle and I will provide the URL's to the github repo which I will be posting the whole module once it finished. In Future I will be post some articles related to Applied Data Science with Python: Applied Plotting, Charting & Data Representation in Python, Applied Machine Learning in Python, Applied Text Mining in Python, Applied Social Network Analysis in Python

SMON - System Monitor  Performs recovery after instance failure, monitor temporary segments and extents clean those temporary extents and segments.  PMON - Process Monitor Failed process resources in shared server architecture monitor and restart any failed dispatcher or server process.  DBWn - Database Writer Database Writer process writes new or changed data blocks into the data-files which are in the buffer cache.  LGWR - Log Writer Log writer is in change of REDO Log buffer management log writer is one of most active processes until log writer success data commit will not be completed. ARCn - Archive Process Copies REDO Logs to one or more destinations. CKPT - Checkpoint Process This helps to reduce the amount of time required for instance recovery. During checkpoint updates the header of the control file and data-files to reflect the last successful  System Changed Number (SCN)  This automatically starts every time the ...

SGA - System Global Area SGA is a  group of shared memory area which is dedicated to oracle instance. Shared Pool Shared pool is dividing into two major areas which are : Library Cache :  Area which stores the SQL information such as select statements, cursors and execution plans. Data Dictionary Cache :  Which contains cache, user information and privileges, segments and extent information as well. Buffer Cache Buffer cache holds blocks of data from disk that have been recently read to satisfy a select statement or that contains modified data-blocks.  Redo Log Buffer Redo Log buffer holds the most recent changes to the data blocks in the data-files.    Large Pool This is an operational area of the SGA it is used for transactions that interact with more than one database. Java Pool Java Pool is used by the Oracle JVM ( Java Virtual Machine ) for all Java code & data within user session. Stream Pool Stream Pool is...

Login to server : sqlplus / as sysdba exp SYSTEM/password FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y or exp SYSTEM/password PARFILE=param.dat where param.dat contains the following information  FILE=dba.dmp   GRANTS=y   FULL=y   ROWS=y   LOG=dba.log To dump a single schema to disk. exp / FILE=dump.dmp OWNER=production Export Specific tables to disk exp SYSTEM/password FILE=export.dmp TABLES=(user1.table,user2.table) Export Data in one user  exp / FILE=export.dmp TABLES=(table1,table2) Using imp:   To import the full database exported in the example above. imp SYSTEM/password FULL=y FIlE=dba.dmp To import just the dept and emp tables from the scott schema imp SYSTEM/password FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp) To import tables and change the owner  imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager) To import just the scott schema exported in t...

DECLARE CURSOR C1 IS   SELECT ID FROM TABLE_NAME_1 WHERE COLUMN IN ('');   CURSOR C2     IS       SELECT ID FROM TABLE_NAME_2 WHERE COLUMN IN ('');              CURSOR C3 (CP_TABLE_01_ID NUMBER,CP_TABLE_02_ID NUMBER)         IS           SELECT COUNT(*) AS COUNT_UP           FROM TABLE_NAME_3           WHERE COLUMN_CONDITION_01=CP_TABLE_02_ID           AND COLUMN_CONDITION_02=CP_TABLE_01_ID; COUNT_UP NUMBER; BEGIN FOR R1 IN C1 LOOP     FOR R2 IN C2     LOOP          OPEN C3(R1.ID,R2.ID);        FETCH C3 INTO COUNT_UP;        CLOSE C3;               IF (COUNT_UP=0) THEN           INSERT           INTO TABL...

Sample String "ABCD123 ETS, NM" UPDATE TABLE_NAME SET COLUMN_NAME = SUBSTR( 'ABCD123 ETS, NM' , 0 , 4 ) || REPLACE ((SUBSTR(( REPLACE ( 'ABCD123 ETS, NM' , ', ' , '/' )), 5 ,INSTR(( REPLACE ( 'ABCD123 ETS, NM' , ', ' , ',/' )), ',' , 1 ) - 4 )), ' ' , '/' ) || REPLACE (SUBSTR( 'ABCD123 ETS, NM' ,INSTR( 'ABCD123 ETS, NM' , ',' , 1 ) + 2 ), 'NM' , 'NEW MEXICO' ) WHERE CONDITIONS;

WITH S AS ( /*SELECT QUERY*/ ) SELECT /*COLUMN NAMES*/ , LENGTH( FLOOR ( /*DECIMAL COLUMN*/ )) + NVL(LENGTH( RTRIM (SUBSTR(CN,INSTR(CN, '.' ) + 1 ), '0' )), 0 ) NUMBER_OF_DEGITS, NVL(LENGTH( RTRIM (SUBSTR(CN,INSTR(CN, '.' ) + 1 ), '0' )), 0 ) NUMBER_OF_DECIMALS FROM ( SELECT /*COLUMN NAMES*/ , TO_CHAR( /*DECIMAL COLUMN*/ , 'FM99999999999999999D99999999999999999' , 'NLS_NUMERIC_CHARACTERS = ''. ''' ) CN FROM S );

SELECT 'curl ' || 'https://www.testapp.net/test/SupportClass!actionClass.action ' || '-H ' || 'Origin: https://www.testapp.net ' || '-H ' || 'Accept-Encoding: gzip, deflate ' || '-H ' || 'Accept-Language: en-US,en;q=0.8 ' || '-H ' || 'User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.87 Safari/537.36 ' || '-H ' || 'Content-Type: application/x-www-form-urlencoded; charset=UTF-8 ' || '-H ' || 'Accept: */* ' || '-H ' || 'Referer: https://www.testapp.net/test/application.jsp ' || '-H ' || 'Cookie: JSESSIONID=5C30005FF6BF00A2FDD18A3C0E258115.app-2; JSESSIONID=QIPalPyhOP2D8HMlGSl7Uw**.node1; SSOID=be2bb104392d40c4a4fea96162906b10; pnctest=1 ' || '-H ' || 'Connection: keep-alive ' || '--data ' || '_operat...

REF CURSOR WILL BE DYNAMICALLY OPENS OR OPEN BASED ON A LOGIC. DECLARE TYPE C1 IS REF CURSOR ; CURSOR C IS SELECT * FROM DUAL; REF_CURSOR RC; BEGIN IF (TO_CHAR(SYSDATE, 'DD' ) = 30 ) THEN OPEN REF_CURSOR FOR 'SELECT * FROM TABLE1' ; ELSIF ( TO_CHAR(SYSDATE, 'DD' ) = 29 ) THEN OPEN REF_CURSOR FOR SELECT * FROM TABLE2; ELSE OPEN REF_CURSOR FOR SELECT * FROM DUAL; END IF ; OPEN C; END ;

To reset password need to log into system as sysdba user. command line or terminal #sqlplus sys as sysdba password SQL > SELECT * FROM DBA_PROFILES; > ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; This will keep you from expiring passwords hereafter SQL > SELECT 'ALTER USER "' || D.USERNAME || '" IDENTIFIED BY VALUES ''' || U.PASSWORD || ''';' C FROM DBA_USERS D, SYS. USER $ U WHERE D.USERNAME = UPPER ( '&&USERNAME' ) AND U. USER # = D. USER_ID ; SQL> ALTER USER "SYSTEM" IDENTIFIED BY VALUES '563CF0CCDBBB0FE4' ;