general sql select procedure 2006-04-26 - By LS Cheng
hi
You have to use dynamic SQL instead of static SQL
On 4/26/06, Simone Saravalli <s.saravalli@(protected)> wrote:
>
> Hi all, I'm working with sql stored procedure and I created these one:
>
> CREATE OR REPLACE PROCEDURE today (tbl_name IN VARCHAR2, old_key IN
> NUMBER) AS
>
> loop_count NUMBER;
>
> BEGIN
> SELECT COUNT(*) INTO loop_count
> FROM tbl_name
> WHERE depno = old_key;
>
> DBMS_OUTPUT.PUT_LINE ('Valore di loop_count: ' || loop_count);
>
> END today;
>
>
> So, tbl_name is an argument passed with
>
> BEGIN
> today ('one_of_my_tables_name', value);
> END;
>
> I would like to have a procedure that works independently from the
> table name provided by the user when calling the today procedure, but
> when I try to compile the code above I receive the following error:
>
> ERROR at line 8: PL/SQL: ORA-00942 (See ORA-00942.ora-code.com): table or view does not exist
>
> So it doesn't recognize the tbl_name argument. How can I do to solve
> this problem?
>
> Thanks in advance
>
> Simone Saravalli
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
hi<br><br>You have to use dynamic SQL instead of static SQL<br><br><div><span
class="gmail_quote">On 4/26/06, <b class="gmail_sendername">Simone Saravalli</b
> <<a href="mailto:s.saravalli@(protected)">s.saravalli@(protected)
</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px
solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Hi all
, I'm working with sql stored procedure and I created these one:
<br><br>CREATE OR REPLACE PROCEDURE today (tbl_name IN VARCHAR2, old_key IN
NUMBER) AS<br><br>loop_count NUMBER;<br><br>BEGIN<br>SELECT COUNT(*) INTO loop
_count<br>FROM tbl_name<br>WHERE depno = old_key;<br><br>DBMS_OUTPUT.PUT_LINE (
'Valore di loop_count: ' || loop_count);
<br><br>END today;<br><br><br>So, tbl_name is an argument passed with<br><br
>BEGIN<br>today ('one_of_my_tables_name', value);<br>END;<br><br>I would like to
have a procedure that works independently from the<br>table name provided by
the user when calling the today procedure, but
<br>when I try to compile the code above I receive the following error:<br><br
>ERROR at line 8: PL/SQL: ORA-00942 (See ORA-00942.ora-code.com): table or view does not exist<br><br>So it
doesn't recognize the tbl_name argument. How can I do to solve<br>
this problem?<br><br>Thanks in advance<br><br>Simone Saravalli<br>--<br><a href
="http://www.freelists.org/webpage/oracle-l">http://www.freelists.org/webpage
/oracle-l</a><br><br><br></blockquote></div><br>
|
|
