10 Replies Latest reply: May 7, 2010 7:21 PM by SomeoneElse RSS

    Create Procedure in Oracle

    764712
      Hi,
      I'm new to Oracle and need to create Store Procedure to define View, that what user want - procedure, as they need "to pass a bunch of parameters", like today's date etc. I'm not sure does it make sence to do this in sp, rather define View as desired and probably there is still possiblity to pass all needed params in there without creating procedure. And this is just View, not materialized.

      I dont' see any option to implement parameters in "New View" menu on my SQL_Developer. So probably sp will be best solution ?

      My view :

      create view v1 as select * from tt1 where date=@today union
      select * from tt2 where date=@today union ;

      Maybe anybody can share any sample sp how to create procedure, I can't compile it without error:
      CREATE OR REPLACE PROCEDURE usp_v1 AS
      BEGIN
      create view v1 as select * from tt1 where date=@today union
      select * from tt2 where date=@today union ;
      END usp_v1;
      Error(4,3): PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe

      Also what is the best Oracle book for beginner application developer on market? I could not find too much on www.


      Tx all

      D
        • 1. Re: Create Procedure in Oracle
          sb92075
          EXECUTE IMMEDIATE CREATE VIEW .......
          • 2. Re: Create Procedure in Oracle
            764712
            sb92075,

            Can you provide bit more details:
            • 3. Re: Create Procedure in Oracle
              sb92075
              Can you provide bit more details:
              http://www.lmgtfy.com/?q=oracle+execute+immediate
              • 4. Re: Create Procedure in Oracle
                SomeoneElse
                Why not just create the view once and be done with it?

                What's the purpose of enclosing it in a stored procedure?

                You're getting an error because you can't directly execute DDL from within a stored procedure. You must use EXECUTE IMMEDIATE, but again...why?
                • 5. Re: Create Procedure in Oracle
                  ajallen
                  As others have been suggesting...

                  A lot of 'stuff' goes into creating a view. Columns, tables (or other views), joins (inner or outer) and join columns, restrictions (filtering), concatenations, calculations, display formatting (may be). Are you going to code all of this syntax checking and construction into your procedure.

                  We do not do this kind of thing.

                  Perhaps you could share your user's thinking here - what is his business problem that he is trying to solve.

                  Hint: Let your customer tell you what (his business problem is), not how (to solve it) - that's your job.
                  • 6. Re: Create Procedure in Oracle
                    764712
                    I need sp for creating view as there are some date params involved specific for each day, as date itself, and table names.

                    Tx to all

                    Edited by: trento on May 3, 2010 1:32 PM
                    • 7. Re: Create Procedure in Oracle
                      Tubby
                      trento wrote:
                      I need sp for creating view as there are some date params involved specific for each day, as date itself, and table names.

                      Tx to all

                      Edited by: trento on May 3, 2010 1:32 PM
                      That's typically what you'd use a reference cursor for.

                      Instead of creating a view whenever the user calls your procedure (that's really not a good technical solution, i would HIGHLY advise you to not do this) you would return a cursor to the required SQL.
                      CREATE OR REPLACE FUNCTION usp_v1 
                      (
                         p_date_parameter  DATE
                      )
                      RETURN sys_refcursor 
                      AS
                         l_out_cursor   sys_refcursor; 
                      BEGIN
                      
                         open l_out_cursor for
                         select * 
                         from tt1 
                         where date = p_date_parameter
                            union
                         select * 
                         from tt2 
                         where date = p_date_parameter;
                         
                         return l_out_cursor;
                         
                      end;
                      /
                      No need for a view, no need for much of anything. When the user wants the data, they call this function and pass in any date they fancy and get the result.
                      • 8. Re: Create Procedure in Oracle
                        764712
                        Tx, Tubby !

                        We actually want to create View as there are several apps that going to use this View, and those apps are not Oracle, some on Perl, Microfocus, ABInition. So probably it would be hard to connect Oracle function to them.


                        Best
                        t
                        • 9. Re: Create Procedure in Oracle
                          764712
                          And I see that my compiler doesn't like procedures with Exec Immediate with single quoutes inside the code, I played with " and () but it still doen's work.

                          create or replace
                          PROCEDURE usp_2 AS

                          BEGIN
                          execute immediate '
                          CREATE OR REPLACE VIEW VV1 AS
                          SELECT COL1,
                          '0' AS ACTION-IND, -- <========== ERROR
                          COL2
                          FROM TT1
                          WHERE COL1 LIKE 'S%' -- <========== ERROR
                          ';
                          • 10. Re: Create Procedure in Oracle
                            SomeoneElse
                            <shrugs>
                            SQL> desc vv1
                            ERROR:
                            ORA-04043: object vv1 does not exist
                            
                            
                            SQL> create table tt1
                              2  (col1 number
                              3  ,col2 number);
                            
                            Table created.
                            
                            SQL> create or replace
                              2  PROCEDURE usp_2 AS
                              3  BEGIN
                              4  execute immediate
                              5    'CREATE OR REPLACE VIEW VV1 AS
                              6     SELECT COL1,
                              7     ''0'' AS ACTION_IND,
                              8     COL2
                              9     FROM TT1
                             10     WHERE COL1 LIKE ''S%''
                             11    ';
                             12  end;
                             13  /
                            
                            Procedure created.
                            
                            SQL> exec usp_2;
                            
                            PL/SQL procedure successfully completed.
                            
                            SQL> desc vv1
                             Name                    Null?    Type
                             ----------------------- -------- ----------------
                             COL1                             NUMBER
                             ACTION_IND                       CHAR(1)
                             COL2                             NUMBER