impact analysis with pl/scope - s3.amazonaws.com · in my demo.zip file from the pl/sql learning...

Copyright©2015Oracleand/oritsaffiliates.Allrightsreserved.|

Impact Analysis with PL/Scope

1

StevenFeuersteinOracleDeveloperAdvocateforPL/SQL

OracleCorporation

Email:[email protected]:@sfonplsql

Blog:stevenfeuersteinonplsql.blogspot.comYouTube:PracticallyPerfectPL/SQL

Copyright©2017Oracleand/oritsaffiliates.Allrightsreserved.| Page2

ResourcesforOracleDatabaseDevelopers• OfficialhomeofSQLandPL/SQL-oracle.com/sqloracle.com/plsql•  SQL-PL/SQLdiscussionforumonOTNhttps://community.oracle.com/community/database/developer-tools/sql_and_pl_sql

•  PL/SQLandEBRblogbyBrynLlewellyn-https://blogs.oracle.com/plsql-and-ebr

• OracleLearningLibrary-oracle.com/oll• Quizzes,workoutsandclasses-devgym.oracle.com•  AskTom-asktom.oracle.com–'nuffsaid(+new!OfficeHours!)•  LiveSQL-livesql.oracle.com–scriptrepositoryand12/712cdatabase•  oracle-base.com-greatcontentfromTimHall•  oracle-developer.net-greatcontentfromAdrianBillington


PL/Scope• Addedin11.1,compiler-driventoolthatcollectsinformationaboutidentifiersandstatements,andstoresitindatadictionaryviews.

• UsePL/Scopetoanswerquestionslike:– Whereisavariableassignedavalueinaprogram?– Whatvariablesaredeclaredinsideagivenprogram?– Whichprogramscallanotherprogram(thatis,youcangetdowntoasubprograminapackage)?

– Findthetypeofavariablefromitsdeclaration.

• Andwith12.2,youcannowalsoanalyzeSQLstatementsinPL/SQL.Allreferencedcodeisavailableatlivesql.oracle.comand

inmydemo.zipfilefromthePL/SQLLearningLibrary:oracle.com/oll/plsql.


LifeWithoutPL/Scope• PriortoPL/Scope,analyzingimpactmostlymeanttextsearchesthroughfiles,orqueriesagainstALL_SOURCEandALL_DEPENDENCIESviews.

• ALL_DEPENDENCIESisfineforgivingyoudependencyinfoatthedatabaseobjectlevel,butnotbelow.– "FindallthepackagesthatreferencetableX".

• With11.1,Oraclenowsupportsfine-graineddependenciesforinvalidation,butthatinformationisnotavailableviadatadictionaryviews.

SELECT owner , name , type

, referenced_owner || '.' || FROM all_dependencies AND referenced_type IN ('TABLE', 'VIEW') AND referenced_name = 'MY_TABLE' ORDER BY name, referenced_owner, referenced_name

11g_fgd*.sql


GettingStartedwithPL/Scope

• PL/Scopemustbeenabled;itisoffbydefault.– BesttosetinyourSQLDeveloperpreferences.

• Whenyourprogramiscompiled,informationaboutallidentifiersarewrittentotheALL_IDENTIFIERSview.

•  Youthenquerythecontentsoftheviewtogetinformationaboutyourcode.

• ChecktheALL_PLSQL_OBJECT_SETTINGSviewforthePL/Scopesettingofaparticularprogramunit.

ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'


TurningonPL/ScopeinSQLDeveloper• Preferences

– Database•  PL/SQLCompiler


KeyColumnsinALL_IDENTIFIERS•  TYPE

– Thetypeofidentifier(VARIABLE,CONSTANT,etc.)• USAGE

– Thewaytheidentifierisused(DECLARATION,ASSIGNMENT,etc.)•  LINEandCOL

– Lineandcolumnwithinlineinwhichtheidentifierisfound•  SIGNATURE

– Uniquevalueforanidentifier.Especiallyhelpfulwhendistinguishingbetweenoverloadingsofasubprogramor"connecting"subprogramdeclarationsinpackagewithdefinitioninpackagebody.

• USAGE_IDandUSAGE_CONTEXT_ID– Revealhierarchyofidentifiersinaprogramunit


Startwithsomesimpleexamples•  Showalltheidentifiersinaprogramunit•  Showallvariablesdeclaredinasubprogram(notatpackagelevel)•  Showallvariablesdeclaredinthepackagespecifications•  Showthelocationswhereavariablecouldbemodified

plscope_demo_setup.sqlplscope_all_idents.sql

plscope_var_declares.sqlplscope_gvar_declares.sql plscope_var_changes.sql


Moreadvancedexamples•  Findexceptionsthataredefinedbutneverraised•  Showthehierarchyofidentifiersinaprogramunit• ValidatenamingconventionswithPL/Scope

plscope_unused_exceptions.sqlplscope_hierarchy.sql

plscope_naming_conventions.sql


PL/ScopeHelperUtilities• Clearly,"datamining"inALL_IDENTIFIERScangetcomplicated.•  SuggestionsforputtingPL/Scopetouse:

– Buildviewstohidesomeofthecomplexity.– Buildpackagestoprovidehigh-levelsubprogramstoperformspecificactions.

plscope_helper_setup.sqlplscope_helper.pkg


12.2EnhancementstoPL/Scope

• GathersdataonSQLstatementsinPL/SQLprogramunits.•  Youcannowfind:

– wherespecificcolumnsarereferenced– allprogramunitsperformingspecificDMLoperationsontable(andhelpyouconsolidatesuchstatements)

– allSQLstatementscontaininghints– alldynamicSQLusages–idealforgettingridofSQLinjectionvulnerabilities– locationsinyourcodewhereyoucommitorrollback– multipleappearancesofsameSQLstatement(sameSQL_ID)– SQLstatementsthatcalluser-definedfunctions

ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL, STATEMENTS:ALL'


NewALL_STATEMENTSView•  TheALL_STATEMENTSview(alongwithUSER_STATEMENTS)containsinformationabouteachSQLstatementinprogramunitscompiledwithPL/Scopeenabled.– full_text –textofSQLstatement– has_into_record –INTOplsql_record– has_current_of –UsesCURRENTOFsyntax– has_for_update –UsesFORUPDATEsyntax– has_into_bulk –UsesBULKCOLLECTINTO– usage_id –SameaswithALL_IDENTIFIERS–anduniqueacrossbothtables!– sql_id –pointertoSQLstatementinv$views


SomeExamples

•  There'ssomuchyoucando!

SELECT owner, object_name, line, full_text FROM all_statements WHERE has_hint = 'YES'

FindSQLStatementswithHintsSELECT idt.line, idt.owner || '.' || idt.object_name code_unit, RTRIM (src.text, CHR (10)) text FROM all_identifiers idt , all_statements st , all_source src WHERE idt.usage = 'REFERENCE' AND idt.TYPE = 'TABLE' AND idt.name = table_in AND idt.owner = owner_in AND idt.line = src.line AND idt.object_name = src.name AND idt.owner = src.owner AND idt.usage_context_id = st.usage_id

FindAllDMLStatementsOnTable


MoreExamples!

SELECT sql_id, text, COUNT (*) FROM all_statements WHERE sql_id IS NOT NULL

GROUP BY sql_id, text HAVING COUNT (*) > 1 /

SameSQLStatementUsed>1?

SameSQL_IDbutdifferentsignature.

SELECT * FROM all_statements WHERE has_into_bulk = 'YES' /

UsesBULKCOLLECTINTO?


MoreExamples:FinddynamicSQLSELECT st.owner, st.object_name, st.line, s.text FROM all_statements st, all_source s WHERE st.TYPE IN ('EXECUTE IMMEDIATE', 'OPEN') AND st.owner = s.owner AND st.object_name = s.name AND st.line = s.line UNION ALL

SELECT idnt.owner, idnt.object_name, idnt.line, src.text FROM all_identifiers idnt, all_source src WHERE idnt.owner <> 'SYS' AND idnt.signature IN (SELECT a.signature FROM all_identifiers a WHERE a.usage = 'DECLARATION' AND a.owner = 'SYS' AND a.object_name = 'DBMS_SQL' AND a.object_type = 'PACKAGE') AND idnt.owner = src.owner AND idnt.name = src.name AND idnt.line = src.line

NativeDynamicSQL–easy!

DBMS_SQLReferences:mustrecompilebuilt-inwithPL/Scopeenabled!


ConclusionsandResources• PL/Scopegivesyoualevelofvisibilityintoyourcodethatwasneverpossibleinearlierversionsofthedatabase.

• With12.2enhancementsaddinganalysisofSQL,youcannowperformdetailedanalysisoftheimpactofchangingyourdatastructures.

livesql.oracle.comsearch:"PL/Scope"

Doc:12.2DatabaseDevelopmentGuide

https://stevenfeuersteinonplsql.blogspot.comSearch:scope

https://github.com/PhilippSalvisberg/plscope-utils

impact analysis with pl/scope - s3.amazonaws.com · in my demo.zip file from the pl/sql learning...

Documents