Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    89

    Unanswered: Migrating SQL to JDeveloper

    Hi everyone.

    I have a rather complex Oracle-based SQL query, with a Union in it and many subqueries and conversions for output and readability, and so it's rather huge, so I won't even post it here.

    It includes 4 tables all inner joined together (although a 5th one (actually a view) is used in several subqueries), and it has 6 variables that the user is asked to define as a set of search criteria.

    e.g. User opens client, opens this big script, executes it ("PL/SQL Developer" then opens a mask asking for all variables to be filled out, or some of them). And then the result frame of the client program shows the listing as queried for.

    Up to now everyone was simply running it inside of this developer client.
    So now, I want to transfer this query into JDeveloper, to make an actual application out of it, utilizing JSF or JSP.

    I tried using one of their (Oracle's) tutorials, but these are trimmed for simple 2-table queries, without any extra hoo-haa, like what I have.

    Ideally I would like to just define my query as an existing view, or something pre-defined, that JDeveloper just needs to execute and I define which result column goes into which JSF widget, for example.

    Does anyone know how to do that? Or maybe, where I can find an explanation on any of the clever Oracle/Java interwebs?
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  2. #2
    Join Date
    Jan 2009
    Posts
    3
    I've blogged an answer to your question over here, with a screencast you can watch to follow-along step by step to achieve your goal (there aren't very many steps!). Hope this helps.

    Dive into Oracle ADF

  3. #3
    Join Date
    Mar 2008
    Posts
    89
    Quote Originally Posted by steve.muench
    I've blogged an answer to your question over here, with a screencast you can watch to follow-along step by step to achieve your goal (there aren't very many steps!). Hope this helps.

    Dive into Oracle ADF
    I wish all answers to my questions were step-by-step video clips!

    ViewObjects are the answer then! exactly what I was looking for.
    Many thanks for this!
    Just one question, does this work with any query? I mean, does it have any limits as to the complexity or the number of database objects involved?
    Any syntax restrictions? (For example, I know UNION syntax is not always supported, at least in JDev 10, which I had used for my attempt)

    But it's been very helpful, I'll get right on testing that with my query!
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  4. #4
    Join Date
    Jan 2009
    Posts
    3
    Using the approach I outlined in the screencast (a read-only view object based on a SQL statement) the query can be of arbitrary complexity, so long as it parses as a valid SQL statement.

  5. #5
    Join Date
    Mar 2008
    Posts
    89
    Quote Originally Posted by steve.muench
    Using the approach I outlined in the screencast (a read-only view object based on a SQL statement) the query can be of arbitrary complexity, so long as it parses as a valid SQL statement.
    This might sound very inexperienced, and well... I am.
    But in my SQL I have been using ampersand variables (i.e. &myvar1, &myvar 2...), instead of real bind variables (:myvar1...), in order to prompt the user to provide the input.

    I would think I can just exchange the &'s with :'s, am I right?
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  6. #6
    Join Date
    Jan 2009
    Posts
    3
    There are lexical (i.e. string substitution) parameter (&paramName) and SQL bind variables (aramName). JDeveloper/ADF does not support lexical substitution parameters since, in general, allowing the end-user to supply values for lexical substitution variables exposes your application to so-called SQL-injection attacks. A carefully crafted value can allow an end-user to see data they shouldn't otherwise be allowed to see. It would be possible to achieve a similar effect, but would require some programming to perform the string substitution in Java. Generally we recommend using proper SQL bind variables so that your application data stays safe from SQL-injection attacks.

    Are you using the string substitution so that a developer can supply a comma-separated list of keys to be substituted into an IN(...) clause? Or some other use case?

  7. #7
    Join Date
    Mar 2008
    Posts
    89
    Ah ok, now that you say it, it makes a lot of sense.

    Alright well, I am using string substitution in the form of
    Code:
    Where username like replace('&Username%','-','')
    so I can do all kinds of string-manipulation,
    but I am also using
    Code:
    Where access_type in (&Access)
    exactly for the effect you mentioned,
    so that the user can search for comma-delimited lists of values.

    Typically the user base is just a bunch of IT Support persons, who don't know any SQL, but were given "PL/SQL Developer" to access a database.
    I wrote them my query as a pseudo-application to access it without using any SQL, but still inside the the above application.
    But now several other departments have shown interest in using this query, so I am going to try and make an actual application out of it, so they don't need to pay for "PL/SQL Developer" licenses.
    "My brain is just no good at being a relational Database - my relations suck real bad!"

Posting Permissions

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