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

    Single quotes problem with execute immediate

    487726
      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' ';
        • 1. Re: Single quotes problem with execute immediate
          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 || '''';
          • 2. Re: Single quotes problem with execute immediate
            247735
            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
              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
                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
                  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
                    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
                      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
                        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
                          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