9 Replies Latest reply on Jul 18, 2006 9:00 PM by 487726

    Single quotes problem with execute immediate

    487726
    487726 Jul 17, 2006 6:45 PM
      Thanks for considering to solve the issue.

      [i]Situation:
      I am trying to create a procedure to perform a set of operations. As part of that, I am trying to create a table using execute immediate statement. This create table statement has a select sub query where p_LOB3 is the variable for the procedure of datatype varchar2.

      Problem :
      I need to pass the variable p_LOB3 as single quoted as it is of type Varchar2. Also I need to enclose the entire create table query within single quotes. How do I specify this as it is throwing an error when the PL/SQL engine is parsing the single quotes in the query used twice for different purposes as mention earlier.


      Query:
      execute immediate'create table test5 as select min(contract_number)as contract_number,contact_id,max(line_of_business) as line_of_business from mytable group by contact_id having min(contract_number) = max(contract_number) and max(Line_of_business) = 'p_LOB3' ';
      • 1405 Views
      • Tags:
        • 1. Re: Single quotes problem with execute immediate
          23650
          23650 Jul 18, 2006 12:04 AM (in response to 487726) 
          execute immediate 'create table test5 as select min(contract_number)
as contract_number,contact_id,max(line_of_business) as line_of_business
from mytable group by contact_id having min(contract_number) = max(contract_number)
and max(Line_of_business) = ''' || p_LOB3 || '''';
          • 2. Re: Single quotes problem with execute immediate
            247735
            247735 Jul 17, 2006 7:03 PM (in response to 487726)
            You have to concatenate the values as in the following query

            execute immediate'create table test5 as select min(contract_number)as contract_number,contact_id,max(line_of_business) as line_of_business from mytable group by contact_id having min(contract_number) = max(contract_number) and max(Line_of_business) = ' || p_LOB3 || ''''

            Message was edited by:
            rajaived
            • 3. Re: Single quotes problem with execute immediate
              487726
              487726 Jul 17, 2006 11:15 PM (in response to 23650)
              Hello Todd,

              Is there a any other way to achieve this, as I am getting an error message say that bind variables cannot be used with DDL statements such as 'create' )..

              Please suggest me if there is a work around for this.

              Thanks in advance.
              -Ac
              • 4. Re: Single quotes problem with execute immediate
                23650
                23650 Jul 18, 2006 12:09 AM (in response to 487726)
                Sorry, I glossed over the DDL issue here. See my edited reply above where you'll need to use double single quotes around the parameter value.

                This would be similar to rajaived's reply except that you need more quotes than shown in that example.
                • 5. Re: Single quotes problem with execute immediate
                  jaggyam
                  jaggyam Jul 18, 2006 10:57 AM (in response to 23650)
                  execute immediate 'create table test5 as select min(contract_number)
                  as contract_number,contact_id,max(line_of_business) as line_of_business
                  from mytable group by contact_id having min(contract_number) = max(contract_number)
                  and max(Line_of_business) = ''' || p_LOB3 || '''';

                  I think you missed a quote here...
                  and max(Line_of_business) = ''' || p_LOB3 || '''';
                  and max(Line_of_business) = '''' || p_LOB3 || '''';
                  • 6. Re: Single quotes problem with execute immediate
                    23650
                    23650 Jul 18, 2006 3:52 PM (in response to jaggyam)
                    No, not really. There is a total of 8 single quotes - don't forget the quote at the beginning of the whole string.
                    • 7. Re: Single quotes problem with execute immediate
                      487726
                      487726 Jul 18, 2006 8:22 PM (in response to 23650)
                      Thank you Todd,

                      Is just worked fine.

                      New issue is: I am not able to put 2 such statements in a single procedure and execute. Before I give parameters to the procedure, PL/SQL engine is actually creating a view of the mytable and naming is as test5, as a result I am not able to create a table as there is a view with the same name.

                      Right now, the workaround I am using is to create three different procedures to create three such tables. I know this is not a good idea....can you please tell me if there is a better way.

                      Procedure
                      CREATE OR REPLACE PROCEDURE SP_CREATE_0_0(p_LOB1 IN varchar2, p_LOB2 IN varchar2)
                      IS
                      BEGIN
                      execute immediate 'create table test5 as select min(contract_number) as
                      contract_number,contact_id,max(line_of_business) as line_of_business from
                      mytable group by contact_id having min(contract_number) = max(contract_number)
                      and max(Line_of_business) = ' ' ' || p_LOB1 || ' ' ' ';

                      execute immediate 'create table test5 as select min(contract_number) as
                      contract_number,contact_id,max(line_of_business) as line_of_business from
                      mytable group by contact_id having min(contract_number) = max(contract_number)
                      and max(Line_of_business) = ' ' ' || p_LOB1 || ' ' ' ';

                      END SP_CREATE_0_0;
                      /
                      • 8. Re: Single quotes problem with execute immediate
                        23650
                        23650 Jul 18, 2006 8:38 PM (in response to 487726)
                        I'm not completely following you here. What "PL/SQL engine" is creating a view called test5? Even setting that question aside, why do you have two consecutive statements trying to create a table with the same name (test5)?

                        Creation of objects in code is not a generally recommended practice in any case. What are you trying to accomplish here? Maybe there is a better way to handle your requirement without having to create objects in code.
                        • 9. Re: Single quotes problem with execute immediate
                          487726
                          487726 Jul 18, 2006 9:00 PM (in response to 23650)
                          Yes, test5 view is being created before I give parameters to the procedure which prohibits from creating a table.

                          It is test5 and test6. that was a mistake.

                          Requirement:All I want to do is to update a flag for all the records from this query:

                          select min(contract_number)as contract_number,contact_id,max(line_of_business) as line_of_business from mytable group by contact_id having min(contract_number) = max(contract_number) and max Line_of_business) = ' ' ' || p_LOB1 ||' ' ' ';

                          I have three four such Line_of_businesses I need to check.

                          Thanks,
                          Avinash