Reg Dynamic sql in stored proc (merged) [message #416693] |
Tue, 04 August 2009 00:19  |
Prajakta001
Messages: 52 Registered: June 2008 Location: Mumbai
|
Member |

|
|
Hi ,
I have a table where the queries are stored in columns
like prim_col_name, display_column , table_name, Filter
I can fetch the values and form a query , execute it dynamically and get the result value.
when i execute it independently i.e. when i execute the query dynamically i get proper result.
I want to put this logic in a stored proc/function
build dynamic sql and return the result according to input parameter i.e. query_id ..
I am defining variables in the declaration . but in execute immediate its not parsing the variables and throwing error.
I can not use using as my filter condition is getting dynamically populated .
Is there any way where i can parse for the input values before execute immediate ?
Please let me know asap ..
I am attaching code for your reference
|
|
|
|
|
|
Re: Reg Dynamic sql in stored proc (merged) [message #416707 is a reply to message #416700] |
Tue, 04 August 2009 01:06   |
Prajakta001
Messages: 52 Registered: June 2008 Location: Mumbai
|
Member |

|
|
hi delna ,
Please refer
CREATE OR REPLACE FUNCTION fn_get_crita_nm (v_crtra_val VARCHAR2)
RETURN VARCHAR2
AS
--v_crtra_val VARCHAR2(100) := 'ACCOUNT_GROUP';
str_query VARCHAR2 (4000);
in_lookup_id lookup.lookup_id%TYPE;
in_acct_no acct.acct_no%TYPE := '-99999999999';
in_lgl_enty_nm lgl_enty.lgl_enty_nm%TYPE := '-99999999999';
in_acct_nm acct.acct_nm%TYPE := '-99999999999';
in_party_id prty_role.party_id%TYPE := '100002';
in_srvc_id srvc.srvc_id%TYPE := 215;
in_acct_grp_nm prty_acct_grp.acct_grp_nm%TYPE
:= 'Account Group for 100002';
v_str VARCHAR2 (4000);
BEGIN
SELECT lookup_id
INTO in_lookup_id
FROM lookup
WHERE UPPER (TRIM (data_source)) = UPPER (TRIM (v_crtra_val));
SELECT ' SELECT '
|| entity_key_cols
|| ' from '
|| entity_name
|| ' '
|| REPLACE (REPLACE (filtr, l1, ':in_'), l2, ' :in_')
INTO str_query
FROM (SELECT entity_key_cols, entity_name, UPPER (filter_condition)
filtr,
UPPER ( ':'
|| TRIM ( SUBSTR (entity_name,
1,
INSTR (entity_name, ',') - 1
)
|| '.'
)
) l1,
UPPER ( ':'
|| TRIM (SUBSTR (entity_name,
INSTR (entity_name, ',') + 1
)
)
|| '.'
) l2
FROM lookup
WHERE lookup_id = in_lookup_id) a;
DBMS_OUTPUT.put_line (str_query);
RETURN v_str;
END;
---select fn_get_crita_nm('ACCOUNT') from dual
strsql:---
/* Formatted on 2009/08/04 10:48 (Formatter Plus v4.8.8) */
SELECT acct.abc_acct_id
FROM acct, clnt_acct_srvc
WHERE acct.acct_typ_cd = 'ACCOUNT'
AND acct.acct_id = clnt_acct_srvc.acct_id
AND clnt_acct_srvc.stat_cd = 'ACT'
AND clnt_acct_srvc.party_id = :in_party_id
AND clnt_acct_srvc.srvc_id = :in_srvc_id
AND (acct.acct_no = :in_acct_no OR '-99999999999' = :in_acct_no)
AND (acct.lgl_enty_nm = :in_lgl_enty_nm OR '-99999999999' = :in_lgl_enty_nm
)
AND (acct.acct_nm = :in_acct_nm OR '-99999999999' = :in_acct_nm)
[Mod-Edit: Frank added code tags]
[Updated on: Tue, 04 August 2009 01:14] by Moderator Report message to a moderator
|
|
|
|
Re: Reg Dynamic sql in stored proc (merged) [message #416713 is a reply to message #416707] |
Tue, 04 August 2009 01:13   |
Prajakta001
Messages: 52 Registered: June 2008 Location: Mumbai
|
Member |

|
|
code tag was missing
CREATE OR REPLACE FUNCTION Fn_get_crita_nm
(v_crtra_val VARCHAR2)
RETURN VARCHAR2
AS
--v_crtra_val VARCHAR2(100) := 'ACCOUNT_GROUP';
str_query VARCHAR2(4000);
in_lookup_id lookup.lookup_id%TYPE;
in_acct_no acct.acct_no%TYPE := '-99999999999';
in_lgl_enty_nm lgl_enty.lgl_enty_nm%TYPE := '-99999999999';
in_acct_nm acct.acct_nm%TYPE := '-99999999999';
in_party_id prty_role.party_id%TYPE := '100002';
in_srvc_id srvc.srvc_id%TYPE := 215;
in_acct_grp_nm prty_acct_grp.acct_grp_nm%TYPE := 'Account Group for 100002';
v_str VARCHAR2(4000);
BEGIN
SELECT lookup_id
INTO in_lookup_id
FROM lookup
WHERE Upper(Trim(data_source)) = Upper(Trim(v_crtra_val));
SELECT ' SELECT '
||entity_key_cols
||' from '
||entity_name
||' '
||Replace(Replace(filtr,l1,':in_'),l2,' :in_')
INTO str_query
FROM (SELECT entity_key_cols,
entity_name,
Upper(filter_condition) filtr,
Upper(':'
||Trim(Substr(entity_name,1,Instr(entity_name,',') - 1)
||'.')) l1,
Upper(':'
||Trim(Substr(entity_name,Instr(entity_name,',') + 1))
||'.') l2
FROM lookup
WHERE lookup_id = in_lookup_id) a;
dbms_output.Put_line(str_query);
RETURN v_str;
END;
---select fn_get_crita_nm('ACCOUNT') from dual
strsql:---
/* Formatted on 2009/08/04 10:48 (Formatter Plus v4.8. */
SELECT acct.abc_acct_id
FROM acct, clnt_acct_srvc
WHERE acct.acct_typ_cd = 'ACCOUNT'
AND acct.acct_id = clnt_acct_srvc.acct_id
AND clnt_acct_srvc.stat_cd = 'ACT'
AND clnt_acct_srvc.party_id = :in_party_id
AND clnt_acct_srvc.srvc_id = :in_srvc_id
AND (acct.acct_no = :in_acct_no OR '-99999999999' = :in_acct_no)
AND (acct.lgl_enty_nm = :in_lgl_enty_nm OR '-99999999999' = :in_lgl_enty_nm
)
AND (acct.acct_nm = :in_acct_nm OR '-99999999999' = :in_acct_nm)
[Updated on: Tue, 04 August 2009 01:17] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: Reg Dynamic sql in stored proc (merged) [message #416805 is a reply to message #416797] |
Tue, 04 August 2009 06:48   |
Prajakta001
Messages: 52 Registered: June 2008 Location: Mumbai
|
Member |

|
|
Thanks for your efforts and time .
I could resolve the issue .
The error i was getting was : i was not able to parse the variables as they were coming as a string from the table .
hence execute immediate was failing for the variables
I have replaced those variables with actual values in teh string with REPLACE lasue
and its working
Just added
str_query := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(str_query,'IN_ACCT_NO',in_acct_no),'IN_LGL_ENTY_NM',''''||in_lgl_enty_nm||''''),'IN_A CCT_NM',''''||in_acct_nm||''''),'IN_PARTY_ID',in_party_id),'IN_SRVC_ID',in_srvc_id),'IN_ACCT_GRP_NM',''''||in_acct_grp_nm||'''');
AND ITS WORKING FINE BEFORE EXECUTE IMMEDIATE STR_QUERY
hope it helps someone else
Regards
Prajakta
|
|
|
|
|
|
|