Concatenation woes.. 1

[RhythmAddict112]

Hi all,
It's friday at 4:57 and my brain is not performating optimally by any means....I'm going wrong somewhere in this proc. with the concats and was hoping someone could point out my error(s?)

AS 
 PROCEDURE ADD

   (i_area IN area.id%TYPE,
   i_group IN ciw_groups.id%TYPE,
   i_table IN varchar2,
   i_description IN group_accounts.description%TYPE,
   RC1 IN OUT REF_CUR
   )
IS 
  v_Exist varchar2(100);
 
BEGIN 
 
  OPEN RC1 FOR
   'SELECT * FROM accounts;';
   || i_table 
   || ' WHERE Area = '
   || i_area
   || ' AND GroupID = '
   || i_group
   || ' AND description = ''
   || i_description 
   || '';';
 
   
--   fetch rc1 into v_exist;
--  dbms_output.put_line(i_description);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXECUTE IMMEDIATE ('INSERT INTO ' ||i_table|| ' 
        (Description, Area, GroupID) 
          VALUES ('
          ||i_description||','
          ||i_area||','
          ||i_group||');');
  END;
END;

I can't get past the first select..so I'm not sure if there are concat errors in the execute immediate/insert statement at the bottom (yet)

description is a VARCHAR, so the 's are throwing me off on the select...Any help is greatly appreciated, thank you

All hail the INTERWEB!

 

[SantaMufasa]

Addict,

You have issues with extraneous ";"s and with embedded sing quotes. I'm not sure what your "i_table" concatenation does for your "FROM" statement since you already name your table to read as "accounts". So I'm going to presume that you want to read FROM the contents of "i_table" instead of from "accounts". Also, I've not seen an "OPEN..." state that contains code concatenation. But presuming that it works, here is code that syntactically appears better. (Whether Oracle "allows" is is another issue.):

OPEN RC1 FOR
   'SELECT * FROM '
   || i_table 
   || ' WHERE Area = '
   || i_area
   || ' AND GroupID = '
   || i_group
   || ' AND description = '''
   || i_description
   || '''';

Note my changes:
1) removal of "accounts"
2) removal of the ";" following "accounts"
3) removal of the ";" following "accounts;"
4) insertion of an additional single quote following "AND description = '' "
5) restructuring the final embedded single quote to appear as " ''''; ".

Again, the only way I know of for Oracle PL/SQL to process a SQL command via concatenation is with the "EXECUTE IMMEDIATE..."-style construction.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[RhythmAddict112]

Santa,
How are you? About the part where I define the table as accounts, that's my fault - I actually put that in there to do some debugging and didn't remove it before I copy/pasted my code. My apologies! Again, I'm not too sharp at that time of day!

As far as Open... working with a ref_cursor, it does work! I'm not really a fan of doing concats on any SQL because I just think it's sketchy - that being said I'm working on adding/modifying a project that another developer has made and I must pass a table name into the procedure - concatenating is the only way I see this possible - I receive a 'table not found' error if I drop my bind variable into my SQL as I normally would do.

In any case, I thank you for your help I will be trying this out Monday morning!


All hail the INTERWEB!

 

[RhythmAddict112]

Oh, correct to my post above...I mean to say Open....does work with concat'd SQL...at least in 9i

All hail the INTERWEB!