13 Replies Latest reply: Jan 7, 2008 12:01 AM by 610337 RSS

    How to write a procedure?

    610337
      Hi,

      I do not know how to write a procedure that will eventually be stored in the
      database.
      I have a lot of question on how to write a procedure.

      First question:
      Do i need to create an extra table to store all the procedure that i wrote?

      Second question:
      How do i write it in a form of a procedure?

      SQL> Conn sys as sysdba

      SQL> create user IAS_SPATIAL identified by IAS_SPATIAL;
      SQL> grant connect,resource to IAS_SPATIAL;

      SQL> conn IAS_SPATIAL/IAS_SPATIAL
      SQL> CREATE TABLE NYP_IMAGES (image_id NUMBER, image_description VARCHAR2(50), image SDO_GEORASTER);
      SQL> EXECUTE sdo_geor_utl.createDMLTrigger('NYP_IMAGES', 'IMAGE');

      SQL> CREATE TABLE NYP_IMAGES_RDT OF SDO_RASTER
      (PRIMARY KEY (rasterID, pyramidLevel, bandBlockNumber,
      rowBlockNumber, columnBlockNumber));

      SQL> conn sys as sysdba

      SQL> call dbms_java.grant_permission('IAS_SPATIAL','SYS:java.io.FilePermission','/home/oracle/upload/L3FloorPlan.tif', 'read' );
      SQL> call dbms_java.grant_permission('MDSYS','SYS:java.io.FilePermission','/home/oracle/upload/L3FloorPlan.tif'',
      'read' );

      SQL> conn IAS_SPATIAL/IAS_SPATIAL

      SQL> DECLARE
      geor MDSYS.SDO_GEORASTER;
      BEGIN

      INSERT INTO NYP_IMAGES values( 1, 'TIFF', sdo_geor.init('NYP_IMAGES_RDT') );

      SELECT IMAGE INTO geor FROM NYP_IMAGES
      WHERE IMAGE_ID = 1 FOR UPDATE;
      sdo_geor.importFrom(geor, NULL, 'TIFF', 'file',
      '/home/oracle/upload/L3FloorPlan.tif'');
      UPDATE NYP_IMAGES SET IMAGE = geor WHERE IMAGE_ID = 1;
      COMMIT;
      END;

      Can anyone please advise me on that?

      Thanks and regards,
      Esther
        • 1. Re: How to write a procedure?
          105967
          A procedure is created like any other database object. See the documentation. But I recommend to use packages, not only procedures.

          In your case there could be something like:
          create or replace procedure insert_file (
            p_id          number,
            p_file_type   varchar2,
            p_file_name   varchar2
            ) as
            geor MDSYS.SDO_GEORASTER;
          begin
            insert into nyp_images values( p_id, p_file_type, sdo_geor.init('NYP_IMAGES_RDT') );
            select image into geor from nyp_images where image_id = p_id for update;
            sdo_geor.importFrom(geor, NULL, p_file_type, 'file', p_file_name);
            update nyp_images set image = geor Where image_id = p_id;
          end insert_file;
          /
          and later on you just call this procedure like:
          begin
            insert_file(1, 'TIFF', '/home/oracle/upload/L3FloorPlan.tif');
            insert_file(2, 'TIFF', '/home/oracle/upload/L2FloorPlan.tif');
            insert_file(3, 'GIF', '/home/oracle/upload/K1FloorPlan.gif');
          end;
          /
          commit;
          • 2. Re: How to write a procedure?
            Alessandro Rossi
            First of all work with a DBA user and connect as SYS just when it's the only option to do what you want to do,

            What do you want to do in that procedure?

            create a user,create a table, call sdo_geor_utl.createDMLTrigger,create an objects table,execute a pl/sql block

            What you have as input and what you want as output are important informations too!!

            Bye Alessandro
            • 3. Re: How to write a procedure?
              610337
              Hi Leo,

              If i want to run this sql script:
              call dbms_java.grant_permission('IAS_SPATIAL','SYS:java.io.FilePermission','/home/oracle/ERD.tif','read');

              *Please note that "/home/oracle/ERD.tif" will be changed constantly.

              Can you help me see if the procedure that i have wrote below, is it correct?

              create or replace procedure grant_permission (
              p_file_name     varchar2)

              begin
              call dbms_java.grant_permission('IAS_SPATIAL', 'SYS:java.io.FilePermission', 'p_file_name','read');
              call dbms_java.grant_permission('MDSYS', 'SYS:java.io.FilePermission', 'p_file_name','read');

              end grant_permission;
              /

              Thanks and regards,
              Esther
              • 4. Re: How to write a procedure?
                Boneist
                You need to remove the single quotes from around p_file_name inside the procedure, otherwise you're telling pl/sql to grant permission on the file called p_file_name. You also need to remove the "call":

                eg.:
                create or replace procedure grant_permission (p_file_name varchar2)
                begin
                  dbms_java.grant_permission('IAS_SPATIAL', 'SYS:java.io.FilePermission', p_file_name,'read');
                  dbms_java.grant_permission('MDSYS', 'SYS:java.io.FilePermission', p_file_name,'read');
                end grant_permission;
                /
                You also need to make sure that the user that owns the procedure is able to execute dbms_java.
                • 5. Re: How to write a procedure?
                  105967
                  take out the word "call", let it run and see what you see ;-)
                  • 6. Re: How to write a procedure?
                    610337
                    When i type this at oracle sqldeveloper,

                    create or replace procedure grant_permission (p_file_name varchar2)
                    begin
                    dbms_java.grant_permission('IAS_SPATIAL', 'SYS:java.io.FilePermission', p_file_name,'read');
                    dbms_java.grant_permission('MDSYS', 'SYS:java.io.FilePermission', p_file_name,'read');
                    end grant_permission;

                    it shows that execution completed with warning; procedure grant_permission compiled.
                    Error(3,1): PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: ; is with authid as cluster order using external deterministic parallel_enable pipelined The symbol "is" was substituted for "BEGIN" to continue.

                    Can anyone please advise me on what is wrong?

                    Thanks and regards,
                    Esther
                    • 7. Re: How to write a procedure?
                      210640
                      create or replace procedure <procedure_name> (parameter_list)
                      IS
                         <variable declaration>
                      begin
                        <executable code(s)>
                      end;
                      /
                      • 8. Re: How to write a procedure?
                        610337
                        Hi,

                        For example i type the below codes:
                        create or replace
                        procedure grant_permission (p_file_name varchar2)
                        begin
                        dbms_java.grant_permission('IAS_SPATIAL', 'SYS:java.io.FilePermission', p_file_name,'read');
                        dbms_java.grant_permission('MDSYS', 'SYS:java.io.FilePermission', p_file_name,'read');
                        end grant_permission;

                        May i know what thing i should change, or what thing i should put under
                        "IS <variable declaration>"

                        Please advise me.

                        Thanks and regards,
                        Esther
                        • 9. Re: How to write a procedure?
                          210640
                          create or replace
                          procedure grant_permission (p_file_name varchar2)
                          begin
                          dbms_java.grant_permission('IAS_SPATIAL',
                          'SYS:java.io.FilePermission', p_file_name,'read');
                          dbms_java.grant_permission('MDSYS',
                          'SYS:java.io.FilePermission', p_file_name,'read');
                          nd grant_permission;
                          I this your create procedure is missing keywork IS. You don't really need to declare variable is you don't need. But whether you declare variable or not, you need to use the keyword IS.
                          • 10. Re: How to write a procedure?
                            610337
                            Hi,
                            i try to type the "IS" in front of the "BEGIN" for one of the procedures that i have type. And it compiled successfully without errors.

                            However i tried this with another procedure that i have wrote, it return me with the same error.
                            This is what i have typed:

                            create or replace procedure insert_file
                            (
                            p_id number,
                            p_file_type varchar2,
                            p_file_name varchar2
                            )as
                            geor MDSYS.SDO_GEORASTER;
                            IS
                            begin
                            insert into nyp_images values( p_id, p_file_type, sdo_geor.init('NYP_IMAGES_RDT') );
                            select image into geor from nyp_images where image_id = p_id for update;
                            sdo_geor.importFrom(geor, NULL, p_file_type, 'file', p_file_name);
                            update nyp_images set image = geor Where image_id = p_id;
                            end insert_file;
                            /

                            And the error message is...
                            Error(3,1): PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: ; is with authid as cluster order using external deterministic parallel_enable pipelined The symbol "is" was substituted for "BEGIN" to continue.

                            Please advise me on it.

                            Thanks and regards,
                            Esther
                            • 11. Re: How to write a procedure?
                              210640
                              create or replace procedure insert_file
                              (
                              p_id number,
                              p_file_type varchar2,
                              p_file_name varchar2
                              )as
                              geor MDSYS.SDO_GEORASTER;
                              IS << remove this line
                              begin
                              insert into nyp_images values( p_id, p_file_type,
                              sdo_geor.init('NYP_IMAGES_RDT') );
                              select image into geor from nyp_images where image_id
                              = p_id for update;
                              sdo_geor.importFrom(geor, NULL, p_file_type, 'file',
                              p_file_name);
                              update nyp_images set image = geor Where image_id =
                              p_id;
                              end insert_file;
                              /
                              Again, in my earlier email, I just don't want you to get confuse by giving so many information, so I just mentioned IS. Actually, IS and AS does the same thing. You could use either one of those. In your current procedure, you already have AS and you are also adding IS before BEGIN. If you delete IS in your script, hopefully, your script should run properly. I haven't tested yet, since I don't have oracle up and running in my computer.
                              • 12. Re: How to write a procedure?
                                Billy~Verreynne
                                It seems to me that you know very little about the PL/SQL language itself. Programming in an unfamiliar language is difficult.. especially if you're not using the reference guide for that language that tels you the syntax and structures of the language.

                                I suggest that you do just that. Read the [url http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm]Oracle® Database PL/SQL User's Guide and Reference guide.

                                It is bad form to ask people in a public forum to fix basic syntax errors in your code. You should be capable to fix such problems yourself.. else you will not be able to progress as a developer in that language at all.
                                • 13. Re: How to write a procedure?
                                  610337
                                  Hi Shy,

                                  Thanks for your advice, i managed to solve the problem. It managed to compile without errors.

                                  Thanks a lot.

                                  Regards,
                                  Esther