Skip to Main Content

Oracle Database Discussions

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!

grants not getting imported

SmohibMay 18 2014 — edited May 19 2014

Hi all,

I have export (exp) dump of a schema (9.2.0.1.0)

I am importing (imp) the dump into newly created database (11.1.0.6.0)

It successfully imports saying "import terminated successfully with warnings" (views & triggers with compilation errors...)

I have all the objects tables,functions,procedures etc but I dont have "grants"...

I verified from 9i database that the count of objects is same, roles is also same (select distinct role,owner from ROLE_TAB_PRIVS where owner='user1')

the grants part....(select granted_role from DBA_ROLE_PRIVS where grantee='user1')

the import command i use is

imp 'system/oracle as sysdba' file=dump.dmp log=test.log grants=y compile=y indexes=y fromuser=user1 touser=user1 ignore=y

I am connecting as sysdba & checking the count

export of the whole schema is taken without any warnings.

Please guide me if I am wrong at any step..

Thanks,

Mohib

Comments

David Berger
Answer

Hello d670...

I would try this code:

DECLARE

  p_studentid   NUMBER;

  v_result      VARCHAR2(20); 

BEGIN 

  p_studentid := 1;

  FOR rec_result IN (SELECT students.firstname

                          , test_history.score

                          , test_id.test_name

                          , test_id.passing_grade 

                       FROM students 

                       JOIN test_history 

                         ON students.STUDENT_ID = test_history.student_id 

                       JOIN test_id 

                         ON test_id.test_id = test_history.test_id 

                      WHERE students.student_id = p_studentid 

                       AND test_history.SCORE < test_id.passing_grade   -->  This row has to be deleted!

                    )

  LOOP

     IF rec_result.score < rec_result.passing_grade

      THEN v_result := 'Fail';

      ELSE v_result := 'Pass';

     END IF;

     DBMS_OUTPUT.PUT_LINE (rec_result.firstname || ' ' || TO_CHAR(rec_result.score) || ' ' || rec_result.test_name || ' ' || TO_CHAR(rec_result.passing_grade) || ' ' || 'Result = ' || v_result);   

  END LOOP; 

END; 

/

I hope it helps.

Regards, David

Marked as Answer by 2652078 · Sep 27 2020
Moazzam

However, if the student has passed it just returns 'Result = Pass'  and fails to return their name, tests, grade and passing grade.


If you find yourself wanting a function that returns multiple values, you would generally want to either

- create a procedure that has multiple OUT parameters

- create a function that returns an object type that encapsulates the values you want to return


can functions return multiple values?


CREATE OR REPLACE TYPE user_data_type AS OBJECT(val1 NUMBER(15), val2 NUMBER(15));
/

CREATE OR REPLACE FUNCTION GET_EMPLOYEE_AND_USER(in_login IN fnd_user.user_name%TYPE)
 
RETURN user_data_type AS

  out_var user_data_type
;

 
CURSOR buffer_cur IS
  
SELECT f.user_id, f.employee_id FROM Fnd_User f WHERE f.user_name = in_login;

BEGIN
 
OPEN buffer_cur;
 
FETCH buffer_cur INTO out_var.val1, out_var.val2;

 
CLOSE buffer_cur;
 
RETURN out_var;
END GET_EMPLOYEE_AND_USER;


plsql - Returning multiple values from PL/SQL function - Stack Overflow

2652078

Hi Folks

Thank you very much for your help. I should have mentioned that i needed to do this in a parameterised cursor. Despite your answers working perfectly, i realised i could just move the DBMS_OUTPUT.PUT_LINE inside the loop and i ended up solving my own problem.

Your code looks much better though!!!

Thanks again

Ben

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 16 2014
Added on May 18 2014
5 comments
316 views