Saturday, April 25, 2009

MS DOT NET FAQS

2 . What’s the difference between Response.Write() andResponse.Output.Write()?
Response.Output.Write() allows you to write formatted output.
3 . What methods are fired during the page load?
Init() - when the page is instantiated
Load() - when the page is loaded into server memory
PreRender() - the brief moment before the page is displayed to the user as HTML
Unload() - when page finishes loading.
4 . When during the page processing cycle is ViewState available?
After the Init() and before the Page_Load(), or OnLoad() for a control.
5 . What namespace does the Web page belong in the .NET Framework class hierarchy?
System.Web.UI.Page
6 . Where do you store the information about the user’s locale?
System.Web.UI.Page.Culture
7 . What’s the difference between Codebehind="MyCode.aspx.cs" andSrc="MyCode.aspx.cs"?
CodeBehind is relevant to Visual Studio.NET only.
8 . What’s a bubbled event?
When you have a complex control, like DataGrid, writing an event processing routine for each object (cell, button, row, etc.) is quite tedious. The controls can bubble up their eventhandlers, allowing the main DataGrid event handler to take care of its constituents.
9 . Suppose you want a certain ASP.NET function executed on MouseOver for a certain button. Where do you add an event handler?
Add an OnMouseOver attribute to the button. Example: btnSubmit.Attributes.Add("onmouseover","someClientCodeHere();");
10 . What data types do the RangeValidator control support?
Integer, String, and Date.
11 . Explain the differences between Server-side and Client-side code?
Server-side code executes on the server. Client-side code executes in the client's browser.
12 . What type of code (server or client) is found in a Code-Behind class?
The answer is server-side code since code-behind is executed on the server. However, during the code-behind's execution on the server, it can render client-side code such as JavaScript to be processed in the clients browser. But just to be clear, code-behind executes on the server, thus making it server-side code.
13 . Should user input data validation occur server-side or client-side? Why?
All user input data validation should occur on the server at a minimum. Additionally, client-side validation can be performed where deemed appropriate and feasable to provide a richer, more responsive experience for the user.
14 . What is the difference between Server.Transfer and Response.Redirect? Why would I choose one over the other?
Server.Transfer transfers page processing from one page directly to the next page without making a round-trip back to the client's browser. This provides a faster response with a little less overhead on the server. Server.Transfer does not update the clients url history list or current url. Response.Redirect is used to redirect the user's browser to another page or site. This performas a trip back to the client where the client's browser is redirected to the new page. The user's browser history list is updated to reflect the new address.
15 . Can you explain the difference between an ADO.NET Dataset and an ADO Recordset?
Valid answers are:
• A DataSet can represent an entire relational database in memory, complete with tables, relations, and views.
• A DataSet is designed to work without any continuing connection to the original data source.
• Data in a DataSet is bulk-loaded, rather than being loaded on demand.
• There's no concept of cursor types in a DataSet.
• DataSets have no current record pointer You can use For Each loops to move through the data.
• You can store many edits in a DataSet, and write them to the original data source in a single operation.
• Though the DataSet is universal, other objects in ADO.NET come in different versions for different data sources.

16 . What is the Global.asax used for?
The Global.asax (including the Global.asax.cs file) is used to implement application and session level events.
17 . What are the Application_Start and Session_Start subroutines used for?
This is where you can set the specific variables for the Application and Session objects.
18 . Can you explain what inheritance is and an example of when you might use it?
When you want to inherit (use the functionality of) another class. Example: With a base class named Employee, a Manager class could be derived from the Employee base class.
19 . Whats an assembly?
Assemblies are the building blocks of the .NET framework. Overview of assemblies from MSDN
20 . Describe the difference between inline and code behind.
Inline code written along side the html in a page. Code-behind is code written in a separate file and referenced by the .aspx page.
21 . Explain what a diffgram is, and a good use for one?
The DiffGram is one of the two XML formats that you can use to render DataSet object contents to XML. A good use is reading database data to an XML file to be sent to a Web Service.
22 . Whats MSIL, and why should my developers need an appreciation of it if at all?
MSIL is the Microsoft Intermediate Language. All .NET compatible languages will get converted to MSIL. MSIL also allows the .NET Framework to JIT compile the assembly on the installed computer.
23 . Which method do you invoke on the DataAdapter control to load your generated dataset with data?
The Fill() method.
24 . Can you edit data in the Repeater control?
No, it just reads the information from its data source.
25 . Which template must you provide, in order to display data in a Repeater control?
ItemTemplate.
26 . How can you provide an alternating color scheme in a Repeater control?
Use the AlternatingItemTemplate.
27 . What property must you set, and what method must you call in your code, in order to bind the data from a data source to the Repeater control?
You must set the DataSource property and call the DataBind method.
28 . What base class do all Web Forms inherit from?
The Page class.
29 . Name two properties common in every validation control?
ControlToValidate property and Text property.
30 . Which property on a Combo Box do you set with a column name, prior to setting the DataSource, to display data in the combo box?
DataTextField property.
31 . Which control would you use if you needed to make sure the values in two different controls matched?
CompareValidator control.
32 . How many classes can a single .NET DLL contain?
It can contain many classes.



Web Service Questions
33 . What is the transport protocol you use to call a Web service?
SOAP (Simple Object Access Protocol) is the preferred protocol.
34 . What does WSDL stand for?
Web Services Description Language.
35 . Where on the Internet would you look for Web services?
http://www.uddi.org
36 . True or False: To test a Web service you must create a Windows application or Web application to consume this service?
False, the web service comes with a test page and it provides HTTP-GET method to test.
37 . Can you give an example of when it would be appropriate to use a web service as opposed to a non-serviced .NET component
Webservice is one of main component in Service Oriented Architecture. You could use webservices when your clients and servers are running on different networks and also different platforms. This provides a loosely coupled system. And also if the client is behind the firewall it would be easy to use webserivce since it runs on port 80 (by default) instead of having some thing else in SOA apps

State Management Questions

38 . What is ViewState?
ViewState allows the state of objects (serializable) to be stored in a hidden field on the page. ViewState is transported to the client and back to the server, and is not stored on the server or any other external source. ViewState is used the retain the state of server-side objects between postabacks.
39 . What is the lifespan for items stored in ViewState?
Item stored in ViewState exist for the life of the current page. This includes postbacks (to the same page).
40 . What does the "EnableViewState" property do? Why would I want it on or off?
It allows the page to save the users input on a form across postbacks. It saves the server-side values for a given control into ViewState, which is stored as a hidden value on the page before sending the page to the clients browser. When the page is posted back to the server the server control is recreated with the state stored in viewstate.
41 . What are the different types of Session state management options available with ASP.NET?
ASP.NET provides In-Process and Out-of-Process state management. In-Process stores the session in memory on the web server. This requires the a "sticky-server" (or no load-balancing) so that the user is always reconnected to the same web server. Out-of-Process Session state management stores data in an external data source. The external data source may be either a SQL Server or a State Server service. Out-of-Process state management requires that all objects stored in session are serializable.
42 . Let's say I have an existing application written using Visual Studio 6 (VB 6, InterDev 6) and this application utilizes Windows 2000 COM+ transaction services. How would you approach migrating this application to .NET
You have to use System.EnterpriseServices namespace and also COMInterop the existing application
43 . Can you give an example of what might be best suited to place in the Application_Start and Session_Start subroutines?
In the Application_Start event you could store the data, which is used throughout the life time of an application for example application name, where as Session_Start could be used to store the information, which is required for that session of the application say for example user id or user name.
44 . If I'm developing an application that must accomodate multiple security levels though secure login and my ASP.NET web appplication is spanned across three web-servers (using round-robbin load balancing) what would be the best approach to maintain login-in state for the users?
Use the state server or store the state in the database. This can be easily done through simple setting change in the web.config.
mode="InProc"
stateConnectionString="tcpip=127.0.0.1:42424"
sqlConnectionString="data source=127.0.0.1;user id=sa;password="
cookieless="false"
timeout="30"
/>
in the above one instead of mode="InProc", you specifiy stateserver or sqlserver.
45 . What are ASP.NET Web Forms? How is this technology different than what is available though ASP (1.0-3.0)?
ASP.NET webforms are analogous to Windows Forms which are available to most VB developers. A webform is essentially a core container in a Page. An empty webform is nothing but a HTML Form tag(control) running at server and posting form to itself by default, but you could change it to post it to something else. This is a container, and you could place the web controls, user controls and HTML Controls in that one and interact with user on a postback basis.
46 . How does VB.NET/C# achieve polymorphism?
Polymorphism is achieved through virtual, overloaded, overridden methods in C# and VB.NET
47 . Describe session handling in a webform, how does it work and what are the its limits

Sometimes it is necessary to carry a particular session data across pages.
And HTTP is a stateless protocol. In order to maintain state between
page calls, we could use cookies, hidden form fields etc. One of them is
using sessions. each sessions are maintain a unique key on the server and
serialize the data on it. Actually it is a hashtable and stores data on key/value
pair of combination. You could set a session using Session Object and retrieve the same data/state
by passing the key.
//Set
Session["abc"] = "Session Value";
// Get
string abc = Session["abc"].ToString();
The downside of sessions is scalability. Say your application gets more and more hits
and you though instead of one webserver handling it, have it in a webfarm (multiple web
servers working under one domain). You cannot transfer the session so easily across multiple
webservers. Reason is like I said, it physically serializes the state data to webserver hard disk.
.NET proposes a new way to handle this using a stateserver (actually a trimmed down sql server)
storing the web session data in a factory configured database schema or using Database with your own
schema defined to handle the sessions.
48 . How would you get ASP.NET running in Apache web servers - why would you even do this?
You need to create a CLRHost, which hosts the CLR (ASP.NET) on top of Apache.
Since Apache is #1 webserver used by many companies, this would allow more number of web site owners
to take advantage of ASP.NET and its richness.
49 . Whats MSIL, and why should my developers need an appreciation of it if at all?

MSIL is Microsoft Intermediate (Intermediary) Language. It is Microsoft's implementation of CIL (standard recognized
by ECMA and ISO) as part of CLI and C# Standardization.

.NET supports more than 21 language (I think 24 now). They compile to IL first and then this IL would get JITted to Native
code at runtime. Learning IL is advantageous in many terms. The important one is sometimes you need to optimize your
code, so you could disassemble your compile assembly using ILDASM and tweak your code and re assemble it using ILASM.
50 . In what order do the events of an ASPX page execute. As a developer is it important to undertsand these events?
This is the order of Page events
i. Page_Init
ii.Page_LoadViewState
iii. Page_LoadPostData
iv. Page_Load
v. Page_RaisePostDataChanged
vi. Page_RaisePostBackEvent
vii. Page_PreRender
viii. Page_SaveViewState
ix. Page_Render
x. Page_Dispose
xii. Page_Error (this is caused whenever there is an exception at the page level).

Out of all the Page_Load is the one where your code gets loaded and your magic should be written. page_init
occurs only once, i.e. when the page is initially created.

As a developer you need to know these, becuase your development activity is coding for these only.
51 . Which method do you invoke on the DataAdapter control to load your generated dataset with data?
Fill()
52 . Can you edit data in the Repeater control?
No. Only DataList and DataGrid provide you editing capabilities.
53 . What method do you use to explicitly kill a user s session?
Session.Abandon
54 . How do you turn off cookies for one page in your site?
Actually I never did this. But there should be a way to do this. May be need to
write your own code to do this using Response.Cookies collection and HTTPCookie class and
also SessionStateMode. Or there may be some simple way to do it. Need to do further research on this.
55 . Which two properties are on every validation control?
The common properties are:
i. IsValid (bool)
ii. ControlToValidate (string)
iii. ErrorMessage (string)
iv. ValidationDisplay (Display)
v. Text (string)
The common method is:
Validate()
56 . What tags do you need to add within the asp:datagrid tags to bind columns manually?
You need to set AutoGenerateColumns Property to false.
57 . How do you create a permanent cookie?
If you are developing web services and the cookies need to be travelled across multiple requests, then
you need to have permanent or persistant cookie.
In order to do this, you have to set the your webserivce CookieContainer to a newly created CookieContainer, and the
its cookie to a session value and then store the cookie(s) into the Service CookieCollection from that cookie container
if something is there othere wise add cookie to the container.
58 . What tag do you use to add a hyperlink column to the DataGrid?
HyperLinkColumn
59 . What is the standard you use to wrap up a call to a Web service
SOAP.
60 . Which method do you use to redirect the user to another page without performing a round trip to the client?
Server.Transfer
Response.Redirect also does that but it requires round trip between client and server.
61 . What does WSDL stand for?
Web Services Description Language.
62 . True or False: A Web service can only be written in .NET
False.
63 . What property do you have to set to tell the grid which page to go to when using the Pager object?
CurrentPageIndex. You need to set this one with the DataGridPageChangedEventArgs' NewPageIndex.
64 . Which control would you use if you needed to make sure the values in two different controls matched?
Use CompareValidator
65 . True or False: To test a Web service you must create a windows application or Web application to consume this service?
False. The webservice comes with a test page and it provides HTTP-GET method to test.
And if the web service turned off HTTP-GET for security purposes then you need to create
a web application or windows app as a client to this to test.
66 . How many classes can a single .NET DLL contain?
many is correct. Yes an assembly can contain one or more classes and an assembly can
be contained in one dll or could spread across multiple dlls. too. Take System.dll, it is
collections of so many classes.
67 . Why would you use an array vs linked-list ?
Linked List:
? They allow a new element to be inserted or deleted at any position in a constant number of operations (changing some references) O(1).
? Easy to delete a node (as it only has to rearrange the links to the different nodes)., O(1).
? To find the nth node, will need to recurse through the list till it finds [linked lists allow only sequential access to elements. ], O(n)

Array
? Insertion or deletion of element at any position require a linear (O(n)) number of operations.
? Poor at deleting nodes (or elements) as it cannot remove one node without individually shifting all the elements up the list by one., O(n)
? Poor at inserting as an array will eventually either fill up or need to be resized, an expensive operation that may not even be possible if memory is fragmented. Similarly, an array from which many elements are removed may become wastefully empty or need to be made smaller, O(n)

? easy to find the nth element in the array by directly referencing them by their position in the array.[ arrays allow random access ] , O(1)

PL/SQL FAQS FOR JNTU II BTech II Sem

PL/SQL FAQ
From Oracle FAQ
Jump to: navigation, search
PL/SQL FAQ - Oracle's Procedural Language extension to SQL:
Contents
[hide]
• 1 What is PL/SQL and what is it used for?
• 2 What is the difference between SQL and PL/SQL?
• 3 Should one use PL/SQL or Java to code procedures and triggers?
• 4 How can one see if somebody modified any code?
• 5 How can one search PL/SQL code for a string/ key value?
• 6 How can one keep a history of PL/SQL code changes?
• 7 How can I protect my PL/SQL source code?
• 8 Can one print to the screen from PL/SQL?
• 9 Can one read/write files from PL/SQL?
• 10 Can one call DDL statements from PL/SQL?
• 11 Can one use dynamic SQL statements from PL/SQL?
• 12 What is the difference between %TYPE and %ROWTYPE?
• 13 How does one get the value of a sequence into a PL/SQL variable?
• 14 Can one execute an operating system command from PL/SQL?
• 15 How does one loop through tables in PL/SQL?
• 16 How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
• 17 I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
• 18 What is a mutating and constraining table?
• 19 Can one pass an object/table as an argument to a remote procedure?
• 20 What is the difference between stored procedures and functions?
• 21 Is there a PL/SQL Engine in SQL*Plus?
• 22 Is there a limit on the size of a PL/SQL block?
• 23 What's the PL/SQL compiler limits for block, record, subquery and label nesting?
• 24 Can one COMMIT/ ROLLBACK from within a trigger?

[edit]
What is PL/SQL and what is it used for?
SQL is a declarative language that allows database programmers to write a SQL declaration and hand it to the database for execution. As such, SQL cannot be used to execute procedural code with conditional, iterative and sequential statements. To overcome this limitation, PL/SQL was created.
PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and data types are similar to that of Ada. Some of the statements provided by PL/SQL:
Conditional Control Statements:
• IF ... THEN ... ELSIF ... ELSE ... END IF;
• CASE ... WHEN ... THEN ... ELSE ... END CASE;
Iterative Statements:
• LOOP ... END LOOP;
• WHILE ... LOOP ... END LOOP;
• FOR ... IN [REVERSE] ... LOOP ... END LOOP;
Sequential Control Statements:
• GOTO ...;
• NULL;
The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance).
PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.
Example PL/SQL blocks:
/* Remember to SET SERVEROUTPUT ON to see the output */
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/
BEGIN
-- A PL/SQL cursor
FOR cursor1 IN (SELECT * FROM table1) -- This is an embedded SQL statement
LOOP
DBMS_OUTPUT.PUT_LINE('Column 1 = ' || cursor1.column1 ||
', Column 2 = ' || cursor1.column2);
END LOOP;
END;
/
[edit]
What is the difference between SQL and PL/SQL?
Both SQL and PL/SQL are languages used to access data within Oracle databases.
SQL is a limited language that allows you to directly interact with the database. You can write queries (SELECT), manipulate objects (DDL) and data (DML) with SQL. However, SQL doesn't include all the things that normal programming languages have, such as loops and IF...THEN...ELSE statements.
PL/SQL is a normal programming language that includes all the features of most other programming languages. But, it has one thing that other programming languages don't have: the ability to easily integrate with SQL.
Some of the differences:
• SQL is executed one statement at a time. PL/SQL is executed as a block of code.
• SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tell the database how to do things (procedural).
• SQL is used to code queries, DML and DDL statements. PL/SQL is used to code program blocks, triggers, functions, procedures and packages.
• You can embed SQL in a PL/SQL program, but you cannot embed PL/SQL within a SQL statement.
[edit]
Should one use PL/SQL or Java to code procedures and triggers?
Both PL/SQL and Java can be used to create Oracle stored procedures and triggers. This often 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 9i supports native compilation of Pl/SQL code to binaries. Not to mention the numerous PL/SQL enhancements made in Oracle 10g and 11g.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the similarities and difference between these two language environments:
PL/SQL:
• Can be used to create Oracle packages, procedures and triggers
• Data centric and tightly integrated into the database
• Proprietary to Oracle and difficult to port to other database systems
• Data manipulation is slightly faster in PL/SQL than in Java
• PL/SQL is a traditional procedural programming language
Java:
• Can be used to create Oracle packages, procedures and triggers
• Open standard, not proprietary to Oracle
• Incurs some data conversion overhead between the Database and Java type

• Java is an Object Orientated language, and modules are structured into classes
• Java can be used to produce complete applications
PS: Starting with Oracle 10g, .NET procedures can also be stored within the database (Windows only). Nevertheless, unlike PL/SQL and JAVA, .NET code is not usable on non-Windows systems.
[edit]
How can one see if somebody modified any code?
The source code for stored procedures, functions and packages are stored in the Oracle Data Dictionary. One can detect code changes by looking at the TIMESTAMP and 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';
Note: If you recompile an object, the LAST_DDL_TIME column is updated, but the TIMESTAMP column is not updated. If you modified the code, both the TIMESTAMP and LAST_DDL_TIME columns are updated.
[edit]
How can one search PL/SQL code for a string/ key value?
The following query is handy if you want to know where certain tables, columns and expressions are referenced in your PL/SQL source code.
SELECT type, name, line
FROM user_source
WHERE UPPER(text) LIKE UPPER('%&KEYWORD%');
If you run the above query from SQL*Plus, enter the string you are searching for when prompted for KEYWORD. If not, replace &KEYWORD with the string you are searching for.
[edit]
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
A better approach is to create an external CVS or SVN archive for the scripts that installs the PL/SQL code. The canonical version of what's in the database must match the latest CVS/SVN version or else someone would be cheating.
[edit]
How can I protect my PL/SQL source code?
Oracle provides a binary wrapper utility that can be used to scramble PL/SQL source code. This utility was introduced in Oracle7.2 (PL/SQL V2.2) and is located in the ORACLE_HOME/bin directory.
The utility use human-readable PL/SQL source code as input, and writes out portable binary object code (somewhat larger than the original). The binary code can be distributed without fear of exposing your proprietary algorithms and methods. Oracle will still understand and know how to execute the code. Just be careful, there is no "decode" command available. So, don't lose your source!
The syntax is:
wrap iname=myscript.pls oname=xxxx.plb
Please note: there is no way to unwrap a *.plb binary file. You are supposed to backup and keep your *.pls source files after wrapping them.
[edit]
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 SERVEROUTPUT ON; command. For example:
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, eg.: 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.
Note that DBMS_OUTPUT doesn't print blank or NULL lines. To overcome this problem, SET SERVEROUTPUT ON FORMAT WRAP; Look at this example with this option first disabled and then enabled:
SQL> SET SERVEROUTPUT ON
SQL> begin
2 dbms_output.put_line('The next line is blank');
3 dbms_output.put_line();
4 dbms_output.put_line('The above line should be blank');
5 end;
6 /
The next line is blank
The above line should be blank
SQL> SET SERVEROUTPUT ON FORMAT WRAP
SQL> begin
2 dbms_output.put_line('The next line is blank');
3 dbms_output.put_line();
4 dbms_output.put_line('The above line should be blank');
5 end;
6 /
The next line is blank

The above line should be blank
[edit]
Can one read/write files from PL/SQL?
The UTL_FILE database package can be used to read and write operating system files.
A DBA user needs to grant you access to read from/ write to a specific directory before using this package. Here is an example:
CONNECT / AS SYSDBA
CREATE OR REPLACE DIRECTORY mydir AS '/tmp';
GRANT read, write ON DIRECTORY mydir TO scott;
Provide user access to the UTL_FILE package (created by catproc.sql):
GRANT EXECUTE ON UTL_FILE TO scott;
Copy and paste these examples to get you started:
Write File
DECLARE
fHandler UTL_FILE.FILE_TYPE;
BEGIN
fHandler := UTL_FILE.FOPEN('MYDIR', 'myfile', 'w');
UTL_FILE.PUTF(fHandler, 'Look ma, Im writing to a file!!!\n');
UTL_FILE.FCLOSE(fHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
END;
/
Read File
DECLARE
fHandler UTL_FILE.FILE_TYPE;
buf varchar2(4000);
BEGIN
fHandler := UTL_FILE.FOPEN('MYDIR', 'myfile', 'r');
UTL_FILE.GET_LINE(fHandler, buf);
dbms_output.put_line('DATA FROM FILE: '||buf);
UTL_FILE.FCLOSE(fHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
END;
/
NOTE: UTL_FILE was introduced with Oracle 7.3. Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
[edit]
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 IMMEDIATE" statement (native SQL). Examples:
begin
EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
end;
begin execute Immediate 'TRUNCATE TABLE emp'; end;
DECLARE
var VARCHAR2(100);
BEGIN
var := 'CREATE TABLE temp1(col1 NUMBER(2))';
EXECUTE IMMEDIATE var;
END;
NOTE: The DDL statement in quotes should not be terminated with a semicolon.
Users running Oracle versions below Oracle 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL).
[edit]
Can one use dynamic SQL statements from PL/SQL?
Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that the statements within quotes are NOT semicolon terminated:
EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';

-- Using bind variables...'
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

-- Returning a cursor...
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:
CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
/
More complex DBMS_SQL example using bind variables:
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('Deptartment 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;
/
[edit]
What is the difference between %TYPE and %ROWTYPE?
Both %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automically picks up the new definition from the database without having to make any code changes.
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.
%TYPE
%TYPE is used to declare a field with the same type as that of a specified table's column. Example:
DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/
%ROWTYPE
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Examples:
DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
/
[edit]
How does one get the value of a sequence into a PL/SQL variable?
As you might know, one cannot use sequences directly from PL/SQL. Oracle (for some silly reason) prohibits this:
i := sq_sequence.NEXTVAL;
However, one can use embedded SQL statements to obtain sequence values:
select sq_sequence.NEXTVAL into :i from dual;
This FAQ was contributed by Ronald van Woensel
[edit]
Can one execute an operating system command from PL/SQL?
There is no direct way to execute operating system commands from PL/SQL. PL/SQL doesn't have a "host" command, as with SQL*Plus, that allows users to call OS commands. Nevertheless, the following workarounds can be used:
Database Pipes
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.
CREATE OR REPLACE FUNCTION host_command( cmd IN VARCHAR2 )
RETURN INTEGER IS
status NUMBER;
errormsg VARCHAR2(80);
pipe_name VARCHAR2(30);
BEGIN
pipe_name := 'HOST_PIPE';
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message(pipe_name);
RETURN status;
END;
/
External Procedure Listeners:
From Oracle 8 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.
Using Java
See example at http://www.orafaq.com/scripts/plsql/oscmd.txt
DBMS_SCHEDULER
In Oracle 10g and above, one can execute OS commands via the DBMS_SCHEDULER package. Look at this example:
BEGIN
dbms_scheduler.create_job(job_name => 'myjob',
job_type => 'executable',
job_action => '/app/oracle/x.sh',
enabled => TRUE,
auto_drop => TRUE);
END;
/

exec dbms_scheduler.run_job('myjob');
[edit]
How does one loop through tables in PL/SQL?
One can make use of cursors to loop through data within tables. Look at the following nested loops 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 LOOP
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 LOOP;

END LOOP;
END;
/
[edit]
How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular belief, 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 undo/ 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 LOOP;
COMMIT;
... to ...
FOR records IN my_cursor LOOP
...do some stuff...
i := i+1;
IF mod(i, 10000) = 0 THEN -- Commit every 10000 records
COMMIT;
END IF;
END LOOP;
COMMIT;
If you still get ORA-1555 errors, contact your DBA to increase the undo/ rollback segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.
[edit]
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);
create or replace procedure proc1
authid current_user is
begin
...
• Move all the tables to one user/schema.
[edit]
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.
[edit]
Can one pass an object/table as an argument to a remote procedure?
The only way to reference an object type between databases is via a database link. Note that it is not enough to just use "similar" 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;
/
[edit]
What is the difference between stored procedures and functions?
• Functions are normally used for computations where as procedures are normally used for executing business logic.
• Functions MUST return a value, procedures doesn't need to.
• You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query. For example, if you have a function that is updating a table, you cannot call that function from a SQL query.
- select myFunction(field) from sometable; will throw error.
• Function returns 1 value only. Procedure can return multiple values (max 1024).
• Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution. Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values(SQL Server). Stored procedure is precompiled execution plan where as functions are not.
• A procedure may modify an object where a function can only return a value.
PS: 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.
[edit]
Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have an embedded PL/SQL engine. Thus, all your PL/SQL code 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.
[edit]
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';
[edit]
What's the PL/SQL compiler limits for block, record, subquery and label nesting?
The following limits apply:
Level of Block Nesting: 255
Level of Record Nesting: 32
Level of Subquery Nesting: 254
Level of Label Nesting: 98
[edit]
Can one COMMIT/ ROLLBACK from within a trigger?
Changes made within triggers should be committed or rolled back as part of the transaction in which they execute. Thus, triggers are NOT allowed to execute COMMIT or ROLLBACK statements (with the exception of autonomous triggers). Here is an example of what will happen when they do:
SQL> CREATE TABLE tab1 (col1 NUMBER);
Table created.

SQL> CREATE TABLE log (timestamp DATE, operation VARCHAR2(2000));
Table created.

SQL> CREATE TRIGGER tab1_trig
2 AFTER insert ON tab1
3 BEGIN
4 INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
5 COMMIT;
6 END;
7 /
Trigger created.

SQL> INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (1)
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.TAB1_TRIG", line 3
ORA-04088: error during execution of trigger 'SCOTT.TAB1_TRIG'
Autonomous transactions:
As workaround, one can use autonomous transactions. Autonomous transactions execute separate from the current transaction.
Unlike regular triggers, autonomous triggers can contain COMMIT and ROLLBACK statements. Example:
SQL> CREATE OR REPLACE TRIGGER tab1_trig
2 AFTER insert ON tab1
3 DECLARE
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
7 COMMIT; -- only allowed in autonomous triggers
8 END;
9 /
Trigger created.

SQL> INSERT INTO tab1 VALUES (1);
1 row created.
Note that with the above example will insert and commit log entries - even if the main transaction is rolled-back!
Retrieved from "http://www.orafaq.com/wiki/PL/SQL_FAQ"
Category: Frequently Asked Questions
Views
• Article
• Discussion
• Edit
• History
Personal tools
• Log in / create account

Site Navigation
• Wiki Home
• Blog Aggregator
• FAQ's
• Forum Home
• Mailing Lists
• Usenet News
Wiki Navigation
• Categories
• Recent changes
• Random page
• Help
Search

Toolbox
• What links here
• Related changes
• Upload file
• Special pages
• Printable version
• Permanent link

• This page was last modified 15:11, 24 November 2008.
• This page has been accessed 280,846 times.
.:: Wiki Home :: Blog Home :: Forum Home :: Contact :: Privacy ::.

Noramlizations in DBMS Article for II BTech II Sem

How many normal forms are there?
There are seven normal forms.
They are
First Normal Form
Second Normal Form
Third Normal Form
Boyce-Codd Normal Form
Fourth Normal Form
Fifth Normal Form
Sixth or Domain-key Normal form
Why do we need to do normalization?
To eliminate redundancy of data i.e. having same information stored at multiple places, which eventually be difficult to maintain and will also increase the size of our database.
With normalization we will have tables with fewer columns which will make data retrieval and insert, update and delete operations more efficient.
What do we mean when we say a table is not in normalized form?
Let’s take an example to understand this,
Say I want to create a database which stores my friends name and their top three favorite artists.
This database would be quite a simple so initially I’ll be having only one table in it say friends table.

Dbms FAQS for II BTech II Sem

1.Define database?Expalin various users of database?
2.Draw database system structure and explain various components?
3.Write differences between Database and File system?

Friday, April 24, 2009

dbms faqs

these site in progress