This discussion is archived
2 Replies Latest reply: Nov 8, 2012 12:20 AM by 949228 RSS

Need help on REPLACEMENT VARIABLE

949228 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks for suggestions

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points