sql - What is wrong with this package declaration and definition -
package cpac stgfile xyz_instance.filename%type; procedure poc (cid in xyz_instance.client_id%type, stgtype in xyz_instance.stg_instance%type, stgsrc in xyz_instance.stg_source%type); end; package body cpac procedure poc (cid in xyz_instance.client_id%type, stgtype in xyz_instance.stg_instance%type, stgsrc in xyz_instance.stg_source%type ) begin select filename stgfile xyz_instance stg_instance = stgtype , stg_source = stgsrc , client_id = cid; end poc; begin poc('0123','19517','l'); dbms_output.put_line(stgfile); end cpac; sql statement alone executing, procedure alone containing sql executing within package error :
error @ line 4
ora-00900: invalid sql statement,
took reference following document:http://docstore.mik.ua/orelly/oracle/prog2/ch16_02.htm
prefix package header , body creation create or replace:
create or replace package cpac stgfile xyz_instance.filename%type; procedure poc (cid in xyz_instance.client_id%type, stgtype in xyz_instance.stg_instance%type, stgsrc in xyz_instance.stg_source%type); end; / create or replace package body cpac procedure poc (cid in xyz_instance.client_id%type, stgtype in xyz_instance.stg_instance%type, stgsrc in xyz_instance.stg_source%type ) begin select filename stgfile xyz_instance stg_instance = stgtype , stg_source = stgsrc , client_id = cid; end poc; begin poc('0123','19517','l'); dbms_output.put_line(stgfile); end cpac; / edit: if don't have permission create package, can't create package. however, may still able create procedure or function within declare section of anonymous pl/sql block, , run procedure/function later on during block. in below sample i'm logged in user doesn't have permissions create stored procedure, hence error attempting create one:
sql> create or replace procedure test begin null; end; 2 / create or replace procedure test begin null; end; * error @ line 1: ora-01031: insufficient privileges sql> declare 2 stgfile xyz_instance.filename%type; 3 4 procedure poc (cid in xyz_instance.client_id%type, 5 stgtype in xyz_instance.stg_instance%type, 6 stgsrc in xyz_instance.stg_source%type 7 ) 8 begin 9 select filename 10 stgfile 11 xyz_instance 12 stg_instance = stgtype 13 , stg_source = stgsrc 14 , client_id = cid; 15 end poc; 16 17 begin 18 poc('0123','19517','l'); 19 dbms_output.put_line(stgfile); 20 end; 21 / test-filename.txt pl/sql procedure completed.
Comments
Post a Comment