9 Replies Latest reply: Dec 8, 2010 8:23 AM by Frank Kulash RSS

    How to write PL SQL function?

    809712
      First of all, whenever i try and put my attempted code for this in a thread it says 'sorry content not allowed'. I have attempted to make the function but i keep getting errors. I think its because my logic is wrong anyway. I am not quite sure how to do it.

      I am a student and this is the first time i have been asked to write a function, i have almost no prior knowledge so please bare with me.

      Could people give me some ideas where to start. It seems i cant even get a simple function to work at the moment! Where is it best to write and test the function? I have been using SQL commands, should i use Script creator instead?

      Any help or feedback would be great.

      Thanks in advance!

      Edited by: Jay on 08-Dec-2010 03:09
        • 1. Re: How to write PL SQL function?
          635471
          Just paste code into the question. maybe you're pasting a screen shot.
          • 2. Re: How to write PL SQL function?
            789895
            Hi,

            Try this link to know more about the PL/SQL

            http://www.java2s.com/Tutorial/Oracle/0540__Function-Procedure-Packages/Catalog0540__Function-Procedure-Packages.htm

            cheers

            VT
            • 3. Re: How to write PL SQL function?
              munky
              Hi Jay

              It probably won't allow you to post your code because there is something in there that the forum software thinks is a naughty word - this crops up quite a bit. Try looking at it very carefully to guess what the offending word / sequence of letters might be or just rename all your parameters variables etc...

              Cheers

              Ben
              • 4. Re: How to write PL SQL function?
                BluShadow
                Jay wrote:
                First of all, whenever i try and put my attempted code for this in a thread it says 'sorry content not allowed'.
                It could be that your code contains some words that aren't permitted on the forums. If it contains certain words that spammers use a lot e.g. "S h o e s" then it may be rejected by the forum on the belief that you're trying to post spam.
                • 5. Re: How to write PL SQL function?
                  Billy~Verreynne
                  Or <i>"d i s c o u n t"</i> and similar words..
                  • 6. Re: How to write PL SQL function?
                    munky
                    But not 'bollocks'!

                    <cringes - sorry couldn't resist>
                    • 7. Re: How to write PL SQL function?
                      BluShadow
                      Munky wrote:
                      But not 'bollocks'!

                      <cringes - sorry couldn't resist>
                      Of course not, we're allowed to talk bollocks. We do most of the time anyway. ;)
                      • 8. Re: How to write PL SQL function?
                        819888
                        and some of it is necessory sometimes ;-)
                        • 9. Re: How to write PL SQL function?
                          Frank Kulash
                          Hi,

                          Here's a simple function, and a test of it:
                          CREATE OR REPLACE FUNCTION hello
                          (     in_txt          IN     VARCHAR2
                          )
                          RETURN     VARCHAR2
                          DETERMINISTIC
                          IS
                               return_txt     VARCHAR2 (100);     -- Value to be returned
                          BEGIN
                               return_txt := 'Hello, '
                                       || in_txt
                                       || '!';
                               RETURN     return_txt;
                          END     hello
                          ;
                          /
                          SHOW ERRORS
                          
                          SELECT     dname
                          ,     hello (dname)     AS hello_results
                          FROM     scott.dept
                          ;
                          Output:
                          Function created.
                          
                          No errors.
                          
                          DNAME          HELLO_RESULTS
                          -------------- ------------------------------
                          ACCOUNTING     Hello, ACCOUNTING!
                          RESEARCH       Hello, RESEARCH!
                          SALES          Hello, SALES!
                          OPERATIONS     Hello, OPERATIONS!
                          I don't know why you can't post your function. Here's how you can find out:

                          First, change the subject line of your first message to something like "IN PROGRESS - Please Do Not Reply Yet".
                          Copy the hello function I posted into your text editor
                          Reply to this message, and post your copy of the function.
                          Did you get the error again? If so, it looks like there's some security feature on your system that's keeping you from posting code.
                          If you can post my function exactly as it is, then change it a little. Update your latest message, a little bit at a time, to make the function more and more like your function. For example, just change the name of the function to your actual function name. Change the message. If it posts corretly, make another small change, until you get the "content not allowed" message. If you can't figure out what's causing it, then change the subject line back to something meaningful, change the last message back to the last version that did post correctly, and describe what you changed that caused the error.

                          You can use SQL*Plus to create and test the function. Write a script (that is, a text file), similar to the one I posted above. If you name the file hello.sql, then you can run the script from the "SQL> " prompt by saying
                          @c:\fubar\sql\hello
                          using the full path name on your system where I used "c:\fubar\sql" above.

                          Edited by: Frank Kulash on Dec 8, 2010 9:18 AM