Examine the structure of the DEPARTMENTS table.
Name Null? Type
—————————– ———— ———-DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
LOCATION_ID NUMBER(4)
View the Exhibit and examine the code that you plan to use for creating a package to obtain the
details of an employee using a host variable on the client side.
In SQL*Plus, you plan to use the following commands:
SQL> VARIABLE x REFCURSOR
SQL> EXECUTE emp_data.get_emp(195,:x)
SQL> PRINT x
Which statement is true about the above scenario?
A.
The package executes successfully and passes the required data to the host variable.
B.
The package specification gives an error on compilation because cursor variable types cannot
be defined in the specification.
C.
The package specification gives an error on compilation because the cursor variable
parameter was specified before you defined it.
D.
The package executes successfully, but does not pass the required data to the host variable
because the cursor is closed before the PRINT statement runs.
Maybe the D answer is correct. I can’t declate VARIABLE x REFCURSOR, but there is no such option in the list of answers :(…
Well, I tested and seems to me that A is correct.
Used a similar example:
PACKAGE credit_card_pkg
type temp_emp_rec is record
(
first_name VARCHAR2(20),
Last_name VARCHAR2(25)
);
type cursor_out is ref cursor return temp_emp_rec;
Procedure:
procedure temp (
p_empId in employees.employee_id%type,
p_out in out cursor_out
) is
begin
open p_out for
select first_name, Last_name
from employees
where employee_id = p_empId;
end temp;
In SQLPLUS:
C:\>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat May 30 21:50:41 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: OE
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> VARIABLE x REFCURSOR;
SQL> EXECUTE credit_card_pkg.temp(195,:x);
PL/SQL procedure successfully completed.
SQL> PRINT x;
FIRST_NAME LAST_NAME
——————– ————————-
Vance Jones
SQL>
“The package executes successfully and passes the required data to the host variable.”
Regards
Forget previous comment. Missed the closed cursor.
D is correct. It executes correctly but the variable has no value:
Corrected with the closed cursor:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> VARIABLE x REFCURSOR;
SQL> EXECUTE credit_card_pkg.temp(195,:x);
PL/SQL procedure successfully completed.
SQL> PRINT x;
ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable “x”
SQL>
Regards
D
D.
SQL> VARIABLE x REFCURSOR;
SQL> EXECUTE credit_card_pkg.temp(195,:x);
PL/SQL procedure successfully completed.
SQL> PRINT x;
ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable “x”
My brother recommended I would possibly like this blog. He was once entirely right. This put up actually made my day. You can not consider just how a lot time I had spent for this info! Thank you!|