Skip to Main Content
Forums

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.

formats, right left justify, help!! sql plus

651180Apr 15 2009 — edited Apr 15 2009
I have this code, but the file is one position off,
for example school year should start in position one and end in position one
then
student_last_name format a16 size 1
should start in position 2 and end in position 17 size 16
and
student_first_name format a16
should start in position 18 end in 33 size 16
etc... etc...
also last name and fisrt name should be left justify
I change RPAD to lpad but it looks wear, is that right?
Please help my boss is waiting for the file 
set verify off
set echo off
----set pagesize 9999
set wrap off
set heading off
set trimspool on
set linesize 9999
column    school_year             format a1;
column    student_last_name        format a16 ;
column    student_first_name       format a16 ;
column    student_mi               format a1;
column    street1                  format a50 ;
column    street2                  format a50 ;
column    city                     format a28 ;
column    state                    format a2;
column    zipcode                  format a9;
column    telnumber                format a10;
column    dateofbirth              format a8;
column    ssn                      format a9;
column    dep_status               format a1;
column    natural_parents          format a1;
column    parent_own_business      format a1;
column    student_finaid_status    format a1;
column    federal_verification     format a1;
column    college_code             format a4;
column    foreign_addr_ind         format a1;
column    country                  format a25;
column    foreign_postal_code      format a15;
column    student_email_address    format a50 ;
column    alternate_id             format a12;
column    parent_email             format a256;
column    filler                   format a279;
column    slash                    format a1;
column    cr/lf                    format a2;
spool finaid2.lst
select
--RPAD(NVL(RZFIDOC_SCHOOL_YEAR,' '),1), 
RPAD(NVL('0',' '),1),
rpad(nvl(RZFIDOC_LAST_NAME,' '),16),
rpad(nvl(RZFIDOC_FIRST_NAME,' '),16),
rpad(nvl(RZFIDOC_MI,' '),1),
rpad(nvl(RZFIDOC_STREET_LINE1,' '),50),
rpad(NVL(RZFIDOC_STREET_LINE2,' '),50),
RPAD(nvl(RZFIDOC_CITY,' '),28),
rpad(nvl(RZFIDOC_STATE_CODE,' '),2),
rpad(nvl(RZFIDOC_ZIP,' '),9),
rpad(nvl(RZFIDOC_TELPHONE,' '),10),
rpad(nvl(to_char(RZFIDOC_BIRTH_DATE,'MMDDYYYY'),' '),8),
rpad(nvl(RZFIDOC_SSN,' '),9),
rpad(nvl(RZFIDOC_DEP_STATUS,' '),1),
rpad(nvl(RZFIDOC_NAT_PAR_MRTL_STATUS,' '),1),
rpad(nvl(RZFIDOC_OWN_BUSINESS,' '),1),
rpad(nvl(RZFIDOC_FINAID_STATUS,' '),1),
rpad(nvl(RZFIDOC_FED_VERIF,' '),2),
rpad(nvl(RZFIDOC_COLLEGE_CODE,' '),4),
rpad(nvl(RZFIDOC_FOREIGN_ADDR_IND,' '),1),
rpad(nvl(RZFIDOC_COUNTRY,' '),25),
rpad(nvl(RZFIDOC_FOR_POSTCODE,' '),15),
rpad(nvl(RZFIDOC_EMAIL,' '),50),
rpad(nvl(RZFIDOC_ID,' '),12),
rpad(nvl(RZFIDOC_PAR_EMAIL,' '),256),
rpad(nvl(RZFIDOC_PAR_EMAIL,' '),279),
'\',
CHR(13)||CHR(10)   
from 
faismgr_midd.RZFIDOC

Comments

Brian Jeffries-Oracle
Hi Laurent,

To create the unit test repository, the repository user needs:
CONNECT, RESOURCE, CREATE VIEW

The "SYS" user can be any user that can connect as SYSDBA
this is used to execute the following:

grant select on dba_roles to repository_user;
grant select on dba_role_privs to repository_user;

create role UT_REPO_ADMINISTRATOR;
create role UT_REPO_USER;
grant create public synonym,drop public synonym to UT_REPO_ADMINISTRATOR;
grant select on dba_role_privs to UT_REPO_USER;
grant select on dba_role_privs to UT_REPO_ADMINISTRATOR;
grant select on dba_roles to UT_REPO_ADMINISTRATOR;
grant select on dba_roles to UT_REPO_USER;
grant select on dba_tab_privs to UT_REPO_USER; --3.1
grant select on dba_tab_privs to UT_REPO_ADMINISTATOR; --3.1
grant execute on dbms_lock to UT_REPO_USER; --3.1
grant execute on dbms_lock to UT_REPO_ADMINISTATOR; --3.1
grant ut_repo_user to UT_REPO_ADMINISTRATOR with admin option;
grant UT_REPO_ADMINISTRATOR to repository_user with admin option;

Alternatively, your dba could do the previous block (create role ... grant UT_REPO_ADMIN...) for repository_user and then you could create one without being prompted for the "SYS" connection.

Brian
SQL Developer Team.

Edited by: bjeffrie on Mar 1, 2012 11:55 AM to update with new privilege requirements
1 person found this helpful
Laurent Schneider
Hi Brian,
I will ask my dba to create the user above.

I will update this thread later if needed

Thank you for your prompt answer,
Laurent
Laurent Schneider
but create public synonym is quite a powerful privilege to give... Can you tell me why is this needed and which synonyms will be created?
Brian Jeffries-Oracle
General answer is that create public synonym is only used in the case of setting a repository as shared. Doing so creates public synonyms for the unit test tables, views, and copy procedure.

Going through final code to see (& test) what the minimum requirements are for using individual/private repositories and will get back to you.

Brian Jeffries
SQL Developer Team
Brian Jeffries-Oracle
Answer
Hi Laurent,

Here is the minimum required to get past the internal checks and be able to use unit testing.

Once your DBA does this:

-- RUN ONCE TO SET UP REQUIRED UT ROLES WITH MINIMUM PRIVILEGES:

-- DROP ROLE UT_REPO_USER ;
-- DROP ROLE UT_REPO_ADMINISTRATOR ;
-- DROP USER MIN_PRIV_UT_REPO WITH CASCADE;

CREATE ROLE UT_REPO_USER ;
GRANT SELECT ON DBA_TAB_PRIVS TO UT_REPO_USER ; -- 3.1
GRANT EXECUTE ON DBMS_LOCK TO UT_REPO_USER ; -- 3.1
CREATE ROLE UT_REPO_ADMINISTRATOR ;
GRANT UT_REPO_USER TO UT_REPO_ADMINISTRATOR ; --3.1

-- EDIT/RUN FOR EACH USER/UNIT TEST REPOSITORY DESIRED:
-- Note: Replace MIN_PRIV_UT_REPO, USERS, TEMP as desired for your situation

CREATE USER MIN_PRIV_UT_REPO IDENTIFIED BY MIN_PRIV_UT_REPO
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

GRANT RESOURCE TO MIN_PRIV_UT_REPO ;
GRANT CONNECT TO MIN_PRIV_UT_REPO ;
GRANT CREATE VIEW TO MIN_PRIV_UT_REPO ;
GRANT SELECT ON DBA_ROLES TO MIN_PRIV_UT_REPO ;
GRANT SELECT ON DBA_ROLE_PRIVS TO MIN_PRIV_UT_REPO ;
GRANT UT_REPO_ADMINISTRATOR TO MIN_PRIV_UT_REPO ;


Then in to SQLDeveloper, select Tools->Unit Test->Select Current Repository, create/select a connection for MIN_PRIV_UT_REPO, and press "Yes" when asked if you want to create one.

Note that with the UT roles set up 'empty' like this, the menu options for managing a shared repository will be active and appear to work, but won't really do anything

Brian Jeffries
SQL Developer Team

Edited by: bjeffrie on Dec 22, 2009 3:08 PM

Edited by: bjeffrie on Dec 22, 2009 4:01 PM

Edited by: bjeffrie on Mar 1, 2012 11:43 AM to update with new privilege requirements for 3.1
Marked as Answer by Laurent Schneider · Sep 27 2020
Laurent Schneider
2 weeks later, I go the permission! I suppose you could optimize this process by removing the need of a role for dedicated repository. Or maybe create the role as a default role in Oracle 12...
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 13 2009
Added on Apr 15 2009
6 comments
3,078 views