Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Component 'PAK_POPL_SUPPL' must be declared error

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Component 'PAK_POPL_SUPPL' must be declared error
# 1  
Old 11-23-2011
Component 'PAK_POPL_SUPPL' must be declared error
Hi,

I am using a shell script to run a Oracle procedure in Linux.I am getting the below error while running the procedure:

Code:
ERROR at line 1:
ORA-06550: line 1, column 22:
PLS-00302: component 'PAK_POPL_SUPPL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SHELL SCRIPT which Iam using is -->

Code:
 
#!/bin/ksh
# File   :  run_package.sh
# --------------------------------------------------
ORACLE_HOME=/opt/oracle/10.2.0
export ORACLE_HOME
PATH=$PATH:/opt/oracle/10.2.0/bin
export PATH
UNAME=dml_prod
PASS=dml_prod
ORACLE_SID=cb22
sqlplus -s $UNAME@$ORACLE_SID/$PASS << !
SET heading off
SET feedback off
echo "Running Package pak_popl_suppl"
EXEC pak_popl_suppl.pr_supplier_update;
#EXEC pak_popl_suppl.pr_supplier_new;
#EXEC pak_popl_suppl.pak_popl_suppl;
!
if [ $? -eq 0 ]
then
   echo "suceess"
   return $?
else
   echo "Fail"
   return $?
fi

ORACLE PROCEDURE which I am using is -->

Code:
 
CREATE OR REPLACE PACKAGE pak_popl_suppl
IS
PROCEDURE pr_supplier_update;
END PAK_POPL_SUPPL;
/
CREATE OR REPLACE PACKAGE BODY pak_popl_suppl
IS
PROCEDURE pr_supplier_update
IS
CURSOR c_new_supplier  IS
SELECT
sps.entity_cd,
sps.supplier_id,
sps.supplier_name,
sps.address_line1 ,
sps.address_line2 ,
sps.address_line3 ,
sps.city ,
sps.post_code ,
sps.state ,
sps.country ,
sps.phone_number ,
sps.currency ,
sps.fax_number ,
sps.email_address
FROM stage_popl_supplier sps
WHERE NOT EXISTS(SELECT *
FROM popl_supplier ps
WHERE sps.entity_cd = ps.entity_cd
AND sps.supplier_id = ps.supplier_id)
AND sps.entity_cd <> '****';    --  exclude the dummy Supplier added Brian Clark 10 Nov 2011
v_count NUMBER :=0;
BEGIN
FOR v_new_supp IN c_new_supplier LOOP
v_count := v_count +1;
INSERT INTO popl_supplier
VALUES
(
v_new_supp.entity_cd,
v_new_supp.supplier_id,
v_new_supp.supplier_name,
v_new_supp.address_line1,
v_new_supp.address_line2,
v_new_supp.address_line3,
v_new_supp.city,
v_new_supp.post_code,
v_new_supp.state,
v_new_supp.country,
v_new_supp.phone_number,
v_new_supp.currency,
v_new_supp.fax_number,
v_new_supp.email_address,
(chappi_popl_supplier.NEXTVAL),
'Y'
);
END LOOP;
Dbms_Output.ENABLE(1000000);
Dbms_Output.put_line(v_count);
COMMIT;
EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK;
Dbms_Output.put_line(SQLCODE ||'-'||SQLERRM);
END pr_supplier_update ;
END PAK_POPL_SUPPL;

# 2  
Old 11-23-2011
Quote:
Originally Posted by csrohit
Hi,

I am using a shell script to run a Oracle procedure in Linux.I am getting the below error while running the procedure:

Code:
ERROR at line 1:
ORA-06550: line 1, column 22:
PLS-00302: component 'PAK_POPL_SUPPL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SHELL SCRIPT which Iam using is -->

Code:
 
#!/bin/ksh
# File   :  run_package.sh
# --------------------------------------------------
ORACLE_HOME=/opt/oracle/10.2.0
export ORACLE_HOME
PATH=$PATH:/opt/oracle/10.2.0/bin
export PATH
UNAME=dml_prod
PASS=dml_prod
ORACLE_SID=cb22
sqlplus -s $UNAME@$ORACLE_SID/$PASS << !
SET heading off
SET feedback off
echo "Running Package pak_popl_suppl"
EXEC pak_popl_suppl.pr_supplier_update;
#EXEC pak_popl_suppl.pr_supplier_new;
#EXEC pak_popl_suppl.pak_popl_suppl;
!
if [ $? -eq 0 ]
then
   echo "suceess"
   return $?
else
   echo "Fail"
   return $?
fi

ORACLE PROCEDURE which I am using is -->

Code:
 
CREATE OR REPLACE PACKAGE pak_popl_suppl
IS
PROCEDURE pr_supplier_update;
END PAK_POPL_SUPPL;
/
CREATE OR REPLACE PACKAGE BODY pak_popl_suppl
IS
PROCEDURE pr_supplier_update
IS
CURSOR c_new_supplier  IS
SELECT
sps.entity_cd,
sps.supplier_id,
sps.supplier_name,
sps.address_line1 ,
sps.address_line2 ,
sps.address_line3 ,
sps.city ,
sps.post_code ,
sps.state ,
sps.country ,
sps.phone_number ,
sps.currency ,
sps.fax_number ,
sps.email_address
FROM stage_popl_supplier sps
WHERE NOT EXISTS(SELECT *
FROM popl_supplier ps
WHERE sps.entity_cd = ps.entity_cd
AND sps.supplier_id = ps.supplier_id)
AND sps.entity_cd <> '****';    --  exclude the dummy Supplier added Brian Clark 10 Nov 2011
v_count NUMBER :=0;
BEGIN
FOR v_new_supp IN c_new_supplier LOOP
v_count := v_count +1;
INSERT INTO popl_supplier
VALUES
(
v_new_supp.entity_cd,
v_new_supp.supplier_id,
v_new_supp.supplier_name,
v_new_supp.address_line1,
v_new_supp.address_line2,
v_new_supp.address_line3,
v_new_supp.city,
v_new_supp.post_code,
v_new_supp.state,
v_new_supp.country,
v_new_supp.phone_number,
v_new_supp.currency,
v_new_supp.fax_number,
v_new_supp.email_address,
(chappi_popl_supplier.NEXTVAL),
'Y'
);
END LOOP;
Dbms_Output.ENABLE(1000000);
Dbms_Output.put_line(v_count);
COMMIT;
EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK;
Dbms_Output.put_line(SQLCODE ||'-'||SQLERRM);
END pr_supplier_update ;
END PAK_POPL_SUPPL;

Does the database user "dml_prod" have the privilege to execute "pak_popl_suppl" ?

tyler_durden
# 3  
Old 11-23-2011
Another question:
-----> The user: dml_prod can "see" the package: PAK_POPL_SUPPL?

Check with this query:
Code:
Select  Owner,
        Object_Name,
        Object_Type
From All_Objects
Where Object_Type = 'PACKAGE'
      And Object_Name = 'PAK_POPL_SUPPL';

If not, this is your problem. =o)
This User Gave Thanks to felipe.vinturin For This Post:
csrohit
# 4  
Old 11-24-2011
while running the above query i am getting the following result:
Code:
 
OWNER       OBJECT_NAME         OBJECT_TYPE
DML_PROD   PAK_POPL_SUPPL     PACKAGE
TV_RSCE     PAK_POPL_SUPPL     PACKAGE

Also if I connect through sqlplus using dml_prod user and then execute the procedure as -
Code:
 
EXECUTE pak_popl_suppl.pr_supplier_update;

The procedure is getting successfully executed.
# 5  
Old 11-24-2011
Some things:
-----> There are some problems inside your sqlplus execution: an "echo" (unix command, not sqlplus) and sqlplus' comments are: --.

Try this:
Code:
sqlplus -s $UNAME@$ORACLE_SID/$PASS << !
SET heading off
SET feedback off
PROMPT "Running Package pak_popl_suppl"
EXEC pak_popl_suppl.pr_supplier_update;
-- EXEC pak_popl_suppl.pr_supplier_new;
-- EXEC pak_popl_suppl.pak_popl_suppl;
!

You can also try the following:
Code:
# cat execSQLPlus.sql
WHENEVER SQLERROR EXIT 1
SET heading off
SET feedback off
PROMPT "Running Package pak_popl_suppl"
EXEC pak_popl_suppl.pr_supplier_update;
-- EXEC pak_popl_suppl.pr_supplier_new;
-- EXEC pak_popl_suppl.pak_popl_suppl;
EXIT

# --> Now call sqlplus like:
echo "START execSQLPlus.sql" | sqlplus -s $UNAME@$ORACLE_SID/$PASS

If it does not work, I forgot one column in my previous query. Please, execute the query below and post the result:
Code:
Select  Owner,
        Object_Name,
        Object_Type,
        Status
From All_Objects
Where Object_Type = 'PACKAGE'
      And Object_Name = 'PAK_POPL_SUPPL';

If the status above is "INVALID", compile the package/body:
Code:
# In SQLPlus
ALTER PACKAGE PAK_POPL_SUPPL COMPILE BODY;
ALTER PACKAGE PAK_POPL_SUPPL COMPILE;

I hope it helps.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

PHP: declared variables, strlen vs isset

greetings, pretty new to php and i think i might be missing some fundamental limitation of isset. i have two php scripts below that are executed by crond, one using --host X and one that does not. and below that are three different attempts at generating a command line that will be executed. the... (8 Replies)
Discussion started by: crimso
8 Replies

2. Shell Programming and Scripting

write in file using printf with variable declared with a phrase

Hi guys, kinda new to unix/linux, could you please help me figure this out. i need to write in a file using printf. printf "%-20s %-40s %-20s\n" $a $b $c >> out.txt but a, b and c are declared in a header file: a='I am a dog' b='I am a cat' c='I am a fish' i want the file to look like... (1 Reply)
Discussion started by: kokoro
1 Replies

3. Shell Programming and Scripting

**URGENT**component 'PAK_POPL_SUPPL' must be declared error

hi, I am using a shell script to run a Oracle procedure in Linux.I am getting the below error while running the procedure: (1 Reply)
Discussion started by: csrohit
1 Replies

4. Shell Programming and Scripting

Assign user input to already declared array

What I am doing is creating a top menu, which a user will select a choice with a number entry. That number corresponds to a string in an array. I then want to assign that response to another array I've already declared. For example: #!/bin/bash colors=(red blue yellow) red=(cherry fire)... (2 Replies)
Discussion started by: Akilleez
2 Replies

5. Shell Programming and Scripting

Help in separating variables declared in the main function

Hi! I've a C program as shown below.. The line numbers and the statements of the program are separated by a space.. 1 #include<stdio.h> 2 char a,b,c; 3 float x,y,z; 4 int main() 5 { 6 int d,e,f; 7 // further declarations 8 // further declarations 9 /* body*/ 10 } 11 void fun1() 12... (1 Reply)
Discussion started by: abk07
1 Replies

6. Programming

Locally Declared Labels

Hi guys. in the Locally Declared Labels section in "The Definitive Guide to GCC" book there is block of code: #define SEARCH(array, target) ({ __label__ found; typeof (target) _SEARCH_target = (target); typeof (*(array)) *_SEARCH_array = (array); int i, j; int value;... (1 Reply)
Discussion started by: majid.merkava
1 Replies

7. AIX

`pthread_rwlock_t' was not declared in this scope

Hello All, I am getting this error while compiling my application on IBM AIX 5.3. As I tried to define _XOPEN_SOURCE=500 in makefile, that didn't work. Please help us to resolve the error. (0 Replies)
Discussion started by: mustus
0 Replies

8. Shell Programming and Scripting

shell script: Bind variable not declared

Hi Friends, I am trying to run a sql query from shell script as below but I get "Bind variable "1" not declared" error. 1.sh shell script has following: sDb="abc/xyz@aaa" a="1.sql" sqlplus -s $sDb @$a $1 1.sql file has following: spool Result.tmp append select cust_name from orders... (1 Reply)
Discussion started by: ppat7046
1 Replies

9. Shell Programming and Scripting

accessing variables declared in another perl script

Hi all, I have a perl script which declares two variables and calls another perl script which accesses those variables. But I am unable to access the variables in the called script. My script is as follows: my $ENV{a}="20"; system("perl called.pl"); and my called.pl contains: print... (3 Replies)
Discussion started by: gurukottur
3 Replies

10. Shell Programming and Scripting

Fax Component

I would like to build a small fax component in Java or Perl. I have the javax.comm. (rxtx gnu.io) for Java for serial connection and get send a message to my fax were the light goes on and port is taken, but after that I am in the dark. Does anyone have some reference as to where I can get some... (1 Reply)
Discussion started by: photon
1 Replies
Login or Register to Ask a Question