| Dynamically passing the Columns values [message #592144] |
Mon, 05 August 2013 00:04  |
|
|
HI all,
I am trying a pl/sql block which which take a string and execute it dynamically. Suppose below is string
M_COL := Q'[(P_CODE=> ':DEPTNO',P_CODE_TYPE => 'STATE')]';
Now trying to execute it. using below
M_STR := 'SELECT CHK_DEPT' || M_COL || ' FROM EMP WHERE EMPNO=''7499''';
EXECUTE IMMEDIATE M_STR
INTO M_DATE;
Now what i want is M_STR vairable to executed as
SELECT CHK_DEPT(P_CODE=> DEPTNO,P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'
instead of
SELECT CHK_DEPT(P_CODE=> 'DEPTNO',P_CODE_TYPE => 'STATE') FROM EMP WHERE EMPNO='7499'
other in other way in parameter P_CODE Column value of DEPTNO should be passed.Also note that DEPTNO column in string M_COL is Dynamic.i.e
M_COL := Q'[(P_CODE=> ':DEPTID',P_CODE_TYPE => 'STATE')]'; OR
M_COL := Q'[(P_CODE=> ':EMP_ID',P_CODE_TYPE => 'STATE')]';
Any suggestions, Please share with me.
[Updated on: Mon, 05 August 2013 00:05] Report message to a moderator |
|
|
|
| Re: Dynamically passing the Columns values [message #592146 is a reply to message #592144] |
Mon, 05 August 2013 00:40  |
 |
dariyoosh
Messages: 538
Registered: March 2009
Location: France
|
Senior Member |
|
|
Hello,
I think what you're looking for is the USING clause of EXECUTE IMMEDIATE.
Here is an example
SET SQLBLANKLINES ON;
SET SERVEROUTPUT ON;
CREATE OR REPLACE FUNCTION incrFun(p_intVal IN PLS_INTEGER)
RETURN PLS_INTEGER
AUTHID CURRENT_USER
IS
BEGIN
RETURN p_intVal + 1;
END incrFun;
/
SHOW ERRORS;
<<bk>>
DECLARE
intVal PLS_INTEGER := 12;
result PLS_INTEGER;
STMT CONSTANT VARCHAR2(100) :=
'SELECT incrFun(p_intVal=>:b_intVal)
FROM "PUBLIC".dual';
BEGIN
EXECUTE IMMEDIATE bk.STMT
INTO bk.result
USING IN bk.intVal;
sys.dbms_output.put_line('result = ' || bk.result);
END;
/
SET SERVEROUTPUT OFF;
Function created.
No errors.
result = 13
PL/SQL procedure successfully completed.
SQL>
Regards,
Dariyoosh
[Updated on: Mon, 05 August 2013 01:05] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Dynamically passing the Columns values [message #592156 is a reply to message #592153] |
Mon, 05 August 2013 03:00 |
|
|
I have created as function
FUNCTION FN_CONV_BIND_VAR(P_STR VARCHAR2) RETURN VARCHAR2 IS
M_STR VARCHAR2(4000);
M_COL_1 VARCHAR2(60);
M_COL_2 VARCHAR2(60);
M_BIND_POS NUMBER;
M_COMM_POS NUMBER;
M_OCC NUMBER;
BEGIN
M_STR := P_STR;
M_OCC := LENGTH(M_STR) - LENGTH(REPLACE(M_STR, '&'));
IF M_OCC > 0 THEN
FOR I IN 1 .. M_OCC LOOP
M_BIND_POS := INSTR(M_STR, '&');
M_COMM_POS := INSTR(M_STR, '''', M_BIND_POS);
DBMS_OUTPUT.PUT_LINE(M_COMM_POS || '<>' || M_BIND_POS);
M_COL_1 := SUBSTR(M_STR,
M_BIND_POS + 1,
M_COMM_POS - M_BIND_POS - 1);
M_COL_2 := SUBSTR(M_STR, M_BIND_POS, M_COMM_POS - M_BIND_POS);
M_STR := REPLACE(M_STR, '''' || M_COL_2 || '''', M_COL_1);
END LOOP;
RETURN M_STR;
ELSE
RETURN M_STR;
END IF;
END;
|
|
|
|
|
|
| Re: Dynamically passing the Columns values [message #592159 is a reply to message #592157] |
Mon, 05 August 2013 03:54 |
|
|
 Well actually i found other way to resolve the problem.
CREATE OR REPLACE FUNCTION FN_CONV_BIND_VAR(P_STR VARCHAR2) RETURN VARCHAR2 IS
M_STR VARCHAR2(4000);
M_COL_1 VARCHAR2(60);
M_COL_2 VARCHAR2(60);
M_BIND_POS NUMBER;
M_COMM_POS NUMBER;
M_OCC NUMBER;
BEGIN
M_STR := P_STR;
M_OCC := LENGTH(M_STR) - LENGTH(REPLACE(M_STR, ':'));
IF M_OCC > 0 THEN
FOR I IN 1 .. M_OCC LOOP
M_BIND_POS := INSTR(M_STR, ':');
M_COMM_POS := INSTR(M_STR, '''', M_BIND_POS);
DBMS_OUTPUT.PUT_LINE(M_COMM_POS || '<>' || M_BIND_POS);
M_COL_1 := SUBSTR(M_STR, M_BIND_POS + 1, M_COMM_POS - M_BIND_POS - 1);
M_COL_2 := SUBSTR(M_STR, M_BIND_POS, M_COMM_POS - M_BIND_POS);
M_STR := REPLACE(M_STR, '''' || M_COL_2 || '''', M_COL_1);
END LOOP;
RETURN M_STR;
ELSE
RETURN M_STR;
END IF;
END;
chk_dept function
CREATE OR REPLACE FUNCTION CHK_DEPT(P_CODE VARCHAR2,P_CODE_TYPE VARCHAR2) RETURN VARCHAR2 IS
M_NAME DEPT.DNAME%TYPE;
BEGIN
FOR I IN (SELECT DNAME FROM DEPT WHERE DEPTNO = P_CODE) LOOP
M_NAME := I.DNAME;
END LOOP;
RETURN M_NAME;
END;
sql block
DECLARE
M_COL VARCHAR2(4000);
M_STR VARCHAR2(4000);
M_RESULT VARCHAR2(4000);
BEGIN
M_COL := Q'[(P_CODE=> ':DEPTNO',P_CODE_TYPE => 'STATE')]';
DBMS_OUTPUT.PUT_LINE(M_COL);
M_COL := FN_CONV_BIND_VAR(M_COL);
DBMS_OUTPUT.PUT_LINE(M_COL);
M_STR := 'SELECT CHK_DEPT' || M_COL || ' FROM EMP WHERE EMPNO=''7499''';
EXECUTE IMMEDIATE M_STR
INTO M_RESULT;
DBMS_OUTPUT.PUT_LINE(M_RESULT);
END;
|
|
|
|
|
|
| Re: Dynamically passing the Columns values [message #592163 is a reply to message #592161] |
Mon, 05 August 2013 04:25 |
|
|
See dariyoosh,
USING clause of the EXECUTE IMMEDIATE i cannot use it as i have mentioned in my post that that its even string is dnamic
Quote:
Also note that DEPTNO column in string M_COL is Dynamic.i.e
M_COL := Q'[(P_CODE=> ':DEPTID',P_CODE_TYPE => 'STATE')]'; OR
M_COL := Q'[(P_CODE=> ':EMP_ID',P_CODE_TYPE => 'STATE')]'; OR
M_COL := Q'[(P_CODE=> ':SAL',P_CODE_TYPE => 'STATE')]';
So it M_COL may have any column which i wont be knowing in run time also.So thats why cannot use USING in excute immediate.
I didnt answer your question as i thought you would get idea from code that how i am trying other approach.
|
|
|
|
| Re: Dynamically passing the Columns values [message #592164 is a reply to message #592163] |
Mon, 05 August 2013 04:39 |
|
dariyoosh
Messages: 538
Registered: March 2009
Location: France
|
Senior Member |
|
|
chandan.rattan wrote on Mon, 05 August 2013 11:25 USING clause of the EXECUTE IMMEDIATE i cannot use it as i have mentioned in my post that that its even string is dnamic
What do you mean its even string is dynamic? Are you talking about the function actual parameters?
chandan.rattan wrote on Mon, 05 August 2013 11:25
Also note that DEPTNO column in string M_COL is Dynamic.i.e
M_COL := Q'[(P_CODE=> ':DEPTID',P_CODE_TYPE => 'STATE')]'; OR
M_COL := Q'[(P_CODE=> ':EMP_ID',P_CODE_TYPE => 'STATE')]'; OR
M_COL := Q'[(P_CODE=> ':SAL',P_CODE_TYPE => 'STATE')]';
There is no department number in the above code.
chandan.rattan wrote on Mon, 05 August 2013 11:25
So it M_COL may have any column which i wont be knowing in run time also.So thats why cannot use USING in excute immediate.
A check department function that can accept as parameter: employee_id, salary and department_id and all of these values for the very same formal parameter ???? Check your application design !
Sorry I think I cannot continue this discussion anymore as I don't have the details of your tables/functions nor their description.
However, I think you should have a look at PL/SQL Language Reference: 7 PL/SQL Dynamic SQL
Regards,
Dariyoosh
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
