1 2 3 Previous Next 31 Replies Latest reply on Jan 27, 2016 7:19 PM by user2598077

    Can't Edit Most User Source in SQL Developer

    user2598077

      Hello,

      I have created an Oracle 12.1.0.2 container database called DEVELOP and loaded from 11GR2 a hundred or so packages and 600 tables with hundreds of APEX 5.0 pages.

      I did this using IMPDP and all the applications run fine in APEX 5.0.2, I just tested all applications.

       

      Each version of SQL Developer (4.1.2, 4.1.3 and the version that comes with 12C for Download) does not allow me to edit any package, procedure, function, or type.

      All I see is 'CREATE OR REPLACE'.

      I can see all tables and all data.

       

      I saw some issues that appear related to this that suggested using 12.1.0.2 if this occurred which I am using.

       

      I create a window using the SYSDBA account for the container and then:

      select * from DBA_OBJECTS where owner = 'LSDB' and object_type ='PACKAGE';

      I see all my packages.

       

      I create a window in the account for these objects, called LSDB then:

      When I run select * from USER_OBJECTS where object_type ='PACKAGE'; I see all my packages, for example.

      When I run select DISTINCT NAME from USER_SOURCE; I see no source code at all, even though I can right click on each package in SQL Developer and download the entire package into a window and edit the entire package.

      The source code is somewhere, just not visible to the viewer tree process it appears.

       

      I don't see much discussion on this issue for 12C 12.1.0.2.

       

      Because this is a test of 12C, I granted all roles and privileges to the LSDB account.

       

      I am new to 12C and SQL Developer 4.1.x. and I am not a wizard at Oracle or SQL Developer internals, so this behavior seems very odd to me.

      Any help would be greatly appreciated.

      Thanks

      Bernie

        • 1. Re: Can't Edit Most User Source in SQL Developer
          thatJeffSmith-Oracle

          are you doing the PL/SQL in the container database, or in a pluggable database?

           

          are you logging in as SYSDBA? (Don't do that) - not unless you want to change up some SYS PL/SQL, which is also not recommended

          • 2. Re: Can't Edit Most User Source in SQL Developer
            user2598077

            Jeff,

            I created a connection to the account (LSDB) inside the container database DEVELOP and started the container database and then opened a window in SQL Developer using the LSDB account.

            I can see my tables in this account and all the objects but can't edit anything.  I can download the source from the object in the account into a worksheet.

            Thanks

            Bernie

            • 3. Re: Can't Edit Most User Source in SQL Developer
              user2598077

              Jeff,

              Sorry,

              I said container database.  The container starts up automatically, I started the pluggable database DEVELOP and then opened SQL Developer,

              BPW

              • 4. Re: Can't Edit Most User Source in SQL Developer
                user2598077

                Jeff,

                I did not login as SYDBA, I connected to LSDB in the pluggable database DEVELOP using SQL Developer 4.1.3.

                I have not changed any SYS PLSQL I just created an account LSDB inside the pluggable database DEVELOP which starts fine and was loaded with all the objects using IMDP.

                Thanks

                Bernie.

                • 5. Re: Can't Edit Most User Source in SQL Developer
                  I did not login as SYDBA, I connected to LSDB in the pluggable database DEVELOP using SQL Developer 4.1.3.

                  I have not changed any SYS PLSQL I just created an account LSDB inside the pluggable database DEVELOP which starts fine and was loaded with all the objects using IMDP.

                  Sorry - but at this point it still isn't clear WHAT you have done, WHERE you did it or WHAT your results really were.

                   

                  Oracle defines/uses VERY SPECIFIC terms: CDB (also referred to as root or root container) - container database - the 'container', PDB - pluggable database

                   

                  PDBs, by default do NOT open when the database (root/CDB). You need to open them manually or use SAVE STATE for them when the DB is shut down.

                   

                  SHOW US - the actual connection parameters you used to connect to the PDB when you did the import and when you tried to view the package source in Sql developer.

                   

                  Please use that latest version of Sql developer for the tests as right now we are just troubleshooting and trying to find out if there is a problem.

                  • 6. Re: Can't Edit Most User Source in SQL Developer
                    thatJeffSmith-Oracle

                    create or replace PROCEDURE PRINT_DATE AS

                    x varchar2(20);

                    y date;

                    z number;

                    BEGIN

                      dbms_output.put_line('The date and time is currently: ' || SYSDATE);

                      select sysdate into y from dual;

                      dbms_output.put_line('Woohoo, it works!');

                      x := to_char(y);

                      dbms_output.put_line('More stuff!');

                      z := to_number(to_char(y, 'DD'));

                    END PRINT_DATE;

                    /

                     

                    Take this code, run it in a SQL Worksheet.

                     

                    Then open the procedure - do you see the code?

                     

                    I'm not sure what happened when you data pump imported your stuff, so let's start from scratch with a new code object.

                    • 7. Re: Can't Edit Most User Source in SQL Developer
                      user2598077

                      Jeff,

                       

                      Thank you for your quick response.

                       

                      I created a pluggable database called DEVELOP in 12c 12.1.0.2.0 and I am accessing it using SQL Developer 4.1.3.20 Build-Main.20.78.

                      I am using 12c for Windows 64-bit.

                       

                      The IMPDP code below was run to load 12c in stages since this is a fair amount of data and code:

                      PLVISION:

                      impdp 'sys@DEVELOP/790_Highland as sysdba' schemas=PLVISION parallel=8 directory=BACKUP_DIR dumpfile=SR175162_PLVISION.dmp logfile=PLVISION_import.log

                       

                      DICOM_ADMIN:

                      impdp 'sys@DEVELOP/790_Highland as sysdba' schemas=DICOM_ADMIN parallel=8 directory=BACKUP_DIR dumpfile=SR175162_DICOM_ADMIN.dmp logfile=DICOM_ADMIN_import.log

                       

                      DICOM:

                      impdp 'sys@DEVELOP/790_Highland as sysdba' schemas=DICOM parallel=8 directory=BACKUP_DIR dumpfile=SR175162_DICOM.dmp logfile=DICOM_import.log

                       

                      DATA:

                      impdp 'sys@DEVELOP/790_Highland as sysdba' schemas=DATA parallel=8 transform=disable_archive_logging:Y status =  60 directory=BACKUP_DIR dumpfile=SR175162_DATA.dmp logfile=DATA_import.log

                       

                      LSDB:

                      impdp 'sys@DEVELOP/790_Highland as sysdba' schemas=LSDB parallel=8 transform=disable_archive_logging:Y status = 60 content = metadata_only directory=BACKUP_DIR dumpfile=SR175162_LSDB.dmp logfile=LSDB_import_meta.log

                       

                      impdp 'sys@DEVELOP/790_Highland as sysdba' schemas=LSDB parallel=8 transform=disable_archive_logging:Y status = 60 content = data_only directory=BACKUP_DIR dumpfile=SR175162_LSDB.dmp logfile=LSDB_import_data.log

                       

                      These are the main accounts I loaded into DEVELOP below:

                      C:\Users\PERSEID>sqlplus

                      SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 15 14:49:20 2015

                      Copyright (c) 1982, 2014, Oracle.  All rights reserved.

                      Enter user-name: sys as sysdba

                      Enter password:

                      Connected to:

                      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

                      With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

                      SQL> shutdown immediate

                      SQL> startup

                      SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

                       

                          CON_ID       DBID NAME                           OPEN_MODE

                      ---------- ---------- ------------------------------ ----------

                               2 2932179927 PDB$SEED                       MOUNTED

                               3  311127671 DEVELOP                        MOUNTED

                      SQL> alter session set container=DEVELOP;

                      Session altered.

                      SQL> startup

                      Pluggable Database opened.

                      SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

                       

                          CON_ID       DBID NAME                           OPEN_MODE

                      ---------- ---------- ------------------------------ ----------

                               3  311127671 DEVELOP                        READ WRITE

                       

                      I did remove APEX from 12C entirely and install it only in the pluggable database as APEX 5.0.2.

                       

                      I startup Oracle and then enter:

                      ALTER SESSION SET CONTAINER = DEVELOP;

                      STARTUP

                       

                      I get:

                      PLUGGABLE DATABASE STARTED.


                      Then I connect to 4.1.3.20 SQL Developer using the connection below.

                       

                      I connect using the USER NAME: LSDB hostname: 192.168.0.49 Port: 1521 Service Name: DEVELOP

                       

                      I see all the correct objects in LSDB in the Viewer that I see in the 11GR2 Instance and all applications in APEX (12) run fine.

                       

                      If I click on a package in the viewer tree I see "CREATE OR REPLACE". I do not see any functions in the package tree I only see the PACKAGE and the PACKAGE BODY.

                      I can only see the package code if I right click and download it into a Worksheet.

                      I cannot see any Views, Functions or Procedures or Types or Synonyms, for example.

                      I hope this helps.

                      Thanks

                      Bernie

                      • 8. Re: Can't Edit Most User Source in SQL Developer
                        thatJeffSmith-Oracle

                        i'm concerned something is up with your 12c database or with the data pump, that's why I suggested you try creating a new object and see if you can see THAT in the browser

                        • 9. Re: Can't Edit Most User Source in SQL Developer
                          user2598077

                          Jeff,

                          Ok.

                          I see the PROCEDURE CREATE_DATE.

                          When I edit it all I see is "CREATE OR REPLACE".

                          When I Quick Edit it to a WORKSHEET, I see:

                          --------------------------------------------------------

                          --  DDL for Procedure PRINT_DATE

                          --------------------------------------------------------

                          set define off;

                           

                           

                            CREATE OR REPLACE EDITIONABLE PROCEDURE "LSDB"."PRINT_DATE" AS

                          x varchar2(20);

                          y date;

                          z number;

                          BEGIN

                            dbms_output.put_line('The date and time is currently: ' || SYSDATE);

                            select sysdate into y from dual;

                            dbms_output.put_line('Woohoo, it works!');

                            x := to_char(y);

                            dbms_output.put_line('More stuff!');

                            z := to_number(to_char(y, 'DD'));

                          END PRINT_DATE;

                           

                          Thanks

                          Bernie

                          • 10. Re: Can't Edit Most User Source in SQL Developer
                            thatJeffSmith-Oracle

                            >>When I edit

                            How exactly are you doing this?

                            • 11. Re: Can't Edit Most User Source in SQL Developer
                              user2598077

                              Jeff,

                              One of two ways:

                              1. Double click on PRINT_DATE.

                              2. Right click on PRINT_DATE and select EDIT.

                               

                              I also Quick Edited an existing PROCEDURE HELLO_WORLD to a worksheet and then DROPPED it and ran the Worksheet and it's there but also can't be edited.  I get "CREATE OR REPLACE" only.

                              Thanks

                              Bernie

                              • 12. Re: Can't Edit Most User Source in SQL Developer
                                thatJeffSmith-Oracle

                                Here is the query we use to get the code for your PL/SQL in the code editor

                                 

                                WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba_SOURCE

                                WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME)

                                SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM src) 

                                WHERE origin_con_id = max_orig

                                ORDER BY LINE

                                 

                                If you run this in a sql worksheet, and supply the 3 bind variable values, does the code come back as expected? --if you don't have access to DBA_SOURCE, use ALL_SOURCE instead

                                 

                                View > Log > Statements

                                this panel will show the queries we run as you click around the tool...

                                • 13. Re: Can't Edit Most User Source in SQL Developer
                                  user2598077

                                  Jeff,

                                  I ran the source with OWNER=LSDB TYPE=PROCEDURE and NAME=PRINT_DATE and the result is a TEXT column that is blank (no lines).

                                  Thanks

                                  BPW

                                  • 14. Re: Can't Edit Most User Source in SQL Developer
                                    user2598077

                                    Jeff,

                                    I did the same thing with ALL_SOURCE and the result is the same (blank column).

                                    Thanks

                                    BPW

                                    1 2 3 Previous Next