sqlplus script tools

Theese scripts will enable you to work efficiently with sqlplus. Some of the most popular scripts are the obj and requests scripts.

The obj.sql script will list from all_objets where object_name like entered parameter.

The requests.sql script enables you to search in concurrent requests for requestor, program name, execution time and age.

The ai.sql will ask you to select one of your responsibilities, and do an apps initialize. After a apps initiliaze the info.sql will show you some of the setups done by the apps initialize.

The x4o_objects.sql will create a view, with the download and upload commands of Oracle Applications objects:

  • ALL_OBJECTS
  • FND_CONCURRENT_PROGRAMS
  • FND_DESCRIPTIVE_FLEXS_VL
  • FND_EXECUTABLES
  • FND_FLEX_VALUE_SETS
  • FND_FORM
  • FND_FORM_CUSTOM_RULES
  • FND_LOOKUP_TYPES
  • FND_MENUS
  • FND_MENU_ENTRIES
  • FND_NEW_MESSAGES
  • FND_PROFILE_OPTIONS_VL
  • FND_REQUEST_GROUPS
  • FND_REQUEST_GROUP_UNITS
  • FND_REQUEST_SETS_VL
  • FND_RESPONSIBILITY_VL
  • FND_USER
  • WF_ITEM_TYPES
xls4oracle.com - scripts for oracle applications developers
------------------------------------------------------------------------------ 
03-dec-2014 / Thomas Lundqvist

non-apps user scripts: 
- @ascii <text> - describes text with ascii character numbers 
- @cnt <partial table name> - count rows in tables 
- @csv <table name> - dumps a table to a csv file 
- @ccid <partial segments> - GL_CODE_COMBINATIONS segments and ccid 
- @cnt <partial object name> - counts number of rows in multiple tables 
- @desc <partial object name> - dumps descriptions to editor file 
- @dump <tablename> - dumps table data in list format 
- @dump2 <tablename> <where clause> - dumps table data in list format 
- ex: @dump2 fnd_user user_id = 8857 
- ex: @dump2 fnd_user "user_name = 'XXTLQ'" 
- @ed <file name> - user defined editor in new session 
- @env - show connect <username>@<databasename> 
- @ext <object name> - extract source from database 
- @find <text> - find any text in the all_source table 
- @grant <partial object name> <grantee> - create grants 
- @grants <partial object name> <grantee> - show grants 
- @header <partial object name> - show $header info from source 
- @help - Show commands (this file) 
- @index <partil table name> - show indexs on table 
- @info - show initialization info 
- @inx <partil table name> - index list 
- @kill <partial session info> - shows all session, with "alter system kill command" 
- @ledger <partial ledger name> - show ledgers 
- @login - default execution script 
- @locks - show locks 
- @obj <partial object name> - find objects 
- @org <partial org. name> - organization list 
- @par <partial parameter name> - show parameter values 
- @reset - sets all parameters <NB: edit setup.sql for your 
- requirements> 
- @select <table name> - build select statement 
- @set_client_info <org_id> - set client info 
- @spool <code> - creates spoolname with timestamp and code 
- @tab <partial table name> - table list 
- @tabinx <partial table name> - table index list 
- @trace - start trace 
- @trace_end - end trace 
- @trace - starts sql trace - extecute sql - end with @trace_end 
- @trace_end - ends sql trace - start with @trace 
- @userenv - list userenv variables 
- @v$sql <partial sql> <hours> - list executed sql's 
- @view <partial view name> - list views 
- @w - creates and open new work script named 
- work_<db_name>_YYYY-MM-DD-HHMI.sql 
apps user scripts: 
- @activate_user <username> - activates developer user (pls. review/edit) 
- @add_resp <username> <partial resp. name> - add responsibilities 
- @ai - select resp. and do apps initialize 
- @api <partial package name> - generate xls4oracle template scripts for all 
- procedures in package 
- @ccid <code_combination_id> - show the code combinations for the id 
- @create_user <user name> <password> - creates new user - using fnd_user_pkg.createuser 
- @csob - show current set of book 
- @del_resp <part. user> <part. resp.name> - deletes (end dates) responsibilities 
- @disable_user <username> - disable user 
- @enable_user <username> - enable user 
- @enable_examine - enable the examine function 
- @ffv <partial valueset name> - list FND_FLEX_VALUES 
- @ffvs <partial valueset name> - list FND_FLEX_VALUE_SETS 
- @flv <partial lookup name> - list FND_LOOKUP_VALUES 
- @fv <partial FLEX_VALUE_SET_NAME> <partial FLEX_VALUE> 
- - list FND_FLEX_VALUES 
- @fvs <partial FLEX_VALUE_SET_NAME> - prescript to @fv 
- @list_context - dbms_session.list_context 
- @load <partial object name> - generates download and upload script. 
- pls. execute @x4o_objects first 
- @password <new password> - new password for current session 
- @prof <partial profile name/value> - list profile values 
- @profile <partial profile name> - list profiles 
- @proto <partial package name> - generate prototype scripts for all procedures in 
- package 
- @req - show conc. request 
- @req2 <request id> - restart conc. request 
- @reqftp - show conc. request - submit reqftp2 
- @reqftp2 <request id> - ftp out and log from request 
- @requests <part.requestor> <part.prog.name> <min exe.time> <min age> 
- - list all requests. 
- @resubmit <request id> - resubmit a request in current session 
- @save_profile_site <prof.name> <value> - saves a profile value on site level 
- @save_profile_user <prof.name> <value> - saves a profile value on user level 
- @setup - prompts for username and generates new reset.sql 
- @sob - list all gl set of books 
- @user_password <user> <password> - updates user password 
- @wf_invflxwf <order no> <line no> - run oecogs workflow for a order line 
- @wf_oecogs <order no> <line no> - run oecogs workflow for a order line 
- trace workflow for cogs account generation 
- @x4o_objects - creates the x4o_objects view 

sqlplus script tools

Leave a Reply