You created a procedure as follows:
CREATE OR REPLACE PROCEDURE query_prod(twhr VARCHAR2)
IS
stmt VARCHAR2(100);
pname VARCHAR2(20);
BEGIN
stmt:=’SELECT product_name FROM products WHERE product_id=:2′;
EXECUTE IMMEDIATE stmt INTO pname USING twhr;
DBMS_OUTPUT.PUT_LINE(pname);
END;
/
View the Exhibit to examine the structure of PRODUCTS table.
Which statement is true about the procedure?
A.
It produces an error when invoked.
B.
It can be invoked only from a PL/SQL block.
C.
It reduces the chances of SQL injection by using bind arguments.
D.
The values for bind arguments remain persistent in the session after the execution of the
procedure.
C.
A and D are invalid.
B is invalid because you can invoke procedure for example using EXECUTE from sql*plus
A, product_id is NUMBER type, and twhr is VARCHAR. Procedure produces an error “ORA-06502: PL/SQL: numeric or value error” when invoked.
A is incorrect. The procedure works correctly.
Using emp from SCOTT:
CREATE TABLE “SCOTT”.”EMP”
( “EMPNO” NUMBER(4,0),
“ENAME” VARCHAR2(10 BYTE),
“JOB” VARCHAR2(9 BYTE),
“MGR” NUMBER(4,0),
“HIREDATE” DATE,
“SAL” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(2,0).
The procedure:
CREATE OR REPLACE PROCEDURE query_EMPNO(twhr VARCHAR2)
IS
stmt VARCHAR2(100);
pname VARCHAR2(20);
BEGIN
stmt:=’SELECT ENAME FROM EMP WHERE EMPNO=:2′;
EXECUTE IMMEDIATE stmt INTO pname USING twhr;
DBMS_OUTPUT.PUT_LINE(pname);
END;
/
executing from sqlplus:
C:\>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 4 16:26:37 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: scott
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> set serveroutput on;
SQL> exec query_EMPNO(7369);
SMITH
PL/SQL procedure successfully completed.
SQL>
Regards
A
HY GUYS
A is right
it’s a problem name too large, end format nvarchar…..
to save into a variable defined varchar and too small
regards
DROP TABLE MCDONALD.PRODUCTS
CREATE TABLE MCDONALD.PRODUCTS
(
PRODUCT_ID NUMBER(6) NOT NULL,
LANGUAGE_ID VARCHAR2(3),
PRODUCT_NAME NVARCHAR2(125),
CATEGORY_ID NUMBER,
PRODUCT_DESCRIPTION NVARCHAR2(2000),
WEIGHT_CLASS NUMBER(1),
WARRANTY_PERIOD INTERVAL YEAR(2) TO MONTH,
SUPPLIER_ID NUMBER(6),
PRODUCT_STATUS VARCHAR2(20),
LIST_PRICE NUMBER(8,2),
MIN_PRICE NUMBER(8,2),
CATALOG_URL VARCHAR2(50));
INSERT INTO MCDONALD.PRODUCTS
( PRODUCT_ID, LANGUAGE_ID , PRODUCT_NAME ,
CATEGORY_ID ,PRODUCT_DESCRIPTION ,WEIGHT_CLASS,
WARRANTY_PERIOD, SUPPLIER_ID ,PRODUCT_STATUS ,LIST_PRICE,
MIN_PRICE ,CATALOG_URL)
VALUES
(1,’ITA’,’NOME NVARCHAR DEL PRODOTTO 1 OLTRE 20 CARATTERI’,
10,’DESCRIZIONE NVARCHAR DEL PRODOTTO 1′ , 1,
TO_YMINTERVAL(’01-04′),10 ,’VALID’,
1000,500,’URL_CATOLOGO1′)
CREATE OR REPLACE PROCEDURE QUERY_PROD(TWHR VARCHAR2)
IS
STMT VARCHAR2(100);
PNAME VARCHAR2(20);
BEGIN
STMT := ‘SELECT PRODUCT_NAME FROM PRODUCTS WHERE PRODUCT_ID = :2’;
EXECUTE IMMEDIATE STMT INTO PNAME USING TWHR;
DBMS_OUTPUT.PUT_LINE(PNAME);
END;
EXEC QUERY_PROD(1);
ORA-06502: PL/SQL: errore di numero o valore
ORA-06512: a “MCDONALD.QUERY_PROD”, line 7
ORA-06512: a line 1
CREATE OR REPLACE PROCEDURE QUERY_PROD(TWHR VARCHAR2) –<————-
IS
STMT VARCHAR2(100);
–PNAME VARCHAR2(20);
PNAME NVARCHAR2(150);
BEGIN
DBMS_OUTPUT.PUT_LINE('PARAMETRO TWHR = ' || TWHR);
STMT := 'SELECT PRODUCT_NAME FROM MCDONALD.PRODUCTS WHERE PRODUCT_ID = :2';
EXECUTE IMMEDIATE STMT INTO PNAME USING TWHR;
DBMS_OUTPUT.PUT_LINE(PNAME);
END;
PARAMETRO TWHR = 1
NOME NVARCHAR DEL PRODOTTO 1 OLTRE 20 CARATTERI
ALSO C IS RIGHT SURE,
but it produces an error at execution time before to be useful…… should change the variable pname to enlarge it same size of archived
“A”: Yes, it may produce an error because of too short var “pname” (see the table definition). The possibility depends on data in the table. I would say it surely produce an error one day.
“ะก”: Such a stupid procedure! The best way to reduce the chances of SQL injection is to remove all dynamic SQL at all. But obvious for this example binding is much better than string concatenation. And it also helps not to litter shared pool with similar statements.
c tested