Home » Infrastructure » Unix » Shell script calling sql*plus file (database version Oracle 10g v.10.2.0.4)
Shell script calling sql*plus file [message #434540] Wed, 09 December 2009 11:19 Go to next message
californiagirl
Messages: 79
Registered: May 2007
Member
I'm using a shell script to call a sql*plus file, but getting errors in the output file. See below:

    v_striden_id                        := stunt_rec.striden_id;
                                                        *
ERROR at line 128:
ORA-06550: line 128, column 37:
PLS-00302: component 'STRIDEN_ID' must be declared
ORA-06550: line 128, column 5:
PL/SQL: Statement ignored
ORA-06550: line 129, column 44:
PLS-00302: component 'STRIDEN_LAST_NAME' must be declared
ORA-06550: line 129, column 5:
PL/SQL: Statement ignored
ORA-06550: line 130, column 45:



The typical answer for this type of problem is

Quote:
PLS-00302: component "string" must be declared

Cause: In a reference to a component (for example, in the name "A.B", "B" is a component of "A"), the component has not been declared. The component might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.

Action: Check the spelling and declaration of the component. Also confirm that the declaration is placed correctly in the block structure.


I have checked the spelling and declarations and all looks fine. c.striden_id is the column in the striden table and has a place holder or declared variable called v_striden_id, so I'm not sure why c.striden_id is a problem. Any advice would help please. See code below:

set serveroutput on size 1000000
set pagesize 0
set linesize 900
set echo off
set feedback off
set heading off
set timing off
set verify off
--
spool /home/stestinstance/brownarc/testoutput.txt

 
DECLARE

-- Paramters passed 
--
 
  v_blank_space varchar2(1) := ' ';
  v_striden_id varchar2(9);
  v_field_delimiter varchar2(1):='^';
  v_striden_last_name     varchar2(30);
    
--Main driver cursor 
  CURSOR cur_GetStuntData IS
    SELECT DISTINCT c.striden_id AS "StuntPerson ID",
                    c.striden_last_name AS "Last Name",
                       FROM   striden c,
   ORDER BY c.striden_id;
    
--Begin looping data
BEGIN


  FOR stunt_rec in  cur_GetStuntData LOOP
  
    v_striden_id := stunt_rec.striden_id;
    v_striden_last_name	:= stunt_rec.striden_last_name;
                                           
--Creating output for file    
 dbms_output.put(rpad(nvl(v_striden_id,' '),9,v_blank_space));
    dbms_output.put(rpadv_field_delimiter,2,v_field_delimiter ));
    dbms_output.put(rpad(substr(v_striden_last_name,1,30),30,v_blank_space ));
    dbms_output.put(rpad(v_field_delimiter,1,v_field_delimiter ));
         
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('some other error. call your administrator');
END;
/
spool off
/
exit;

[Updated on: Wed, 09 December 2009 11:21] by Moderator

Report message to a moderator

Re: Shell script calling sql*plus file [message #434541 is a reply to message #434540] Wed, 09 December 2009 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ ERROR at line 128:
There is not 128 lines in what you posted

2/ WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('some other error. call your administrator');
This is simple a BUG, remove that, it is MANDATORY.

3/ Whatever you think Oracle is right in its error message, so double-check, triple-check and so on until you find why it says so. Check owned objects, synonyms...

Regards
Michel

[Updated on: Wed, 09 December 2009 11:29]

Report message to a moderator

Re: Shell script calling sql*plus file [message #434543 is a reply to message #434540] Wed, 09 December 2009 11:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ERROR you posted did not come from code you posted.

post DDL for striden table

while debugging eliminate the EXCEPTION handler code entirely!
Re: Shell script calling sql*plus file [message #434549 is a reply to message #434543] Wed, 09 December 2009 12:12 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
DDL from striden table


CREATE TABLE NEPTUNE.STRIDEN
(
  STRIDEN_PID                 NUMBER(8)         NOT NULL,
  STRIDEN_ID                  VARCHAR2(9 BYTE)  NOT NULL,
  STRIDEN_LAST_NAME           VARCHAR2(60 BYTE) NOT NULL,
  STRIDEN_FIRST_NAME          VARCHAR2(15 BYTE),
  STRIDEN_MI                  VARCHAR2(15 BYTE),
  STRIDEN_CHANGE_IND          VARCHAR2(1 BYTE),
  STRIDEN_ENTITY_IND          VARCHAR2(1 BYTE),
  STRIDEN_ACTIVITY_DATE       DATE              NOT NULL,
  STRIDEN_USER                VARCHAR2(30 BYTE),
  STRIDEN_ORIGIN              VARCHAR2(30 BYTE),
  STRIDEN_SEARCH_LAST_NAME    VARCHAR2(60 BYTE),
  STRIDEN_SEARCH_FIRST_NAME   VARCHAR2(15 BYTE)
)



I also changed my variables as well and re-ran the script and removed the exception and still got the same errors.

Changed variables to:

 v_striden_id varchar2(9);
 v_field_delimiter varchar2(1):='^';
 v_striden_last_name     varchar2(60);
  


Any advice on what else to look at would be greatly appreciated.

Re: Shell script calling sql*plus file [message #434552 is a reply to message #434549] Wed, 09 December 2009 12:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
it works when you use valid syntax

SQL> set serveroutput on size 1000000
SQL> set pagesize 0
SQL> set linesize 900
SQL> set echo on
SQL> set term on
SQL> set feedback off
SQL> set heading off
SQL> set timing off
SQL> set verify off
SQL> --
SQL> spool /tmp/testoutput.txt
SQL> 
SQL> 
SQL> DECLARE
  2  
  3  -- Paramters passed
  4  --
  5  
  6    v_blank_space varchar2(1) := ' ';
  7    v_striden_id varchar2(9);
  8    v_field_delimiter varchar2(1):='^';
  9    v_striden_last_name	  varchar2(30);
 10  
 11  --Main driver cursor
 12    CURSOR cur_GetStuntData IS
 13  	 SELECT DISTINCT c.striden_id,
 14  			 c.striden_last_name
 15  			    FROM   striden c
 16  	ORDER BY c.striden_id;
 17  --Begin looping data
 18  BEGIN
 19  	DBMS_OUTPUT.ENABLE(100000);
 20    FOR stunt_rec in  cur_GetStuntData LOOP
 21  	 v_striden_id := stunt_rec.striden_id;
 22  	 v_striden_last_name := stunt_rec.striden_last_name;
 23  	 dbms_output.put(rpad(nvl(v_striden_id,' '),9,v_blank_space));
 24  	 dbms_output.put(rpad(v_field_delimiter,2,v_field_delimiter ));
 25  	 dbms_output.put(rpad(substr(v_striden_last_name,1,30),30,v_blank_space ));
 26  	 dbms_output.put(rpad(v_field_delimiter,1,v_field_delimiter ));
 27  
 28    END LOOP;
 29  END;
 30  /
SQL> spool off
SQL> /
SQL> 
Re: Shell script calling sql*plus file [message #434567 is a reply to message #434552] Wed, 09 December 2009 14:25 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
That last "/" warrants the biggest comment... What happens in sqlplus, is that your anonymous pl/sql gets loaded into the sqlppus buffer, then / executes the content of the buffer.

To stop spooling you enter just "spool off" or optionally "spool off;", but no /. The second / simply executes the content of the buffer again (2nd blah output). "l" lists the buffer content (the pl/sql - not the spool off) and so you know what / will run...

SQL> set serveroutput on
SQL> spool t.log
SQL> begin
  2   dbms_output.put_line('blah');
  3  end;
  4  /
blah

PL/SQL procedure successfully completed.

SQL> spool off
SQL> /
blah

PL/SQL procedure successfully completed.

SQL> l
  1  begin
  2   dbms_output.put_line('blah');
  3* end;
SQL>


Re: Shell script calling sql*plus file [message #434583 is a reply to message #434552] Wed, 09 December 2009 17:00 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Thanks for the advice. Now I added more columns and getting the following errors that are simular to the other errors I received.
v_stele_phone_number_a:= stunt_rec.stele_phone_number;
                                           *

ORA-06550: line 134, column 44:
PLS-00302: component 'STELE_PHONE_NUMBER' must be declared
ORA-06550: line 134, column 5:
PL/SQL: Statement ignored
ORA-06550: line 135, column 44:
PLS-00302: component 'STELE_PHONE_AREA' must be declared
ORA-06550: line 135, column 5:
PL/SQL: Statement ignored
ORA-06550: line 141, column 38:
PLS-00302: component 'ROLLED_IND' must be declared
ORA-06550: line 141, column 5:
PL/SQL: Statement ignored


There are more lines to the code but I only added the pertinent information.


 set serveroutput on size 1000000
set pagesize 0
set linesize 900
set echo on
set term on
set feedback off
set heading off
set timing off
set verify off

spool /tmp/testoutput.txt
 
 
DECLARE
 
 -- Paramters passed
 --
  
  v_blank_space varchar2(1) := ' ';
  v_striden_id varchar2(9);
  v_field_delimiter varchar2(1):='^';
  v_striden_last_name	  varchar2(30);
  v_stele_phone_area_b    VARCHAR2(3);
  v_stele_phone_number_a	VARCHAR2(7);
  v_stat   varchar2(1);
 
 --Main driver cursor
    CURSOR cur_GetStuntData IS
  	 SELECT DISTINCT c.striden_id,
  			 c.striden_last_name,
REPLACE ( (e.stele_phone_area
                                        || e.stele_phone_number), ',', '-')
                     ,
                    DECODE
                    (
                       b.rolled_ind,
                       'Y',
                       'A',
                       'N',
                       'I'
                    )
  			    FROM   striden c, stele e
where c.striden_id = e.stele_id
  	ORDER BY c.striden_id;
  --Begin looping data
  BEGIN
  	DBMS_OUTPUT.ENABLE(100000);
    FOR stunt_rec in  cur_GetStuntData LOOP
    v_striden_id := stunt_rec.striden_id;
    v_striden_last_name := stunt_rec.striden_last_name;
    v_sprtele_phone_number_a:= stunt_rec.stele_phone_number;
    v_sprtele_phone_area_b  := stunt_rec.stele_phone_area;
    v_stat      		:= stunt_rec.rolled_ind;
  	 dbms_output.put(rpad(nvl(v_striden_id,' '),9,v_blank_space));
  	 dbms_output.put(rpad(v_field_delimiter,2,v_field_delimiter ));
   	 dbms_output.put(rpad(substr(v_striden_last_name,1,30),30,v_blank_space ));
   	 dbms_output.put(rpad(v_field_delimiter,1,v_field_delimiter ));
dbms_output.put(rpad(nvl(v_stele_phone_area_b||v_stele_phone_number_a,' ')  ,12,v_blank_space ));
    dbms_output.put(rpad(v_field_delimiter,3,v_field_delimiter ));
    dbms_output.put(rpad(nvl(v_stat ,' '),1,v_field_delimiter ));
   
   END LOOP;
   END;
   /
spool off







Any advice...I don't know why I can't seem to figure out this problem.
Re: Shell script calling sql*plus file [message #434584 is a reply to message #434567] Wed, 09 December 2009 17:01 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Thanks for the advice andrew_again.


Re: Shell script calling sql*plus file [message #434587 is a reply to message #434584] Wed, 09 December 2009 17:51 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
You need to Alias any columns in the query after you manipulate them. Also - implicit cursor is much easier, faster etc that explicit cursors and you can eliminate local variables (unless you need them for something else).

-- simplified example...
SQL> set serveroutput on
SQL>
SQL> -- "set serveroutput on" enables DBMS_OUTPUT buffer. and looks for output in the buffer and print out
SQL> --begin   DBMS_OUTPUT.ENABLE (100000); end;
SQL>
SQL> declare
  2    v_blank_space varchar2(1) := ' ';
  3    v_field_delimiter varchar2(1):='^';
  4  BEGIN
  5    FOR i IN (SELECT empno, ename || ' - '|| job name_job from emp)
  6    LOOP
  7     DBMS_OUTPUT.put (RPAD (NVL (to_char(i.empno), ' '), 9, v_blank_space));
  8     DBMS_OUTPUT.put (RPAD (v_field_delimiter, 2, v_field_delimiter));
  9     DBMS_OUTPUT.put_line (RPAD (SUBSTR (i.name_job, 1, 30),30,v_blank_space));
 10    END LOOP;
 11  END;
 12  /
7839     ^^KING - PRESIDENT
7698     ^^BLAKE - MANAGER
7782     ^^CLARK - MANAGER
7566     ^^JONES - MANAGER
7654     ^^MARTIN - SALESMAN
7499     ^^ALLEN - SALESMAN
7844     ^^TURNER - SALESMAN
7900     ^^JAMES - CLERK
7521     ^^WARD - SALESMAN
7902     ^^FORD - ANALYST
7369     ^^SMITH - CLERK
7788     ^^SCOTT - ANALYST
7876     ^^ADAMS - CLERK
7934     ^^MILLER - CLERK

PL/SQL procedure successfully completed.

SQL>
Re: Shell script calling sql*plus file [message #434739 is a reply to message #434587] Thu, 10 December 2009 08:17 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
What about the variables..shouldn't that eliminiate the problem? so your saying to take the query itself and make it all implicit which would eliminate the variables all together?

There is no way to work around the current issue, other than what you suggested?



Re: Shell script calling sql*plus file [message #434750 is a reply to message #434587] Thu, 10 December 2009 08:59 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Also a variable is going to be passed into the query. T_id IN varchar2(8);



Re: Shell script calling sql*plus file [message #434753 is a reply to message #434583] Thu, 10 December 2009 09:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>v_stele_phone_number_a:= stunt_rec.stele_phone_number;
It is all about scope of variable.
In line above STELE_PHONE_NUMBER only exists while/when CURSOR associated with stunt_rec is open.
Re: Shell script calling sql*plus file [message #434771 is a reply to message #434753] Thu, 10 December 2009 11:32 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I mis-spoke - you arent using explicit cursors (even though you declare the cursor) because you don't explicitly open, fetch and close it. So using your cursor definition, it takes a form like this...
-- Beware that dbms_buffer defaults for 20,000 bytes (or chars?)
-- so it will blow out if too small for your resultset size.
-- 10.2 goes up to "unlimited", else 1,000,000 is max for earlier versions.
SQL> st serveroutput on size unlimited
SQL> declare
  2    v_blank_space varchar2(1) := ' ';
  3    v_field_delimiter varchar2(1):='^';
  4    cursor c1 is SELECT empno, ename || ' - '|| job name_job from emp;
  5  BEGIN
  6    FOR i IN c1
  7    LOOP
  8     DBMS_OUTPUT.put (RPAD (NVL (to_char(i.empno), ' '), 9, v_blank_space));
  9     DBMS_OUTPUT.put (RPAD (v_field_delimiter, 2, v_field_delimiter));
 10     DBMS_OUTPUT.put_line (RPAD (SUBSTR (i.name_job, 1, 30),30,v_blank_space));
 11    END LOOP;
 12  END;
 13  /
7839     ^^KING - PRESIDENT
7698     ^^BLAKE - MANAGER
7782     ^^CLARK - MANAGER
7566     ^^JONES - MANAGER
7654     ^^MARTIN - SALESMAN
7499     ^^ALLEN - SALESMAN
7844     ^^TURNER - SALESMAN
7900     ^^JAMES - CLERK
7521     ^^WARD - SALESMAN
7902     ^^FORD - ANALYST
7369     ^^SMITH - CLERK
7788     ^^SCOTT - ANALYST
7876     ^^ADAMS - CLERK
7934     ^^MILLER - CLERK

PL/SQL procedure successfully completed.

SQL>


To see if you need to alias a column, just see what heading sqlplus gives you. If it's not a simple name, then you need to alias it. e.g.
-- ENAME||'-'||JOB isn't a name you can refer to in your code
SQL> SELECT empno, ename || ' - '|| job from emp where rownum=1;

     EMPNO ENAME||'-'||JOB
---------- ----------------------
      7839 KING - PRESIDENT

-- so Alias it as "name_job". Now sqlplus shows that as column heading - a name you can refer to in your loop.
SQL> SELECT empno, ename || ' - '|| job name_job from emp where rownum=1;

     EMPNO NAME_JOB
---------- ----------------------
      7839 KING - PRESIDENT

SQL>


Unless you really want your local variables, you can just do the whole thing in SQL...
SQL> set pagesize 0
SQL> select RPAD (NVL (to_char(empno), ' '), 9, ' ')||
  2         RPAD ('^', 2, '^')||
  3         RPAD (SUBSTR (name_job, 1, 30),30,' ') The_Line
  4  from(SELECT empno, ename || ' - '|| job name_job from emp);
7839     ^^KING - PRESIDENT
7698     ^^BLAKE - MANAGER
7782     ^^CLARK - MANAGER
7566     ^^JONES - MANAGER
7654     ^^MARTIN - SALESMAN
7499     ^^ALLEN - SALESMAN
7844     ^^TURNER - SALESMAN
7900     ^^JAMES - CLERK
7521     ^^WARD - SALESMAN
7902     ^^FORD - ANALYST
7369     ^^SMITH - CLERK
7788     ^^SCOTT - ANALYST
7876     ^^ADAMS - CLERK
7934     ^^MILLER - CLERK

14 rows selected.

SQL>

Re: Shell script calling sql*plus file [message #435356 is a reply to message #434771] Tue, 15 December 2009 14:28 Go to previous message
californiagirl
Messages: 79
Registered: May 2007
Member
I found the column where I left the alias out of..thank you very much for your advice and support.


Previous Topic: 10g on AIX vs Linux
Next Topic: SP2-0310: unable to open file
Goto Forum:
  


Current Time: Tue Dec 03 07:58:44 CST 2024