Issue in dynamic sql. [message #674645] |
Wed, 06 February 2019 12:19 |
|
rajeshsvnr
Messages: 9 Registered: June 2017
|
Junior Member |
|
|
Hi All,
I have an issue in executing a oracle procedure.
Can anyone help me out.
I have the below employee table :-
EMP:-
---------------------------
ENAME JOB DEPTNO
---------------------------
KING MANAGER 10
BLAKE MANAGER 30
CLARK MANAGER 10
JONES SALESMAN 20
JONES ANALYST 20
FORD23 SALESMAN 20
SMITH MANAGER 20
ALLEN2 CLERK 30
WARD CLERK 30
MARTIN SALESMAN 30
TURNER SALESMAN 30
ADAMS TEST 20
JAMES CLERK 30
MILLER CLERK 10
sdsdhh MANAGER 10
---------------------------
I wrote the sample procedure:-
CREATE OR REPLACE
PROCEDURE PROC_TEST(
P_JOB IN VARCHAR2)
AS
TYPE T_EMP
IS
RECORD
(
ENAME VARCHAR2(1000),
job VARCHAR2(1000) );
v_t_emp t_emp;
TYPE t1
IS
TABLE OF t_emp INDEX BY BINARY_INTEGER;
v_emp t1;
TYPE t3
IS
REF
CURSOR;
c_ref_cur t3;
V_SQL VARCHAR2(2000);
BEGIN
V_SQL :='select ename,job from emp';
V_SQL :=V_SQL ||' where deptno='||10;
V_SQL :=V_SQL ||' and job = '''||P_JOB||'''';
OPEN C_REF_CUR FOR V_SQL;
FETCH c_ref_cur bulk collect INTO v_emp;
CLOSE C_REF_CUR;
FOR i IN v_emp.FIRST..v_emp.LAST
LOOP
BEGIN
dbms_output.put_line(v_emp(i).ename||' '||v_emp(i).job);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END PROC_TEST;
/
When i executed the procedure as below :-
EXEC PROC_TEST('MANAGER');
Its gives the below output:-
---------------------------
KING MANAGER
CLARK MANAGER
sdsdhh MANAGER
---------------------------
But when i executed the below code, its giving the following error:-
EXEC PROC_TEST('MANAGER''S');
Error starting at line 41 in command:
EXEC PROC_TEST('MANAGER''S')
Error report:
ORA-00933: SQL command not properly ended
ORA-06512: at "BIR_SPMAT.PROC_TEST", line 26
ORA-06512: at line 1
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
Required Output should be no records:-
----------------------
ENAME JOB DEPTNO
----------------------
----------------------
[Updated on: Wed, 06 February 2019 12:20] Report message to a moderator
|
|
|
|
|
Re: Issue in dynamic sql. [message #674648 is a reply to message #674645] |
Wed, 06 February 2019 13:04 |
|
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Tue, 13 November 2018 15:04
...
Before, Please How to use [code] tags and make your code easier to read.
Michel Cadot wrote on Sun, 18 November 2018 17:17
You must:
...
4/ Before, read How to use [code] tags and make your code easier to read and apply it to your post
5/ BEFORE, feedback in your previous topic
Michel Cadot wrote on Tue, 20 November 2018 07:57
It seems you succeeded in your previous topic. How? What was the problem?
You got help, so now help others with your problems and explain what were they and how you solved them.
In short, ALWAYS feedback in your topics.
Please read OraFAQ Forum Guide
Do you deserve to be helped?
[Updated on: Wed, 06 February 2019 13:06] Report message to a moderator
|
|
|
|
Re: Issue in dynamic sql. [message #674680 is a reply to message #674645] |
Sat, 09 February 2019 06:31 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Obviously. Take your:
' and job = '''||P_JOB||''''
And replace P_JOB with 'MANAGER''S'. You'l get:
' and job = '''||'MANAGER''S'||''''
which results in:
You would need to account for possible quotes within P_JOB and double them:
' and job = '''||REPLACE(P_JOB,'''','''''')||''''
That will work but look what happens. You are constructing a different SQL each time new P_JOB is passed. Oracle keeps all of them (well, until they age out) in shared pool. As a results it takes longer for Oracle to read through shared pool looking if we already had such SQL and therefore avoid costly hard parse. And at the end it will not find it since SQL text is different due to first time P_JOB value. So we flooded shared pool which affects not just us but every session, wasted time on reading through shared pool and ended up with hard parse anyway. So what't the solution? Bind variables:
V_SQL :=V_SQL ||' and job = :P_JOB';
OPEN C_REF_CUR FOR V_SQL USING P_JOB;
But then you'll run into next issue - your code isn't checking if bulk fetch returned any rows and it doesn't when P_JOB is MANAGER'S. So you end up with NULL v_emp.FIRST and v_emp.LAST and FOR loop will throw exception. You need to check v_emp.COUNT before looping.
SY.
|
|
|
|