PL/SQL DOCUMENTATION
SQL, PL/SQL FAQ
*************************** AP *********************
1) What is the Flow of AP module or Procedure to Pay?
2) How to transfer transactions from AP to GL?
3) What are the Base tables we have in AP?
4) What is Distributions set?
6) What are Payment terms?
7) What are the types Invoices?
8) What is Credit memo and Debit Memo?
9) What are the types of Payments we have?
10) What is diff between Supplier and Vendor?
11) Where the Accounting entries will be stored? ap_ae_headers_all,ap_ae_lines_all
12) What are the Interfaces we have in AP module?
*************************** AP *********************
1) What is the Flow of AP module or Procedure to Pay?
Ans. Invoice
Payments
AP to GL transactions
2) How to transfer transactions from AP to GL?
Ans. PAYMENT TRANSFER TO GENERAL LEDGER using this concurrent program we can transfer the all invoices payment information into the GL module.
3) What are the Base tables we have in AP?
Ans. AP_INVOICES_ALL (all the invoice info)
AP_INVOICE_DISTRIBUTIONS_ALL
AP_AE_HEADERS_ALL (accounting details)
AP_AE_LINES_ALL (accounting details)
AP_LOOKUP_CODES
4) What is Distributions set?
Ans. It is group of accounting distributions, we use this, to automatically enter distributions for an invoice
While creating the invoice we have a field called distribution set, we will attach to the invoice, as per the distribution set , we create the entries.
5) What are Payment terms?
Ans. Is one of the methods which we will use, make the payment schedule.
Navigation: setup/invoice/payment terms
While creating the invoice at the header level we have a column called , payment terms , we will attach this payment term.
7) What are the types Invoices?
Ans. 8types
1. Standard
2. Credit memo
3. Debit memo
4. With holding tax
5. Prepayment
6.
7. Quick match
8. Mislenious
8) What is Credit memo and Debit Memo?
Ans. Both invoices will be a negative amount
It adjusted against standard invoice
If supplier is giving the discount we create the credit memo
If company is deducting then we create the debit memo
9) What are the types of Payments we have?
Ans. Three types
1. Manual
2. Quick
3. Refund
10) What is diff between Supplier and Vendor?
Ans.
11) Where the Accounting entries will be stored?
Ans.
AP_AE_HEADERS_ALL
AP_AE_LINES_ALL
12) What are the Interfaces we have in AP module?
Ans. Invoice outbound interface
******************* INTERFACE ************
1) What process you have followed to develop Inbound Interface?
2) What are the Interface tables?
3) What are the Base tables?
4) What is the Standard program has used to import data?
5) How to track the errors in Interface?
6) What are the Validations you have done?
7) Tell me some of tech errors which you have faced?
8) Have you used "Autonomous Transaction" in Interface?
9) What is the diff between Inbound and Outbound?
10) Whether you have used UTL_FILE or SQL*loader?
11) What is format of Data file? (.csv)
12) Whether your Interface is scheduled or manually running ? (Scheduled)
14) What is the Difference between Conversions and Interface?
******************* INTERFACE ************
1) What process you have followed to develop Inbound Interface?
Flat file
2) What are the Interface tables?
Ans. PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
3) What are the Base tables?
Ans. PO_HEADERS
PO_LINES
PO_LINES_LOCATION
PO_DISTRIBUTION
4) What is the Standard program has used to import data?
Ans.
Import Standard Purchase Order
5) How to track the errors in Interface?
Ans. If records are validate insert into interface tables
If records not validate insert into error tables (po_interface_error)
6) What are the Validations you have done?
Ans. Purchase order Interface validations are:à
Header level validations:à
- INTERFACE_HEADER_ID : accepecting primary key values, it can create the sequence, we can insert the sequence value
- DOCUMENT_TYPE_CODE: this column will capture type of purchase order, like standard, blanket and contract
- CREATION_DATE: valid date format less then sysdate
- VENDOR_ID
VENDOR_NAME- VENDOR_SITE_ID
- VENDOR_SITE_CODE All these four columns will accept the existing vendor id’s and vendor_site_id’s . We can find out , All the vendor’s siteid’s from po_vendors , po_vendor_sites_all
SHIP_TO_LOCATION- BILL_TO_LOCATION these two columns will accepts valid locations, it can find out all the valid locations from hr_locations table
- CURRENCY_CODE: Should be a valid currency as per the application, will find out all the valid currencies , from fnd_currencies
- BUYER OR AGENT (AGENT_NAME): From the tables
po_agents
per_all_people_f
- APPROVEL_STATUS : this column will fallow the fallowing statuses
1. Approved
2. Inprocess
3. Preapproved
4. Cancelled
5. In complete
6. Closed
Line level validations: à
- INTERFACE_LINE_ID: Will accepting primary key values. which will be inserting in to the line number field
- INTERFACE_HEADER_ID: we will use the header sequence, current values.
- LINE TYPE: Its accepting the fallowing values
1. Goods
2. Services
3. Consulting or quality
4. ITEM
5. ITEM_DESCRIPTION: these two columns will accept the validation and item description. We will get it valid items from mtl_system_item_b table
6. ITEM_REVISION: It should be valid item revision, it can all the item revisions from mtl_item_revisions table
7. CATEGORY: This column will accept valid item categories with in this all the valid item categories with in this all the valid item categories from mtl_categories table
8. UOM (UNIT OF MEASUREMENT): This column will accept uom codes, we can find out valid UOM codes from mtl_units_of_measure
9. QUANTITY
10. PRICE : These two columns will accept positive numbers
11. PROMISSED_DATE
12. NEED_BY_DATE: These two columns will accept valid date formats, both dates should be more then system date.
13. ORGANIZATION_CODE: It should be a valid organization code, we can find out all the valid organization codes from org_organization_definitions table
14. SHIP_TO_LOCATION: hr_locations
7)Tell me some of teh errors which you have faced?
Ans.
EP04: This data is not a business day
EP02: This set of books does not have any operations
EC08: Invalid currency code
EB03: The budget is not open
EE02: Invalid or disable encumbrance type
EM03: Invalid set of books id
EM26: Invalid originating company
8) Have you used "Autonomous Transaction" in Interface?
Ans. (Doubt) Irrespective of parent transaction child is committed, after finished child transaction get back to the parent transaction.
9) What is the diff between Inbound and Outbound?
Ans. Inbound: legacy system à Oracle application base table
Out bound: Oracle application base tableà legacy system
10) Whether you have used UTL_FILE or SQL*loader?
Ans.
UTL_FILE: This is PL/SQL package, using this we can create the files, we can transfer the data in to the file
Before going to create the file, we have to find out UTL file directory list
J How can I get utl_file directory list? L
| à Select * from v$parameter where name like ‘%utl_file%’ |
File creation: utl_file.fopen (‘directory path’, name of file, mode)
Inserting records: utl_file.put_line (fileid, columnnames)
Close file: utl_file.fclose (file id)
Exceptions:
Utl_file.invalid_operations
Utl_file.invalid_path
Utl_file.invalid_mode
Utl_file.invalid_fielhandler
Utl_file.read_error
Utl_fiel.internal_error
11) What is format of Data file?
Ans.
(.csv)
12) Whether your Interface is scheduled or manually running?
Ans.
(Scheduled)
14) What is the Difference between Conversion and Interface?
Ans.
Conversion: single time process
Interface : Repeated process
*******************GL INTERFACE******************
1) How many days GL Interface taken to upload?
2) What are the Interface tables? E
3) What are the Base tables?
4) What process you have followed?
5) What are the Error tables?
6) What is the standard concurrent Program to upload data?
7) What are the Parameters we need to give while running Journal Import?
8) What are the Validations you have done?
9) What re the tables you have come across?
10) What are the Mandatory columns in Interface table?
11) What are the setups needs to e done before GL Interface?
12) How to Post the Jornals? And How to correct and Delete the journals?
13)What are the Errors you have faced while running GL Import?
*******************GL INTERFACE******************
1) How many days GL Interface taken to upload?
Ans. 30 days (some time extension up to 15 days..)
2) What are the Interface tables?
Ans.
XX_GL_INTERFACE
3) What are the Base tables?
Ans. GL_JE_HEADERS
GL_JE_BATCHES
GL_JE_LINES
4) What process you have followed?
Ans.
FLAT FILE
Process:
XX_GL_INTERFACE
GL_INTERFACE
5) What are the Error tables?
Ans. Journal import execution report.
6) What is the standard concurrent Program to upload data?
Ans. Journal import
7) What are the Parameters we need to give while running Journal Import?
Ans. Take source as a parameter, we give the legacy source name (Because we are uploading the legacy data)
8) What are the Validations you have done?
Ans. (Not a connectivity answer…)
Before going to develop GL interface fallowing functional setup needs to be done.
- Legacy source creation
- Legacy category creation
- Set of books defining
- Periods opening
- Currency
MANIDATORY COLUMNS IN GL-INTERFACE:à
1. ACCOUNTING_DATE: Valid date format as per the accounting rules
2. DATE_CREATED : Should be a valid creation date and it should not be more than system date
3. CREATED_BY: It will accept valid USER_ID who has created transations.we can find out valid user_id’s from fnd_user table
4. SET_OF_BOOKS_ID: These columns will accept valid set of books ids. we can find out valid set of books from gl_set_of_books table
5. ACTUAL_FLAG: This column will accept only one character
That character either should be
A à Actual amount
B à Budged amount
E à Encumbrance amount
Noteà other characters is not valid
6. CURRENCY_CODE: Accept valid currency code (fnd_currencies)
7. USER_JE_CATEGORY_NAME: This column will accept valid category names
We can find out all the categories from gl_je_categories
8. USER_JE_SOURCE_NAME: This will be a valid source name we can find all valid sources from gl_je_sources table
9. STATUS: This column will accept the string, normally we enter the standard string called ‘NEW’ indicate that data has come from legacy.
10. ENTER_CR (ENTER CREDIT AMOUNT)
11. ENTER_DR (ENTER DEBIT AMOUNT): Two columns will accept the valid positive number, but both column values should be equal
Note:à: Both column value should be equal , Other wise accounting transation will come under suspense account.
12.ACCOUNTED_CR
13. ACCOUNTED_DR These two columns will accept valid positive number containing amount after transferring the 1. enter_dr 2. enter_cr
14. ***L PERIOD NAME**: Before inserting transactions period should be created, and it should be in open status.
This period should be open or not find out in the gl_period_statuses
Period created or not checks in gl_periods
***L”How can I execute the standard program “JOURNAL IMPORT”L***
Once data has come into the interface table with all validations we will run JOURNAL IMPORT program, from GL module
Navigation is:
| Journals/import/run |
Take source as parameter, we give the legacy source name (Because we are uploading the legacy data)
Then we will select the import button. It will run the concurrent request automatically.
CORRECT JOURNALS:à We can use the correct journals form to do same modification after inserting the data.
DELETE: If you want to delete the journals, which are uploaded successfully, we use this Delete journal form
We can find out all the valid journals from the enter journal form (journals/enter)
*:LNoteLà After importing the journals successfully we will post the journals & This journals will be uploaded in to the gl_balences
9)what re the tables you have come Across?
Ans.
1. GL_JE_HEADERS
2. GL_JE_BATCHES
3. GL_JE_LINES
4. FND_USER
5. GL_SET_OF_BOOKS
6. FND_CURRENCIES
7. GL_JE_CATEGORIES
8. GL_JE_SOURCES
9. GL_PERIOD_STATUSES
10. GL_PERIODS
10) What are the Mandatory columns in Interface table?
Ans.
- ACCOUNTING_DATE
- DATE_CREATED
- CREATED_BY
- SET_OF_BOOKS_ID
- ACTUAL_FLAG
- CURRENCY_CODE
- USER_JE_CATEGORY_NAME
- USER_JE_SOURCE_NAME
- STATUS
- ENTER_CR
- ENTER_DR
- ACCOUNTED_CR
- ACCOUNTED_DR
- PERIOD NAME
11) What are the setups needs to be done before GL Interface?
Ans.
Before going to develop GL interface fallowing functional setup needs to be done.
1. Legacy source creation
2. Legacy category creation
3. Set of books defining
4. Periods opening
5. Currency
12) How to Post the Jornals? And How to correct and Delete the journals?
Ans.
***L”How can I execute the standard program “JOURNAL IMPORT”L***
Once data has come into the interface table with all validations we will run JOURNAL IMPORT program, from GL module
Navigation is:
| Journals/import/run |
Take source as parameter, we give the legacy source name (Because we are uploading the legacy data)
Then we will select the import button. It will run the concurrent request automatically.
CORRECT JOURNALS:à We can use the correct journals form to do same modification after inserting the data.
DELETE: If you want to delete the journals, which are uploaded successfully, we use this Delete journal form
We can find out all the valid journals from the enter journal form (journals/enter)
*:LNoteLà After importing the journals successfully we will post the journals & This journals will be uploaded in to the gl_balences
13) What are the Errors you have faced while running GL Import?
Ans.
EP04: This data is not a business day
EP02: This set of books does not have any operations
EC08: Invalid currency code
EB03: The budget is not open
EE02: Invalid or disable encumbrance type
EM03: Invalid set of books id
EM26: Invalid originating company
*****L PL/SQL STORED PROCEDURES*******
1. What is the mandatory parameter we will use whole register procedure as concurrent program.
Ans. errbuf, retcode
2. What is the purpose of errbuf, retcode
Ans.
ERRBUF: is one of the parameter, if which will be defined, in pl/sql stored procedure to get the error messages in to the log file,
If any errors occurs in side of procedure.
RETCODE: this parameter will be used, to get the status of concurrent program, whether it has been completed successfully or any warnings or any errors.
If it will give the values 0 for success
1 for warning
2 for error
3. What is the parameter mode of these two parameter (errbuf, retcode)
Ans.
In/out
4. How to write the message into output file and log file from the pl/sql procedure
Ans.
Fnd_file.put_line (fnd_file.output,’message’||variables); IS WRITE FOR THE OUTPUT FILE.
Fnd_file.put.line(fnd_file.log,’message’||variables); IS USED FOR LOGFILE
5. Do you need transfer the procedure to server or not?
a) No need to transfer the procedure to server, it will stored in database and system will pick up the data from the database itself
If at all required for back up purpose then will be a one folder called PL/SQL inside of 11.5.0 we will transfer this procedure code to in the folder PL/SQL
6. How to execute the package procedure in concurrent program?
Ans.
Package name. Execution name as a executable file.
7. How to run the function from concurrent program we can’t make the function as concurrent program?
Ans.
We will define the procedure, inside of these procedure we will called the function, and we will make the procedure as a concurrent program.
8. What are the values come in report?
Ans. 0 à success
1 à warning
2 à error
******************** SQL LOADER ************
1) Extensions of all files?
2) Control files syntax?
3) How to issue commit issue inside of control file?
4) What are the modes or Paths we have what is the diff between those paths?
5) How to insert data into multiple tables at a time?
6) How to insert data into from multiple file at a time?
7) How to ignore a particular column?
8) How to mention discard file path in control file?
9) Where do we will keep Control file?
10) How to pass parameters to Control file?
11) What are the different loads we have? -insert append truncate
12) What are Trailing Null cols?
13) How to register SQl*Loader file as Concurrent Program?
14) How to issue where clause inside of Control file?
15) What is Data and Discard files?
******************** SQL LOADER ************
1) Extensions of all files?
Ans.
1. Flat or data file: .dat,.txt,.csv(comma separated view)
2. Control file: .ctl
3. Badfile: .bad
4. Discard file: .dis
5. Log file: .log
2) Control file syntax?
Syntax:
Load data
Infile ‘datafile path’
Insert into ‘table name’
Field termintated by ‘,’
Where deptno= 10
(column1 , empno
column2, ename
column3, deptno)
Once we develop the control file we will execute this by using fallowing command
C :\> sqlldr user/passward @ database
Control= name of control file (with extension .ctl)
3) How to issue commit issue inside of control file?
Ans. Alter table temp_emp add (sno number (10))
Delete from temp_emp;
Commit;
4) What are the modes or Paths we have what is the diff between those paths?
Ans.
We can execution sql* loader in two paths or nodes
1. Direct
2. Conventional
By default sql*loader will be running in conventional mode, if we want to run in direct mode will use the fallowing syntax
C :\> sqlldr userid/passward@database control=text1.ctl path=direct
Direct mode will disable the table and column constrains and it will insert the data
Conventional path will check every constrains, if it is satisfied it will insert the record
Conventional path is just like ‘insert statement’
5) How to insert data into multiple tables at a time?
Ans.using
6) How to insert data into from multiple file at a time?
Ans.
7) How to ignore a particular column?
Ans. Using skip
8) How to mention discard file path in control file?
Ans.
Load data
Infile ‘data file path’
DISCARD FILE ‘DISCARD FILE PATH’
LOG FILE ‘LOG FILE PATH’
Insert into ‘table name’
Field termintated by ‘,’
Where deptno= 10
(column1, empno
column2, ename
column3, deptno)
9) Where do we will keep Control file?
Ans.
Custom_top/11.5.0/bin/.ctl
10) How to pass parameters to Control file?
Ans. We can have the parameter of control file normally we can use
1. Control file path
2. Data file path
As a parameters. These parameters we can refer inside of control file
11) What are the different loads we have?
Ans. 1. Insert
2. Append
3. Truncate
12) What are Trailing Null cols?
Ans. Is a key ward to find null values
13) How to register SQl*Loader file as Concurrent Program?
Ans.
step1. Develop the control (.ctl) file
step2. Move this control file to the respective path to the server
custom_top/11.5.0/bin/.ctl
step3. We will create the executable with execution method as sql*loader
And name of the control file and execution file name
step4. We will create the concurrent program and we will attach parameters, incompatibilities programs
step5. Attach the concurrent program to request group
step6. Attach the request group to the responsibility
step7. Attach the responsibility to the user
14) How to issue where clause inside of Control file?
Ans. Insert into table temp_emp where dept_no=’10’;
15) What is Data and Discard files?
Ans.
FLAT OR DATA FILE: This file contains the records in a special format; these records will be fetch for other legacy.
The extension of these files might be .dat, .txt, or .csv(comma separated view).
CONTROL FILE: This is sql loader execution file, which will be used to transfer the date from file to table
BADFILE: Bad file contain the records, which are rejected by the sql*loader
Sql*loader will reject the records, when ever the flat file format is not correct. And in any internal error comes it will rejected.
The extension of bad file is .bad
DISCARD FILE: Discard file contains the records which are rejecting the control file, control file will reject the records, if record is not satisfying the conditions, which we have mentioned inside of control files the extension of discard file is .dis
LOGFILE: It contains the complete log of process, like no of records successfully loaded in to the table
No of records successfully loaded in to the bad file& discard file.
And where the bad, discard file gets created and time taken to complete the process.
Taking the complete log.
16. SQL COMMANDS LIMITATIONS:
1. to_date,to_char,upper,lower,initcap,string, decode,nvl
2. when clause
3. sequence name.next value, if-cursor
4. sysdate,ltrim, rtrim, constant
*************************** CONCURRENT *****************
1) How will you develop report in Oracle apps?
2) What is Concurrent Program?
3) What is Request group?
4) What is Data Group?
5) What is Token?
6) How to make Parameter as Mandatory?
7) What are the Default types we have?
8) How many value sets we have? What is that?
9) What is: $FLEX$ and: $PROFILE$?
10) What is Dependent and Table Value set/
11) What is Incompatibility?
12) Can we delete concurrent Program?
14) What are the Who columns?
15) How many types of Execution methods we have?
16) Why we will not use TOKEN for PL/SQL Procedure?
17) Can we delete attached Responsibility to the User?
18) What are Concurrent Managers available?
19) What is US Folder?
20) What is 11.5.0 Folder?
21) Where do we keep the .rdf inside of Server?
22) What is Executable Short Name?
23) What is CUS_TOP ? What is the advantage of this?
24. How you connect to the server
25. How will you find out whether your application will install for single language or multiple language
26. In which mode we will transfer the forms & reports to the server
27. Where do you keep sql* loader files
28. Where you keep forms/reports in side the server?
29. What are the parameter default types?
*************************** CONCURRENT *****************
1) How will you develop report in Oracle apps?
Ans.
EXECUTABLE
CONCURENT PROGRAM
REQEST GROUP
RESPONSIBILITY
USER
STEP1: Develop report with 6i as per the client requirement
STEP2: Move the report from local machine to oracle apps server
Cus_top/11.5.0/reports/us/.rdf
STEP3: we will create executable
1. Name of your report
2. Application name
3. Execution method (totally 11 methods)
STEP4. We will create concurrent program and we will attach
1. executable
2. parameters
3. incompatibilities
STEP5: Create request group, attach concurrent program to that request group
STEP6: Create responsibilities, attach request group to that responsibility.
STEP7: Create user, attach responsibility to that user.
STEP8: User will run concurrent program from srs(stand request submission) window
2) What is Concurrent Program?
Ans.
Nothing but a instance of execution file along with parameters and incompatibilities.
3) What is Request group?
Ans. Collection of concurrent programs and reports
4) What is Data Group?
Ans. Is Nothing but collection of oracle application user_id’s , based on the user-id’s system will pick up the data from database we are using standard data group and we are give to the application name we are using for responsibility.
5) What is Token?
Ans. Token is one of the fields which will be used to map the concurrent programs parameter with report bind variables.
6) How to make Parameter as Mandatory?
Ans. Use ing required check box
7) What are the Default types we have?
Ans.columns/rows: These two fields we mansion the no’s of columns/rows if we went to display per page to the concurrent program output.
Note:à by default is landscape columns -132, rows-45
8) How many value sets we have? What are those?
Ans. 8 types
1. None
2. Independent
3. Dependent
4. Table
5. Translated Independent
6. Translated Dependent
7. Special
8. Pair
9) What is: $FLEX$ and: $PROFILE$?
Ans.
: $flex$ to get previous parameter value
Syntax: :$flex$.previousparameter value set name
:$profile$ Using this keyword we can get the user profile depends on the user , who logged in to the system.
Syntax: :$profile$.name of profile
10) What is Dependent and Table Value set/
Ans.
Value set: Is nothing but a list of values, with validations, which will be used to retrieve the end user, with out entering the invalid data
Independent: It will show the standard values as lov, to the end user should select the values from the list, values will be entered manually.
Depended: Will be going to change based on the Independent value. If you want to create dependent values we should have one independent
Table: Will be used, to display the data from the data base tables.
Difference is in independent and dependent, we will enter the values manually, where as in table get the values from database table.
11) What is Incompatibility?
Ans. Is not compatible with the current concurrent program
If we have two programs A and B while running A program
If we don’t want to run B program we will make it B as incompatible with A program
If A program is process in side the server other user can’t submit B program event if they submit that, program will not get start unless and until A program is over
12) Can we delete concurrent Program?
Ans. No, we can’t delete that, instead of this we can disable this by unchecking the check box called Enabled
14) What are the Who columns?
Ans. 4 columns are there, they are
- Created by,
- creation date,
- updated by,
- updation_date
- Last Login date
Who columns are the default columns of each and every table.
15) How many types of Execution methods we have?
Ans. 11 types.
1. Sqlscript
2. pl/sql stored procedure
3. Sql*loader
4. Spanned concurrent program
5. Request set stage function
6. Oracle reports
7. Multi language function
8. Java conc. program
9. Java stored procedure
10. Immediate
11. Host
16) Why we will not use TOKEN for PL/SQL Procedure?
Ans. Pl/sql parameters are already in sequence. So no need to use token.
17) Can we delete attached Responsibility to the User?
Ans. No make it disables
18) What are Concurrent Managers available?
Ans.
19) What is
Ans. This is language specific
20) What is 11.5.0 Folder?
Ans. Oracle release name
21) Where do we keep the .rdf inside of Server?
Ans. Custom_top/11.5.0/reports/us/.rdf
22) What is Executable Short Name?
Ans. Is primary key for Executable window, we will refer always short name.
23) What is CUS_TOP? What is the advantage of this?
Ans. Including all these product tops client will have his own top called custom top
The structure of these cus_top remains as it is with existing product tops
Because of Oracle apply any patches to fix the bugs. That product will over write all our customization and development
24. How you connect to the server
ans. Through UNIX
- ftp
- toad
- winscp3
- putty
- filezilla
25. How will you find out whether your application will install for single language or multiple language
Ans.
Select language_code from fnd_languages
Where installed_flag in (‘I’,’B’);
I:à Installed success
B:à Base language (by default us)
D:à Disable
26. In which mode we will transfer the forms & reports to the server
Ans. Forms/reports à Binary (GUI scripts)
Sql/plsql à ASCII (character scripts)
27. Where do you keep sql* loader files
. Cus_top/11.5.0/bin/.ctl
28. Where you keep forms/reports in side the server?
Ans. Cus_top/11.5.0/forms/us/.fmx
Cus_top/11.5.0/reports/us/.rdx
29. What are the parameter default types?
Ans. 4 default types
- constant
- profile
- sql statement
- segment
P_CONC_REQUEST_ID: Bind parameter, this will automatically retrieve the request_id of concurrent program.
And this request_id will be use to do some concurrent processing operations
SRWINIT: (BEFORE REPORT TRIGGER):
Syntax: Srw.user_exit (‘fnd srwinit’)
Which will be used to retrieve the user profiles? A person who is submitting the request.
SRWEXIT: (AFTER REPORT TRIGGER)
Syntax: srw.user_exit(‘fnd srwexit’)
Freeze the memory which is occupied by the user profiles
.
USER_EXIT (); is one of the program which will be used to transfer the control from report builder executable to another third General Language
To get some Information, it will complete the remaining process.
*************** PURCHASE ORDER*************
1. What is the flow of
Ans.
REQUISITION
RFQ
QUATATION
PO
RECEIPT
APINTERFACE INVENTORY INTERFACE
2. What are the types of
Ans.
1. Standard
2. planned
3. blanket(po_releases_all)
4. contract
3. What are the types of RFQ& quotation?
Ans.
1. Bid
2. Catalog
3. Standard
4. What is auto create option?
Ans.
If you want to create purchase order directly from requisitions we will use this
Auto create option
1. Select the auto create option, give the requisition no
2. Find the lines
3. select the line’
4. Then we will create
5. What are the match approval levels we have?
Ans. While creating the
2-way: matching purchase order + Invoice
3-way: matching purchase order + Invoice+ receipt
4-way: matching purchase order+ invoice +receipt + Inception
6. What are the receipt tables we have?
Ans.
1. RCV_SHIPMENT_HEADERS
2. RCV_SHIPMENT_LINES
3. RCV_TRANSACTIONS
7. What are the
Ans.
- PO_HEADERS_ALL ( HEADERS INFO)
- PO_LINES_ALL (LINE INFO)
- PO_LINE _LOCATION_ALL(SHIPMENT INFO)
- PO_DISTRIBUTION_ALL (DISTRIBUTION INFO)
8. What are the supplier tables we have?
Ans. PO_VENDORS AP_SUPPLIERS
PO_VENDOR_SITES_ALL AP_SUPPLIER_SITES_ALL
PO_VENDOR_CONTACTS AP_SUPPLIER_CONTACTS
9. Why there are no_all for po_vendor tables?
Ans. Business group à legal entitiesàoperating unità Inventory
Note:L :à operating unit implemented in Purchase Order (
Supplier is not under the multi_org concept, because companies can purchase the items from any supplier
If you make it as a multi org, that company should purchase the goods from the Organization suppliers
Note:à Site level (po_vendor_sites_all) there well be a multi org but supplier level we don’t have multi org)
10. Where the item will be stored?
Ans. Mtl_system_item_b
11. What are the terms and conditions we have in
12. If I give the requisition_no how can u fine out the corresponding
Ans.
Po_requisition_headers_all
Po_requisiton_lines_all
Po_req_distributions_all
Po_distributions_all:à 1. REQ_HEADER_REFERENCE_NUM
2. REQ_DISTRIBUTION_ID
3. REQ_LINE_REFERENCE_NUM
Using three columns we ca join the (po_req_distributions_all) table and get the po_number.
*********************MULTI-ORG****************************
1) WHAT IS MULTIORG?
2) WHAT IS APPLICATION HIRARCHY OR MULTIORG HIRARCHCY?
3) What is Legal Entity?
4) What is Operating Unit?
5) What is the diff between ORG-ID AND ORGANIZATION_ID?
6) In which table we can find all the Operating Units?
7) In which table we can find all the Inventory organizations?
8) Through which profile system will find out your Org_id?
9) What is Client -info?
10) How can you Implement Multiorg in Reports?
11) How can you Implement Multiorg in sql/plsql Programs?
12) How will you find out whether multiorg set up implemented successfully or not?
13) Which user exit will capture the User profiles?
14) What is Multi Org table (Or) _ALL table?
15) What is Multi org view?
16) While developing programs, forms and Reports whether we will use Multi org tables or Multiorg views?
*********************MULTI-ORG****************************
1) What is multiorg?
Ans. MultiOrg is nothing but, under single implementation maintaining multiple orgs, like Business-groups, set of books, legal entities, Operating Units, Inventory Orgs.
2. What Is Application Hierarchy Or Multiorg Hierarchy?
BUSINESS GROUPS (Top level of organization)
Hrfv_business_groups
SET OF BOOKS (currency, calendar, chart of a/c)
Gl_sets_of_books
LEGAL ENTITIES (Organization where IT calculations will happened)
Hr_legal_entities
OPERATING UNIT
(where exactly your company Business (purchases, sales, receiving, full financial mode, a/c payable receivable, transaction etc..) hr_operating_units
INVENTORY
(Organization complete, covers the Manufactures, Inventory module , Working process, Engineering module, Bill of materials, Process manufacturing.)
mtl_parameters
org_orgnisation_definitions
3. What is Business group?
Ans. Business group is topmost Organization in the multi org hierarchy
This business group success, human resources information.
For e.g.: When you request a list of employees, he will see all employees assign to the business group. Of which your organization is a part.
4. What is set of books?
Ans. It’s a Financial reporting entity, is uses a particular, functional currency and accounting calendar and chart of a/c.
Oracle GL MODULE secures Transaction information, when we use specific responsibilities which is assigned a specific set of books
Then we can see only that set of books information.
4. What is Legal Entity?
Ans. A legal entity for which, will prepare tax reports and we will assigned this reports to the government.
5) What is Operating Unit?
Ans. Operating Unit is a organization where company is exactly business operations like sales, purchasing, payments, receiving and so on.
Operating Unit may be sales offices or division or department
6) What is Inventory Organization?
Ans. It is a organization for which we track the inventory transactions and balances and organization distributing products..
All the Organizations which are fallowing manufacturing process, will come under Inventory Organization level
7) What is Client -info?
Ans.
Client-Info is a RDBMS BIND VARIABLE which contains the user org-id, when ever user log into the system, system will be capturing org-id and it will be stored in the client-info variable.
***While developing forms, reports program will be using the multi-org view instead of multi org tables
8) What is the diff between ORG-ID AND ORGANIZATION_ID?
Ans.
Org-Id: Operating unit specific
Organization-id: Inventory organization specific
9) In which table we can find all the Operating Units?
Ans. HR_OPERATING_UNITS
10) In which table we can find all the Inventory organizations?
Ans.
MTL_PARAMETERS
ORG_ORGANIZATON_DIFINITIONS
11) Through which profile system will find out your Org_id?
Ans.
Fnd_profile.value (‘org_id’);
12) How can you Implement Multiorg in Reports?
Ans.
Before report:
Srw.user_exit (‘fnd srwinit’)
Fnd_profile.get(‘user name’,:p_user_name);
Fnd_profile.get (‘Resp_name’,:p_resp_name);(If I want responsibility also)
After report:
Srw.user_exit(‘fnd srwexit’);
User parameters:
P_conc_request_id
13) How can you Implement Multiorg in sql/plsql Programs?
Ans.
In case of sql/plsql programs we will not having the user_exits Instead of user_exits we will user the fallowing API’S
1. Dbms_application_info.set_client_info (204);
(Or)
2, Begin
fnd_client_info.set_org_context (204)
Or
fnd_client_info.set_or_context (fnd_profile.value (org_id));
End
14) How will you find out whether multiorg set up implemented successfully or not?
Ans.
Select multi_org_flag from fnd_product_groups;
If out put =’Y’ then multi or implemented successfully…
15) Which user exit will capture the User profiles?
Ans. (Before report trigger) Srw.user_exit (‘fnd srwinit’)
16) What is Multi Org table (Or) _ALL table?
Ans. In oracle application we have multi org tables, which will be used to capture multiple organizations data, this table having <_all> at the end of table name
Every table contains one extra column called
1. Business_group_id à Business Group
2. Org-id à Operating Unit
3. Organization-id à Inventory Org
17) What is Multi org view?
Ans. Every multiorg table having one view called multiorg view, this view will be created based on the multiorg table including Where clause
The view name will be same as the multiorg name except _ALL
18) While developing programs, forms and Reports whether we will use Multi org tables or Multiorg views?
Ans. Multi org views
************ PROFILES*********************
PROFILE: Is a option which will change the way of application running, we can set the profile value to every user at different level ( S A R U)
1. User
2. responsibility
3. application
4. site
SITE LEVEL: If you assign some value to profile at site level that will be applicable to all the users
APPLICATION: If we assign a profile value at application level that will be applicable to the users who are having the access to that application.
If we set the same profile at application level, if user is eligible both SITE & APPLICATION.
*** Then application will over write the site value
RESPONSIBILITY: If we set the profile value at responsibility level, that will be applicable to the users who has got he access, to the specific responsibility if user is eligible for, all site application responsibility, then responsibility will overwrite both application & site level.
USER: Is the topmost level, here we give a value to specific user, it will be applicable only for that user.
If user is eligible for all the levels then user will over writes the responsibility , application & site
SOME PROFILES:
MO: OPERATING UNIT: Once we create the user in oracle applications. We will mention which operating unit is related. That value will be setting in this profile, we will get all operating units from hr_operating_units table . and we will assign the value.
HR : BUSINESS GROUP: This profile will be used to explain which business group, user is related . We will get all the business group names from hrfv_business_groups
MFG_ORGANIZATION_ID: If user is working at inventory organization, then we will set the organization id, in this profile
We will get the list of organization id’s from org_organization_definition table
HR: USER_TYPE: This profile contain the type of employment, whether user is permanent employee or contract or x-employee
GL_SET_OF_BOOKS: If client having multiple set of books, then we will assign the set of books to every user depends up on the user_organization
We will find the all the set of books names from gl_sets_of_books
USER_ID
USER_NAME
RESP_ID
RESP_APPL_ID : All the profiles are user profiles, which will be captured by the system automatically we can’t change these values
L How can I getting profile values from backend??? L
Using the fallowing two (API’S) applications we can retrieve the profile value from the back end
- variable := fnd_profile.value(name of profile)
- variable := fnd_profile.get(name of profile)
It get the attribute profile name variable name, here value à is a function
Get à is a procedure
Both will be use to retrieve profile value, some time we need to get profile value in select clause,
We can’t use procedures in select clause. That time we will use the functions.
******************************** INVENTORY *************
1) What is Master Item?
2) What is on hand and Available Qty?
3) What is Move Orders?
4) What are the Inventory Organizations we have?
Ans. sun inventories, stores, shop floors
5) What are the KFF we have?
6) Tell me some of the Base tables in Inventory Module?
7) In which column item will be stored?
Ans. Segment1
8) What is the Primary key in MTL_SYSTEM_ITEMS_B table?
Ans. Inventory_item_id
9) In which table we are find out Master Organizations?
Ans. mtl_parameters
10) In which table we can find out Sub inventories?
Ans. mtl_secondary_inventories
11) In which column we can find out Item category name?
Ans. segment1, segment2
12) What is ABC analysis and ATP date?
13) What are the Item Transactions we have?
14) What are the reports you have developed or Customized in Inventory?
1) What is On-hand and Available qty?
2) What are the flex fields available?
3) What are the tables you have used while developing inv reports?
4) What is the table name for items? in which column item will be stored?
5) What is category?
6) Where the category will be stored?
7) What are Move orders?
8) How will you find out the Master organizations?
9) What is the table name for Material Transactions?
10) What is the Diff between master Item and Organization Item?
11) What are Item Attributes we have?
******************************* WF ********************
1) What are the Customizations you have done in Workflow?
2) What are the API's you have come across?
3) How to test Workflow?
4) What is Process?
5) What are the Activities we have?
6) What is Function activity?
7) What are the Access levels we have?
8) What is Lookup type?
9) How to launch WF from backend?
10) How to customize workflow?
11) Can we call one process from another Process?
12) What are the Parameters we need to pass while creating Function in WF?
13) What is Item key?
14) What is Differed Process and Timeout?
15) What are the Function modes we have?
16) What is the meaning of COST field?
17) What is the difference between Function and Notification Function?
18) What is Item Attribute?
19) How to send E-mail from WF Process? -Oracle Workflow 11i E-mail Notification Setup
20) What is Synchronous Wf and Asynchronous Wf?
21) Can we change the internal name of Workflow?
About Triggers:
1. What is triggers? What are the different types of triggers?
A database trigger is a stored subprogram associated with a database table, view, or
Event. For instance, you can have Oracle fire a trigger automatically before or after
An INSERT, UPDATE, or DELETE statement affects a table.
A Database Trigger is a stored procedure that is fired when a DML operation is performed on the table. In total there are 13 types of Triggers
Syntax for creating a trigger:
CREATE OR REPLACE TRIGGER
[INSERT / UPDATE / DELTE] ON {For each Statement / Row} {When Types of Triggers: Before After For each Row For each Statement (default) Instead of Trigger: This trigger is defined on a view rather than a table. System Triggers: A new feature of Oracle8i, wherein the trigger is fired when the database startup / shutdown process. Schema Triggers: These triggers are fired whenever a DDL statement is executed. (Creation or Deletion of any DB Objects) Order of Trigger Firing: · Before Statement trigger (If present) · Each row affected by the statement (a) Execute row level trigger (If present) (b) Execute the statement itself (c) Execute the after row level trigger (If Present) · After statement trigger (If Present) 2. What are the different types of joins available in Oracle? Equi Join: When primary and foreign key relationship exists between the tables that are going to be joined. Self Join : If comparision comes in a single table Cartesian Join: When tables are joined without giving any join condition. Inner Join: The resultant set includes all the rows that satisfy the join condition. Outer Join: The resultant set includes the rows which doesn’t satisfy the join condition. The outer join operator Plus sign (+) will be included in the join condiiton. Example: SELECT a. column1, a. column2, b.column3….. from a, b where a.column1(+)=b.Column1 Here the rows from table a which doesn’t satisfy the join condition will also be fetched. 3. What are Indexes? What are the different types of Index? If a table consists of more than one Index how to enforce the statement to use the second Index? An Index is a DB object, which is used to improve the performance of the data retrieval. CREATE INDEX Types of Indexes: Bitmap Index (Used for Low cardinality column) Btree Index (Used for high cardinality column) Note: It is not possible to crea 4. What is Mutating Table? Table under transition is called Mutating Table. 5. What are views? What is Inline View?? Views are window to a table. It contains no data; it is based on the actual table called the base table or a view. Inline View means writing select statement in the Query itself instead of selecting a Column Name. Cursor is a private SQL area created in SGA to do multi row operation in a PL/SQL programmer Explicit Cursor, Implicit Cursor. Implicit Cursor: System (Oracle) automatically declares and uses for all DML SQL Statements. Explicit Cursor: Cursor declared explicitly in the PL/SQL programme to do multi row operation Syntax: Declare Cursor C1 is SELECT SAL, EMPNO FROM EMP X number; Y Varchar2 (30); Begin Open C1; Fetch C1 INTO x, y; Exit when c1%NOTFOUND End End; Exit in case for Cursor? A Cursor for loop can be used simplify the explicit cursor, no need to explicitly Open, fetch and close. No explicitly EXIT statement is required. %Found %NotFound %RowCount %IsOpen FOR UPDATE statement in Cursor is used to update a Column in the selected table by using the CURRENT OF A Package is a PL/SQL Construct that allow related object to be stored together. Package contains 2 parts, Package Specification and Package Body, each stored separately in the Data Dictionary. Once the Package is called all the related Procedure and functions of the package gets compiled and stored in the memory as P-code. How does u call a Package? DBMS_SQL, DBMS_JOBS, DBMS_DDL, DBMS_LOCK Overloading is oops concept (Object Oriented Programming) By Using the same name we can write any number of Procedure or functions in a package but either number of parameters in the procedure/function must be vary or parameter data type must vary. Function is an object that takes one or more arguments and returns only value. But in case of procedures we can return more than one parameters. Function always returns a value, whereas procedure may or may not return a value. UTL_FILE The SQL statements which are built at run time are called the Dynamic SQL. Dynamic SQL can be built by using DBMS_SQL package. Procedure of Dynamic SQL OPEN_CURSOR, PARSE, BIND_VARIABLE, DEFINE_COLUMN, EXECUTE, FETCH_ROWS, CLOSE_CURSOR. Oracle8i onwards there is another built in to construct Dynamic SQL called EXECUTE_IMMEDIATE. Declare a user defined exception? The error condition in PL/SQL is termed as an exception. Two types of Exception: Pre-Defined Exception: Example No_Data_Found, Storage Error, Zero_Error, Invlid_Cursor, Too_Many_Rows User-Defined Exception: Anything Syntax: Declare Xyz Exception; Begin SELECT ENAME FROM EMP RAISE XYZ; End; According to the Oracle standards “When Others “exception must be the last exception. All the Predefined exceptions must be used before the “When others” exception. If “When others” exception used before any pre-defined exceptions then procedure/function shows the compilations errors Bitmap Indexes, Drop a Column, Bulk Insert and Bulk Update Materialized views, Dynamic Sql (Execute Immediate etc) External tables (We query the data directly from a file like select * from “c:/abcd.csv”) Multi Table Insert with single command, resemble process etc. Developers? SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception By using this we can define our messages by handling the oracle messages Temporary tables are used to store the data temporarly. Mainly there are 2 types They are transaction and Session types Syntax: Create global temporary table This temporarly table is used to store the data temporally once you exit from session then that table will get erased a. User source table will stores the information of the user defined definitions b. All_Source and dba_source tables will stores the system defined schema objects definitions as well as user defined. c. All_Tab_Columns and ben_all_tab_columns are used to list out the all the columns name and respected table names also. Select lpad ('*', level * 2), empno, ename, mgr from EMP Connect by prior empno = mgr start with empno = 7839 It results the hierarchy of the employees Note: For Answers Check the Next Page * What is PL/SQL and what is it used for? * Should one use PL/SQL or Java to code procedures and triggers? * How can one see if somebody modified any code? * How can one search PL/SQL code for a key? * How can one keep a history of PL/SQL code changes? * How can I protect my PL/SQL source code? * Can one print to the screen from PL/SQL? * Can one read/write files from PL/SQL? * Can one call DDL statements from PL/SQL? * Can one use dynamic SQL statements from PL/SQL? * What is the difference between %TYPE and %ROWTYPE? * How does one get the value of a sequence into a PL/SQL variable? * Can one execute an operating system command from PL/SQL? * How does one loop through tables in PL/SQL? * How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy? * I can SELECT from SQL*Plus but not from PL/SQL. What is wrong? * What is a mutating and constraining table? * Can one pass an object/table as an argument to a remote procedure? * Is it better to put code in triggers or procedures? What is the difference? * Is there a PL/SQL Engine in SQL*Plus? * Is there a limit on the size of a PL/SQL block? * Where can one find more info about PL/SQL? What is PL/SQL and what is it used for? PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and data types are similar to that of Should one use PL/SQL or Java to code procedures and triggers? Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?” Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries. PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments: PL/SQL: Data centric and tightly integrated into the database Proprietary to Oracle and difficult to port to other database systems Data manupilation is slightly faster in PL/SQL than in Java Easier to use than Java (depending on your background) Java: Open standard, not proprietary to Oracle Incurs some data conversion overhead between the Database and Java type systems Java is more difficult to use (depending on your background) 1) How can one see if somebody modified any code? Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example: SELECT OBJECT_NAME, TO_CHAR (CREATED, 'DD-Mon-RR HH24: MI’) CREATE_TIME, TO_CHAR (LAST_DDL_TIME, 'DD-Mon-RR HH24: MI’) MOD_TIME, STATUS FROM USER_OBJECTS WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE'; How can one search PL/SQL code for a key? The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code. SELECT TYPE, NAME, LINE FROM USER_SOURCE WHERE UPPER (TEXT) LIKE '%&KEYWORD%'; * By using DBA_DEPENDENCIES table you can find out. - Ezhil How can one keep a history of PL/SQL code changes? One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes? Look at this example: CREATE TABLE SOURCE_HIST -- Create history table AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.* FROM USER_SOURCE WHERE 1=2; CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table AFTER CREATE ON SCOTT.SCHEMA -- Change SCOTT to your schema name DECLARE BEGIN if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE') then -- Store old code in SOURCE_HIST table INSERT INTO SOURCE_HIST SELECT sysdate, user_source.* FROM USER_SOURCE WHERE TYPE = DICTIONARY_OBJ_TYPE AND NAME = DICTIONARY_OBJ_NAME; end if; EXCEPTION WHEN OTHERS THEN raise_application_error(-20000, SQLERRM); END; / show errors How can I protect my PL/SQL source code? PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code. This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your Proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available. The syntax is: Wrap iname=myscript.sql oname=xxxx.plb Can one print to the screen from PL/SQL? One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus if you issue the SET Set serveroutput on Begin dbms_output.put_line ('Look Ma, I can print from PL/SQL!!!'); End; / DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, e.g.: set serveroutput on size 200000 If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL. If you haven't cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block. Can one read/write files from PL/SQL? Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command. Copy this example to get started: DECLARE fileHandler UTL_FILE.FILE_TYPE; BEGIN fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w'); UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n'); UTL_FILE.FCLOSE(fileHandler); EXCEPTION WHEN utl_file.invalid_path THEN raise_application_error (-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.'); END; / Can one call DDL statements from PL/SQL? One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDATE" statement. Users running Oracle versions below 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL). begin EXECUTE IMMEDIATE 'CREATE TABLE X (A DATE)'; End; NOTE: The DDL statement in quotes should not be terminated with a semicolon. Can one use dynamic SQL statements from PL/SQL? From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements. Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS cur integer; rc integer; BEGIN DBMS_SQL.PARSE (cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE); rc := DBMS_SQL.EXECUTE(cur); DBMS_SQL.CLOSE_CURSOR (cur); END; / Another example: CREATE OR REPLACE PROCEDURE DEPARTMENTS (NO IN DEPT.DEPTNO%TYPE) AS v_cursor integer; V_dname char (20); v_rows integer; BEGIN v_cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE (v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7); DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no); DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20); v_rows := DBMS_SQL.EXECUTE(v_cursor); loop If DBMS_SQL.FETCH_ROWS (v_cursor) = 0 then Exit; End if; DBMS_SQL.COLUMN_VALUE_CHAR (v_cursor, 1, v_dname); DBMS_OUTPUT.PUT_LINE ('Department name: '||v_dname); End loop; DBMS_SQL.CLOSE_CURSOR (v_cursor); EXCEPTION When others then DBMS_SQL.CLOSE_CURSOR (v_cursor); raise_application_error (-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm); END; / What is the difference between %TYPE and %ROWTYPE? The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs. %ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Example: DECLARE V_EmpRecord EMP%ROWTYPE; %TYPE is used to declare a field with the same type as that of a specified table's column. Example: DECLARE v_EmpNo emp.empno%TYPE; How does one get the value of a sequence into a PL/SQL variable? As you might know, oracle prohibits this: i := sq_sequence.NEXTVAL; (For some silly reason). But you can do this: Select sq_sequence.NEXTVAL into: i from dual; Thanks to Ronald van Woensel Can one execute an operating system command from PL/SQL? There is no direct way to execute operating system commands from PL/SQL in Oracle7. However, one can write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide. In Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example. How does one loop through tables in PL/SQL? Look at the following nested loop code example. DECLARE CURSOR dept_cur IS SELECT deptno FROM dept ORDER BY deptno; -- Employee cursor all employees for a dept number CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS SELECT ename FROM emp WHERE deptno = v_dept_no; BEGIN FOR dept_rec IN dept_cur dbms_output.put_line ('Employees in Department '||TO_CHAR (dept_rec.deptno)); FOR emp_rec in emp_cur (dept_rec.deptno) LOOP dbms_output.put_line ('...Employee is '||emp_rec.ename); END END END; / How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy? Contrary to popular believe, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the rollback segments will be cleared for new transactions, causing ORA-1555 errors. To fix this problem one can easily rewrite code like this: FOR records IN my_cursor LOOP ...do some stuff... COMMIT; END ... to ... FOR records IN my_cursor LOOP ...do some stuff... i := i+1; IF mod(i, 10000) THEN -- Commit every 10000 records COMMIT; END IF; END If you still get ORA-1555 errors, contact your DBA to increase the rollback segments. NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard. I can SELECT from SQL*Plus but not from PL/SQL. What is wrong? PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions: Grant direct access on the tables to your user. Do not use roles! GRANT select ON scott.emp TO my_user; Define your procedures with invoker rights (Oracle 8i and higher); Move all the tables to one user/schema. What is a mutating and constraining table? "Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state. Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well. There are several restrictions in Oracle regarding triggers: A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) . A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete. Etc. Can one pass an object/table as an argument to a remote procedure? The only way the same object type can be referenced between two databases is via a database link. Note that it is not enough to just use the same type definitions. Look at this example: -- Database A: receives a PL/SQL table from database B CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS BEGIN -- do something with TabX from database B null; END; / -- Database B: sends a PL/SQL table to database A CREATE OR REPLACE PROCEDURE pcalling IS TabX DBMS_SQL.VARCHAR2S@DBLINK2; BEGIN pcalled@DBLINK2(TabX); END; / Is it better to put code in triggers or procedures? What is the difference? In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers. Is there a PL/SQL Engine in SQL*Plus? No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus, all your PL/SQL is sent directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and sent to the database individually. Is there a limit on the size of a PL/SQL block? Yes, the max size is not an explicit byte limit, but related to the parse tree that is created when you compile the code. You can run the following select statement to query the size of an existing package or procedure: SQL> select * from dba_object_size where name = 'procedure_name'; Forms/Reports 1. How you declare global variables in forms ? Global variable will declared in When-New-Form-Instance Triggers 2. What are Table Handlers and Event Handlers? 3. What are new feature in forms 6i compared to forms4.5 4.What is the Difference between callform, Newform, Openform 5.What is the Use of Program Units in form/reports ? 6.How many triggers is there in reports and what are they and what is the Order of firing Before Parameter Form After Parameter Form Before report Between pages After Report 7.Which trigger will get fired while opening an LOV in forms Key-List-Value 8. How Many Types of reports are there? Name it?? 9.What is the Use of Anchors in Reports 10. What is the uses and differences between Summary Column, Formula column and Place holder column? 11. What is the Difference between Bind parameter and lexical parameter? Which trigger will be used to specify the conditions for a lexical parameter. 12. What is the use of Destype, Desname, Desformat in Parameter form. 13.When the Between Pages Trigger will fire? After first page this trigger will fire until last page and after that for last page it wont fire. 14. What are the form Modules? They are 4 types of form modules are there 1) Alerts 2) Forms Modules 3) Menu Modules 4) PLSQL Libraries 15.Some of the New Features in Reports6i In 6i we can generate the report in different types like PDF,HTML,XML, RTF etc In reports goto -> Layout model à Header or Body or Margin sections à property Pallateà Distributions. Specify the Type and file name with path. Then gotoà File à Distribute. 16. What is the difference between Format Triggers and Action Triggers Action Trigger is Procedure whereas Format Trigger is Procedure By using Action trigger we can open the other form or report 17 What is the Difference between FlexMode and Confine Mode and their differences 18. What is the Order of triggers firing W-N-F-I, Pre-Form, W-N-I-I, W-N-B-I ?? 19 What is the Major Differences and uses between Property Class and Visual Attribute 20. Other than Run_Product how can we run a report from a form By using Run_Report_Object function we can run the reports( This is 6i New Feature) To use this Add that report in Form Object Navigator and pass the Id of that report. 21. What are the Default triggers will be created when a master-detail form is created There are 3 types are triggers will be created in form level when a master-detail form is created. 22. What is the Diiference between .pll, .pls and .plx in Libraries 23. What is Object Library and Attached Library Object Library can be used to stored Function, Procedure, Package. Attached library will be used to avoid any change in source code. Object library can be converted into .PLX and attached to Attache library. 24 What is the difference between writing code in Programme Unit and Library Files? The code written in Programme unit is form’s specific, whereas code written in Library files, can be used across the forms. 25. When a form is run, which are the triggers fire, and in what sequence they fire? PRE-FORM WHEN-NEW-FORM-INSTANCE PRE-BLOCK WHEN-NEW-BLOCK-INSTANCE WHEN-NEW-ITEM-INSTANCE POST-BLOCK POST-FORM Tab Page utility is not available in 4.5 Version RUN_REPORT Property Class is defining properties of objects along with their settings. The property class inheritance allows the user to perform global changes very quickly and efficiently. Methods: Object Navigator Method Properties Window Method which one will fire first? The trigger written at the lower level Item Level Fires first then at Block Level and at last it fires in Form Level. Execution? If Yes, where it needs to be changed? Yes, in the trigger property (Before, After, Default)by changing the attributes. System and User defined Parameters.(Bind and Lexical Parameters) 1.EXPLAIN PL/SQL, PL/SQLBLOCK? PL/SQL is a procedural language that has both interactive SQL and procedural language constructs such as iterations, conditional branching. PL/SQL block is a block-structured language. Each block is supposed to perform one logical unit of job. 2.MAIN BLOCKS OF PL/SQL? PL/SQL block is having 3 parts · Declaration part · Executable part · Exception handling part Declaration part: PL/SQL enforces the declaration of variables before their use in executable portion. All variables (or) constants have to be declared in this block. Syntax: variable name data type; Executable part: this is main section of the block, all the procedural and SQL statements are defined here. Exception handling part: this is used for responding to runtime errors encountered by a program. MANDATORY 3.WHAT IS NAMED BLOCK? Named blocks are the blocks that have a name associated with 3 types · Labeled blocks: blocks with a label that gives the block name. · Sub program: consists of procedures and functions. · Triggers: consists of pl/sql block that is associated with an event that occurs in the database. 4.WHAT IS ANONYMOUS BLOCK? These blocks are generally constructed dynamically and execute only once. Block is often issued from a client program to call a sub program in the database. 5.EXPLAIN % TYPE, % % TYPE: it is used to give data type of predefined variable and database column. Ex: declare Item code number (10); I code item code% type; %ROWTYPE: it is used to provide record data type to a variable. The variable can stop row of the table (or) row fetched from the cursor. 6.DATA TYPES IN PL/SQL? · Scalar data type: number, character, Boolean, date/time. · Composite data type: table, record. · Reference data type: ref cursor. · Lob types: (large objects) Bfile (variable stores locator of the file) BLob (for storing large raw data like graphics or sound data) Clob(stores location, which provides location of data) PL/SQL TABLES: these are temporary array like objects used in pl/sql block. These can have one column & a primary key. These are declared in the declarative part of any block, sub program Or package. Syntax: STEP1: type STEP2: NESTED TABLES: similarly to PL/SQL block along with adding the ability to store nested tables within a database table will be there. Syntax: type table name is table of table type; VARRAYS: this are implemented differently, elements are inserted into varray starting at index1 upto maximum length declared in varray. Syntax: type type-name is varry(max-size) of element-type(not null); 8.CAN WE PASS PL/SQL TABLE AS APARAMETER TO ANOTHER PROCEDURE OR NOT, IF SOHOW WILL IT BE PASSED? GIVE THE SYNTAX? 9.EXPLAIN AUTONOMOUS TRANSACTION, RESTRICT-REFERENCE AND EXCEPTION-INIT? AUTONOMOUS TRANSACTION: it is used when ever in a transaction with in another transaction should be committed or rollback irrespective of parent transaction commit or rollback. RESTRICT-REFERENCE: it is used to assert the purity level for the user-defined functions. Syntax: PRAGMA RESTRICT-REFERENCES (function-name, [rnds], [wnds], [rnps], [wnps]) EXCEPTION-INIT: used for associating a named exception with in a particular oracle error Syntax: PRAGMA EXCEPTION-INIT (exception-name, oracle error number) 10.WHAT IS PRAGMA? Pragmas are compiler directives, it serves as instructions to the pl/sql compiler. The compiler will act on the pragma during the compilation of the block. 11.WHAT IS Exception handling is used to handle the errors according to users way and functions It will be used to generate error messages and replacing default messages. These are 2 types standard & user defined exceptions. STANDARD EXCEPTIONS 0R BUILT IN EXCEPTIONS: - Oracle package standard had defined exceptions for certain common errors Some of them are: · Too-many-rows (ora-01422) · No-data-found (ora-1403) · Value-error (ora-06502) · Zero-divide (ora-01476) · Invalid-number (ora-01722) · Dup-value-on-index (ora-00001) · Program-error (ora-06501) USER DEFINED EXCEPTIONS: The user defines these exceptions and these are used to take care of abnormal conditions that are application specific. 12.EXPLAIN SQL CODE & SQLERRM? These are functions, which return error code and error message of the recent error. SQL CODE: it returns error code as negative number. For NO-DATA-FOUND it returns ‘+100’. SQL ERRRM: it returns length of the messages in 512 characters, which includes code, message, tablename&column name. Syntax: ercode: =sqlcode; Er msg: =sqlerrm; Insert into error table values (errcode, ermsg); 13.WHAT IS RAISE-APPLICATION-ERROR? This is a procedure used to generate user-defined errors. Syntax: raise-application-error (errorcode, errormessage, true/false); Error code- (range is –20000 to-20999). Error message (length is 2048 bytes). True/false- true indicates error is put in stack False is mentioned then the error replace all the previous errors. 14. What is a cursor? Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows. 15. What is a cursor for loop? Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed. 16.For Update Of Clause: -when declaring the cursor itself we will be mentioning for update clause then we can update the records inside of the cursor. 17.Where Current Of Clause: - 18.PROCEDURE: -A procedure is a logically grouped set of SQL and PL/SQL statements that perform a specific task. it may or may not return a value. Procedures are made up of · Declaration part · Executable part · Exceptional part Here declarative part and executable part are mandatory while exceptional part is optional. Syntax: -CREATE OR REPLACE PROCEDURE procedure name {IN, OUT, INOUT} {IS, AS} Variable declaration; Constant declaration; Begin PL/SQL subprogram body; Exception Exception block; End; 19.FUNCTION: - A procedure is a logically grouped set of SQL and PL/SQL statements that perform a specific task. It returns a value. Functions having Declaration block Executable block Exception block Syntax: - CREATE OR REPLACE FUNCTION function name {IN} Return data type {IS, AS} Variable declaration; Constant declaration; Begin PL/SQL subprogram body; Exception Exception block; End; 20.difference between procedure and function? · Procedures may or may not return a value but function should return a value · Procedures we cannot use inside of select statement Functions are used inside of select statement 21.PARAMETER MODES (IN, OUT, INOUT) IN: - when we pass the parameter in IN mode that will work like a constant inside a procedure. OUT: -this is used to return a value. INOUT: -this will be using in both way. 22.ACTUAL PARAMETERS: -while calling the procedure will pass the values this will be calling it as actual parameters The procedure declaration variables will be receiving these values called FORMAL parameters 23.PROCEDURE OVERLOADING: -multiple procedures that are declared with the same name are called overloading procedures. 24.FUNCTION OVERLOADING: - multiple functions that are declared with the same name are called overloading functions. 25.PROCEDURE, FUNCTION FORWARD DECLARATION: - 26.PACKAGE: -A package is an oracle object, which holds other objects with in it these objects may be · · Functions · Cursors , which are logically related. · Variables · Constants 27.COMPONENTS OF PACKAGE: - A package has usually two components Specification Body A package specification declares the types, memory variables, constants, exceptions, cursors and subprograms that are a variable for use. A package body fully defines cursors, procedures and thus implements the specification. 28.package body with out specification is possible or not? 29.can we define cursor inside with out package? If so how to call the cursor? 30.what is cursor variable? 31. We created specification and body, if we delete specification whether the body will present or not? 32.we have package body and specification inside of the package we are writing procedure to insert some thing to a table, if we delete the table, the above package will valid or not? 33.we have package and we have grants to execute that package inside of that we have table, here we don’t have privileges to this table? Whether this table will execute or not? 34.TRIGGERS: Trigger is a pl/sql block, which will fire automatically whenever some event occurs like insert, update and delete. Types of triggers: - Event Insert Update level Delete Row level Time statement level Before After Row level triggers: -it will fire for each row Statement level triggers: -it will fire only once for the whole statements. 35.can we use DLL command inside of trigger? If not then what is the alternative. 36.TRIGGER PREDICATES? These are 3 types · Inserting · Updating · Deleting Whenever we want to do inserting inside of the trigger that time we will be using inserting predicate trigger.llly for updating and deleting. 37. : NEW and :OLD Both will be used in triggers to get the new and old values. In case of update both NEW and OLD are valid. In case of delete only OLD is valid. In case of insert only NEW is valid. Both: NEW and :OLD will work only for row level triggers. .(
Procedures
