Home » SQL & PL/SQL » SQL & PL/SQL » Date conversion using a table (Oracle 10g)
Date conversion using a table [message #354685] Mon, 20 October 2008 18:33 Go to next message
Leonard Martin
Messages: 45
Registered: May 2002
Location: Canada
Member
Hello Oracle gurus
Can you please let me know why this script below does not work.
This is an example of what i am trying to do in a procedure.
Any help is appreciated.

declare
TYPE DST_dates IS RECORD
(
spring NUMBER,
fall NUMBER
);

TYPE dst_tabletype IS TABLE OF DST_dates INDEX BY BINARY_INTEGER;
g_DST dst_tabletype;

cursor c1 is
select g_dst.(TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))).FALL temp from gimusage
where rownum <2;


begin
g_dst(2008).fall:= TO_NUMBER(to_char(SYSDATE,'YYYY'));

dbms_output.put_line('asdfsa:'|| g_dst(2007).fall);
for g_dstrec in g_dst loop
dbms_output.put_line('asdfsa:'|| g_dstrec.temp);
end loop;
end;
Re: Date conversion using a table [message #354692 is a reply to message #354685] Mon, 20 October 2008 20:24 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If you want our help, you will need to do a few things:

Quote:
1) format your code. Nobody wants to look at unformatted code. That is why there is a code formatter on OraFaq. Here is the link:

SQL and PLSQL formatter

2) post a working test case. If you expect anyone to run this plsql we need some populated tables. Give us the creates/inserts.

3) don't be ambiguous with your question. "it does not work". Hmm... so you say... wonder what "it does not work" means. If I had a clue I might try to figure it out. So, please provide us with a more indepth description of the problem, because your description is less than useless.

Good luck, waiting for the goods, Kevin
Re: Date conversion using a table [message #354702 is a reply to message #354692] Mon, 20 October 2008 22:33 Go to previous messageGo to next message
Leonard Martin
Messages: 45
Registered: May 2002
Location: Canada
Member
Ok, Here is the formatted code. There is no any physical tables. And yes, this script can be run from sqlplus or plsql devleloper.


DECLARE
TYPE dst_Dates IS RECORD(Spring NUMBER,
Fall NUMBER);
TYPE dst_Tabletype IS TABLE OF DST_DATES INDEX BY BINARY_INTEGER ;
g_dst DST_TABLETYPE;
CURSOR c1 IS

BEGIN
g_dst(2008).Fall := To_Number(To_Char(SYSDATE,'YYYY'));

dbms_Output.Put_Line('asdfsa:'
||g_dst(2007).Fall);

FOR g_dstrec IN g_dst LOOP
dbms_Output.Put_Line('asdfsa:'
||g_dstrec.TEMP);
END LOOP;
END;
Re: Date conversion using a table [message #354715 is a reply to message #354702] Mon, 20 October 2008 23:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not formatted, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session and explain what you mean by "does not work".

What I see is (note how I format it):
SQL> DECLARE
  2    TYPE dst_Dates IS RECORD(Spring NUMBER,
  3                              Fall NUMBER);
  4     TYPE dst_Tabletype IS TABLE OF DST_DATES INDEX BY BINARY_INTEGER ;
  5     g_dst  DST_TABLETYPE;
  6     CURSOR c1 IS 
  7       
  8  BEGIN
  9    g_dst(2008).Fall := To_Number(To_Char(SYSDATE,'YYYY'));
 10    
 11    dbms_Output.Put_Line('asdfsa:'
 12                         ||g_dst(2007).Fall);
 13    
 14    FOR g_dstrec IN g_dst LOOP
 15      dbms_Output.Put_Line('asdfsa:'
 16                           ||g_dstrec.TEMP);
 17    END LOOP;
 18  END;
 19  /
BEGIN
*
ERROR at line 8:
ORA-06550: line 8, column 1:
PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
( select <a SQL statement>
ORA-06550: line 18, column 4:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map

Nothing strange for you at end of line 6? Where is the cursor definition?

Regards
Michel

Re: Date conversion using a table [message #354716 is a reply to message #354715] Mon, 20 October 2008 23:42 Go to previous messageGo to next message
Leonard Martin
Messages: 45
Registered: May 2002
Location: Canada
Member
Hi Mike
Not sure how that got missed.
HEre is the code

declare
TYPE DST_dates IS RECORD
(
spring NUMBER,
fall NUMBER
);

TYPE dst_tabletype IS TABLE OF DST_dates INDEX BY BINARY_INTEGER;
g_DST dst_tabletype;

cursor c1 is
select g_dst.(TO_CHAR(SYSDATE,'YYYY')).FALL temp from gimusage
where rownum <2;


begin
g_dst(2008).fall:= TO_NUMBER(to_char(SYSDATE,'YYYY'));

dbms_output.put_line('asdfsa:'|| g_dst(2007).fall);
for g_dstrec in g_dst loop
dbms_output.put_line('asdfsa:'|| g_dstrec.temp);
end loop;
end;
Re: Date conversion using a table [message #354718 is a reply to message #354716] Mon, 20 October 2008 23:55 Go to previous messageGo to next message
Leonard Martin
Messages: 45
Registered: May 2002
Location: Canada
Member
Hi Mike
Pls Disregard the previous one

Here is the right code

declare
TYPE DST_dates IS RECORD(
spring NUMBER,
fall NUMBER);

TYPE dst_tabletype IS TABLE OF DST_dates INDEX BY BINARY_INTEGER;
g_DST dst_tabletype;

cursor g_dst is
select g_dst.(TO_CHAR(SYSDATE, 'YYYY')) .FALL temp from dual;

begin
g_dst(2008).fall := TO_NUMBER(to_char(SYSDATE, 'YYYY'));

dbms_output.put_line('asdfsa:' || g_dst(2007).fall);
for g_dstrec in g_dst loop
dbms_output.put_line('asdfsa:' || g_dstrec.temp);
end loop;
end;
Re: Date conversion using a table [message #354722 is a reply to message #354718] Tue, 21 October 2008 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again read the forum guide and use code tags... and SQL*Plus:
SQL> declare
  2     TYPE DST_dates IS RECORD(
  3        spring NUMBER,
  4        fall   NUMBER);
  5  
  6     TYPE dst_tabletype IS TABLE OF DST_dates INDEX BY BINARY_INTEGER;
  7     g_DST dst_tabletype;
  8  
  9     cursor g_dst is
 10        select g_dst.(TO_CHAR(SYSDATE, 'YYYY')) .FALL temp from dual;
 11  
 12  begin
 13     g_dst(2008).fall := TO_NUMBER(to_char(SYSDATE, 'YYYY'));
 14  
 15     dbms_output.put_line('asdfsa:' || g_dst(2007).fall);
 16     for g_dstrec in g_dst loop
 17        dbms_output.put_line('asdfsa:' || g_dstrec.temp);
 18     end loop;
 19  end;
 20  /
      select g_dst.(TO_CHAR(SYSDATE, 'YYYY')) .FALL temp from dual;
                   *
ERROR at line 10:
ORA-06550: line 10, column 20:
PL/SQL: ORA-01747: invalid user.table.column, table.column, or column specification
ORA-06550: line 10, column 7:
PL/SQL: SQL Statement ignored

You are not allowed to use variable in name and you are not allowed to use PL/SQL type inside SQL.

Regards
Michel

[Updated on: Tue, 21 October 2008 00:31]

Report message to a moderator

Re: Date conversion using a table [message #354723 is a reply to message #354685] Tue, 21 October 2008 00:31 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You can go here to format your code:

sql and plsql formatter on orafaq

you can use the elipses on the formatting tool bar above where you type your post, looks like this: {..}. You must highlight your code and then click the elipses button. This will cause html tags to appear around what you highlight. Then click preview below the text box (and too the right) to see what your post will look like before you post it.

Once formatted and wrapped in the code tags, you get this (actually you don't get this, there is an error in your code that the formatter can't handle and it drops the sql statement so I added it back):

/*Notice: Formatted SQL is not the same as input*/

DECLARE
  TYPE dst_Dates IS RECORD(Spring NUMBER,
                            Fall NUMBER);
   TYPE dst_Tabletype IS TABLE OF DST_DATES INDEX BY BINARY_INTEGER ;
   g_dst  DST_TABLETYPE;
   CURSOR g_dst IS 
     select g_dst.(TO_CHAR(SYSDATE, 'YYYY')) .FALL temp from dual;

BEGIN
  g_dst(2008).Fall := To_Number(To_Char(SYSDATE,'YYYY'));
  
  dbms_Output.Put_Line('asdfsa:'
                       ||g_dst(2007).Fall);
  
  FOR g_dstrec IN g_dst LOOP
    dbms_Output.Put_Line('asdfsa:'
                         ||g_dstrec.TEMP);
  END LOOP;
END;
/


which when run produces this error:

select g_dst.(TO_CHAR(SYSDATE, 'YYYY')) .FALL temp from dual;
             *
ERROR at line 10:
ORA-06550: line 10, column 14:
PL/SQL: ORA-01747: invalid user.table.column, table.column, or column
specification
ORA-06550: line 10, column 1:
PL/SQL: SQL Statement ignored


This error is caused by your cursor name and associative array name. They both are the same: G_DST. This causes a scoping/identifier issue for plsql. Once we rename the cursor and its associated record we get this code and error:

SQL> DECLARE
  2    TYPE dst_Dates IS RECORD(Spring NUMBER,
  3                              Fall NUMBER);
  4     TYPE dst_Tabletype IS TABLE OF DST_DATES INDEX BY BINARY_INTEGER ;
  5     g_dst  DST_TABLETYPE;
  6     CURSOR c_dst IS 
  7       select g_dst(TO_CHAR(SYSDATE, 'YYYY')) .FALL temp from dual;
  8  
  9  BEGIN
 10    g_dst(2008).Fall := To_Number(To_Char(SYSDATE,'YYYY'));
 11    
 12    dbms_Output.Put_Line('asdfsa:'
 13                         ||g_dst(2007).Fall);
 14    
 15    FOR c_dstrec IN c_dst LOOP
 16      dbms_Output.Put_Line('asdfsa:'
 17                           ||c_dstrec.TEMP);
 18    END LOOP;
 19  END;
 20  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 12


This error comes from the array lookup on line 12. There is no element 2007. Thus when oracle looks for the element and does not find it, it raises a no_data_found exception because it found no data. I presume this is a typo and you meant to use the index 2008, so replacing 2007 with 2008 we get this:

SQL> set serveroutput on
SQL> DECLARE
  2    TYPE dst_Dates IS RECORD(Spring NUMBER,
  3                              Fall NUMBER);
  4     TYPE dst_Tabletype IS TABLE OF DST_DATES INDEX BY BINARY_INTEGER ;
  5     g_dst  DST_TABLETYPE;
  6     CURSOR c_dst IS 
  7       select g_dst(TO_CHAR(SYSDATE, 'YYYY')) .FALL temp from dual;
  8  
  9  BEGIN
 10    g_dst(2008).Fall := To_Number(To_Char(SYSDATE,'YYYY'));
 11    
 12    dbms_Output.Put_Line('asdfsa:'
 13                         ||g_dst(2008).Fall);
 14    
 15    FOR c_dstrec IN c_dst LOOP
 16      dbms_Output.Put_Line('asdfsa:'
 17                           ||c_dstrec.TEMP);
 18    END LOOP;
 19  END;
 20  /
asdfsa:2008
asdfsa:2008

PL/SQL procedure successfully completed.


Was this where you were headed? One way to find errors in plsql like this is to fall back on the old tried and true debugging method of commenting out large sections of code to see where errors are being generated.

Simple mistakes, but we have all made them.

Good luck, Kevin
Previous Topic: nested materialized view
Next Topic: Passing key value pairs for :new from trigger to procedure
Goto Forum:
  


Current Time: Thu Dec 08 02:28:22 CST 2016

Total time taken to generate the page: 0.29063 seconds