What is p-code and sourcecode ?
PCode is the parsed Code of the Procedure which is ready for
execution... This is the code that is loaded into the shared pool and
executed for you.
Source code is the Text of the procedure.It is accessed from USER_SOURCE data dictionary view.
P-code is Compiled object code which is not accessible.
What is the rollforward in PL/SQL
Rollforward is to Redo, as Rollback is to undo.
Roll forward refers to the process Oracle goes through to apply changes contained in the redo log files (both online and archive). The database clock (as measured by the system change number) is moved...
Return statement and out parameters
What are return statement and out parameter in PL/SQL function?
Functions always return a value.
Procedures do not.
The use of IN, OUT, and IN_OUT parameters as scalar values or ref cursors is dependent on your need and overall performance not on a pseudo-standard as answered above.
Function must have return statement by which it returns one value.Though we can use out parameter in function(function not getting called from select statement or DML), it is not good programming prac...
Functions can have multiple out Parameters one with Return Parameter and others can be IN OUT Parameter to the function. this way a function can return multiple out parameters.Eg : Function ( a in num...
1.Functions are used to computer a value.2.Function can return more than one value using out parameter. But it is not a good practice to return more than one value in functions.3.Proced...
Explain, is it possible to have same name for package and the procedure in that package.
yes its possible.
Yes, its possible to have same name for package and the procedure in that package.
What is an exception ? What are types of exception ?
exception is the error handling part of PL/SQL block. The types are predefined and user defined. Some of predefined exceptions are. cursor_already_open dup_val_on_index no_data_found ...
Hi all, Exception is nothing but Error. Exception can serve as an ALERT message also. (using RAISE_APPLICATION_ERROR)There are two types of exceptions: 1> Pre-defineddefine exception (2&g...
Exception is nothing but error in the PL/SQL program. If any error occured in the PL/SQL program that terminates from the program. To handle that exceptions we are using exception handling part in the...
What is call by value and call by reference in parameters (in, out, inout)?
Answered by: lisha.varghese
View all answers by lisha.varghese
Member Since Nov-2008 | Answered On : Nov 16th, 2008
Three types of parameter modes
1) IN parameter mode- This mode is used to pass values to the calling module when invoked.The value of IN parameter can't be changed in the module.
2) OUT parameter mode -This mode is used to return a value to the main block.The value of OUT parameter can change anywhere in the program.
3)IN OUT parameter mode-This mode is used to pass values to the calling module and return a value to the main block.The value of IN OUT parameter can change anywhere in the program.
In Call By value ,the copy of actual parameter is passed to the formal parameter,So any changes to the formal parameter doesn't affect the actual parameter.
In Call By reference,the address of actual parameter is passed to the formal parameter,so any changes to the formal parameter will change the actual parameter also,because both of them are pointing to the same memory location.
Here no copying is required.
The IN parameter is passe by reference,so we can't change the value of IN parameter inside the module,It acts as a constant,But the OUT and IN OUT parameters are passed by value,we can change the values of OUT & IN OUT paremeters
In Parameter:: call by reference
OUT Parameter: call by value
IN OUT Parameter: call by value
if you use the hint NOCOPY with OUT Parameter and IN OUT Parameter then ::: call by reference
Three types of parameter modes1) IN parameter mode- This mode is used to pass values to the calling module when invoked.The value of IN parameter can't be changed in the module.2) OUT parameter mo...
Can I write PL/SQL block inside expection
Sure, you can write plsql block inside exception.
Yes you can write PL/SQL block inside exception section. Suppose you want to insert the exception detail into your error log table, that time you can write insert into statement in exception part. To ...
What are the restrictions on functions ?
Function having a return type as Boolean ,can't be used in select statement.
In functions we can use all three types of parametershere is a simple exampleCREATE OR REPLACE FUNCTION TEST_FUNC(A IN NUMBER, B IN OUT NUMBER,C OUT NUMBER) RETURN NUMBER IS &n...
Error will be like "Cursor already open"
CURSOR_ALREADY_OPEN ORA-06511 An attempt was made to open a cursor that was already open.
How can we generate debugging output from PL/SQL?
Use "SHOW ERRORS" command.
Regards,
Karthik
stshekhar is right,
oracle supplied package DBMS_OUTPUT with procedures PUT, PUT_LINE, NEW_LINE etc in case of debugging pl/sql
About DBMS_output.Put_line( ) package
What is the maximum size of the message that we can give in DBMS_output.Putline();
The maximum size of package is 32k
We can't give more than that
255 bytes was the size limits of earlier version of oracle..ie prior to 10 g ..in 10g 32767 byte is the limit..
What is pragma restrict_references:
Pragma RESTRICT_REFERENCES is a compiler directive.
It makes sure that the function maintains the purity rules and code in fuction creation satisfy the purity rules.
There are 4 values for it.
WNDS,RNDS,WNPS,RNPS.
Thanks
Jai Sainath
By using pragma_restrict_references we can give the different status to functions,Like WNDB(WRITE NO DATA BASE),RNDB(read no data base),Write no package state,read no packge state.W
Here is the answer in 1 lineIN-- value is used in a program. It cannot be a variable .COuld be literal,expression,value .OUT-- value is returned back from the progr...
hi friends,To make it simple, keep in mind,IN : It is a CONSTANT in the sub-program and u can not modify its value in sub- program. if its value is modified in the s...
Ans : A & D
A and D
You cannot use the database name before the table name like db1.t1.we can only use a user name and then table name. eg. let we have a user "deepak" then we can write select * from deepak.t1 . We ca...
Type schema name before table name or your id dont have the access to view the data from that table
Is the order in which the conditions are given in the 'where' clause are important?
Mean I heard from Oracle 8i onwards the order of where clause condition does not matter which Oracle creates the explain plan...And execute..Is it true?
Yes the order is important. But it is important for the performance of the query and not for the final result. The output of the query will be the same irrespective of the order in which the join con...
Hi,
Where Clause conditions execution are independent of order in explain plan but it reads top to bottom.
Thanks,
Gunjan
First, let us understand why these errors cropup.1) NO_DATA_FOUND - is raised only for "select into" statements when the where clause of the query does not match any rows.2)TOO_MANY_rows - is raised...
If you don't want an excpetion to be raised on a SELECT statement, you can avoid executing the statement by having a count just before the SELECT as shown belowSELECT count(rowid)into v_count from...
Unlike tables ref cursors do not store data. They act like mold to hold data during the execution of the piece of code so proper coding and use of bind variables can help decrease the burden of proces...
Spaces will be padded with y variable,its means y will store y='sudhir ' . while x only store 6 six character...
ans is both values are not equal char(20)='name' covered in memorey areea is 20 but varchar2(20)='name' covered in memorey areea is 4 becoze char is fixed lenth char data type but varchar2 is variable lenth char data type