This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 5, 2012 10:35 AM by glauser Go to original post RSS
  • 15. Re: Bug EA 4 (3.0.03.97) Bind Variable Not Declared on Insert from select
    glauser Newbie
    Currently Being Moderated
    Even with version 3.2.10.09 of SQL Developer, depending on the syntax and complexity of a Select-Statement, no one or not all bind variables are asked. Example query:

    (papg_shop.suche is a table function)
    <pre>
    select
    su.*,
    ar.artnr artnrar, ar.aktiv,
    ar.artart, nvl (art.text, kgtxt.text) text, kgtxt.titel titel,
    LOWER(NVL(NVL(froschg.pfad, froschp.pfad),'null')) oekologo,
    dl.lovid markencode,
    dl.text1 markenfile,
    dl.bez markenbez
    from table(cast (papg_shop.suche(
    *:pKatalog,*
    null -- :pSortimente, +/* normal :pSortimente, für JDEV tapg_number(:pSortimente), für Toad: tapg_number(500,502), */+
    *:pSprache,*
    *:pSuchbegriff,*
    *:pKatgliedart,*
    *:pKatgliederungroot,*
    null, --:pArtnrs, /* für Toad: varchar2array('525000', '525101')
    *:pSuchfeld, -- 1 = volltext 2 = artnr 4 = original*
    *:pArtikelvorkommen, -- 1= katalog 2 = nicht-katalog*
    *:pAusverkauft, -- 1= nicht ausverkauft, 2 = ausverkauft*
    *:pMinanztreffer,*
    *:pMitallenchilds,*
    *:pKundnr,*
    *:pHatartikelohnepreise*
    ) as tapg_katgliederung_erw)) su,
    artikel ar, arttext art, katgliedtext kgtxt,
    katgliedmultimedia froschg,
    katgliedmultimedia froschp,
    katgliederung kgp
    , katgliedtext kgpt
    , divlovs dl
    WHERE ar.art_id (+) = su.art_id
    AND art.art_id (+) = ar.art_id
    AND su.katgliederung = kgtxt.katgliederung(+)
    AND kgtxt.sprache(+) = 'D'
    AND NVL(art.sprache,'D') = 'D'
    AND NVL(art.arttextart,'basis') = 'basis'
    AND NVL(ar.artnr, '0') <> '099999'
    AND su.parent = kgp.katgliederung (+)
    AND froschg.katgliederung (+) = su.parent
    AND froschg.katgliedmmtyp (+) = 'OEKOLOGO'
    AND froschp.katgliederung (+) = kgp.parent
    AND froschp.katgliedmmtyp (+) = 'OEKOLOGO'
    AND LOWER(NVL(NVL(froschg.pfad, froschp.pfad),'null')) like *:pLogo*
    AND su.parent = kgpt.katgliederung (+)
    AND kgpt.sprache (+) = *:pSprache*
    AND ar.dlart3 = dl.lovart (+)
    AND ar.lovid3 = dl.lovid (+)
    ORDER BY DECODE(su.art_id, null, kgtxt.titel, kgpt.titel)
    ,DECODE(su.art_id, null, su.katgliederung, su.parent)
    ,DECODE(su.art_id, null, 1, 2)
    ,NVL(su.bez, su.artnr) ;
    </pre>
    In this case, SQL Developer asks for NO bind variables at all (no dialog is displayed)

    There are examples where only some of the bind variables appear in the dialog.

    Edited by: glauser on 05.11.2012 19:27

    Edited by: glauser on 05.11.2012 19:32
1 2 Previous Next

Legend

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