- What is PL/SQL?
Answer: 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.
The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding.
- How can I protect my PL/SQL source code?
Answer: 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 read/write files from PL/SQL?
Answer: Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.ORA file.
Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp', 'myoutput', 'W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %s\n', func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;
- Can one use dynamic SQL within PL/SQL?
Answer: 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
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;
- How does one get the value of a sequence into a PL/SQL variable?
Answer: select sq_sequence.NEXTVAL into :i from dual;
- Is there a PL/SQL Engine in SQL*Plus?
Answer: No.All your PL/SQL are send directly to the database engine for execution.
- Is there a limit on the size of a PL/SQL block?
Answer: Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K.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'
