GridView DeleteCommand Erroring on StoredProcedure?

23 Jan 2006 5:08 PM

David R. Longnecker

I'm attempting to call a stored procedure to delete a grouping of records
based on the GridView's DeleteCommand; however, I constantly receive an
error message from Oracle that, after searching through Google and Meta,
does not provide much insight:

ORA-06550: line 1, column 7:
PLS-00801: internal error [22503]
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The Code:
DeleteCommand="KPR_DELETE(:report_id)" DeleteCommandType="StoredProcedure"

<DeleteParameters>
    <asp:FormParameter FormField="report_id" Name="report_id" Type="string"
/>
</DeleteParameters>

The Stored Procedure:

1 CREATE OR REPLACE PROCEDURE KPR_Delete (ReportID IN VARCHAR)
2   IS
3 BEGIN
4   DELETE FROM REPORTS_DETAILS where report_id = TO_NUMBER(ReportID);
5   DELETE FROM REPORTS where report_id = TO_NUMBER(ReportID);
6 COMMIT;
7 END;

I can run the stored procedure just fine from SQLPlus using : "exec
KPR_Delete (123);"; however, the error continues to pop-up when running it
through the web page. Is there a particular way to pass these variables
from the GridView that I'm missing?

Thanks in advance!

-David

--

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

24 Jan 2006 2:12 AM

Kevin Yu [MSFT]

Hi David,

The ORA-06550 is a compilation error. But the stored procedure seems to be
fine. Could you try to add a colon before report_id in the parameter name
like the following?

<DeleteParameters>
<asp:FormParameter FormField="report_id" Name=":report_id"
Type="string"
/>

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

24 Jan 2006 4:01 PM

David R. Longnecker

With the added colon on the parameter, I receive the following error.

Updated code:

<DeleteParameters>
<asp:FormParameter FormField="report_id" Name=":report_id" Type="string" />
</DeleteParameters>

Error:

--
ORA-06550: line 1, column 42:
PLS-00103: Encountered the symbol ":" when expecting one of the following:

( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
The symbol ":" was ignored.

Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.OracleClient.OracleException: ORA-06550: line
1, column 42:
PLS-00103: Encountered the symbol ":" when expecting one of the following:

( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
The symbol ":" was ignored.
--

Thanks!

-David

Show quoteHide quote

"Kevin Yu [MSFT]" <v-k***@online.microsoft.com> wrote in message
news:x$Qj1uIIGHA.1240@TK2MSFTNGXA02.phx.gbl...
> Hi David,
>
> The ORA-06550 is a compilation error. But the stored procedure seems to be
> fine. Could you try to add a colon before report_id in the parameter name
> like the following?
>
> <DeleteParameters>
> <asp:FormParameter FormField="report_id" Name=":report_id"
> Type="string"
> />
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>

25 Jan 2006 6:43 AM

Kevin Yu [MSFT]

Hi David,

Have you tried using some trace tool of Oracle to see what SQL statement is
being called on the server?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

25 Jan 2006 3:29 PM

David R. Longnecker

To both of the trace questions, I'll see what I can do. Personally, I just
have SQL Plus; however, I'm sure we have someone with the Enterprise Suite
around here somewhere.

I'll post more when I have it.

-David

Show quoteHide quote

"Kevin Yu [MSFT]" <v-k***@online.microsoft.com> wrote in message
news:xqBn8qXIGHA.3944@TK2MSFTNGXA02.phx.gbl...
> Hi David,
>
> Have you tried using some trace tool of Oracle to see what SQL statement
> is
> being called on the server?
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>

26 Jan 2006 2:43 AM

Kevin Yu [MSFT]

Thank you David, please post back when you have the trace done.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

26 Jan 2006 4:01 PM

David R. Longnecker

Apparently we do not have any tools in our array that can provide the same
output as the SQL Profiler that I'm used to with SQL Server. Is there any
other way to gather this information for Oracle? (though perhaps a question
best targeted for an Oracle group (^_~))

Thanks!

-David

Show quoteHide quote

"Kevin Yu [MSFT]" <v-k***@online.microsoft.com> wrote in message
news:hC9bDJiIGHA.3680@TK2MSFTNGXA02.phx.gbl...
> Thank you David, please post back when you have the trace done.
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>

27 Jan 2006 4:38 AM

Kevin Yu [MSFT]

Hi David,

^_^ I'm not quite familiar with the tools to do trace for Oracle. But I
remember that there is one with the older versions. And I believe there
should be one in the current version. Maybe it's an optional component in
the installation disc and you can try to install it again.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."