prog:plsql_string_tokenizer
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
prog:plsql_string_tokenizer [2016/11/08 09:24] – [APEX_UTIL.STRING_TO_TABLE] gpipperr | prog:plsql_string_tokenizer [2016/11/08 09:24] (aktuell) – [APEX_UTIL.STRING_TO_TABLE] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | =====Oracle PL/SQL Tokenizer - Strings zerlegen ===== | ||
+ | **Aufgabe: | ||
+ | |||
+ | |||
+ | Um solch einen String zu erzeugen => siehe auch [[prog: | ||
+ | |||
+ | ---- | ||
+ | ====APEX_UTIL.STRING_TO_TABLE==== | ||
+ | |||
+ | Alternativ in Apex die Methode: | ||
+ | |||
+ | <code plsql> | ||
+ | DECLARE | ||
+ | | ||
+ | BEGIN | ||
+ | -- | ||
+ | -- Convert the colon separated string of values into | ||
+ | -- a PL/SQL array | ||
+ | v_selected := APEX_UTIL.STRING_TO_TABLE(: | ||
+ | -- | ||
+ | -- Loop over array to insert department numbers and sysdate | ||
+ | -- | ||
+ | FOR i IN 1..v_selected.count | ||
+ | LOOP | ||
+ | dbms_output.put_line(v_selected(i)); | ||
+ | END LOOP; | ||
+ | END; | ||
+ | </ | ||
+ | ---- | ||
+ | |||
+ | ==== in PL/SQL eine Liste wieder zerlegen mit DBMS_UTILITY ==== | ||
+ | |||
+ | Mit Hilfe von **dbms_utility.comma_to_table** lässt sich eine Liste in ein Array transformieren. | ||
+ | |||
+ | Beispiel: | ||
+ | <code SQL> | ||
+ | |||
+ | declare | ||
+ | | ||
+ | | ||
+ | -- not working | ||
+ | | ||
+ | -- working | ||
+ | -- v_list | ||
+ | BEGIN | ||
+ | |||
+ | | ||
+ | |||
+ | | ||
+ | , tablen | ||
+ | , tab => v_values | ||
+ | ); | ||
+ | |||
+ | if v_tabLength=4 then | ||
+ | dbms_output.put_line(' | ||
+ | dbms_output.put_line(' | ||
+ | dbms_output.put_line(' | ||
+ | dbms_output.put_line(' | ||
+ | else | ||
+ | raise_application_error( -20020, 'Konto Key has not enough values ::' || v_list); | ||
+ | end if; | ||
+ | |||
+ | END; | ||
+ | </ | ||
+ | |||
+ | siehe auch: http:// | ||
+ | |||
+ | == BUG - Falls String nur aus Zahlen wie " | ||
+ | |||
+ | In meiner 12c Umgebung erhalten ich allerdings den folgenden Fehler wenn der String aus Zahlen besteht: | ||
+ | |||
+ | < | ||
+ | ERROR at line 1: | ||
+ | ORA-20001: comma-separated list invalid near # | ||
+ | ORA-06512: at " | ||
+ | ORA-06512: at " | ||
+ | ORA-06512: at line 16 | ||
+ | </ | ||
+ | |||
+ | |||
+ | Daher meine eigene Routine erstellt, da mir gerade die Apex Methode nicht mehr einfällt ... | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== PL/SQL Routine zum Zerlegen eines Strings ==== | ||
+ | |||
+ | Seperator kann eine beliebige Zeichenkette sein. | ||
+ | |||
+ | |||
+ | <code plsql> | ||
+ | create or replace PACKAGE GPI_APEX_UTILS AS | ||
+ | |||
+ | -- ===================== | ||
+ | type splitResultTab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; | ||
+ | | ||
+ | -- ===================== | ||
+ | -- split a string | ||
+ | -- ===================== | ||
+ | | ||
+ | function splitString( p_string | ||
+ | , | ||
+ | return splitResultTab; | ||
+ | | ||
+ | -- ===================== | ||
+ | -- test the function | ||
+ | --- ===================== | ||
+ | procedure testsplitString( p_string | ||
+ | , | ||
+ | | ||
+ | |||
+ | END GPI_APEX_UTILS; | ||
+ | / | ||
+ | |||
+ | create or replace PACKAGE body GPI_APEX_UTILS | ||
+ | -- ===================== | ||
+ | -- split a string | ||
+ | -- ===================== | ||
+ | function splitString ( p_string | ||
+ | , | ||
+ | return splitResultTab | ||
+ | is | ||
+ | v_returnTab splitResultTab; | ||
+ | v_token_count pls_integer; | ||
+ | v_tocken | ||
+ | v_string | ||
+ | v_pos1 | ||
+ | v_sep_length pls_integer; | ||
+ | begin | ||
+ | |||
+ | -- get the count of the tocken (count sperator + one ) | ||
+ | v_token_count: | ||
+ | | ||
+ | v_sep_length: | ||
+ | | ||
+ | if v_token_count=1 then | ||
+ | raise_application_error( -20020, ' seperator Char not exist in string to split - p_seperator: | ||
+ | end if; | ||
+ | | ||
+ | -- add at the end on seperator sign as end signal for the loop! | ||
+ | v_string: | ||
+ | | ||
+ | -- loop of the result string | ||
+ | -- in each round cut off the tocken at the begin of the string | ||
+ | for i in 1 .. v_token_count | ||
+ | loop | ||
+ | | ||
+ | -- find the first delimiter in the string | ||
+ | | ||
+ | |||
+ | -- get the tocken | ||
+ | | ||
+ | | ||
+ | |||
+ | -- cut the read tocken from the string | ||
+ | | ||
+ | end loop; | ||
+ | | ||
+ | return v_returnTab; | ||
+ | | ||
+ | end splitString; | ||
+ | | ||
+ | -- ===================== | ||
+ | -- test the function | ||
+ | --- ===================== | ||
+ | |||
+ | procedure testsplitString( p_string | ||
+ | , | ||
+ | is | ||
+ | v_resTab splitResultTab; | ||
+ | begin | ||
+ | v_resTab: | ||
+ | | ||
+ | FOR i IN v_resTab.FIRST .. v_resTab.LAST | ||
+ | LOOP | ||
+ | -- ausgeben | ||
+ | IF v_resTab.EXISTS(i) | ||
+ | THEN | ||
+ | DBMS_OUTPUT.put_line(' | ||
+ | END IF; | ||
+ | END LOOP; | ||
+ | end; | ||
+ | |||
+ | END GPI_APEX_UTILS; | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | Testen: | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | sqlplus gpi/gpi | ||
+ | |||
+ | exec GPI_APEX_UTILS.testsplitString(' | ||
+ | |||
+ | element #1 = 0100 | ||
+ | element #2 = 26941 | ||
+ | element #3 = 0 | ||
+ | element #4 = 0 | ||
+ | |||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== RegEx Lösungen ==== | ||
+ | |||
+ | * http:// | ||
+ | * https:// | ||
+ | |||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | ==== Quellen ==== | ||
+ | |||
+ | Mehr => [[prog: |
prog/plsql_string_tokenizer.txt · Zuletzt geändert: 2016/11/08 09:24 von gpipperr