2 Replies Latest reply: Nov 8, 2012 2:20 AM by 949228 RSS

    Need help on REPLACEMENT VARIABLE

    949228
      I'm trying to use a replacement variable in the following script.
      Someone can help me to find the error?
      Thanks in advance

      Paolo

      ----------

      SYS@orcl1 SQL> !uname -a
      Linux localhost.localdomain 2.6.18-274.17.1.el5 #1 SMP Tue Jan 10 17:26:03 EST 2012 i686 i686 i386 GNU/Linux

      SYS@orcl1 SQL> select * from v$version;

      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
      PL/SQL Release 10.2.0.1.0 - Production
      CORE 10.2.0.1.0 Production
      TNS for Linux: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production



      SYS@orcl1 SQL> !cat query1.sql
      declare
      TYPE QueryCurType is REF CURSOR;
      query1 QueryCurType ;
      cursor c1 is select owner,table_name from dba_tables where owner not in ('SYS','SYSTEM') and table_name not like '%$%';
      cursor c2(t1 varchar2) is select column_name from dba_tab_columns where table_name=t1 and DATA_TYPE in ('NVARCHAR2','VARCHAR2','CHAR');
      temp_var varchar2(3000);
      query varchar2(3000);
      begin
      for tab1 in c1 loop
      for col in c2(tab1.table_name) loop
      query:='select '||col.column_name||' from '||tab1.owner||'.'||tab1.table_name||' where '||col.column_name||' like ' ||*'&&1'*;
      dbms_output.put_line('executing..'||query);
      open query1 for query;
      loop
      fetch query1 into temp_var;
      if concat('a',temp_var) != 'a' then
      dbms_output.put_line('Found String: "'||temp_var||'"# Column:'||col.column_name||'# Table:'||tab1.table_name);
      end if;
      exit when query1%NOTFOUND;
      end loop;
      end loop;
      end loop;
      end;
      /


      SYS@orcl1 SQL> @query1 '%TAB%';
      old 11: query:='select '||col.column_name||' from '||tab1.owner||'.'||tab1.table_name||' where '||col.column_name||' like ' ||'&&1';
      new 11: query:='select '||col.column_name||' from '||tab1.owner||'.'||tab1.table_name||' where '||col.column_name||' like ' ||'%TAB%';
      declare
      *
      ERROR at line 1:
      ORA-00911: invalid character
      ORA-06512: at line 13


      SYS@orcl1 SQL> @query1 %TAB%
      old 11: query:='select '||col.column_name||' from '||tab1.owner||'.'||tab1.table_name||' where '||col.column_name||' like ' ||'&&1';
      new 11: query:='select '||col.column_name||' from '||tab1.owner||'.'||tab1.table_name||' where '||col.column_name||' like ' ||'%TAB%';
      declare
      *
      ERROR at line 1:
      ORA-00911: invalid character
      ORA-06512: at line 13
        • 1. Re: Need help on REPLACEMENT VARIABLE
          rp0428
          No - not in this forum. This forum is for sql developer questions. Mark the question ANSWERED and repost it in the sql and pl/sql forum
          SQL and PL/SQL
          >
          I'm trying to use a replacement variable in the following script.
          Someone can help me to find the error?
          >
          Yes - you can help yourself find the error.

          Don't try to automate what you can't run manually.

          The first step in trying to automate query generation and execution is to generate the query. Note that I did not say 'generate hundreds of queries'. Generate ONE query.

          The second step is to run that query manually to make sure it runs properly. That is the step you are NOT doing.

          If you do that step it will be obvious what the error is.

          Modify your script to comment out the execution parts of the script and to only generate ONE query. Then try to run that query in another session and see what happens.

          Only AFTER you can generate and run ONE query manually should you go to step two and try to automate that.

          Your code also has NO exception handling at all. That is a sure recipe for disaster.
          • 2. Re: Need help on REPLACEMENT VARIABLE
            949228
            Thanks for suggestions