HomeSQLclBeing in command


Being in command — 3 reacties

  1. (I work for Oracle)
    I am mostly responsible for alias command (someone else did script command – I probably get to bug fix script command)
    Alias is pretty flexible – I am trying to find where it can be improved and where more documentation is required.

    Features that might be handy:
    -A bit of confusion – alias uses binds (better for sql parse) instead of (more flexible) substitution variables
    – however you can change binds to substitution variables with tosub command (rather than sqlplus style column new_val),
    -tosub command (bind to substitution variable see help tosub) – as well as official functionality – good to force a bind reference –
    i.e. force a bind reference: make sure that bind is required in alias arguments (and force the bind position in alias arguments).
    issue: varchar2 only
    -alias command code can be q string or bind (and plsql and SQLCL script – not SQL only)
    issue: q string and bind (containing command code) not documented (it made help too long/too confusing)
    -create and run alias within an alias -> can run generated SQLCL(including PLSQL/SQL) code
    issue: the drop of the created alias – and potentially other feedback/text visible to user – might be hard to avoid/mask.
    -‘output bind’ (I have not tried this recently)
    – make sure ‘output’ bind is set before calling alias
    – binds set on input are changed when alias is finished (i.e. not reset to starting value).
    issue: some people might consider not set back to starting value a bug. You could use substitution variables for varchar2 output.
    (substitution variables are not implicitly set/reset).

    What are the problems with the alias command?
    Looks like binds cannot be used ‘everywhere’ (but substitutions can (or its a bug – i.e. internally might use pre substitution sql text))
    i.e. your issue is Is this what you want to work: alias file2bind=script file2bind :b1 :b2 :b3;
    You _can_ do: (force new binds to be visible to alias and therefore can be used as parameters)
    SQL> alias file2bind=q”
    4* /
    SQL> file2bind 1 2
    where: SQL> alias nr_of_obj=select count(*) object_count
    2 from dba_objects
    3 where object_type like upper(:b1)
    4 and object_name like upper(:b2)
    5* ;
    You did not give your real file2bind statement. If you want output bind available – (from memory I should write a test case) you need to set it before call (so it will not be wiped on exit, only ‘new binds’ are passed in as parameters, and wiped on exit – binds changed within the alias are changed permanently – i.e. can be output binds). (For extreme flexibility – you can write sqlcl code into an bind, use that bind to create an alias, run the alias, and delete the alias. (i.e. run generated code))

  2. (My reply come out too long/formatted badly)

    I am responsible for alias command – some features that might come in handy:

    #1/ help tosub – bind var to substitution var. (Or force a bind reference in script execution order)

    #2/ code for alias command can be given by q string – or bind variables (not just one line not just sql – but sqlcl scripts including script, plsql and sql). (Not documented)

    #3/ run generated code – by creating running deleting alias at run time

    #4/ output binds – existing binds are not reset to value at start of alias time.

    #5/ substitution variables are not implicitly set or reset in alias.

    I might also be assigned to fix script/javascript bugs. (I have not been assigned to script bugs so far).

    • (As the author has coded client side script/javascript works in this case (client side file contents) rather than server side plsql),
      I am clearing up/finding any alias issues

      SQL> alias file2bind=q…/ was eaten by markdown

      was an alias set by q sting the alias contained:
      tosub thesub=:b1
      tosub thesub2=:b2

      so you can call it with

      file2bind 1 2

      with :b1 = 1
      :b2 = 2 passed into file2bind

      You could set existing binds however you want (input or output) as long as they are not command line arguments. i.e. lobs in this case for file contents to be output.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *

Deze site gebruikt Akismet om spam te verminderen. Bekijk hoe je reactie-gegevens worden verwerkt.

HTML tags allowed in your comment: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">