Path: newssvr20.news.prodigy.com!newsmst01a.news.prodigy.com!prodigy.com!newsfeed.telusplanet.net!newsfeed.telus.net!cyclone.bc.net!nntp.cs.ubc.ca!newsfeeds.ihug.co.nz!ihug.co.nz!ken-transit.news.telstra.net!lon-in.news.telstra.net!news.telstra.net!news-server.bigpond.net.au!53ab2750!not-for-mail
From: "Douglas Hawthorne" <douglashawthorne@yahoo.com.au>
Newsgroups: comp.databases.oracle.server
References: <shaokao.159e9s@mail.mcse.ms>
Subject: Re: newbie's question : how to get the prodecure's parameters ingomation
Lines: 81
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <9IKic.9281$qq6.345@news-server.bigpond.net.au>
Date: Sun, 25 Apr 2004 08:32:05 GMT
NNTP-Posting-Host: 138.130.85.109
X-Complaints-To: abuse@bigpond.net.au
X-Trace: news-server.bigpond.net.au 1082881925 138.130.85.109 (Sun, 25 Apr 2004 18:32:05 EST)
NNTP-Posting-Date: Sun, 25 Apr 2004 18:32:05 EST
Organization: BigPond Internet Services
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:260126

"shaokao" <shaokao.159e9s@mail.mcse.ms> wrote in message
news:shaokao.159e9s@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