Q: – 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.
Q: – How can I know which stored PL/SQL code is wrapped?
The following query gives the list of all wrapped PL/SQL code:
select owner, name, type
from dba_source
where line = 1
and instr(text, ' wrapped'||chr(10))+instr(text, ' wrapped '||chr(10)) > 0
order by 1, 2, 3
Q: – What are 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
Q: – 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;
/
Q: – 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:
rap iname=myscript.pls oname=xxxx.plb
Q: – what is a trace file?
when ever an internal error is detected by a process in oracle it dumps the information about the error into a trace file. Alter session set sql_trace=TRUE
Q: – What is Data cleaning and testing.
Data Cleaning: Transformation of data in its current state to a pre-defined, standardized format using packaged software or program modules.
Q: – How do we Tune the Queries?
Queries can be tuned by Checking the logic (table joins), by creating Indexes on objects in the where clause, by avoiding full table scans. Finally use the trace utility to generate the trace file, use the TK-Prof utility to generate a statistical a nalysis about the query using which appropriate actions can be taken.
Q: – How to display messages in Log file and Output file?
Using FND_FILE.PUT_LINE
