Re: Describing bound variables for PL/SQL

In article <8hmc35$ibk$1_at_nnrp1.deja.com>,   arivlin_at_my-deja.com wrote:
> Hi, all,
> Here is a puzzle. I can use OCIDescribeAny to get parameters to bind
> variables for procedures and tables. For example I can use
> OCIDescribeAny and pass it table name and then get data types for
> columns.
>
> Now, I have this:
> begin
> declare :v_1 int, :v2 int;
> cursor :my_cur is select to_char ( sysdate, 'dd/mm/yyyy' ) from
 dual;
> end;
>
> I want to use OCIDescribeAny to find out that 1st bound var is int,
> second is int, third is ref cursor returning varchar2(...).
>
> Anyone had done that?
> Alex
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

well, first
> declare :v_1 int, :v2 int;

won't work -- you cannot bind IDENTIFIERS, what would work is:

declare

   v_1 int default :v_1;
   v_2 int default :v_2;
...

as to the answer -- when you compile a procedure into the database, we generate a table of all of the inputs/outputs (look at all_arguments). When you describe a procedure -- it uses that to tell you whats expected to be bound to these parameters.

When you create an anonymous block, its done just like a select statment -- no such table is populated, no such information is available. for example, say you parse:

   select * from emp where empno = :x;

it'll not tell you :x is a number (in actuality -- :x is not necessarily a number, you can bind anything you want to it) -- you have to tell IT what you are wanting to bind to it.

Well, the same is true for:

declare

   x int default :x;
begin

   ...
end;

YOU tell us what YOU have to bind to it. You can bind an Integer, you can bind a string, you can bind whatever you happen to have in the client to that field.

Only when you have a procedure with formal, typed arguments compiled into the database will you be able to discover the types that would be most relevant to bind.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.