CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqloracle-sqldeveloper
user8590010
user8590010

Reputation:

How to listed all the tables of a User in a database? n

Hi so i have the following plan : i want to Write a script, which calls another script. A parameter V_USERNAME should be passed from the 1st script to the 2nd script.( Using the keyword DEFINE)

My code look like : 

@@C:\Users\pe.k\Documents\script2.sql &p_v_username

set serveroutput on

define p_v_username =  "user";

In the 2nd script all tables of the user should be output. (Using the key word EXECUTE IMMEDIATE and a cursor).

The output control is to be done via a parameter in the script or when calling the script.

Example call: SQL> @start_script1 MML

declare 
    &p_v_username  varchar2(100); 
    v_result    varchar2(100);
    cursor cp_username (&p_v_username varchar2) 
    is
        select owner, table_name           
        from all_tables
        where owner = &p_v_username  
        order by owner, table_name;       
begin 
     dbms_output.put_line('Alle Tabellen der User'); --l_username);
     open cp_username(&p_v_username);
     --loop
         --fetch cp_username into v_result;
        -- dbms_output.put_line(v_result);
    --end loop;
     close cp_username;
end;
/

And i have the errors and i am lost. I dont know how to do it

Upvotes: 0

Views: 43

Answers (1)

Popeye
Popeye

Reputation: 35930

Your second procedure should be something like this:

BEGIN
    DBMS_OUTPUT.PUT_LINE('Alle Tabellen der User'); --l_username);
    FOR TABS IN (
        SELECT OWNER, TABLE_NAME
        FROM ALL_TABLES
        WHERE OWNER = '&P_V_USERNAME'
        ORDER BY OWNER, TABLE_NAME
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(TABS.TABLE_NAME);
    END LOOP;
END;
/

In order to print the output generated by the DBMS_OUTPUT package from PL/SQL, make sure to set serveroutput on

Upvotes: 1

Related Questions