Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

executing procedure in toad

858631Aug 20 2012 — edited Aug 20 2012
Hi Guru's

I am new to plsql please guide me

I am using toad 9.7.2

I have created a sample procedure - Goal when i pass the table name the procedure should give the count
of that table .
below is the procedure code
CREATE OR REPLACE PROCEDURE test_count(p_table_name IN VARCHAR2,
                                       p_cnt        OUT NUMBER,
                                       p_err_msg    OUT VARCHAR2) IS

    v_sql VARCHAR2(1000);
BEGIN
    v_sql := 'select count(1) from ' || p_table_name;
    EXECUTE IMMEDIATE v_sql
        INTO p_cnt;
        
EXCEPTION
    WHEN OTHERS THEN
        p_err_msg := 'Exception : ' || SQLERRM;
END;
My doubts
--------------
1)when i execute the procudure F5 is say procedure created .
2) when i try to see the output in toad I am stuck
i try to execute my procedure like this method to see theoutput even i gave dbms_output.put_line in my code

exec test_count; (F5) to execute
exec test_count ( 'employees'); -- trying to pass the table name employees which has 20 record but gets error

ERROR

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TEST_COUNT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I tried this too but same error

Begin
test_count;
END;


how to execute the procedure in TOAD ? and how to see the source code in toad? Please suggest

I tried this but can see only no rows return.... but the procedure is created
SELECT   text  FROM user_source where name = 'test_count'
or 
SELECT text FROM all_source WHERE name= 'test_count'
Thanks
Suresh
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 17 2012
Added on Aug 20 2012
6 comments
6,947 views