Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Posts
    10

    Unanswered: To_char using dblink

    b:= ' insert into ' || tabName ||
    ' (' || colList || ',EXT_ORG_BUS_DESC,COUNTRY_INC,DUNS,UPDATED_PGUID, LAST_UPDATED) ' ||
    ' select ' || colList ||', to_clob(ext_org_bus_desc) EXT_ORG_BUS_DESC,null,(' || TO_CHAR (DUNS,'000000000') || '),null,systimestamp ' ||
    ' from ' || tabName ||'@'||dblink;


    execute immediate(b);

    Its asking me to declare the DUNS Identifier, But the Column does exist in the Table, How am I suppsoed to Handle this problem ?

    Thanks

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Possibly the query is being built with a table you are not expecting?

    Try catching the SQL statement just before you execute it. Then you can run that SQL statement by itself ie via Sql-Plus and pinpoint your problem.

    HIH

  3. #3
    Join Date
    Jan 2005
    Posts
    10
    I cehcked it, Something is wrong near To_char , AM I missing any quotes or anyhting, Iam executing with execute Immediate. T

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    You do have parens around the to_char. Don't know why you need them.

    ,(' || TO_CHAR (DUNS,'000000000') || '),

    should probably be

    select ' || colList ||', to_clob(ext_org_bus_desc) EXT_ORG_BUS_DESC,null,' || TO_CHAR (DUNS,'000000000') || ',null,systimestamp ' ||

    if you grab out the SQL after it is parse and before it is executed, you can see for sure what doesn't belong.

    HIH

  5. #5
    Join Date
    Jan 2005
    Posts
    10
    I did exactly what you asked me to do, Its still saying the Identifier DUNS Must be declared .

    b:= ' insert into ' || tabName ||
    ' (' || colList || ',EXT_ORG_BUS_DESC,COUNTRY_INC,DUNS,UPDATED_PGUID, LAST_UPDATED) ' ||
    ' select ' || colList ||', to_clob(ext_org_bus_desc) EXT_ORG_BUS_DESC,null,' || TO_CHAR (DUNS,'000000000') || ',null,systimestamp ' ||
    ' from ' || tabName ||'@'||dblink;

    PLS-00201: identifier 'DUNS' must be declared

    Thanks for the Help.

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1, Its looking for DUNS not in the table but as a variable as it is outside your single quotes.

    2.use dbms_output.put_line to output the string afterwards to check its validity.

    Alan

Posting Permissions

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