Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: October 31, 2016 - 2:39 pm UTC

Last updated: November 04, 2016 - 4:54 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi , can we call sql scripts containing procedures and packages without a SQL*plus.

As of now we are running some scripts through sql*plus command line. So if we want to run these scripts from a system where they don't have sql plus installed is there a way to do it? We are thinking of adding some interactive validation to these scripts. To implement this do we need to go for power shell scripts or batch files or shell scripts?

and Chris said...

Install SQL*Plus! ;)

But seriously, you need a client of some form to connect to the database. You can use PowerShell. But you still need to get the (ODP.NET) client:

http://www.oracle.com/technetwork/topics/dotnet/index-085163.html
https://blogs.technet.microsoft.com/heyscriptingguy/2012/12/04/use-oracle-odp-net-and-powershell-to-simplify-data-access/

So you going to have to install "something" to enable connectivity. May as well go with SQL*Plus.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SQLCI

Rajeshwaran Jeyabal, November 04, 2016 - 11:31 am UTC

.....
can we call sql scripts containing procedures and packages without a SQL*plus.
.....


Yes, we can with SQLCI in place, it goes like this.

scott@ORA11G>
scott@ORA11G>
scott@ORA11G> ddl emp;
  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

scott@ORA11G>
scott@ORA11G>
scott@ORA11G> ddl p;
  CREATE OR REPLACE PROCEDURE "SCOTT"."P" as
begin
        execute immediate ' alter index demo.t_idx rebuild nologging';
end;
/

scott@ORA11G> ddl pkg;
  CREATE OR REPLACE PACKAGE "SCOTT"."PKG" as
  procedure p1;
  procedure p2;
end;
/
  CREATE OR REPLACE PACKAGE BODY "SCOTT"."PKG" as
  procedure p1 as
  begin
    null ;
  end ;
  procedure p2 as
  begin
    null ;
  end ;
end;
/

scott@ORA11G>

Chris Saxon
November 04, 2016 - 4:54 pm UTC

You mean SQLCL right? That would make a good choice too.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library