Creating an Oracle Stored Procedure and Executing it from .sql file -
i have 2 .sql files both oracle stored procedures both take in input parameters. first connect remote oracle database using sqlplus in command line , want first use both files create respective stored procedures see them under procedures connection in oracle sql developer.
after have 2 more .sql files , designed take input parameters , execute stored procedures. 1 of files meant execute stored procedure "report".
declare name varchar2(200); version varchar2(200); startdate date; enddate date; begin name := '&1'; version := '&2'; startdate := '&3'; enddate := '&4'; exec report(name, version, startdate, enddate); exception when others raise_application_error(-20101,sqlerrm); end; /
in command prompt first try create stored procedure in database by: c:\users\desktop>sqlplus username/password @report_setup.sql
when try output empty lines numbered , beginning @ number 1 greater last line of .sql file. report_setup.sql file 81 lines long , output of sqlplus command blank numbered lines beginning @ 83.
please let me know how can create , execute these stored procedures through sqlplus.
thanks in advance,
i think have remove 'exec'-word, , it's crucial have slash @ bottom @ start of line, no spaces in front of it:
declare name varchar2(200); version varchar2(200); startdate date; enddate date; begin name := '&1'; version := '&2'; startdate := '&3'; enddate := '&4'; report(name, version, startdate, enddate); exception when others raise_application_error(-20101,sqlerrm); end; /
Comments
Post a Comment