Oracle Apps PL-SQL Interview Questions – Set 2

Oracle Apps PL-SQL Interview Questions – Set 2

Q: 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:

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 automatically 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;

/

Q: 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 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 restriction has been removed in oracle 11g and the former syntax can be used.

Q: 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’);

Q: 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.

Q: 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.

Q: 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.

Q: 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.

Q: 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;

/

Q: What is the difference between stored procedures and functions?

Functions MUST return a value, procedures don’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.