13 Replies Latest reply: Jun 5, 2007 6:56 AM by 579590 RSS

    Programmatically adding a schema to a Workspace

    579590
      Hi People,

      How can I Programmatically add another schemas to a Workspace??

      Thanks
        • 1. Re: Programmatically adding a schema to a Workspace
          60437
          Marco,

          No, there is no provision for this. Why do you need to do it?

          Scott
          • 2. Re: Programmatically adding a schema to a Workspace
            579590
            Hi Scott,

            I work for an private university with about 30,000 students.
            And we are trying to use APEX as a tool for Professors and students of TI area.

            To start, we have about (only) a hundred studients, each one with a schema in a 10G database.

            So we would like that each student could access APEX, restrict in his shema, in a common workspace.

            Do you have a better idea to implemet??

            Thanks
            • 3. Re: Programmatically adding a schema to a Workspace
              joelkallman-Oracle
              Marco,

              Do all these users need to be in the same workspace? Can they simply have their own workspace (with their associated schema)?

              Joel
              • 4. Re: Programmatically adding a schema to a Workspace
                579590
                Joel,

                Do all these users need to be in the same workspace? Can they simply have their own workspace (with their associated schema)?

                No. They don't.
                yes, they can. So, how can I create a workspace programmaically for each one user??

                Thanks
                Marco
                • 5. Re: Programmatically adding a schema to a Workspace
                  579590
                  No answers. I suppose I'm asking something impossible.

                  I presuming it is not possible to create a hundred Workspace programmatically or associate a hundred schema to the same workspace.

                  If somebody has any idea, please tell me.
                  • 7. Re: Programmatically adding a schema to a Workspace
                    Juan Pablo Lapa
                    Hola Marco,
                    Hola te escribo por que me llamo la atencion tu apellido ya que por Argentina no hay muchos Lapa, sos decendiente de Italianos?
                    Bueno te comento que estoy hace un tiempo con Apex, te podria dar una mano con algo y vos a mi capaz que tambien, con respecto a tu pregunta, te voy a averiguar.

                    si queres comunicarte te paso mi correo
                    saludos...
                    juanpablolapa@yahoo.com
                    • 8. Re: Programmatically adding a schema to a Workspace
                      553967
                      <p>Marco,</p>
                      <p>One of the characteristics of APEX is that in the end everything is PL/SQL or SQL. Adding a schema to a workspace (in a browser) results in some PL/SQL and SQL statements executed.</p><p>The question is which statements are executed? The administrative interface itself is an APEX application. Look for the APEX internal applications in the scripts directory of your APEX installation folder.</p><p> The next step is to import the administrative interface and find all the necessary statements (and APEX constraints) and compose your own procedure.</p><p>Just my two cents...</p>

                      <p>With kind regards,</p>

                      <p>Jornica</p>
                      • 9. Re: Programmatically adding a schema to a Workspace
                        Dietmar Aust
                        Marco,

                        following the approach outlined by Jornica, I came up with the following script:
                        prompt *** create a user first and provision the workspace later

                        prompt *** connect as sys
                        conn / as sysdba

                        prompt *** unlock apex owner and connect
                        alter user flows_020200 account unlock;
                        alter user flows_020200 identified by dummyxxx;
                        connect flows_020200/dummyxxx

                        create user student1 identified by student1 default tablespace users temporary tablespace temp;
                        grant resource,connect to student1;
                        grant create procedure, create synonym, create view to student1;

                        begin
                          wwv_flow_api.set_security_group_id(p_security_group_id=>10);
                          wwv_flow_provisioning.auto_provision_company
                                              (p_company_name             => 'STUDENT1',
                                               p_schema_name              => 'STUDENT1',
                                               p_schema_password          => null,    -- specify null and the schema will be reused
                                               p_admin_password           => 'student1',
                                               p_database_size            => null,
                                               --
                                               p_admin_userid             => 'STUDENT1',
                                               p_admin_first_name         => 'Manfred',
                                               p_admin_last_name          => 'Mustermann',
                                               p_admin_title              => '',
                                               p_admin_email              => 'manfred.mustermann@university.edu',
                                               --
                                               p_project_description      => '',
                                               p_security_group_id        => null
                                              );
                         
                        end;
                        /

                        prompt *** automatically create the user in a separate FLOWS_ tablespace

                        begin
                          wwv_flow_api.set_security_group_id(p_security_group_id=>10);
                          wwv_flow_provisioning.auto_provision_company
                                              (p_company_name             => 'STUDENT2',
                                               p_schema_name              => 'STUDENT2',
                                               p_schema_password          => 'student2',
                                               p_admin_password           => 'student2',
                                               p_database_size            => 'MEDIUM',   -- SMALL (2MB), MEDIUM(5MB), LARGE(50MB), VERYLARGE(100MB)
                                               --
                                               p_admin_userid             => 'STUDENT2',
                                               p_admin_first_name         => 'Manfred',
                                               p_admin_last_name          => 'Mustermann',
                                               p_admin_title              => '',
                                               p_admin_email              => 'manfred.mustermann@university.edu',
                                               --
                                               p_project_description      => '',
                                               p_security_group_id        => null
                                              );
                        end;
                        /

                        commit;


                        prompt *** lock apex owner
                        alter user flows_020200 account lock;
                        There are two different cases:
                        - Student1: The database user is created first and the workspace using this schema is created afterwards
                        - Student2: The schema (and tablespace for this user) is created automatically by the provisioning procedure.

                        @Joel/Scott: Do you see any issues with this? Besides not being supported ;).

                        I haven't tried it on Apex 3.0, perhaps somebody can validate it.

                        Thanks,
                        ~Dietmar.
                        • 10. Re: Programmatically adding a schema to a Workspace
                          Dietmar Aust
                          I just tried it on Apex 3.0 using the FLOWS_03000 user. Seems to work fine.

                          Regards,
                          ~Dietmar.
                          • 11. Re: Programmatically adding a schema to a Workspace
                            60437
                            Dietmar,

                            I don't see any problems so if your testing shows no problems then the proof is in the pudding.

                            Scott
                            • 12. Re: Programmatically adding a schema to a Workspace
                              Dietmar Aust
                              Hi Scott,

                              thanks for validating it ... and thanks for the pudding ;).

                              Greetinx,
                              ~Dietmar.
                              • 13. Re: Programmatically adding a schema to a Workspace
                                579590
                                Hi Dietmar,

                                It's working fine.

                                Thanks,

                                Marco Lapa