As, the procedures I have to write and debug are hundreds of lines and
will be in packages and I will have to work on those scripts on my
local file system and only when everything works fine I should check
in the code to the database.
Hence I want to know how to execute such procedures which are in
source scripts ?
Thanks,
Hima
On Dec 29, 10:31 am, ddf <orat...@msn.com> wrote:
> Comments embedded.
>
> On Dec 29, 11:59 am, zalesam <bindu.rang...@gmail.com> wrote:
>
>
>
> > Thanks DAvid for your time.
>
> > When I write and run this code at the command line it works as you
> > said. But when I tried to write and run it from a sql file. It fails
> > with the same errors as mentioned above. It created the PACKAGE and
> > PACKAGE BODY successfully tho with no errors but when I tried to
> > execute, it is giving the same error but shows no errors when I typed
> > SHOW ERRORS.
>
> > SQL> @/home/user/DBRelated/pkg_test_globals.sql;
>
> > Package created.
>
> > Package body created.
>
> > SQL> exec /home/user/DBRelated/pack_test_globals.increase_and_display;
> > BEGIN /home/user/DBRelated/pack_test_globals.increase_and_display;
> > END;
>
> > *
> > ERROR at line 1:
> > ORA-06550: line 1, column 7:
> > PLS-00103: Encountered the symbol "/" when expecting one of the
> > following:
> > begin case declare 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
>
> Why on earth are you attempting to execute the stored procedure from
> the source script????? You successfully executed the script BEFORE
> you attempted to execute the package procedure, and did you not see
> how to execute such procedures from my previous post? I provided 7
> examples of how to call such a procedure. I will repost that
> information HERE in hopes you'll see the error of your ways:
>
> SQL> exec pack_test_globals.increase_and_display
>
> Notice there is no file name supplied; the package is now RESIDENT in
> your database and ready to use.
>
> > SQL> show errors;
> > No errors..
>
> > Please help. I need to have my procedure in package body in sql script
> > file.
>
> > /*********************************************************
> > CODE IN THE SCRIPT FILE ( pkg_test_globals.sql )
> > */
>
> > CREATE OR REPLACE PACKAGE pack_test_globals IS
> > v_public NUMBER :=0;
> > procedure increase_and_display;
> > end pack_test_globals;
> > /
>
> > create or replace
> > PACKAGE BODY pack_test_globals IS
> > v_private number:= 0;
>
> > procedure increase_and_display IS
> > v_local NUMBER:= 0;
>
> > BEGIN
>
> > v_public := v_public + 1;
> > v_private := v_private + 1;
> > v_local := v_local + 1;
>
> > DBMS_OUTPUT.PUT_LINE('PUBLIC VARIABLE IS ----->' || v_public);
> > DBMS_OUTPUT.PUT_LINE('PRIVATE VARIABLE IS ----->' || v_private);
> > DBMS_OUTPUT.PUT_LINE('LOCAL VARIABLE IS ----->' || v_local);
>
> > END increase_and_display;
> > END pack_test_globals;
> > /
>
> > Thanks,
> > Sam
>
> > On Dec 29, 6:06 am, ddf <orat...@msn.com> wrote:
>
> > > On Dec 29, 3:35 am, zalesam <bindu.rang...@gmail.com> wrote:
>
> > > > Hi,
> > > > I am beginner. Please help.
>
> > > > I have a stored procedure in a package and I saved this locally. I am
> > > > not able to execute the procedure from command line. Stored the
> > > > following code in pkg_test_global.sql file on my local machine. I am
> > > > on Linux.
>
> > > > CREATE OR REPLACE PACKAGE pack_test_globals IS
> > > > v_public NUMBER :=0;
> > > > procedure increase_and_display;
> > > > end pack_test_globals;
>
> > > > CREATE OR REPLACE PACKAGE BODY pack_test_globals IS
> > > > v_private number:= 0;
> > > > procedure increase_and_display IS
> > > > v_local NUMBER:= 0;
> > > > BEGIN
> > > > v_public := v_public + 1;
> > > > v_private := v_private + 1;
> > > > v_local := v_local + 1;
> > > > DBMS_OUTPUT.PUT_LINE('PUBLIC VARIABLE IS -----' || v_public);
> > > > DBMS_OUTPUT.PUT_LINE('PRIVATE VARIABLE IS -----' || v_private);
> > > > DBMS_OUTPUT.PUT_LINE('LOCAL VARIABLE IS -----' || v_local);
> > > > END increase_and_display;
> > > > END pack_test_globals;
>
> > > > SQL>@/home/ranganah/DBRelated/pkg_test_global.sql
>
> > > > Warning: Package created with compilation errors.
>
> > > > How do I see these warnings with compilation errors and how to resolve
> > > > this?
>
> > > > When I tried to call the procedure I get this error. How to get over
> > > > it. ?
>
> > > > SQL>exec /home/ranganah/DBRelated/
> > > > pack_test_globals.increase_and_display;
>
> > > > BEGIN /home/ranganah/DBRelated/pack_test_globals.increase_and_display;
> > > > END;
>
> > > > *
> > > > ERROR at line 1:
> > > > ORA-06550: line 1, column 7:
> > > > PLS-00103: Encountered the symbol "/" when expecting one of the
> > > > following:
> > > > begin case declare 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
>
> > > > Please Help me resolve this. I am blocked.
>
> > > > Thanks,
> > > > Sam
>
> > > Modify your code:
>
> > > CREATE OR REPLACE PACKAGE pack_test_globals IS
> > > v_public NUMBER :=0;
> > > procedure increase_and_display;
> > > end pack_test_globals;
> > > /
>
> > > show errors
>
> > > CREATE OR REPLACE PACKAGE BODY pack_test_globals IS
> > > v_private number:= 0;
> > > procedure increase_and_display IS
> > > v_local NUMBER:= 0;
> > > BEGIN
> > > v_public := v_public + 1;
> > > v_private := v_private + 1;
> > > v_local := v_local + 1;
> > > DBMS_OUTPUT.PUT_LINE('PUBLIC VARIABLE IS -----' || v_public);
> > > DBMS_OUTPUT.PUT_LINE('PRIVATE VARIABLE IS -----' ||
> > > v_private);
> > > DBMS_OUTPUT.PUT_LINE('LOCAL VARIABLE IS -----' || v_local);
> > > END increase_and_display;
> > > END pack_test_globals;
> > > /
>
> > > show errors
>
> > > But, I don't receive the same message you do:
>
> > > SQL> CREATE OR REPLACE PACKAGE pack_test_globals IS
> > > 2 v_public NUMBER :=0;
> > > 3 procedure increase_and_display;
> > > 4 end pack_test_globals;
> > > 5 /
>
> > > Package created.
>
> > > SQL>
> > > SQL> show errors
> > > No errors.
> > > SQL>
> > > SQL> CREATE OR REPLACE PACKAGE BODY pack_test_globals IS
> > > 2 v_private number:= 0;
> > > 3 procedure increase_and_display IS
> > > 4 v_local NUMBER:= 0;
> > > 5 BEGIN
> > > 6 v_public := v_public + 1;
> > > 7 v_private := v_private + 1;
> > > 8 v_local := v_local + 1;
> > > 9 DBMS_OUTPUT.PUT_LINE('PUBLIC VARIABLE IS -----' ||
> > > v_public);
> > > 10 DBMS_OUTPUT.PUT_LINE('PRIVATE VARIABLE IS -----' ||
> > > v_private);
> > > 11 DBMS_OUTPUT.PUT_LINE('LOCAL VARIABLE IS -----' ||
> > > v_local);
> > > 12 END increase_and_display;
> > > 13 END pack_test_globals;
> > > 14 /
>
> > > Package body created.
>
> > > SQL>
> > > SQL> show errors
> > > No errors.
> > > SQL>
>
> > > And it runs without error:
>
> > > SQL> set serveroutput on size 1000000
> > > SQL> exec pack_test_globals.increase_and_display
> > > PUBLIC VARIABLE IS -----2
> > > PRIVATE VARIABLE IS -----2
> > > LOCAL VARIABLE IS -----1
>
> > > PL/SQL procedure successfully completed.
>
> > > SQL> exec pack_test_globals.increase_and_display
> > > PUBLIC VARIABLE IS -----3
> > > PRIVATE VARIABLE IS -----3
> > > LOCAL VARIABLE IS -----1
>
> > > PL/SQL procedure successfully completed.
>
> > > SQL> exec pack_test_globals.increase_and_display
> > > PUBLIC VARIABLE IS -----4
> > > PRIVATE VARIABLE IS -----4
> > > LOCAL VARIABLE IS -----1
>
> > > PL/SQL procedure successfully completed.
>
> > > SQL> exec pack_test_globals.increase_and_display
> > > PUBLIC VARIABLE IS -----5
> > > PRIVATE VARIABLE IS -----5
> > > LOCAL VARIABLE IS -----1
>
> > > PL/SQL procedure successfully completed.
>
> > > SQL> exec pack_test_globals.increase_and_display
> > > PUBLIC VARIABLE IS -----6
> > > PRIVATE VARIABLE IS -----6
> > > LOCAL VARIABLE IS -----1
>
> > > PL/SQL procedure successfully completed.
>
> > > SQL> exec pack_test_globals.increase_and_display
> > > PUBLIC VARIABLE IS -----7
> > > PRIVATE VARIABLE IS -----7
> > > LOCAL VARIABLE IS -----1
>
> > > PL/SQL procedure successfully completed.
>
> > > SQL> exec pack_test_globals.increase_and_display
> > > PUBLIC VARIABLE IS -----8
> > > PRIVATE VARIABLE IS -----8
> > > LOCAL VARIABLE IS -----1
>
> > > PL/SQL procedure successfully completed.
>
> > > SQL>
>
> > > My guess is that you're running your code as written, which won't work
> > > (notice how I modified your code in the working example) which will
> > > produce that error:
>
> > > SQL> CREATE OR REPLACE PACKAGE pack_test_globals IS
> > > 2 v_public NUMBER :=0;
> > > 3 procedure increase_and_display;
> > > 4 end pack_test_globals;
> > > 5
> > > 6
> > > 7 CREATE OR REPLACE PACKAGE BODY pack_test_globals IS
> > > 8 v_private number:= 0;
> > > 9 procedure increase_and_display IS
> > > 10 v_local NUMBER:= 0;
> > > 11 BEGIN
> > > 12 v_public := v_public + 1;
> > > 13 v_private := v_private + 1;
> > > 14 v_local := v_local + 1;
> > > 15 DBMS_OUTPUT.PUT_LINE('PUBLIC VARIABLE IS -----' ||
> > > v_public);
> > > 16 DBMS_OUTPUT.PUT_LINE('PRIVATE VARIABLE IS -----' ||
> > > v_private);
> > > 17 DBMS_OUTPUT.PUT_LINE('LOCAL VARIABLE IS -----' ||
> > > v_local);
> > > 18 END increase_and_display;
> > > 19 END pack_test_globals;
> > > 20
> > > 21 /
>
> > > Warning: Package created with
>
> ...
>
> read more »
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
