Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Oct 2012
    Posts
    10

    Unanswered: unix shell scripting and ORACLE

    Hi ,

    I have generate a dynamic sql query in unix and i want to run that generated sql query on unix
    box.But when i have tried to run this its shows error because ther is a space in the generated
    sql...Please assist

    for example:

    code_list=`sqlplus -s Test/test123 <<EOF
    set heading off
    set pagesize 0
    set tab off
    set feedback off
    select decode(rownum, 1, ' ', 'union ') ||'select distinct '''||trial_name||''' as Schema_Name,
    viewname from '||trial_name||'.CA_DATA a where view
    name not in(select distinct object_name from dba_objects a
    where a.owner='''||trial_name ||''' and (a.object_name like ''CV%''))'
    from (
    select distinct owner as trial_name from dba_objects
    where owner like 'MK%'
    or OWNER like 'V%')
    /
    exit
    EOF`
    sqlplus -s Test/test123 <<EOF
    $code_list ;
    exit
    EOF`



    in this query we first connect with DB and put the sql value in the variable.

    and then i want to run that variable(code_list).this variable contain the sql query.I just want to
    run the quey in code_list variable .but its not runing due to spce issue in the genearated dynamic
    query.

    Please assist.it's a very critical request.


    Thanks for you help in advance.I am waiting for ur responce.

    Regards,
    Vikas

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    show us actual results
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    You can use a cursor to loop through the owner names retrieved, try the below code:
    Code:
    code_list=`sqlplus -s Test/test123 <<SQL1
    set heading off pagesize 0 tab off feedback off
    whenever sqlerror exit sql.sqlcode
    declare
      v_sql       varchar2( 2000 );
      v_first     boolean  :=  TRUE;
    cursor tn_cur is
      select distinct owner as trial_name
       from  dba_objects
      where owner like 'MK%'
        or  owner like 'V%';
    begin
      for tn_rec in tn_cur
      loop
        if not v_first then
          v_sql := v_sql || ' union ';
        end if;
        v_sql  :=  v_sql || 'select distinct ''' || tn_rec.trial_name || ''' as schema_name, viewname' ||
                            ' from ' || tn_rec.trial_name || '.ca_data '                               ||
                            'where viewname not in( select distinct object_name'                       ||
                            '                        from dba_objects '                                ||
                            '                       where owner = ''' || tn_rec.trial_name  || ''''    ||
                            '                        and  object_name like ''CV%'' )';
      v_first  :=  FALSE;
      end loop;
      v_sql := v_sql || ';';
      dbms_output.put_line( v_sql );
    end;
    /
    exit
    SQL1`
    sqlplus -s Test/test123 <<SQL2
    $code_list
    SQL2`

  4. #4
    Join Date
    Oct 2012
    Posts
    10

    ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer

    Thanks for ur reply.this code is really very helpfull. However facing this size issue.the generated query size is huge.
    error msg:
    ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512:

    I have extent the varchar2 size at maximum but its not help me to resolve this issue.

    Please assist.I am waiting for ur reply.Many Thanks!

    Regards,
    Vikas
    Last edited by vikassri786; 10-21-12 at 03:43.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what problem are you really trying to solve?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Oct 2012
    Posts
    10

    any update?

    any update?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by vikassri786 View Post
    any update?
    No, you don't appear to have answered the questions yet.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Oct 2012
    Posts
    10

    kindaly provide update...

    please suggest the solution of the problem.it a critical request.

    thanks alot in advance for ur help.i am waiting for ur response.

    Regards,
    Vikas

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It isn't possible to provide you an answer to your question until you provide a response to anacedent's questions. While anacedent's questions are broader than this, two specific items that you need to provide are:

    1) What error message does your original PL/SQL script generate? Please post the full output from running that script.
    2) What business or real world problem are you trying to solve?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Oct 2012
    Posts
    10
    the only error msg i recived it is:

    error msg:
    ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    and i want to make the scripting to get the valuabale information.its for my personal use.but the i have to implement this in my job in future.Thanks!

    Regards,
    Vikas

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    since we don't have your tables or data, we can't run, test, debug or improve your code.

    we don't know what are expected or desired results
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Oct 2012
    Posts
    10

    detais

    I have a ca_data table.it contain the object_name (table or view named CV%)which should be in the schema.I have to find out the missing object_name in the ca_data table.

    I have do this in all the schema.so i have extract the schema name from DBA_objects table.

    for ex.

    ca_data table have a column viewname.

    viewname(in ca_data table)
    cv_a
    cv_b
    cv_c
    cv_d


    and in the same schema cv_a,cv_b,cv_c view is available.

    i want to crate a query which find missing viewname in ca_data.in this case output should be CV_d.


    i have to make this in all the schema.and send the output using mailx feature in unix.

    Please assist and let me know if u need any other info.

    Regards,
    Vikas

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Select viewname from ca_data
    minus
    select table_name from user_tables;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Oct 2012
    Posts
    10
    i want to make it generic for all the schemas. so please provide me the details to make it generic(for all schemas) select the schemas from dba_objects

  15. #15
    Join Date
    Oct 2012
    Posts
    10
    this below code is helpfull for me but its a size issue in varchar2 variable:

    code_list=`sqlplus -s Test/test123 <<SQL1
    set heading off pagesize 0 tab off feedback off
    whenever sqlerror exit sql.sqlcode
    declare
    v_sql varchar2( 2000 );
    v_first boolean := TRUE;
    cursor tn_cur is
    select distinct owner as trial_name
    from dba_objects
    where owner like 'MK%'
    or owner like 'V%';
    begin
    for tn_rec in tn_cur
    loop
    if not v_first then
    v_sql := v_sql || ' union ';
    end if;
    v_sql := v_sql || 'select distinct ''' || tn_rec.trial_name || ''' as schema_name, viewname' ||
    ' from ' || tn_rec.trial_name || '.ca_data ' ||
    'where viewname not in( select distinct object_name' ||
    ' from dba_objects ' ||
    ' where owner = ''' || tn_rec.trial_name || '''' ||
    ' and object_name like ''CV%'' )';
    v_first := FALSE;
    end loop;
    v_sql := v_sql || ';';
    dbms_output.put_line( v_sql );
    end;
    /
    exit
    SQL1`
    sqlplus -s Test/test123 <<SQL2
    $code_list
    SQL2`




    v_sql contain the sql buti have faced a errror "ORA-06502: PL/SQL: numeric or value error: character string buffer too small "

    Please help me to resolve this error.this code is perfact for me.


    Please assist.

    Regads,
    Vikas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •