3 Replies Latest reply: May 28, 2013 2:51 AM by 1011290 RSS

    Binding variable in procedure and function

    1011290
      Hi

      I have started reading ORACLE 11g R2 PL/SQL programming doc, and I am stuck at bind variable. I just copied the example in the books and found error -

      First I have written below procedure and compiled successfully -
      CREATE OR REPLACE PROCEDURE FORMAT_STRING ( string_in IN OUT VARCHAR2) AS
      BEGIN
       string_in := '[' || string_in || ']';
      END FORMAT_STRING;
      Then I have written below function and also compiled successfully -
      create or replace function join_strings  (str1 varchar2, str2 varchar2)
      return varchar2 is
      begin
        return str1||' '||str2||'.';
      end;
      Later I wrote below code in the SQL editor and found ORA-00900 error. Please help me to figure it out.
      variable session_var varchar2(200)
      call join_strings ('I Love','you') into :session_var;
      call format_string(:session_var);
      Mahfuj
        • 1. Re: Binding variable in procedure and function
          jeneesh
          Working fine in sqlplus
          SQL> variable session_var varchar2(200)
          SQL> call join_strings ('I Love','you') into :session_var;
          
          Call completed.
          
          SQL> call format_string(:session_var);
          
          Call completed.
          
          SQL> print :session_var
          
          SESSION_VAR
          -------------------------------------------------------------
          
          [I Love you.]
          • 2. Re: Binding variable in procedure and function
            Karthick_Arp
            Later I wrote below code in the SQL editor and found ORA-00900 error. Please help me to figure it out.
            variable session_var varchar2(200)
            call join_strings ('I Love','you') into :session_var;
            call format_string(:session_var);
            If you want to do it in PL/SQL you can use a anonymous PL/SQL block like this.
            set serveroutput on
            declare
              session_var varchar2(200);
            begin
              session_var := join_strings ('I Love','you');
              format_string(session_var);
              dbms_output.put_line(session_var);
            end;
            /
            If you want to execute it directly in SQL Plus you can do this
            var session_var varchar2
            
            exec :session_var := join_strings ('I Love','you')
            
            exec format_string(:session_var)
            
            print session_var
            • 3. Re: Binding variable in procedure and function
              1011290
              Hi

              Thank you all for your support. I went through all the helps and found everything OK.

              Mahfuj

              Edited by: Mahfuj on May 28, 2013 1:50 PM