13 Replies Latest reply on Mar 28, 2012 3:55 PM by rp0428

    PLS-00103: Encountered the symbol "Create"

    923580
      Hi,

      I have created a package in Oracle sql developer to communicate with LDAP; which has two procedures and one function.

      Two Procedures - One for establishing a connection and another for closing the connection

      Function - To search a particular string in LDAP and return 1 if the string is exists else it will return 0.

      Apart from this, I have a query in which I used to call the function. ie the output of the query will retrieve a set of records and one of the data (string) in a column will go as an input to the function. The function will search for the string in the LDAP and return the number. If the return value is 1 we will display it as 'Active' in our query. The query will run multiple times based on the user selection.

      The reason for the creation of package is to keep the LDAP connection alive. If I use it as a single function each time (for each string) it has to establish a connection with LDAP, search the string and closing the connection and it badly affects the performance.

      Now my issue is, I'm getting the error: Error(6,14): PLS-00103: Encountered the symbol "create" (seems to be issue occurs in sql developer)

      I have tried lot and searched lot but nothing resolved the issue. Also tried by including slash (/) after the end; as suggessted in many forums, but no use. I'm getting the same error.

      I have posted the package below. It would be great if some one help me to resolve it. The aim of the package is to keep the LDAP connection alive.

      Thanks in advance.

      CREATE OR REPLACE
      PACKAGE GET_LDAP AS

      PROCEDURE GET_LDAP_CON (l_retval PLS_INTEGER, l_session DBMS_LDAP.session);
      FUNCTION GET_LDAP_SEARCH (loginname VARCHAR2, l_attrs DBMS_LDAP.string_collection, l_count NUMBER) RETURN NUMBER;
      PROCEDURE GET_LDAP_DISCON (l_retval PLS_INTEGER);
      END GET_LDAP;

      CREATE OR REPLACE
      PACKAGE BODY GET_LDAP AS

      ldapLogon:='FALSE';
      l_ldap_host VARCHAR2(256) := '';
      l_ldap_port VARCHAR2(256) := '';
      l_ldap_user VARCHAR2(256) := '';
      l_ldap_passwd VARCHAR2(256) := '';
      l_ldap_base VARCHAR2(256) := '';
      l_retval PLS_INTEGER;
      l_session DBMS_LDAP.session;
      l_attrs DBMS_LDAP.string_collection;
      l_message DBMS_LDAP.message;
      l_filter varchar2(35):='<xxxx>='|| loginname;
      l_count NUMBER:=0;

      -- code for procedure GET_LDAP_CON

      PROCEDURE GET_LDAP_CON (l_session, l_retval) is
      BEGIN

      l_session := DBMS_LDAP.init(l_ldap_host, l_ldap_port);
      l_retval := DBMS_LDAP.simple_bind_s(l_session, l_ldap_user, l_ldap_passwd);
      END GET_LDAP_CON;

      -- code for function GET_LDAP_SEARCH

      FUNCTION GET_LDAP_SEARCH (loginname, l_attrs, l_count)
      RETURN NUMBER
      IS
      BEGIN
      DBMS_LDAP.USE_EXCEPTION := TRUE;
      l_attrs(1) := 'XXXX';
      l_retval := DBMS_LDAP.search_s(l_session, l_ldap_base, scope => DBMS_LDAP.SCOPE_SUBTREE, filter => l_filter, attrs => l_attrs, attronly => 0, res => l_message);
      l_count:=DBMS_LDAP.count_entries(l_session, l_message);
      return l_count;

      EXCEPTION
      WHEN OTHERS THEN
      dbms_output.put_line('Error :'||SQLERRM);
      return 0;

      END GET_LDAP_SEARCH;

      -- code for procedure GET_LDAP_DISCON

      PROCEDURE GET_LDAP_DISCON (l_retval) is
      BEGIN
      l_retval := DBMS_LDAP.unbind_s(l_session);
      END GET_LDAP_DISCON;

      END GET_LDAP;
        • 1. Re: PLS-00103: Encountered the symbol "Create"
          rp0428
          >
          I'm getting the error: Error(6,14): PLS-00103: Encountered the symbol "create"
          >
          Do you get error compiling the spec or the body?

          Compile the spec by itself and see if the error occurs.

          If successful compile the body by itself.
          Post results.
          • 2. Re: PLS-00103: Encountered the symbol "Create"
            923580
            Thanks for looking into this.

            Compiling the package spec and body separately or both at the same time always produce the same error.

            Error(9,1): PLS-00103: Encountered the symbol "CREATE"

            Also when I run the below query (to get detailed error), I got three more errors additionaly.

            SELECT * FROM USER_ERRORS WHERE NAME='GET_LDAP'

            PLS-00103: Encountered the symbol "CREATE"
            "PLS-00103: Encountered the symbol "IF" when expecting one of the following: := . ( @ % ;The symbol "IF" was ignored."
            "PLS-00103: Encountered the symbol "GET_LDAP_SEARCH" when expecting one of the following: if"
            "PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map"

            Please help me to solve this.
            • 3. Re: PLS-00103: Encountered the symbol "Create"
              Jim Smith
              Ignore this, didn't read the post fully.

              Edited by: Jim Smith on Mar 21, 2012 5:19 PM
              • 4. Re: PLS-00103: Encountered the symbol "Create"
                Jim Smith
                You say you included a slash after the end. Did you do that for both package and package body?
                • 5. Re: PLS-00103: Encountered the symbol "Create"
                  rp0428
                  Are you saying you can't even compile ONLY the package spec by itself?

                  What is the 4 digit Oracle version?

                  And where would this have come from?
                  >
                  "PLS-00103: Encountered the symbol "IF" when expecting one of the following: := . ( @ % ;The symbol "IF" was ignored."
                  >
                  You don't have the word IF anywhere in your code.

                  When troubleshooting SQL eliminate as many variables as possible. In your case that includes sql developer.
                  Open a sql*plus window, drop the package and compile only the package spec and post results
                  >
                  CREATE OR REPLACE
                  PACKAGE GET_LDAP AS

                  PROCEDURE GET_LDAP_CON (l_retval PLS_INTEGER, l_session DBMS_LDAP.session);
                  FUNCTION GET_LDAP_SEARCH (loginname VARCHAR2, l_attrs DBMS_LDAP.string_collection, l_count NUMBER) RETURN NUMBER;
                  PROCEDURE GET_LDAP_DISCON (l_retval PLS_INTEGER);
                  END GET_LDAP;
                  >
                  If that works use sql*plus to compile the body.
                  • 6. Re: PLS-00103: Encountered the symbol "Create"
                    Vadim Tropashko-Oracle
                    I assume you are running script from SQL Worksheet. Then, it executes SQL*Plus commands which are demarcated by slashes. Therefore, insert slashes both after create pkg and create body statements. (In fact it is the slash after create package that is critical; the create body trailing slash is not).

                    After doing this you would still have compilation errors -- missing declaration types, but at least both package and body objects would be created in the database, and you can see them in the navigation pane, and open them individually with PL/SQL editor.
                    • 7. Re: PLS-00103: Encountered the symbol "Create"
                      923580
                      The Oracle SQL Developer version I am using is 1.5.5

                      Sorry I don't have permissions to install sql * plus, so I am trying in sql developer.

                      When I include (/) at the end of package specification and body, I got the similar error Error(46,1): PLS-00103: Encountered the symbol "/" The symbol "/" was ignored.

                      When I compile the Package specification alone by removing the slash, there is no error. It compiled successfully.

                      But when I compile the Package body alone without slash (with slash the same error), I got too many errors.

                      As I said, my aim is to keep the LDAP connection alive throughout the session, I have made few changes in the Package body and reduced the error count to three.

                      Now the errors are (similar kind of errors):

                      PLS-00363: expression 'ISLOGGEDON' cannot be used as an assignment target
                      PLS-00363: expression 'L_SESSION' cannot be used as an assignment target
                      PLS-00363: expression 'L_RETVAL' cannot be used as an assignment target

                      I have removed one procedure to disconnect from LDAP, now the package has one procedure (to establish connection to LDAP) and one function (to search the LDAP and return the number). Also I have added the if condition later. I am not sure whether I have done everything perfectly.

                      Please let me know if I did any thing wrong in the Package body.

                      I have posted the pacakge specification and body below. Please validate or check this and help me to resolve the errors. Let me know if you need more information.

                      Package Specification: (Compiled Successfully)
                      ================================
                      create or replace
                      PACKAGE "GET_LDAP" AS

                      PROCEDURE GET_LDAP_CON (l_session DBMS_LDAP.session, l_retval PLS_INTEGER, isLoggedOn NUMBER);
                      FUNCTION GET_LDAP_SEARCH (loginname VARCHAR2) RETURN NUMBER;
                      end GET_LDAP;

                      Package Body: (three errors)
                      =======================

                      create or replace
                      PACKAGE BODY "GET_LDAP" AS

                      isLoggedOn number := 0;
                      l_ldap_host VARCHAR2(256) := '';
                      l_ldap_port VARCHAR2(256) := '';
                      l_ldap_user VARCHAR2(256) := '';
                      l_ldap_passwd VARCHAR2(256) := '';
                      l_ldap_base VARCHAR2(256) := '';
                      l_retval PLS_INTEGER;
                      l_session DBMS_LDAP.session;
                      l_attrs DBMS_LDAP.string_collection;
                      l_message DBMS_LDAP.message;
                      l_count NUMBER:=0;

                      -- Procedure to establish connection with LDAP

                      PROCEDURE GET_LDAP_CON (l_session DBMS_LDAP.session, l_retval PLS_INTEGER, isLoggedOn NUMBER) is
                      BEGIN

                      DBMS_OUTPUT.put_line( '..running logon()' );
                      isLoggedOn := 1;

                      l_session := DBMS_LDAP.init(l_ldap_host, l_ldap_port);
                      l_retval := DBMS_LDAP.simple_bind_s(l_session, l_ldap_user, l_ldap_passwd);
                      END GET_LDAP_CON;

                      -- Function to search the LDAP

                      FUNCTION GET_LDAP_SEARCH (loginname VARCHAR2)
                      RETURN NUMBER
                      IS
                      BEGIN
                      IF isLoggedOn = 0 then
                      GET_LDAP_CON(l_session, l_retval, isLoggedOn);
                      end if;

                      l_attrs(1) := 'xxxx';
                      l_retval := DBMS_LDAP.search_s(l_session, l_ldap_base, scope => DBMS_LDAP.SCOPE_SUBTREE, filter => 'xxxx='|| loginname, attrs => l_attrs, attronly => 0, res => l_message);
                      l_count:=DBMS_LDAP.count_entries(l_session, l_message);
                      return l_count;

                      END GET_LDAP_SEARCH;

                      END GET_LDAP;
                      • 8. Re: PLS-00103: Encountered the symbol "Create"
                        Jim Smith
                        I still don't understand why you are getting problems using slashes, but 1.5.5 is very old. The current release is 3.1.


                        You need to brush up on your PL/SQL. You are trying to assign values to IN parameters. If you want to pass values back through procedure parameters (as opposed to a function return value) they need to be specified as OUT or IN OUT.
                        • 9. Re: PLS-00103: Encountered the symbol "Create"
                          rp0428
                          >
                          PLS-00363: expression 'ISLOGGEDON' cannot be used as an assignment target
                          PLS-00363: expression 'L_SESSION' cannot be used as an assignment target
                          PLS-00363: expression 'L_RETVAL' cannot be used as an assignment target
                          >
                          You have defined each of these as IN parameters so you cannot assign values to them. They need to be 'IN OUT' or 'OUT' to assign values.
                          PROCEDURE GET_LDAP_CON (l_session DBMS_LDAP.session, l_retval PLS_INTEGER, isLoggedOn NUMBER);
                          . . .
                          isLoggedOn := 1;
                          . . .
                          l_session := DBMS_LDAP.init(l_ldap_host, l_ldap_port);
                          l_retval := DBMS_LDAP.simple_bind_s(l_session, l_ldap_user, l_ldap_passwd);
                          Are you passing in the parameter values? Or is the procedure supposed to create and assign them?
                          • 10. Re: PLS-00103: Encountered the symbol "Create"
                            923580
                            Hi,

                            I am very new to pl sql, LDAP and package concepts. But all of your guidance helped me lot to resolve the errors.

                            Now the Package body is compiled successfully. Thanks everyone.

                            But the major issue is still exists.

                            As I said earlier, the aim of this package is to keep the LDAP connection alive. But I am not sure whether the connection is alive; since the Performance is not yet improved.

                            As I said, we have a query which retrieves a set of records and one of the column's value will go as an input to the LDAP search function to search the active directory and based on the return value we will display the reult. ie if return value is 1 'Active' (data exists in LDAP) else 'Inactive' (data not exists in LDAP).

                            This is not a one time process. ie the control goes from Query to Package ldap function and package ldap function to query continuously until all the records are fetched.

                            Its affects the performance. So I planned to keep the connection alive. But it doesn't seems to be alive throughout the session.

                            As I am new to this concept, it would be great, if you check the Package and let me know the necessary changes need to done in order to keep the connection alive.

                            Here the key is the "isLoggedOn" variable. The idea is during the first connection it will be 0 and call the connection Procedure (GET_LDAP_CON) and its value should be changed to 1. From next time onwards it should not go the connection Procedure rather directly go to search function (GET_LDAP_SEARCH).

                            Is it possible to prove this? Please help.
                            • 11. Re: PLS-00103: Encountered the symbol "Create"
                              803006
                              My Procedure content is:

                              procedure taulut_ja_sarakkeet1
                              as
                              testi integer;
                              begin
                              select
                              at.table_name,
                              atc.column_name,
                              atc.data_type
                              from ALL_TABLES at
                              left join ALL_TAB_COLUMNS atc
                              on at.table_name = atc.table_name
                              where at.tablespace_name = 'CRM_DATA'
                              order by at.table_name, atc.column_id
                              end;

                              I got following error:
                              LINE/COL ERROR
                              -------- -----------------------------------------------------------------
                              5/3 PL/SQL: SQL Statement ignored
                              13/41 PL/SQL: ORA-00933: SQL command not properly ended
                              14/4 PLS-00103: Encountered the symbol "end-of-file" when expecting
                              one of the following:
                              begin case declare end exception 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

                              Somebody resolve it
                              • 12. Re: PLS-00103: Encountered the symbol "Create"
                                Jim Smith
                                This is the forum for SQL Developer, not for general SQL/PLSQL questions. You should ask this question in the SQL and PL/SQL forum.

                                What steps have you taken to resolve this yourself? All the information you require is there.
                                • 13. Re: PLS-00103: Encountered the symbol "Create"
                                  rp0428
                                  Please don't hijack the thread by posting your own questions in it.
                                  Create a new thread in the appropriate forum for your question.