Solved

Learning how to use Virtual Box Oracle SQL Developer... Trying to create package

Posted on 2013-01-08
15
591 Views
Last Modified: 2013-01-13
I'm trying learn how to use VB and I'm having trouble creating a package on Oracle SQL Developer in Virtual Box.

I go to file -> new -> Package...

then I don't see two separate files (one for spec and one for body) so I tried to insert both spec and body into one file but I keep getting an error, no matter how I try to insert my code for a working package.

I normally use PL/SQL Developer so I know my package already works on there but I can't get it to compile in VB.

I try to compile and because I have both spec and body in one file it is complaining about my "CREATE" that is coming from the body i.e. CREATE OR REPLACE PACKAGE BODY DROP_CREATE_TABLES_NIGHTLY IS
Any suggestions?
0
Comment
Question by:Mark_Co
  • 6
  • 5
  • 3
15 Comments
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 167 total points
ID: 38756392
There should be a ; at the end of the package header specification.  Is there a / on the line after the ;?  If not, then I would put one there and try it.  That would be my guess as to what you need to do to get it to run.

If that doesn't work, can you post a few lines on either side of the error and what the error actually is?
0
 

Author Comment

by:Mark_Co
ID: 38756405
This is what I have

CREATE OR REPLACE PACKAGE DROP_CREATE_TABLES_NIGHTLY IS

              /*
               Original Author: Me
               Created Date: 3-Jan-2013
               Purpose: For storing procedures to drop, create, and archive new tables /
               To drop initial tables and recreate them nightly
              */

              PROCEDURE W(STR VARCHAR2); --WRAPPER FOR DBMS_OUTPUT
              PROCEDURE DROP_TABLES
              (
                            P_SCHEMA VARCHAR2
                           ,P_TABLENAME VARCHAR2
              ); --DROPS PRE-EXISTING TABLES
              PROCEDURE CREATE_NEW_ARCHIVED_TABLES; --RECREATES TABLES THEMSELVES
              PROCEDURE BUILD_ARCHIVED_TABLES; --POPULATE FRESH DATA INTO TABLES

END DROP_CREATE_TABLES_NIGHTLY;
/

CREATE OR REPLACE PACKAGE BODY DROP_CREATE_TABLES_NIGHTLY IS

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

              /* procedure 'W' is a wrapper for DBMS output. Placed at top of package to make globally available*/
              PROCEDURE W(STR VARCHAR2) IS
              
              BEGIN
              
                            /* setting variable to simpler to use STR variable for outputting */
                            DBMS_OUTPUT.PUT_LINE(STR);
              END;

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

              /* 
               Procedure purpose: For dropping previously archived tables so that new ones can be created 
               Ask yourself "what does a typical DROP TABLE SQL look like...
               I.E. 'DROP TABLE ME.CUSTOMERS' 
               therefore pass in the schema.tablename and voila, there is the procedure you want.
              */
              PROCEDURE DROP_TABLES

Open in new window

etc...
0
 
LVL 34

Expert Comment

by:johnsone
ID: 38756648
And what is the error?
0
 

Author Comment

by:Mark_Co
ID: 38756666
Error(20,1): PLS-00103: Encountered the symbol "/"
0
 

Author Comment

by:Mark_Co
ID: 38756669
So I remove the "/" and the next error is Error(22,1): PLS-00103: Encountered the symbol "CREATE"
0
 
LVL 34

Expert Comment

by:johnsone
ID: 38756756
That is not a tool that I am familiar with, but based on the errors that you are getting, it would seem that you cannot combine the package header and body into one script with that tool.
0
 

Author Comment

by:Mark_Co
ID: 38756766
I figured. Hmmm, I'll keep trying and seeing how I can get them into separate files but still connected to each other. At work, PL/SQL Developer auto creates a spec and a body file. I don't know how to do so in Virtual box Oracle SQL Developer
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 73

Expert Comment

by:sdstuber
ID: 38760248
virtual box shouldn't be relevant.   in what way is it impacting you?

I use virtual box for several different types of servers and once logged in, the virtual nature of my system is invisible to my applications.
0
 

Author Comment

by:Mark_Co
ID: 38760256
I want to use it for home practice. But it's pretty useless if I can't figure out how to create packages and procedures on it
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 333 total points
ID: 38760270
what exactly are you doing?  where does virtualbox come into play?

is your database running inside a virtual box machine?

are you running sql developer from within virtualbox machine?

both? neither?  if both, are they the same machine?  

if neither, how is virtual box involved?


also note, the code snippet above is incomplete.  It shouldn't compile anywhere because the package body is missing syntax.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 333 total points
ID: 38760329
I filled out the rest of the package body like below and copy it into sql developer.
I then run the script with F5 or by clicking the "Run Script" icon.

Both the package and package body created fine.


CREATE OR REPLACE PACKAGE DROP_CREATE_TABLES_NIGHTLY IS

              /*
               Original Author: Me
               Created Date: 3-Jan-2013
               Purpose: For storing procedures to drop, create, and archive new tables /
               To drop initial tables and recreate them nightly
              */

              PROCEDURE W(STR VARCHAR2); --WRAPPER FOR DBMS_OUTPUT
              PROCEDURE DROP_TABLES
              (
                            P_SCHEMA VARCHAR2
                           ,P_TABLENAME VARCHAR2
              ); --DROPS PRE-EXISTING TABLES
              PROCEDURE CREATE_NEW_ARCHIVED_TABLES; --RECREATES TABLES THEMSELVES
              PROCEDURE BUILD_ARCHIVED_TABLES; --POPULATE FRESH DATA INTO TABLES

END DROP_CREATE_TABLES_NIGHTLY;
/

CREATE OR REPLACE PACKAGE BODY drop_create_tables_nightly
IS
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------

    /* procedure 'W' is a wrapper for DBMS output. Placed at top of package to make globally available*/
    PROCEDURE w(str VARCHAR2)
    IS
    BEGIN
        /* setting variable to simpler to use STR variable for outputting */
        DBMS_OUTPUT.put_line(str);
    END;

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

    /*
     Procedure purpose: For dropping previously archived tables so that new ones can be created
     Ask yourself "what does a typical DROP TABLE SQL look like...
     I.E. 'DROP TABLE ME.CUSTOMERS'
     therefore pass in the schema.tablename and voila, there is the procedure you want.
    */
    PROCEDURE drop_tables(p_schema VARCHAR2, p_tablename VARCHAR2)       --DROPS PRE-EXISTING TABLES
    IS
    BEGIN
        NULL;
    END;

    PROCEDURE create_new_archived_tables                               --RECREATES TABLES THEMSELVES
    IS
    BEGIN
        NULL;
    END;

    PROCEDURE build_archived_tables                                --POPULATE FRESH DATA INTO TABLES
    IS
    BEGIN
        NULL;
    END;
END;

Open in new window

                         
                 

If you get different results, please post exactly the steps you are performing, including anything that would make virtual box a relevant factor
0
 

Author Comment

by:Mark_Co
ID: 38769432
I guess it's just virtualbox that has issues. Thanks for trying
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38769436
how does virtual box figure into this?  I'm willing to try to help but you need to tell me what you're doing.  What are the errors? What happens? What is leading you to think virtualbox is having a problem?  What are you doing differently than what I have above?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 38769442
why the B?

a penalty grade is not appropriate here
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server - Check Constraint or NOT NULL? 11 67
Help writing a query 6 70
append to an ms access field 6 15
Extract string portion 2 0
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now