Home » SQL & PL/SQL » SQL & PL/SQL » Issue in dynamic sql. (Oracle 18c)
Issue in dynamic sql. [message #674645] Wed, 06 February 2019 12:19 Go to next message
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 #674646 is a reply to message #674645] Wed, 06 February 2019 12:43 Go to previous messageGo to next message
ThomasG
Messages: 3209
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Print the V_SQL variable with dbms_output and you will probably see the problem....
Re: Issue in dynamic sql. [message #674647 is a reply to message #674645] Wed, 06 February 2019 12:48 Go to previous messageGo to next message
BlackSwan
Messages: 26567
Registered: January 2009
Location: SoCal
Senior Member
EXECUTE IMMEDIATE
Into a single VARCHAR2 variable construct the whole SQL statement.
Use DBMS_OUTPUT to print the string before issuing execute immediate
Cut & Paste the statement into SQL*Plus to see exactly what is wrong & where.
Debug the statement using SQL*PLUS & then correct your SP.
Repeat as many time as necessary.

Remove, delete & NEVER use WHEN OTHERS NULL ever again.

http://www.orafaq.com/wiki/WHEN_OTHERS
Re: Issue in dynamic sql. [message #674648 is a reply to message #674645] Wed, 06 February 2019 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
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 #674650 is a reply to message #674645] Wed, 06 February 2019 14:55 Go to previous messageGo to next message
flyboy
Messages: 1872
Registered: November 2006
Senior Member
You should start to follow good practices, at least in constructing and using the cursor query.

There is nothing dynamic in the constructed SQL statement. You should use static SQL: https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/static-sql.html#GUID-8E770DA2-799E-454E-9AD8-1DDBACE1E3A2 or https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/static-sql.html#GUID-2DEFB351-F8B6-453C-AA74-86B5B99C3B20

If the query has some dynamic part and contains fixed number of parameters, you should bind them: https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/dynamic-sql.html#GUID-BAC52F38-B427-4860-9B6D-336B3AEE6AD2

If the number of parameters is not fixed, you should use DBMS_SQL package for binding: http://www.orafaq.com/wiki/Dynamic_SQL
Re: Issue in dynamic sql. [message #674680 is a reply to message #674645] Sat, 09 February 2019 06:31 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
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:

 and job = 'MANAGER'S'

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.
Re: Issue in dynamic sql. [message #674681 is a reply to message #674680] Sat, 09 February 2019 06:37 Go to previous message
Solomon Yakobson
Messages: 2863
Registered: January 2010
Location: Connecticut, USA
Senior Member
Actually, since bulk fetch always populates collection consecutively starting with index=1, you can simply change

FOR i IN v_emp.FIRST..v_emp.LAST

to

FOR i IN 1..v_emp.COUNT

SY.
Previous Topic: Need help to Schedule Job to Purge dba_audit_trails along with fga_audit_trail
Next Topic: Pull out string between two words in string column
Goto Forum:
  


Current Time: Tue Jul 23 12:53:27 CDT 2019