Home » SQL & PL/SQL » SQL & PL/SQL » Reg Dynamic sql in stored proc (merged)
Reg Dynamic sql in stored proc (merged) [message #416693] Tue, 04 August 2009 00:19 Go to next message
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 #416698 is a reply to message #416693] Tue, 04 August 2009 00:37 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Can you show, what did you do? And the error you are getting?
Simply copy and paste your SQL Plus session here using CODE tag.

regards,
Delna
Re: Reg Dynamic sql in stored proc (merged) [message #416699 is a reply to message #416693] Tue, 04 August 2009 00:39 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Prajakta001 wrote on Tue, 04 August 2009 06:19

Please let me know asap ..


Quick everyone! drop what you're doing and get right onto solving this problem. Chop chop! Wink
Re: Reg Dynamic sql in stored proc (merged) [message #416700 is a reply to message #416693] Tue, 04 August 2009 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not multipost your question.
If something goes wrong, go to forum topic list BEFORE reposting.

Quote:
I am attaching code for your reference

Nothing is attached.

Quote:
Please let me know asap ..

Please read OraFAQ Forum Guide, ASAP.

Have a look at print_table T. Kyte's function.

Regards
Michel
Re: Reg Dynamic sql in stored proc (merged) [message #416707 is a reply to message #416700] Tue, 04 August 2009 01:06 Go to previous messageGo to next message
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 #416708 is a reply to message #416700] Tue, 04 August 2009 01:07 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Hi Michel ,
i do not have access to the link for print table
Re: Reg Dynamic sql in stored proc (merged) [message #416713 is a reply to message #416707] Tue, 04 August 2009 01:13 Go to previous messageGo to next message
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 #416716 is a reply to message #416713] Tue, 04 August 2009 01:35 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
but in execute immediate its not parsing the variables and throwing error.


where are you doing EXECUTE IMMEDIATE and where are the errors?

[Updated on: Tue, 04 August 2009 01:36]

Report message to a moderator

Re: Reg Dynamic sql in stored proc (merged) [message #416719 is a reply to message #416708] Tue, 04 August 2009 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prajakta001 wrote on Tue, 04 August 2009 08:07
Hi Michel ,
i do not have access to the link for print table

More precisely?

Regards
Michel

Re: Reg Dynamic sql in stored proc (merged) [message #416727 is a reply to message #416693] Tue, 04 August 2009 02:02 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
Prajakta001 wrote on Tue, 04 August 2009 08:07
Hi Michel ,
i do not have access to the link for print table

More precisely?

Regards
Michel


I think,
Quote:

Have a look at print_table T. Kyte's function.

Regards
Michel


regards,
Delna
Re: Reg Dynamic sql in stored proc (merged) [message #416733 is a reply to message #416727] Tue, 04 August 2009 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"More precisely" was about "i do not have access to the link" not on the link I gave.

Regards
Michel
Re: Reg Dynamic sql in stored proc (merged) [message #416780 is a reply to message #416716] Tue, 04 August 2009 05:16 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

execute immediate str_query ;

is written after dbms_output.put_line (str_query ) ;

sorry ..while debugging i had removed it Sad
Re: Reg Dynamic sql in stored proc (merged) [message #416797 is a reply to message #416693] Tue, 04 August 2009 05:53 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
So do you want to post the code with the execute immediate in it along with the error message - then we might actually stand a chance of working out what you're doing wrong.
Re: Reg Dynamic sql in stored proc (merged) [message #416805 is a reply to message #416797] Tue, 04 August 2009 06:48 Go to previous messageGo to next message
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
Re: Reg Dynamic sql in stored proc (merged) [message #416806 is a reply to message #416805] Tue, 04 August 2009 06:54 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
are you sure its giving you right answer? Smile
Re: Reg Dynamic sql in stored proc (merged) [message #416809 is a reply to message #416806] Tue, 04 August 2009 07:06 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

yes Ayush..May be its not generic solutin but its working for me ..If you ahve anything better to share please do that
Regards
Prajakta
Re: Reg Dynamic sql in stored proc (merged) [message #416810 is a reply to message #416809] Tue, 04 August 2009 07:19 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
for example in first replace you have replaced like this

REPLACE(str_query,'IN_ACCT_NO',in_acct_no)


while in your procedure
 in_acct_no      acct.acct_no%TYPE := '-99999999999'; 


seems a string type.
Re: Reg Dynamic sql in stored proc (merged) [message #416817 is a reply to message #416810] Tue, 04 August 2009 07:39 Go to previous message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

that wont be an issue ..It works
Previous Topic: utl_smtp
Next Topic: error adding column
Goto Forum:
  


Current Time: Wed Dec 07 20:19:12 CST 2016

Total time taken to generate the page: 0.29782 seconds