Re: newbie's question : how to get the prodecure's parameters ingomation

"shaokao" <shaokao.159e9s_at_mail.mcse.ms> wrote in message news:shaokao.159e9s_at_mail.mcse.ms...
>
> i want to dispaly it in a aspx page
>
> i can't compile my procedure
>
> create or replace procedure
> BUSTRANS.GetProcedureParameter(procedurename varchar2)
> is
> V_OverLoad DBMS_DESCRIBE.NUMBER_TABLE;
> V_Position DBMS_DESCRIBE.NUMBER_TABLE;
> V_Level DBMS_DESCRIBE.NUMBER_TABLE;
> V_Argument_Name DBMS_DESCRIBE.VARCHAR2_TABLE;
> V_DataType DBMS_DESCRIBE.NUMBER_TABLE;
> V_Default_Value DBMS_DESCRIBE.NUMBER_TABLE;
> V_In_Out DBMS_DESCRIBE.NUMBER_TABLE;
> V_Length DBMS_DESCRIBE.NUMBER_TABLE;
> V_Precision DBMS_DESCRIBE.NUMBER_TABLE;
> V_Scale DBMS_DESCRIBE.NUMBER_TABLE;
> V_Radix DBMS_DESCRIBE.NUMBER_TABLE;
> V_Spare DBMS_DESCRIBE.NUMBER_TABLE;
>
> begin
>
> EXEC(Create Global Temporary table parameter_tmp(V_Argument_Name
> varchar2(200),V_DataType Number(10),V_IN_OUT Number(10),V_Length
> Number(10),V_Precision Number(10),V_Scale Number(10)) on commit delete
> rows);
>
> DBMS_DESCRIBE.DESCRIBE_PROCEDURE (
> procedurename,
> NULL,
> NULL,
> V_OverLoad,
> V_Position,
> V_Level,
>
> V_Argument_Name,
> V_DataType,
> V_Default_Value,
> V_In_Out,
> V_Length,
> V_Precision,
> V_Scale,
> V_Radix,
> V_Spare);
>
> FOR V_Cnt IN 1..V_Argument_Name.COUNT() LOOP
> Insert Into
>

parameter_tmp(V_Argument_Name,V_DataType,V_IN_OUT,V_Length,V_Precision,V_Sca le)
> values(V_Argument_Name,V_DataType,V_IN_OUT,V_Length,V_Precision,V_Scale);
>
> END LOOP ;
>
> Commit;
>
> end;
> /
>
>
>
> --
> shaokao
> ------------------------------------------------------------------------
> Posted via http://www.mcse.ms
> ------------------------------------------------------------------------
> View this thread: http://www.mcse.ms/message601457.html
>

Shaokao,

Instead of doing all of this work, why don't you use the ALL_ARGUMENTS view instead? See p.2-12 of the "Oracle 10G Database Reference" for more details.

Douglas Hawthorne Received on Sun Apr 25 2004 - 03:32:05 CDT