5 Replies Latest reply: May 10, 2012 4:24 PM by 871594 RSS

    execute immediate will work in SQL Block in shell script?

    871594
      Hi Friends,

      i am wirting script to get the account id's corresponding to CIF from oracle table and need to pass to procedure.
      Here first_org and last_org are shell variables.
      When i execute the below program it is throwing execute not found.

      Can you please correct me where i made a mistake? and also please let me know how to display some content in below sql block ?dbms_output.put_line or print which need to use and provide me the syntax for both.


      `sqlplus -s crmuser/******@dotis11<<ENDOFSQL
      whenever sqlerror exit 1
      declare
      qstr varchar2(200);
      facid varchar2(20);
      Lacid varchar2(20);
      begin

      qstr:='select ACCOUNTID from accounts where ORGKEY=:1';
      execute immediate qstr into facid using $first_org;
      dbms_output.put_line(facid);
      qstr:='select ACCOUNTID from accounts where ORGKEY=:1';
      execute immediate qstr into Lacid using $last_org;
      dbms_output.put_line(Lacid);
      exec Retail_Otu_Dedup_Account(facid,Lacid)
      end;
      /
      exit;
      ENDOFSQL`

      Thanks,
      Venkat Vadlamudi.
        • 1. Re: execute immediate will work in SQL Block in shell script?
          SamFisher
          What's the error you are getting?
          Did you assign values to first_org & last_org?
          • 2. Re: execute immediate will work in SQL Block in shell script?
            Solomon Yakobson
            You need SET SERVEROUTPUT ON otherwise DBMS_OUTPUT.PUT_LINE will not display. You don't need backticks unless you are assigning sqlplus output to a shell variable:
            sqlplus -s /<<ENDOFSQL
            set serveroutput on
            declare
            qstr varchar2(200);
            facid varchar2(20);
            Lacid varchar2(20);
            begin
            qstr:='select dummy from dual where dummy=:1';
            execute immediate qstr into facid using '$first_org';
            dbms_output.put_line(facid);
            end;
            /
            exit;
            ENDOFSQL
            X

            PL/SQL procedure successfully completed.
            SY.
            • 3. Re: execute immediate will work in SQL Block in shell script?
              871594
              Hi SY,

              I Included set serveroutput on but i didn't include single quotes for shell variable in execute immediate.

              Now iam able to execute and getting proper o/p.

              Thanks SY for your help...

              and how to print shell variable in above sql block..i tried like this. but throwing error.

              dbms_output.put_line('$first_org');

              dbms_output.put_line('VCIF1320');
              ***
              ERROR at line 5:
              ORA-06550: line 5, column 12:
              PLS-00103: Encountered the symbol "." when expecting one of the following:
              constant exception <an identifier>
              *<a double-quoted delimited-identifier> table long double ref*
              char time timestamp interval date binary national character
              nchar
              The symbol "<an identifier>" was substituted for "." to continue.
              ORA-06550: line 6, column 5:
              PLS-00103: Encountered the symbol "=" when expecting one of the following:
              constant exception <an identifier>
              *<a double-quoted delimited-identifier> table long double ref*
              char time timestamp interval date binary national characte

              Thanks,
              Venkat Vadlamudi.
              • 4. Re: execute immediate will work in SQL Block in shell script?
                Solomon Yakobson
                868591 wrote:

                I Included set serveroutput on but i didn't include single quotes for shell variable in execute immediate.
                The reason I enclosed shell variable in quotes is column dummy in dual table is a string with value 'X'. If ORGKEY in your case is a string, you should use quotes too. In regards to dbms_output, post your shell script.

                SY.
                • 5. Re: execute immediate will work in SQL Block in shell script?
                  871594
                  Yes SY, ORGKEY is also a string.

                  i resolved the issue of printing shell variable in sqlblock .

                  dbms_output.put_line('$first_org'); // First i tried like this.got a error
                  dbms_output.put_line('${first_org}'); // Changed to this.which is working.

                  Thanks,
                  Venkat Vadlamudi